projclass.cs

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

        {
            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;
        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 = BaseClass.DBCon();
        SqlCommand myCommand = new SqlCommand("sp_test_create", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@courseId", SqlDbType.VarChar, 20).Value = courseId;
        myConnection.Open();
        SqlDataReader reader = myCommand.ExecuteReader();
        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;
    }
}