examsaccess.cs

考试管理系统考试管理系统考试管理系统考试管理系统考试管理系统考试管理系统考试管理系统考试管理系统

using System;
using System.Data;
using System.Data.SqlClient;
using ExamSystem.Common.Objects;
using ExamSystem.Common.DB;
using ExamSystem.Common;

namespace ExamSystem.DataAccess.ExamAccess
{

	/// <summary>
	/// ExamAccess 的摘要说明。
	/// </summary>
	public class ExamsAccess
	{
		DataBaseOperate oper;
		/// <summary>
		/// 实例化考试数据访问对象
		/// </summary>
		/// <param name="dataBaseOperate">数据连接对象</param>
		public ExamsAccess(DataBaseOperate dataBaseOperate)
		{
			oper=dataBaseOperate;
		}
		
		public DataSet getExam(Student stu)
		{
			string sql="select ExamID,ExamName,ExamClass,ExamTitles,ExamTime,ExamCate,isEnd from ExamList,StuLogin where ExamList.ExamClass=StuLogin.StuClass and (ExamList.ExamID not in(select ExamID from ExamResult where ExamResult.ExamID=ExamList.ExamID and ExamResult.StuID=StuLogin.StuID and ExamResult.isDel=0)) and ExamList.isDel=0 and ExamList.isEnd=0";
			ParamList pList=new ParamList();
			if(stu.StuID>-1)
			{
				sql+=" and StuLogin.StuID=@stuID";
				pList.AddNew("@stuID",SqlDbType.Int,stu.StuID);
			}

			if(stu.StuNum>=0)
			{ 
				sql+=" and StuLogin.StuNumber=@StuNumber";
				pList.AddNew("@StuNumber",SqlDbType.Int,stu.StuNum);
			}

			if(stu.StuName!="")
			{
				sql+=" and StuLogin.StuName=@StuName";
				pList.AddNew("@StuName",SqlDbType.VarChar,stu.StuName);
			}

			if(stu.StuClass>=0)
			{
				sql+=" and StuLogin.StuClass=@StuClass";
				pList.AddNew("@StuClass",SqlDbType.Int,stu.StuClass);
			}

			if(stu.StuBirday!=DateTime.MinValue)
			{
				sql+=" and StuLogin.StuBirday=@StuBirday";
				pList.AddNew("@StuBirday",SqlDbType.DateTime,stu.StuBirday);
			}

			
			if(stu.StuSex!=SearchBooleans.NULL)
			{
				sql+=" and StuLogin.StuSex=@StuSex";
				pList.AddNew("@StuSex",SqlDbType.Bit,(int)stu.StuSex);
			}
			sql+=" and StuLogin.isDel=0";
			DataSet ds=oper.Query(sql,"Exam",pList);
			return ds;
		}

		public DataSet getExam(Exam exam)
		{
			string sql="select ExamID,ExamName,ClassName,CreateDate,ExamTitles,CateName,isEnd,ExamCate,ExamClass,ExamTime from ExamList,Cate,Class where ClassID=ExamClass and ExamCate=CateID and ExamList.isDel=0";
			ParamList list=new ParamList();
			if(exam.ExamID>=0)
			{
				sql+=" and ExamID=@ExamID";
				list.AddNew("@ExamID",SqlDbType.Int,exam.ExamID);
			}
			if(exam.ExamName!="")
			{
				sql+=" and ExamName=@ExamName";
				list.AddNew("@ExamName",SqlDbType.VarChar,exam.ExamName);
			}
			if(exam.ExamClass>=0)
			{
				sql+=" and ExamClass=@ExamClass";
				list.AddNew("@ExamClass",SqlDbType.Int,exam.ExamClass);
			}

			if(exam.ExamCate>=0)
			{
				sql+=" and ExamCate=@ExamCate";
				list.AddNew("@ExamCate",SqlDbType.Int,exam.ExamCate);
			}

			if(exam.IsEnd!=SearchBooleans.NULL)
			{
				sql+=" and isEnd=@isEnd";
				list.AddNew("@isEnd",SqlDbType.Bit,(int)exam.IsEnd);
			} 
			DataSet ds=oper.Query(sql,"Exam",list);
			return ds;
		}

		public void insertExam(Exam exam)
		{
			string sql="insert into ExamList(ExamName,ExamClass,ExamTitles,ExamCate,ExamTime) values(@ExamName,@ExamClass,@ExamTitles,@ExamCate,@ExamTime)";
			ParamList list=new ParamList();
			list.AddNew("@ExamName",SqlDbType.VarChar,exam.ExamName);
			list.AddNew("@ExamClass",SqlDbType.Int,exam.ExamClass);
			list.AddNew("@ExamTitles",SqlDbType.VarChar,exam.ExamTitles);
			list.AddNew("@ExamCate",SqlDbType.Int,exam.ExamCate);
			list.AddNew("@ExamTime",SqlDbType.Int,exam.ExamTime);
			oper.Execute(sql,list);
		}

		public void updateExam(Exam exam)
		{
			string sql="update ExamList set isDel=0";
			ParamList list=new ParamList();
			if(exam.ExamName!="")
			{
				sql+=",ExamName=@ExamName";
				list.AddNew("@ExamName",SqlDbType.VarChar,exam.ExamName);
			}

			if(exam.ExamClass>=0)
			{
				sql+=",ExamClass=@ExamClass";
				list.AddNew("@ExamClass",SqlDbType.Int,exam.ExamClass);
			}

			if(exam.ExamCate>=0)
			{
				sql+=",ExamCate=@ExamCate";
				list.AddNew("@ExamCate",SqlDbType.Int,exam.ExamCate);
			}

			if(exam.ExamTime>=0)
			{
				sql+=",ExamTime=@ExamTime";
				list.AddNew("@ExamTime",SqlDbType.Int,exam.ExamTime);
			}
			

			if(exam.ExamTitles!="")
			{
				sql+=",ExamTitles=@ExamTitles";
				list.AddNew("@ExamTitles",SqlDbType.VarChar,exam.ExamTitles);
			}

			if(exam.IsEnd>=0)
			{
				sql+=",isEnd=@isEnd";
				list.AddNew("@isEnd",SqlDbType.Int,exam.IsEnd);
			}

			sql+=" where ExamID=@ExamID";
			list.AddNew("@ExamID",SqlDbType.Int,exam.ExamID);
			oper.Execute(sql,list);
		}


		public void deleteExam(Exam exam)
		{
			string sql="update ExamList set isDel=1 where isDel=0";
			ParamList list=new ParamList();
			if(exam.ExamID>=0)
			{
				sql+=" and ExamID=@ExamID";
				list.AddNew("@ExamID",SqlDbType.Int,exam.ExamID);
			}
			if(exam.ExamClass>=0)
			{
				sql+=" and ExamClass=@ExamClass";
				list.AddNew("@ExamClass",SqlDbType.Int,exam.ExamClass);
			}
			
			if(exam.ExamCate>=0)
			{
				sql+=" and ExamCate=@ExamCate";
				list.AddNew("@ExamCate",SqlDbType.Int,exam.ExamCate);
			}
			
			if(exam.IsEnd!=SearchBooleans.NULL)
			{
				sql+=" and isEnd=@isEnd";
				list.AddNew("@isEnd",SqlDbType.Int,(int)exam.IsEnd);
			} 
			oper.Execute(sql,list);
		}

	}
}