projclass.cs

考试系统,测试帐号：学生ID：012003021314

			myCommand.Parameters.Add("@teacherId",SqlDbType.VarChar,15).Value=teacherId;
			myCommand.Parameters.Add("@teacherPwd",SqlDbType.VarChar,10).Value=teacherPwd;
			myCommand.Parameters.Add("@teacherName",SqlDbType.VarChar,10).Value=teacherName;
			myCommand.Parameters.Add("@courseId",SqlDbType.VarChar,20).Value=courseID;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;
		
			}
			finally
			{
				myConnection.Close();
			}

		}
		public int getStudentCount()
		//取得数据库中学生的人数
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_student_getCount",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myConnection.Open();
			int count=(int)myCommand.ExecuteScalar();
			return count;
		}
		

		public int deleteStuInfo(string stuId)
			//在student表中删除记录,成功返回1,失败返回0
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_student_delete",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@stuId",SqlDbType.VarChar,15).Value=stuId;
			
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;//删除成功
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;//删除失败
		
			}
			finally
			{
				myConnection.Close();
			}

		}
		public int deleteTeacherInfo(string teacherId)
			//在student表中删除记录,成功返回1,失败返回0
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_teacher_delete",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@teacherId",SqlDbType.VarChar,15).Value=teacherId;
			
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;//删除成功
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;//删除失败
		
			}
			finally
			{
				myConnection.Close();
			}

		}
		public int updateStuStatus(string stuId,string courseId,int status)
			//在学生表里对学生的状态进行修改
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_student_StatusUpdate",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@stuId",SqlDbType.VarChar,15).Value=stuId;
			myCommand.Parameters.Add("@courseId",SqlDbType.VarChar,20).Value=courseId;
			myCommand.Parameters.Add("@stuStatus",SqlDbType.Int).Value=status;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;//成功
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;//失败
		
			}
			finally
			{
				myConnection.Close();
			}

		}
		
		public int updateStuPwd(string stuId,string pwd)
			//在学生表里对学生的密码进行修改
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_student_pwdUpdate",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@stuId",SqlDbType.VarChar,15).Value=stuId;
			myCommand.Parameters.Add("@stuPwd",SqlDbType.VarChar,10).Value=pwd;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;//成功
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;//失败
		
			}
			finally
			{
				myConnection.Close();
			}
			

		}
		public int insertTest(string testContent,string testAns1,string testAns2,string testAns3,string testAns4,int rightAns,int pub,int testScore,string testCourseId)
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_test_insert",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			
			myCommand.Parameters.Add("@testContent",SqlDbType.NVarChar,100).Value=testContent;
			myCommand.Parameters.Add("@testAns1",SqlDbType.VarChar,50).Value=testAns1;
            myCommand.Parameters.Add("@testAns2",SqlDbType.VarChar,50).Value=testAns2;
			myCommand.Parameters.Add("@testAns3",SqlDbType.VarChar,50).Value=testAns3;
			myCommand.Parameters.Add("@testAns4",SqlDbType.VarChar,50).Value=testAns4;
			myCommand.Parameters.Add("@rightAns",SqlDbType.Int).Value=rightAns;
			myCommand.Parameters.Add("@pub",SqlDbType.Int).Value=pub;
			myCommand.Parameters.Add("@testScore",SqlDbType.Int).Value=testScore;
			myCommand.Parameters.Add("@testCourseId",SqlDbType.VarChar,20).Value=testCourseId;
		
			try
			{
				myConnection.Open();
				int count=myCommand.ExecuteNonQuery();
				return count;
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;
		
			}
			finally
			{
				myConnection.Close();
			}

		}
		public string getCourseIdAsCourseName(string courseName)
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_course_selectCourseIdAsCourseName",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@courseName",SqlDbType.VarChar,20).Value=courseName;
			myConnection.Open();
			SqlDataReader thisReader=myCommand.ExecuteReader();
			if(thisReader.Read())
			{return thisReader.GetString(0);
			}
			else return "none";

		}
		public string getCourseNameAsCourseId(string courseId)
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_course_selectCourseNameAsCourseId",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@courseId",SqlDbType.VarChar,20).Value=courseId;
			myConnection.Open();
			SqlDataReader thisReader=myCommand.ExecuteReader();
			if(thisReader.Read())
			{
					return thisReader.GetString(0);
			}
			else return "none";

		}
		public bool getIsTestAsCourseId(string courseId)
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_course_isTest_select",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@courseId",SqlDbType.VarChar,20).Value=courseId;
			myConnection.Open();
			SqlDataReader thisReader=myCommand.ExecuteReader();
			if(thisReader.Read())
			{
				 bool isTest=thisReader.GetBoolean(0);
				return isTest;
			}
			else return false;
			
		}
		public DataSet getTestInfoAsId(string testId)
			//根据试题ID来获取试题详细信息
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_test_selectAsId",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@testId",SqlDbType.UniqueIdentifier).Value=new Guid(testId);
			myConnection.Open();
			SqlDataAdapter adapter=new SqlDataAdapter(myCommand);
			DataSet ds=new DataSet();
			adapter.Fill(ds,"testInfo");
			myConnection.Close();
			return ds;
		}
		public int updateTestInfo(string testId,string testContent,string testAns1,string testAns2,string testAns3,string testAns4,int rightAns,int pub,int testScore,string testCourseId)
			//更新试题信息,返回1表示更新成功,返回0则更新失败
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_test_update",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@testId",SqlDbType.UniqueIdentifier).Value=new Guid(testId);
			myCommand.Parameters.Add("@testContent",SqlDbType.NVarChar,100).Value=testContent;
			myCommand.Parameters.Add("@testAns1",SqlDbType.VarChar,50).Value=testAns1;
			myCommand.Parameters.Add("@testAns2",SqlDbType.VarChar,50).Value=testAns2;
			myCommand.Parameters.Add("@testAns3",SqlDbType.VarChar,50).Value=testAns3;
			myCommand.Parameters.Add("@testAns4",SqlDbType.VarChar,50).Value=testAns4;
			myCommand.Parameters.Add("@rightAns",SqlDbType.Int).Value=rightAns;
			myCommand.Parameters.Add("@pub",SqlDbType.Int).Value=pub;
			myCommand.Parameters.Add("@testScore",SqlDbType.Int).Value=testScore;
			myCommand.Parameters.Add("@testCourseId",SqlDbType.VarChar,20).Value=testCourseId;
		
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;
		
			}
			finally
			{
				myConnection.Close();
			}
             

		}
		public int deleteTestInfo(string testId)
			//在test表中删除记录,成功返回1,失败返回0
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_test_delete",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
		   myCommand.Parameters.Add("@testId",SqlDbType.UniqueIdentifier).Value=new Guid(testId);
			
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;//删除成功
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;//删除失败
		
			}
			finally
			{
				myConnection.Close();
			}

		}
		
		public SqlDataReader createTest(string courseId)
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_test_create",myConnection);
            myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@courseId",SqlDbType.VarChar,20).Value=courseId;
			myConnection.Open();
			//try
			//{	
			 SqlDataReader reader=myCommand.ExecuteReader();
			//}
			//catch(SqlException SQLexc)
			//{
			//	Console.WriteLine("SqlException:{0}",SQLexc);
				
		
			//}
			return reader;
		}
		
		public int ifCourseHasTest(string stuId,string courseId)
			//判断指定的用户指定的科目是否已经考试过
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_score_courseStatus_select",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@stuId",SqlDbType.VarChar,15).Value=stuId;
			myCommand.Parameters.Add("@courseId",SqlDbType.VarChar,10).Value=courseId;
			myConnection.Open();
			SqlDataReader thisReader=myCommand.ExecuteReader();
			
			
			if(thisReader.Read())
			{
				return 1;//说明找到了记录,即用户已经考试过了.
			
			}
			thisReader.Close();
			myConnection.Close();
			return 0;

		}
		public int getRightAnsAsTestId(string testId)
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_test_rightAnsselectAsId",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@testId",SqlDbType.UniqueIdentifier).Value=new Guid(testId);
			myConnection.Open();
			SqlDataReader reader=myCommand.ExecuteReader();
			int rightAns=1;
			if(reader.Read())
			{
				rightAns=reader.GetInt32(0);
			}
			reader.Close();
			return rightAns;
		}
		public DataSet getStuScore(string stuId)
			//取得学生考试结果的信息
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_score_select",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@stuId",SqlDbType.VarChar,15).Value=stuId;
			myConnection.Open();
			
			SqlDataAdapter da=new SqlDataAdapter(myCommand);
			DataSet ds=new DataSet();
			da.Fill(ds,"stuInfo");
			myConnection.Close();
			return ds;
		}

	}
}