userdb.cs

本系统实现了网上考试的目的

        if (paramCache == null)
        {
            paramCache = new SqlParameter[]{
												   new SqlParameter("@RoleID",SqlDbType.VarChar),
												   new SqlParameter("@RoleName",SqlDbType.VarChar),
                                                   new SqlParameter("@ID",SqlDbType.Int,8)};
            SQLHelper.CacheParameters(paramAddRole, paramCache);
        }
        SQLHelper.AddMyCommandParams(myCommand, paramCache);
        paramCache[0].Value = RoleID;
        paramCache[1].Value = RoleName;
        paramCache[2].Direction = ParameterDirection.ReturnValue;

        try
        {
            //打开数据库的连接
            myConnection.Open();
        }
        catch (Exception ex)
        {
            throw new GlobalDB.MyException("10001", "数据库连接失败!", ex);
        }

        try
        {
            //执行数据库的存储过程（访问数据库）
            myCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new GlobalDB.MyException("10001", ex.Message, ex);
        }
        finally
        {
            if (myConnection.State == ConnectionState.Open)
            {
                //关闭数据库的连接
                myConnection.Close();
            }
        }

        return (int)paramCache[2].Value;
    }
    #endregion

#region 在Role表中删除角色记录,成功返回1,失败返回0
    public int deleteRoleInfo(string RoleID)
    //在Role表中删除角色记录,成功返回1,失败返回0
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Role_delete", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@RoleID", SqlDbType.VarChar, 50).Value = RoleID;

        try
        {
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return 1;//删除成功
        }
        catch (SqlException SQLexc)
        {
            Console.WriteLine("SqlException:{0}", SQLexc);
            return 0;//删除失败

        }
        finally
        {
            myConnection.Close();
        }

    }
    #endregion

#region 根据教师ID来获取教师详细信息
    public DataSet getTeacherInfoAsId(int ID)
    //根据教师ID来获取教师详细信息
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Teacher_selectAsId", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        SqlParameter parameterID = myCommand.Parameters.Add("@ID", SqlDbType.Int);
        parameterID.Value = ID;
        myConnection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
        DataSet ds = new DataSet();
        adapter.Fill(ds, "TeacherInfo");
        myConnection.Close();
        return ds;

    }
    #endregion

#region 取得教师的信息
    public DataSet getTeacherAllInfo()
    //取得教师的信息
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_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;
    }
    #endregion

#region 在Users表中删除教师记录,成功返回1,失败返回0
    public int deleteTeacherInfo(string UserID)
    //在Users表中删除教师记录,成功返回1,失败返回0
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Teacher_delete", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@UserID", SqlDbType.VarChar, 50).Value = UserID;

        try
        {
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return 1;//删除成功
        }
        catch (SqlException SQLexc)
        {
            Console.WriteLine("SqlException:{0}", SQLexc);
            return 0;//删除失败

        }
        finally
        {
            myConnection.Close();
        }

    }
    #endregion

#region 判断是否存在此教师!
    public int ifTeaExist(string UserID)
    //判断是否存在此教师!
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Teacher_ifExist", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@UserID", SqlDbType.VarChar, 50).Value = UserID;
        myConnection.Open();
        SqlDataReader thisReader = myCommand.ExecuteReader();
        if (thisReader.Read())
        {
            thisReader.Close();
            return 1;
        }
        else
            thisReader.Close();
        return 0;
    }
    #endregion

#region 从数据库的ClassifyID表中取得ClassifyID信息,从而绑定到页面的dropdownlist中
    public DataSet getClassifyID()
    //从数据库的ClassifyID表中取得ClassifyID信息,从而绑定到页面的dropdownlist中
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_ClassifyID_selectClassifyID", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myConnection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        myConnection.Close();
        return ds;
    }
    #endregion

#region 通过ClassifyID得到ClassifyName的信息
    public string getClassifyNameAsClassifyID(string ClassifyID)
    {//通过ClassifyID得到ClassifyName的信息
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_selectClassifyNameAsClassifyID", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@ClassifyID", SqlDbType.VarChar, 50).Value = ClassifyID;
        myConnection.Open();
        SqlDataReader thisReader = myCommand.ExecuteReader();
        if (thisReader.Read())
        {
            return thisReader.GetString(0);
        }
        else return "none";

    }
#endregion

#region 通过UserID得到ClassifyID的信息
    public string getClassifyIDAsUserID(string UserID)
    {//通过UserID得到ClassifyID的信息
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_selectClassifyIDAsUserID", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@UserID", SqlDbType.VarChar, 50).Value = UserID;
        myConnection.Open();
        SqlDataReader thisReader = myCommand.ExecuteReader();
        if (thisReader.Read())
        {
            return thisReader.GetString(0);
        }
        else return "none";

    }
    #endregion

#region 添加教师
    private const string paramAddTeacher = "UserID_UserName_UserPassword_Email_RoleID_ClassifyID";
    public int AddTeacher(String sUserID, String sUserName, String sUserPassword, String sEmail, String sClassifyID)
    {
        //定义数据库的Connection and Command 
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_AddTeacher", myConnection);

        //定义访问数据库的方式为存储过程
        myCommand.CommandType = CommandType.StoredProcedure;

        //创建访问数据库的参数
        SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramAddTeacher);
        if (paramCache == null)
        {
            paramCache = new SqlParameter[]{
												   new SqlParameter("@UserID",SqlDbType.VarChar),
												   new SqlParameter("@UserName",SqlDbType.VarChar),
												   new SqlParameter("@UserPassword",SqlDbType.VarChar),
                                                   new SqlParameter("@Email",SqlDbType.VarChar),
                                                   new SqlParameter("@ID",SqlDbType.Int,8),
                                                   new SqlParameter("@ClassifyID",SqlDbType.VarChar)};
            SQLHelper.CacheParameters(paramAddTeacher, paramCache);
        }
        SQLHelper.AddMyCommandParams(myCommand, paramCache);
        paramCache[0].Value = sUserID;
        paramCache[1].Value = sUserName;
        paramCache[2].Value = sUserPassword;
        paramCache[3].Value = sEmail;
        paramCache[4].Direction = ParameterDirection.ReturnValue;
        paramCache[5].Value = sClassifyID;

        try
        {
            //打开数据库的连接
            myConnection.Open();
        }
        catch (Exception ex)
        {
            throw new GlobalDB.MyException("10001", "数据库连接失败!", ex);
        }

        try
        {
            //执行数据库的存储过程（访问数据库）
            myCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new GlobalDB.MyException("10001", ex.Message, ex);
        }
        finally
        {
            if (myConnection.State == ConnectionState.Open)
            {
                //关闭数据库的连接
                myConnection.Close();
            }
        }

        return (int)paramCache[4].Value;
    }
    #endregion

#region 更新教师的信息,返回1表示更新成功,返回0则更新失败
    public int updateTeacherInfo(string ID, string teacherId, string teacherName, string teacherPassword, string Email, string ClassifyID)
    //更新教师的的信息,返回1表示更新成功,返回0则更新失败
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_TeacherInfo_update", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("ID", SqlDbType.Int).Value = ID;
        myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = teacherId;
        myCommand.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = teacherName;
        myCommand.Parameters.Add("@UserPassword", SqlDbType.VarChar, 50).Value = teacherPassword;
        myCommand.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = Email;
        myCommand.Parameters.Add("@ClassifyID", SqlDbType.VarChar, 50).Value = ClassifyID;

        //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();
        }


    }
    #endregion

#region 添加选择题
    private const string paramAddTest = "testContent_testAns1_testAns2_testAns3_testAns4_rightAns_pub_testScore_testCourseId";
    public int AddTest(String testContent, String testAns1, String testAns2, String testAns3, String testAns4, int rightAns, int pub, int testScore, String testCourseId)
    {
        //定义数据库的Connection and Command 
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_test_insert", myConnection);

        //定义访问数据库的方式为存储过程
        myCommand.CommandType = CommandType.StoredProcedure;

        //创建访问数据库的参数
        SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramAddTest);
        if (paramCache == null)
        {
            paramCache = new SqlParameter[]{
												   new SqlParameter("@testContent",SqlDbType.Text),
												   new SqlParameter("@testAns1",SqlDbType.Text),
												   new SqlParameter("@testAns2",SqlDbType.Text),
                                                   new SqlParameter("@testAns3",SqlDbType.Text),
                                                   new SqlParameter("@testAns4",SqlDbType.Text),
                                                   new SqlParameter("@rightAns",SqlDbType.Int,8),
                                                   new SqlParameter("@pub",SqlDbType.Int,8),
                                                   new SqlParameter("@testScore",SqlDbType.Int,8),
                                                   new SqlParameter("@testCourseId",SqlDbType.VarChar),
                                                   new SqlParameter("@ID",SqlDbType.Int,8)};
            SQLHelper.CacheParameters(paramAddTest, paramCache);
        }
        SQLHelper.AddMyCommandParams(myCommand, paramCache);
        paramCache[0].Value = testContent;
        paramCache[1].Value = testAns1;
        paramCache[2].Value = testAns2;
        paramCache[3].Value = testAns3;
        paramCache[4].Value = testAns4;
        paramCache[5].Value = rightAns;
        paramCache[6].Value = pub;
        paramCache[7].Value = testScore;
        paramCache[8].Value = testCourseId;
        paramCache[9].Direction = ParameterDirection.ReturnValue;

        try
        {