exam.cs

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

		//this method is called to generate the exam code for every  
		//new exam that is to be shcedeuled
		public string GenerateExamCode(string subjectCode)
		{
			try
			{
				string returnStr="";//to return to the calling function
				int readerCount=1;
				string examId="";
				//extract the exam code from the database based on the 
				//subject selected on the form
				string str = "Select ExamCode from tbl_Exams where SubjectCode like '"+subjectCode+"'";
				DataAccess objDA=DataAccess.GetInstance ();

				DataAccess.command.CommandText=str;

				//use reader to execute the query
				OleDbDataReader reader=DataAccess.command.ExecuteReader();
							
				//if the query returns any rows
				if (reader.HasRows)
				{
					readerCount = 0;

					//read till end of table
					while (reader.Read())
					{
						examId = (reader.GetString(0));
							
					}//end of while
					reader.Close();

					//exam id will now have the last exam code
					//extract the string part of the subject Code
					string strpart=examId.Substring(0,3);
				
					//extract the number part of the subject Code
					string numpart=examId.Substring(3,4);
				
					//convert the number part to integer
					int val=Convert.ToInt32(numpart);
				
					//increment
					val++;
				
					//to keep track of the zeroes
					int count = 4-val.ToString().Length;
					int iCounter=0;
					while (iCounter<count)
					{
						strpart+="0";
						iCounter++;
					}

					returnStr=strpart+val.ToString();
				}
				else if(readerCount != 0) //no rows returned by reader
				{
					reader.Close();
					//string strpart=subjectCode.Substring(0,3);

					string strpart=subjectCode.Substring(0,1);
					
					if (strpart.Equals("J"))
					{
						strpart=strpart+"AV";
					}
					if(strpart.Equals("S"))
					{
						strpart=strpart+"QL";
					}
					if(strpart.Equals("C"))
					{
						strpart=strpart+"SH";
					}
					if(strpart.Equals("H"))
					{
						strpart=strpart+"TM";
					}

					int numpart=0001;

					//keep track of zeroes
					int count = 4-numpart.ToString().Length;
					int iCounter=0;
					while (iCounter<count)
					{
						strpart+="0";
						iCounter++;
					}
					returnStr=strpart+numpart.ToString();
				}
				return returnStr;
			}//end of try
			catch(Exception excp)
			{
				Console.WriteLine("发生错误"+excp.Message.ToString ());
				return "no records";
			}
		}

		//this method is called to generate a test paper for the exam that is currently shceduled
		//the method checks for availability of questions in a particular subject and returns true or false accordingly

		///modif by ww date:2004-12-26
		public bool GenerateTestPaper(string examCode, string subjectCode, int noQuestions)
		{
			try
			{
				//int questNo=0;
				
				//deleted string str="select QuestNo from tbl_QuestionBank where SubjectCode like '"+subjectCode+"'";
				string str="select count(*) from tbl_QuestionBank where SubjectCode = '"+subjectCode+"'";
		
				
				
				DataAccess.command.CommandText=str;

				int totalRows = Convert.ToInt32(DataAccess.command.ExecuteScalar());

				//call createTable to create a temporary table
				/*CreateTable();
                
				DataRow drTestPaper;

				//read question number from reader and dump into table
				if (reader.HasRows)
				{
					while (reader.Read())
					{
						questNo = (reader.GetInt32(0));
						
						drTestPaper=dtTestPaper.NewRow();

						drTestPaper["ExamCode"]=examCode;
						drTestPaper["QNumber"]=questNo;
						dtTestPaper.Rows.Add(drTestPaper);
					}
				}
				reader.Close ();*/
				//the temporary table now has all the questions for 
				//that particular subject
				
				//assign questions selected while creating exam
				int questionCount=noQuestions;

				//assign totla number of questions for that subject
				
				
				if(totalRows < questionCount)
				{
					return false; 
				}
				
				//create an array to store random numbers generated
				//array size will be equal to the total questions selected
				//by the teacher on the form while creating exam
				int []narr=new int[questionCount];
				System.Collections.ArrayList arr = new System.Collections.ArrayList();
				for (int i=0;i<questionCount; ++i)
				{
					arr.Add(i+1);
				}
				Random rand = new Random();

				//have a loop to add random numbers into the array
				for (int i=0;i<questionCount;++i)
				{
					//generate random number by setting minimum
					//and maximum values
													
					int ind = rand.Next(0,questionCount-i);
					
					int QNumber = (int)arr[ind];
					arr.RemoveAt(ind);
                    

					narr[i]=QNumber;
					
				}//end of outside for
				
				//the array will hold random number as many as required for
				//the exam

				for (int i=0;i<narr.Length;i++)
				{
					//create the query to insert the array values into
					//the table
					DataAccess.sql="Insert into tbl_TestPaper values('" + examCode + "',"+ narr[i]+");";

					DataAccess.command.CommandText=DataAccess.sql;
					
					DataAccess.command.ExecuteNonQuery ();

				}//end of for to iterate thru the array
				
			}//end of try block
			catch(OleDbException)
			{
				//Console.WriteLine ("出现异常"+excep.Message); 
				return false; //added
			}
			return true;
		}//end of GenerateTestPaper

		public void CreateTable()
		{
			//create a temporary table to extract all questions for a
			//particular subject
			dtTestPaper = new DataTable("tbl_TestPaper");
			DataColumn pKeyCol = dtTestPaper.Columns.Add("ExamCode",  typeof(String));
			pKeyCol.AllowDBNull = false;
			dtTestPaper.Columns.Add("QNumber", typeof(Int32));
		}

		public DataTable FetchExamReports(string[] searchValue)
		{
			//code to return questions of a subject for the specified zero or 
			//more keywords	 for question field of QuestionBank table

			string str="";

			////除去str="select ExamCode, UsrId,CorrectlyAns from tbl_ExamResults where ExamCode"+
			////除去	"in (select ExamCode from tbl_Exams where SubjectCode ='"+searchValue[0]+"'"+
			////除去	"and CDate(ScheduledOn)>=#"+searchValue[1]+"#) ";
			///
			//所添加的CODES
			str="select tbl_ExamResults.ExamCode,UsrId,CorrectlyAns from tbl_ExamResults inner join tbl_Exams "+
				"on tbl_Exams.ExamCode = tbl_ExamResults.ExamCode where tbl_Exams.SubjectCode='"+searchValue[0]+"'"+
				"and CDate(tbl_Exams.ScheduledOn)>=#"+searchValue[1]+"# ";
			//以上所添加的CODES

			DataAccess.oleAdapter=new OleDbDataAdapter(str,DataAccess.oleconn);
			DataAccess.dSet = new DataSet("tbl_ExamResults");
			DataAccess.oleAdapter.Fill(DataAccess.dSet,"tbl_ExamResults");

			//除去Console.WriteLine(DataAccess.dSet.Tables[0].Rows.Count.ToString());
			//除去Console.WriteLine(DataAccess.dSet.Tables[0].Rows[0][0].ToString());
			
			return DataAccess.dSet.Tables[0]; 

		}


		/// <summary>
		/// 返回考试及格人数.
		/// </summary>
		/// <param name="strExamCode" >考试代码</param>
		
		public string ExamPass(string strExamCode)
		{
			string strExamPassNumber="";
			string strSql="select count (*) from tbl_ExamResults where score>=60 and ExamCode='"+strExamCode+"'";
			OES.DataAccess ds=new OES. DataAccess ();
			strExamPassNumber=ds.ExPass (strSql);
			return strExamPassNumber;
			
		}

		
		/// <summary>
		/// 返回考试不及格人数.
		/// </summary>
		/// <param name="strExamCode" >考试代码</param>
		
		public string ExamFlunk(string strExamCode)
		{
			string strExamPassNumber="";
			string strSql="select count (*) from tbl_ExamResults where score<60 and ExamCode='"+strExamCode+"'";
			OES.DataAccess ds=new DataAccess ();
			strExamPassNumber=ds.ExPass (strSql);
			return strExamPassNumber;
			
		}

		/// <summary>
		/// 返回考试总人数.
		/// </summary>
		/// <param name="strExamCode" >考试代码</param>
		
		public string ExamTotal(string strExamCode)
		{
			string strExamPassNumber="";
			string strSql="select count (*) from tbl_ExamResults where ExamCode='"+strExamCode+"'";
			OES.DataAccess ds=new DataAccess ();
			strExamPassNumber=ds.ExPass (strSql);
			return strExamPassNumber;
			
		}

		/// <summary>
		/// 返回考试成绩.
		/// </summary>
		/// <param name="strExamCode" >考试代码</param>
		
		public DataTable ExamResult(string strExamCode)
		{
			
			string strSql="select * from tbl_ExamResults a, tbl_User b where a.ExamCode='"+strExamCode+"'"+" and a.UsrId=b.UsrId";
			OES.DataAccess ds=new DataAccess ();
			DataTable ExamResul=ds.ExResult (strSql);
			return ExamResul;
			
		}
}
}