exam.cs

本论文叙述了联机考试系统的现状以及C#语言的概况。重点介绍了联机考试系统的实现过程：包括系统分析、 系统调查、 数据流程分析、功能设计、 数据库设计、 系统物理配置方案、 系统实现、 系统测试和调试。

using System;
using System.Data;
using System.Data.OleDb;
namespace OES
{
	/// <summary>
	/// Summary description for Exam.
	/// </summary>
	public class Exam
	{
		public static DataTable dtTestPaper;//to create a temporary table
		public Exam()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		//return the fetched data in the form of a table back to 
		//the calling form
		/// <summary>
		/// /修改情况：部门：ACCP产品开发培训部   修改人：王文
		/// 1.将代码中的""-〉改为string.Empty;    修改时间：2004-12-25 16：00
		/// 2.ScheduledOn的判断条件统一更改为〉=; 修改时间：2004-12-25 16：10
		/// 
		/// </summary>
		/// <param name="searchValue"></param>
		/// <returns></returns>

		public DataTable FetchExamDetails(string[] searchValue)
		{
			string str="";
			
			if(searchValue[3]=="Score")
			{
				//str="select UsrId as UserId, tbl_Exams.ExamCode ,tbl_Exams.ExamTitle, totalQuest as TotalQuestions , CorrectlyAns as CorrectlyAnswered , (100/totalQuest*correctlyAns) as Score from tbl_ExamResults,tbl_Exams where tbl_Exams.ExamCode=tbl_ExamResults.ExamCode and tbl_Exams.SubjectCode like '"+searchValue[0]+"'";

				/*str="select tbl_Exams.ExamCode ,tbl_Exams.ExamTitle,"+
					"totalQuest as TotalQuestions,Score "+
					"from tbl_ExamResults,tbl_Exams where "+
					"tbl_Exams.ExamCode=tbl_ExamResults.ExamCode and "+

									"tbl_Exams.SubjectCode = '"+ searchValue[0]+"'";*/
				string s = string.Empty;
				if (searchValue[1]!=string.Empty)
				{
					s += " and CDate(tbl_Exams.ScheduledOn)>=#"+searchValue[1]+"#";
					//str += " and tbl_Exams.ScheduledOn>='"+searchValue[1]+"'";
				}
				str="SELECT max(tbl_ExamResults.ExamCode), max(tbl_Exams.ExamTitle),"+
					" Count(*) AS NoOfAttend, Sum(IIf(score>=60,1,0)) AS NoOfPassed," +
					" Sum(IIf(score<60,1,0)) AS NoOfFailed FROM tbl_ExamResults,"+
					" tbl_Exams where tbl_ExamResults.ExamCode = tbl_Exams.ExamCode "+
					" and tbl_Exams.SubjectCode = '"+ searchValue[0]+"'"+ s +
					" GROUP BY tbl_ExamResults.ExamCode";

				

				

			}
			else if(searchValue[3] !="Score")
			{
				str="select * from tbl_exams";
			
				if(searchValue[3]=="examsresult")  //在frmViewExamResult.btnSearch_Click中用到
				{
					str="select UsrId , tbl_Exams.ExamCode ,"+
						"tbl_Exams.ExamTitle, totalQuest as TotalQuestions ,"+
						"CorrectlyAns as CorrectlyAnswered , Score ,"+
						"iif(Score>=60,'ok','fail') as result "+
						"from tbl_ExamResults,tbl_Exams "+
						"where tbl_ExamResults.ExamCode = tbl_Exams.ExamCode";

					//if(searchValue[2]!="")改为
					if(searchValue[2]!=string.Empty)
					{
						//str=str+" and tbl_Exams.ScheduledOn = '"+searchValue[2]+"'";改为
						str=str+" and CDate(tbl_Exams.ScheduledOn)>=#"+searchValue[2]+"#";
					}
					//if(searchValue[1]!="")改为
					if(searchValue[1]!=string.Empty)
					{
						str=str +" and tbl_ExamResults.usrId like '" +searchValue[1]+"'";
					}
					//if(searchValue[0]!="")改为
					if(searchValue[0]!=string.Empty)
					{
						str=str+" and tbl_Exams.SubjectCode like '" + searchValue[0]+"'";
					}
				}
				else if(searchValue[3]==string.Empty)  //被frmExamSearch.btnSearch_Click用到
													   //被frmExamSchedule_Load用到
				{
					if(searchValue[0] !=string.Empty) //sched date is given
					{
						str = str+" WHERE CDate(scheduledon) >=#"+searchValue[0]+"# ";		
			
						if(searchValue[1] != string.Empty) //subject code is given
						{
							str += " and  SubjectCode like '"+searchValue[1]+"'" ;
						}
						if(searchValue[2] != string.Empty) //exam code is given
						{
							str += "and  examcode = '"+ searchValue[2]+"'";
						}
						//所添加的CODES

						if(searchValue.Length>4&&searchValue[4] != string.Empty) //exam code is given
						{
							str += " and ExamCode not in (select ExamCode from tbl_ExamResults where UsrId='"+searchValue[4]+"')";
						}
						//以上所添加的CODES
	
					}
					else if(searchValue[0] == string.Empty) //if sched date is not given then check for the other two fields
					{
						if(searchValue[1] != string.Empty) //if subject code is given
						{
							str="select * from tbl_exams where SubjectCode = '"+searchValue[1]+ "'";
						
							if(searchValue[2] != string.Empty) //if examcode is also given
								str += " and examcode = '"+ searchValue[2]+"'";
						}
						else
						{
							if(searchValue[2] != string.Empty)//if only examcode is given 
								str = " Select * from tbl_exams where examcode = '"+searchValue[2]+"'";
							else
								str = " Select * from tbl_exams";
						}						
						//除去	Console.WriteLine (str);				
					}			

					//code to return questions of a subject for the specified zero or more keywords	 for question field of QuestionBank table
				else
				{
			
					if(searchValue[3]=="keywordsearch")
					{
						str="select ExamCode, ExamTitle, ScheduledOn, ScheduledTime, Duration from tbl_Exams where SubjectCode like '"+searchValue[0]+"' and ScheduledOn like '"+searchValue[1]+"' ";
			
					}
				}					
			}
			}
			DataAccess.oleAdapter= new OleDbDataAdapter(str, DataAccess.oleconn);
			DataAccess.dSet      = new DataSet("Exam");
			DataAccess.oleAdapter.Fill(DataAccess.dSet,"Exam");
			
			return DataAccess.dSet.Tables[0]; 
		}

		public DataTable FetchExamResult(string[] searchValue)
		{
		
			string str="";
			str="select distinct(usrId) from tbl_ExamResults";
			if(searchValue[0] != "") //userid is not null
			{
				str=str +" where usrId like '"+searchValue[0]+ "'";
				if(searchValue[1] != "") // examcode is not null
				{
					str += " and ExamCode = '"+searchValue[1]+"'";
					str += " and CorrectlyAns = '"+searchValue[2]+"'";
				}
				else if(searchValue[2] != "")
					str += " and CorrectlyAns = '"+searchValue[2]+"'";
			}
			else
			{
				if(searchValue[1] != "")
				{
					str=str +" where ExamCode = '"+searchValue[1]+ "'";
					str += " and CorrectlyAns = '"+searchValue[2]+"'";
				}
				else if(searchValue[2]!= "")
				{
					str += " where CorrectlyAns = '"+searchValue[2]+"'";

				}

			}

			
	
			DataAccess.oleAdapter=new OleDbDataAdapter(str,DataAccess.oleconn);
			DataSet dSetExam = new DataSet("ExamResults");
			DataAccess.oleAdapter.Fill(dSetExam,"ExamResults");
			Console.WriteLine(dSetExam.Tables[0].Rows.Count.ToString());
			return dSetExam.Tables[0]; 
		}
		
		//modify date:2004-12-27
		public DataTable FetchStudentResults(string[] searchValue)
		{
			string str="";
			str="select usrId, tbl_Exams.ExamCode, CorrectlyAns,Score ,"+
				"iif(Score>=60,'ok','fail') as result "+
				"from tbl_ExamResults,tbl_Exams where "+
				"tbl_ExamResults.ExamCode=tbl_Exams.ExamCode and usrId = '"+
				searchValue[3]+"' and tbl_Exams.ScheduledOn between ('"+
				searchValue[0]+"') and ('"+searchValue[1]+"')";
			if (searchValue[2]!="")  
			 str += "and tbl_Exams.SubjectCode like '"+searchValue[2]+"'";
			
		

			DataAccess.oleAdapter=new OleDbDataAdapter(str,DataAccess.oleconn);
			DataSet dSetExam = new DataSet("ExamResults");
			DataAccess.oleAdapter.Fill(dSetExam,"ExamResults");
			Console.WriteLine(dSetExam.Tables[0].Rows.Count.ToString());
			return dSetExam.Tables[0]; 
		}
				public void CreateExam(DataRow drExam)
		{
			try
			{ 
				//prepare sql statement to insert into table
				DataAccess.sql=
					"Insert into tbl_Exams values('"+drExam["ExamCode"]+"','"+
					drExam["SubjectCode"]+"','"+
					drExam["ScheduledOn"]+"','"+
					drExam["ScheduledTime"]+"',"+
					drExam["TotalQuest"] + ",'" +
					drExam["ExamTitle"] + "','" +
					drExam["Duration"]+"');";
							
				DataAccess.command.CommandText=DataAccess.sql;
				
				//execute the sql statement
				DataAccess.command.ExecuteNonQuery ();
			}
			catch(OleDbException)
			{
				//Console.WriteLine ("出现异常"+excep.Message);   
			}

		}