projclass.cs

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

			//return thisReader;
			SqlDataAdapter da=new SqlDataAdapter(myCommand);
			DataSet ds=new DataSet();
			da.Fill(ds,"scoreInfo");
			myConnection.Close();
			return ds;
		}public DataSet getTeacherAllInfo()
			 //取得考试结果的信息
		 {
			 SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			 SqlCommand myCommand=new SqlCommand("sp_teacher_selectAllInfo",myConnection);
			 myCommand.CommandType=CommandType.StoredProcedure;
			 myConnection.Open();
			 //	SqlDataReader thisReader=myCommand.ExecuteReader();
			 //return thisReader;
			 SqlDataAdapter da=new SqlDataAdapter(myCommand);
			 DataSet ds=new DataSet();
			 da.Fill(ds,"teacherInfo");
			 myConnection.Close();
			 return ds;
		 }
		public int updateStuInfo(string stuId,string stuPwd,string stuName,int stuSex)
			//更新学生信息,返回1表示更新成功,返回0则更新失败
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_student_update",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@stuId",SqlDbType.VarChar,15).Value=stuId;
			myCommand.Parameters.Add("@stuPwd",SqlDbType.VarChar,10).Value=stuPwd;
			myCommand.Parameters.Add("@stuSex",SqlDbType.Int).Value=stuSex;
			myCommand.Parameters.Add("@stuName",SqlDbType.Char,10).Value=stuName;
			//myCommand.Parameters.Add("@isTest",SqlDbType.Int,0).Value=stuStatus;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
            	return 1;
			}catch(SqlException SQLexc)
	    	{
				Console.WriteLine("SqlException:{0}",SQLexc);
			return 0;
		
		   }
		finally
			{
				  myConnection.Close();
		    }
             

		}
		public int updateAdminPwd(string adminId,string adminPwd)
			//更新管理员信息,返回1表示更新成功,返回0则更新失败
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_admin_update",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@adminId",SqlDbType.VarChar,15).Value=adminId;
			myCommand.Parameters.Add("@adminPwd",SqlDbType.VarChar,10).Value=adminPwd;
			
			//myCommand.Parameters.Add("@isTest",SqlDbType.Int,0).Value=stuStatus;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;
		
			}
			finally
			{
				myConnection.Close();
			}
             

		}
		public int updateTeacherPwd(string teacherId,string teacherPwd)
			//更新教师的密码,返回1表示更新成功,返回0则更新失败
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_teacher_update",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@teacherId",SqlDbType.VarChar,15).Value=teacherId;
			myCommand.Parameters.Add("@teacherPwd",SqlDbType.VarChar,10).Value=teacherPwd;
			
			//myCommand.Parameters.Add("@isTest",SqlDbType.Int,0).Value=stuStatus;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;
		
			}
			finally
			{
				myConnection.Close();
			}
             

		}
		public int updateTeacherInfo(string teacherId,string teacherPwd,string teacherName,string courseId)
			//更新教师的信息,返回1表示更新成功,返回0则更新失败
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_teacher_updateInfo",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			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;
			
			//myCommand.Parameters.Add("@isTest",SqlDbType.Int,0).Value=stuStatus;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;
		
			}
			finally
			{
				myConnection.Close();
			}
             

		}
		public int insertStuStatusToScore(string stuId,string courseId,int courseStatus,int score)
			//在分数表里插入相关记录
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_score_courseStatus_insert",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("@courseStatus",SqlDbType.Int,0).Value=courseStatus;
			myCommand.Parameters.Add("@score",SqlDbType.Int,0).Value=score;
			int ifTest=(new projClass()).ifCourseHasTest(stuId,courseId);
			if(ifTest==0)
			{
				try
				{
					myConnection.Open();
					myCommand.ExecuteNonQuery();
					return 1;
				}
				catch(SqlException SQLexc)
				{
					Console.WriteLine("SqlException:{0}",SQLexc);
					return 0;
		
				}
				finally
				{
					myConnection.Close();
				}
			}else return 0;
             

		}
		public int updateScore(string stuId,string courseId,int courseStatus,int score)
			//在分数表里插入相关记录
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_score_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("@courseStatus",SqlDbType.Int,0).Value=courseStatus;
			myCommand.Parameters.Add("@score",SqlDbType.Int,0).Value=score;
			int ifTest=(new projClass()).ifCourseHasTest(stuId,courseId);
			
				try
				{
					myConnection.Open();
					myCommand.ExecuteNonQuery();
					return 1;
				}
				catch(SqlException SQLexc)
				{
					Console.WriteLine("SqlException:{0}",SQLexc);
					return 0;
		
				}
				finally
				{
					myConnection.Close();
				}
			
             

		}
		public int updateCourseInfo(string courseId,string courseName,bool isTest)
			//更新课程信息,返回1表示更新成功,返回0则更新失败
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_course_update",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@courseId",SqlDbType.VarChar,20).Value=courseId;
			myCommand.Parameters.Add("@courseName",SqlDbType.VarChar,20).Value=courseName;
			myCommand.Parameters.Add("@isTest",SqlDbType.Bit).Value=isTest;
			//myCommand.Parameters.Add("@isTest",SqlDbType.Int,0).Value=stuStatus;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;
		
			}
			finally
			{
				myConnection.Close();
			}
             

		}
		public int insertCourseInfo(string courseId,string courseName,bool isTest)
			//向course表中添加记录
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_course_insert",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@courseId",SqlDbType.VarChar,20).Value=courseId;
			myCommand.Parameters.Add("@courseName",SqlDbType.VarChar,20).Value=courseName;
			myCommand.Parameters.Add("@isTest",SqlDbType.Bit).Value=isTest;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;
		
			}
			finally
			{
				myConnection.Close();
			}

		}
		public int insertAdmin(string adminId,string adminPwd)
			//向管理员表中添加记录
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_admin_insert",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@adminId",SqlDbType.VarChar,20).Value=adminId;
			myCommand.Parameters.Add("@adminPwd",SqlDbType.VarChar,20).Value=adminPwd;
			
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				return 1;
			}
			catch(SqlException SQLexc)
			{
				Console.WriteLine("SqlException:{0}",SQLexc);
				return 0;
		
			}
			finally
			{
				myConnection.Close();
			}

		}
		public int deleteCourseInfo(string courseId)
			//在course表中删除记录,成功返回1,失败返回0
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_course_delete",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			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 deleteScoreInfo(string stuId,string courseId)
			//在course表中删除记录,成功返回1,失败返回0
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_score_delete",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@courseId",SqlDbType.VarChar,20).Value=courseId;
			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 insertStuinfo(string stuId,string stuName,string stuPwd,int stuSex,int stuStatus,string courseID)
			//向student表中添加记录
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_student_insert",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			myCommand.Parameters.Add("@stuId",SqlDbType.VarChar,15).Value=stuId;
			myCommand.Parameters.Add("@stuPwd",SqlDbType.VarChar,10).Value=stuPwd;
			myCommand.Parameters.Add("@stuName",SqlDbType.Char,10).Value=stuName;
			myCommand.Parameters.Add("@stuStatus",SqlDbType.Int).Value=stuStatus;
            myCommand.Parameters.Add("@stuSex",SqlDbType.Int).Value=stuSex;
            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 insertTeacherinfo(string teacherId,string teacherPwd,string teacherName,string courseID)
			//向student表中添加记录
		{
			SqlConnection myConnection=new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
			SqlCommand myCommand=new SqlCommand("sp_teacher_insert",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;