projclass.cs

本系统属于小型的在线考试系统

    public DataSet getScoreAsStuIdAndCourseId()
    //取得考试结果的信息
    {
        SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
        SqlCommand myCommand = new SqlCommand("sp_score_selectASstuIdAndCourseId", 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, "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 = BaseClass.DBCon();
        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