dataaccess.cs

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

using System;
using System.Data;
using System.Data.OleDb;



namespace OES
{
	/// <summary>
	/// Summary description for DataAccess.
	/// </summary>
	public class DataAccess
	{
		private static DataAccess instance = null;
		public static OleDbConnection oleconn;
        public static OleDbCommand command;
		public static OleDbDataAdapter oleAdapter;
		
		//private OleDbDataReader reader;
		public static DataSet dSet;
        public static string sql;
        
		public DataAccess()
		{
			Connection();
		}

		public static DataAccess GetInstance()
		{
			if (instance == null)
				instance = new DataAccess();
			return instance;			
		}

		public void Connection()
		{
			try
			{
		
				string db=Environment.CurrentDirectory + "\\OESystem.mdb";
				oleconn = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+ db +";Persist Security Info=False");
				
				oleconn.Open();
				Console.WriteLine ("Connection created");

				command = oleconn.CreateCommand();
				 oleAdapter=new OleDbDataAdapter ("Select * from tbl_user", oleconn);
				dSet = new DataSet();
				oleAdapter.Fill(dSet,"User"); 
			}
			catch(OleDbException excep)
			{
				Console.WriteLine ("Exception occured checkout"+excep.Message); 
			}
		}

		/*public void SaveRecord (User user)
		{
			string ID = user.getUserID();
			OleDbDataAdapter adapter = new OleDbDataAdapter();
			adapter.SelectCommand = new OleDbCommand("Select * from tbl_user", oleconn);

			string sql = "INSERT INTO tbl_User " + 
				"(UsrId, " +
				"UsrName, " +
				"Passwd, " +
				"UsrStatus, " +
				"UsrType, " +
				"Gender, " +
				"Address, " +
				"EmailId, " +
				"Phone) " +
				"Values (@UsrId, " +
				"@UsrName, " +
				"@Passwd, " +
				"@UsrStatus, " +
				"@UsrType, " +
				"@Gender, " +
				"@Address, " +
				"@EmailId, " +
				"@Phone) ";
			OleDbCommand cmd = new OleDbCommand(sql, oleconn);

			cmd.Parameters.Add("@UsrId", OleDbType.VarChar, 10, ID);
			cmd.Parameters.Add("@UsrName", OleDbType.VarChar, 25, ID);
			cmd.Parameters.Add("@Passwd", OleDbType.VarChar, 7, ID);
			cmd.Parameters.Add("@UsrStatus", OleDbType.VarChar, 1, "A");
			cmd.Parameters.Add("@UsrType", OleDbType.VarChar, 10, ID);
			cmd.Parameters.Add("@Gender", OleDbType.VarChar, 1, "M");
			cmd.Parameters.Add("@Address", OleDbType.VarChar, 25, ID);
			cmd.Parameters.Add("@EmailId", OleDbType.VarChar, 25, ID);
			cmd.Parameters.Add("@Phone", OleDbType.VarChar, 20, ID);


//			DataSet ds = new DataSet();
//			adapter.TableMappings.Add("tbl_User", "tbl_User");
//

//			adapter.Fill(ds, "tbl_user");

			adapter.InsertCommand = cmd;

			
			DataTable table = ds.Tables["tbl_User"];
			DataRow newRow = table.NewRow();
			newRow["UsrID"] = user.getUserID();
			newRow["UsrName"] = "test";
			table.Rows.Add(newRow);
		
		//	adapter.Update(ds, "tbl_User");

	//		ds.Tables
			
		}*/

		//this.txtUsername.Text, this.txtPassword.Text, this.status, this.txtName.Text, this.cboUserType.Text, this.gender, this.txtAddress.Text, this.txtEmail.Text, this.txtPhone.Text);
		//public void InsertRecords(DataRow drUser)
		//public void InsertRecords(string UserName, string Passwd, string Status, string Name, string UserType, 
		//	string Sex, string Address, string Email, string Phone)			
		public void InsertRecords(string UserName, string Passwd, string Status, string Name, string UserType,
			string Sex, string Address, string Email, string Phone)
		{
			try
			{ 	
/*				string db=Environment.CurrentDirectory + "\\OESystem.mdb";
				OleDbConnection oleconn1 = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+db
					+";Persist Security Info=False");
*/				
				//oleconn.Open();
				//OleDbCommand command = oleconn.CreateCommand();
		
				sql="Insert into tbl_User values('"
					+ UserName + "','"
					+ Name +"','"
					+ Passwd +"','"
					+ Status +"','"
					+ UserType +"','"
					+ Sex +"','"
					+ Address +"','"
					+ Email +"','"
					+ Phone +"');";

				Console.WriteLine(sql);
				command.CommandText = sql;
				command.Connection = oleconn;
				command.ExecuteNonQuery ();
				
				Console.WriteLine("added");
			}
			catch(OleDbException excep)
			{
				Console.WriteLine ("Exception occured: "+excep.Message);   
			}
		}
		
		//objDA.InsertExams(this.txtExamCode.Text, this.txtExamTitle.Text, this.cboSubject.Text, this.cboNumofQuestions.Text, 
		//this.dtpSchedule.Text, this.txtDuration.Text, this.txtScheduledAt.Text);

		public void InsertExams(string ExamCode, string ExamTitle, string Subject, string NumQuestions, 
			string Schedule, string Duration, string ScheduledAt)
		{
			try
			{ 	
				string db=Environment.CurrentDirectory + "\\OESystem.mdb";
				OleDbConnection oleconn1 = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+db
					+";Persist Security Info=False");
				
				oleconn1.Open();

				OleDbCommand command = oleconn1.CreateCommand();
				
				

				// check the subject entered and then accordingly enter its ID
				string SubjectID;

				if (Subject.Equals("Java"))
				{
					SubjectID = "JAV0001";
				}
				else if (Subject.Equals("C#"))
				{
					SubjectID = "CSH0001";
				}
				else if(Subject.Equals("HTML"))
				{
					SubjectID = "HTM0001";
				}
				else
				{
					SubjectID = "SQL0001";
				}

				sql="Insert into tbl_Exams values('"
					+ ExamCode + "','"
					+ SubjectID +"','"
					+ Schedule +"','"
					+ Duration +"',"
					+ NumQuestions +",'"
					+ ExamTitle +"','"
					+ ScheduledAt +"');";

				Console.WriteLine(sql);
				command.CommandText = sql;
				command.Connection = oleconn1;
				command.ExecuteNonQuery ();
				
				Console.WriteLine("added");
			}
			catch(OleDbException excep)
			{
				Console.WriteLine ("Exception occured: "+excep.Message);   
			}
		}
		
		public void InsertQuestions(int Qno, string SubjectID, int SubQno, string Question, string OptA, string OptB, 
			string OptC, string OptD, string corrAns)
		{
			try
			{ 	
				string db=Environment.CurrentDirectory + "\\OESystem.mdb";
				OleDbConnection oleconn1 = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+db
					+";Persist Security Info=False");
				
				oleconn1.Open();

				OleDbCommand command = oleconn1.CreateCommand();
				
				sql="Insert into tbl_QuestionBank values("
					+ Qno + ",'"
					+ SubjectID +"',"
					+ SubQno +",'"
					+ Question +"','"
					+ OptA +"','"
					+ OptB +"','"
					+ OptC +"','"
					+ OptD +"','"
					+ corrAns +"');";

				Console.WriteLine(sql);
				command.CommandText = sql;
				command.Connection = oleconn1;
				command.ExecuteNonQuery ();
				
				Console.WriteLine("added");
			}
			catch(OleDbException excep)
			{
				Console.WriteLine ("Exception occured: "+excep.Message);   
			}
		}

		public void UpdateRecords(string UserName, string Passwd, string Status, string Name, string UserType,
			string Sex, string Address, string Email, string Phone)
		{
			try
			{ 	
				string db=Environment.CurrentDirectory + "\\OESystem.mdb";
				OleDbConnection oleconn1 = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+db
					+";Persist Security Info=False");
				
				oleconn1.Open();

				OleDbCommand command = oleconn1.CreateCommand();
				
				sql="Update tbl_User Set UsrName = '"
					+ Name +"', Passwd = '"
					+ Passwd +"', UsrStatus = '"
					+ Status +"', UsrType = '"
					+ UserType +"', Gender = '"
					+ Sex +"', Address = '"
					+ Address +"', EmailId = '"
					+ Email +"', Phone = '"
					+ Phone +"' where UsrId = '"+ UserName +"';";				

				Console.WriteLine(sql);
				command.CommandText = sql;
				command.Connection = oleconn1;
				command.ExecuteNonQuery ();
				
				Console.WriteLine("Updated");
			}
			catch(OleDbException excep)
			{
				Console.WriteLine ("Exception occured: "+excep.Message);   
			}
		}

		public void UpdateExam(string ExamCode, string ExamTitle, string Subject, int NumQuest, string ScheduleDt,
			string Duration, string location)
		{
			try
			{ 	
				// check the subject entered and then accordingly enter its ID
				string SubjectID;

				if (Subject.Equals("Java"))
				{
					SubjectID = "JAV0001";
				}
				else if (Subject.Equals("C#"))
				{
					SubjectID = "CSH0001";
				}
				else if(Subject.Equals("HTML"))
				{
					SubjectID = "HTM0001";
				}
				else
				{
					SubjectID = "SQL0001";
				}

				string db=Environment.CurrentDirectory + "\\OESystem.mdb";
				OleDbConnection oleconn1 = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+db
					+";Persist Security Info=False");
				
				oleconn1.Open();

				OleDbCommand command = oleconn1.CreateCommand();
				
				sql="Update tbl_Exams Set SubjectCode = '"
					+ SubjectID +"', ScheduledOn = '"
					+ ScheduleDt +"', ScheduledTime = '"
					+ Duration +"', TotalQuest = "
					+ NumQuest +", ExamTitle = '"
					+ ExamTitle +"', Location = '"
					+ location +"' where ExamCode = '"+ ExamCode +"';";				

				Console.WriteLine(sql);
				command.CommandText = sql;
				command.Connection = oleconn1;
				command.ExecuteNonQuery ();
				
				Console.WriteLine("Updated");
			}
			catch(OleDbException excep)
			{
				Console.WriteLine ("Exception occured: "+excep.Message);   
			}
		}

//cboSubject.Text, QID, txtQuestion.Text, txtOptionA.Text, txtOptionB.Text, txtOptionC.Text, txtOptionD.Text, opt

		public void UpdateQuestions(int QID, string Question, string optA, string optB, string optC, string optD, string correctans)
		{
			try
			{ 	
				string db=Environment.CurrentDirectory + "\\OESystem.mdb";
				OleDbConnection oleconn1 = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+db
					+";Persist Security Info=False");
				
				oleconn1.Open();

				OleDbCommand command = oleconn1.CreateCommand();
				
				sql="Update tbl_QuestionBank Set Question = '"
					+ Question +"', OptionA = '"
					+ optA +"', OptionB = '"
					+ optB +"', OptionC = '"
					+ optC +"', OptionD = '"
					+ optD +"', CorrectAns = '"
					+ correctans +"' where QNumber = "+ QID +";";				

				Console.WriteLine(sql);
				command.CommandText = sql;
				command.Connection = oleconn1;
				command.ExecuteNonQuery ();
				
				Console.WriteLine("Updated");
			}
			catch(OleDbException excep)
			{
				Console.WriteLine ("Exception occured: "+excep.Message);   
			}
		}

		/// <summary>
		/// 插入考试分数.
		/// 
		/// </summary>
		/// <param name="strUserName" >用户名称</param>
		/// <param name="strExamCode" >考试代码</param>
		/// <param name="intCorAns" >正确答题数</param>
		/// <param name="intScore" >考试分数</param>
		///

		public static void SaveStudentScore(string strUserName,string strExamCode,int intCorAns,int intScore)
		{
			string strSql="INSERT INTO tbl_ExamResults VALUES ("+"'"+strUserName+"',"+"'"+strExamCode+"',"+intCorAns+","+intScore+")";
			SqlCommand (oleconn,strSql);
		}

		/// <summary>
		/// 处理数据库Sql命令.
		/// </summary>
		/// <param name="oleconn" >连接对象</param>
		/// <param name="strSql" >sql语句</param>
		private static void SqlCommand(OleDbConnection oleconn,string strSql)
		{
		
//			oleconn.Open ();
			command=new OleDbCommand (strSql,oleconn);
			command.ExecuteNonQuery ();
			
		}

		/// <summary>
		/// 返回考试信息(及格人数,不及格人数,考试人数).
		/// </summary>
		/// <param name="strSql" >查询语句</param>
		public string ExPass (string strSql)
		{
			string strValue="";
			command=new OleDbCommand (strSql,oleconn);
			strValue=command.ExecuteScalar ().ToString ();
			return strValue;		

		}

		/// <summary>
		/// 返回考试成绩
		/// </summary>
		/// <param name="strSql" >查询语句</param>
		public DataTable ExResult (string strSql)
		{
			DataTable dtResult;
			command=new OleDbCommand (strSql,oleconn);
			oleAdapter=new OleDbDataAdapter ();
			DataSet ds=new DataSet ();
			oleAdapter.SelectCommand =command;
			oleAdapter.Fill (ds,"tbl_ExamResults");
			dtResult=ds.Tables [0];
			return dtResult;		

		}

		
	}
}