userdb.cs

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

            //打开数据库的连接
            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[9].Value;
    }
    #endregion

#region 获得选择题信息
    public DataSet getTestAllInfo()
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_test_selectAllInfo", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myConnection.Open();
        SqlDataAdapter da = new SqlDataAdapter(myCommand);
        DataSet ds = new DataSet();
        da.Fill(ds, "TestInfo");
        myConnection.Close();
        return ds;
    }
    #endregion

#region 根据选择题ID来获取选择题详细信息
    public DataSet getTestInfoAsId(int ID)
    //根据选择题ID来获取选择题详细信息
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Test_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, "TestInfo");
        myConnection.Close();
        return ds;

    }
    #endregion

#region 删除选择题记录
    public int deleteTestInfo(string ID)
    //删除选择题记录
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Test_delete", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@ID", SqlDbType.VarChar, 50).Value = ID;

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

        }
        finally
        {
            myConnection.Close();
        }

    }
    #endregion

#region 更新选择题
    public int updateTestInfo(string ID, string testContent, string testAns1, string testAns2, string testAns3, string testAns4, int rightAns, int pub, int testScore, string testCourseId)
    //更新试题信息,返回1表示更新成功,返回0则更新失败
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_test_Update", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
        myCommand.Parameters.Add("@testContent", SqlDbType.Text).Value = testContent;
        myCommand.Parameters.Add("@testAns1", SqlDbType.Text).Value = testAns1;
        myCommand.Parameters.Add("@testAns2", SqlDbType.Text).Value = testAns2;
        myCommand.Parameters.Add("@testAns3", SqlDbType.Text).Value = testAns3;
        myCommand.Parameters.Add("@testAns4", SqlDbType.Text).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();
        }


    }
    #endregion

#region 根据ClassifyID来获取试题详细信息
    public DataSet getTestInfoAsClassifyID(string ClassifyID)
    //根据ClassifyID来获取试题详细信息
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_test_selectAsClassifyId", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        SqlParameter parameterID = myCommand.Parameters.Add("@ClassifyID", SqlDbType.Int);
        parameterID.Value = ClassifyID;
        myConnection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
        DataSet ds = new DataSet();
        adapter.Fill(ds, "TestInfo");
        myConnection.Close();
        return ds;

    }
#endregion

#region 获得完形填空题信息
    public DataSet getWXTKAllInfo()
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_WXTK_selectAllInfo", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myConnection.Open();
        SqlDataAdapter da = new SqlDataAdapter(myCommand);
        DataSet ds = new DataSet();
        da.Fill(ds, "WXTKInfo");
        myConnection.Close();
        return ds;
    }
    #endregion

#region 根据ClassifyID来获取完形填空题详细信息
    public DataSet getWXTKInfoAsClassifyID(string ClassifyID)
    //根据ClassifyID来获取完形填空题详细信息
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_WXTK_selectAsClassifyId", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        SqlParameter parameterID = myCommand.Parameters.Add("@ClassifyID", SqlDbType.Int);
        parameterID.Value = ClassifyID;
        myConnection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
        DataSet ds = new DataSet();
        adapter.Fill(ds, "WXTKInfo");
        myConnection.Close();
        return ds;

    }
    #endregion

#region 添加完形填空题的完整信息
    private const string paramWXTKDetail = "WXNumber_testAns1_testAns2_testAns3_testAns4_rightAns_testScore_WXID";
    public int AddWXTKDetail(String WXNumber, String testAns1, String testAns2, String testAns3, String testAns4, int rightAns, int testScore,string WXID)
    {
        //定义数据库的Connection and Command 
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_WXTKDetail_insert", myConnection);

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

        //创建访问数据库的参数
        SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramWXTKDetail);
        if (paramCache == null)
        {
            paramCache = new SqlParameter[]{
												   new SqlParameter("@WXNumber",SqlDbType.Int,8),
												   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("@testScore",SqlDbType.Int,8),
                                                   new SqlParameter("@ID",SqlDbType.Int,8),
                                                   new SqlParameter("@WXID",SqlDbType.VarChar)};
            SQLHelper.CacheParameters(paramWXTKDetail, paramCache);
        }
        SQLHelper.AddMyCommandParams(myCommand, paramCache);
        paramCache[0].Value = WXNumber;
        paramCache[1].Value = testAns1;
        paramCache[2].Value = testAns2;
        paramCache[3].Value = testAns3;
        paramCache[4].Value = testAns4;
        paramCache[5].Value = rightAns;
        paramCache[6].Value = testScore;
        paramCache[7].Direction = ParameterDirection.ReturnValue;
        paramCache[8].Value = WXID;

        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[7].Value;
    }
    #endregion

#region 添加完形填空题的信息
    private const string paramWXTK = "WXName_IfIssue_ClassifyID_WXID";
    public int AddWXTK(String WXName, int IfIssue, string ClassifyID,string WXID)
    {
        //定义数据库的Connection and Command 
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_WXTK_insert", myConnection);

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

        //创建访问数据库的参数
        SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramWXTK);
        if (paramCache == null)
        {
            paramCache = new SqlParameter[]{
												   new SqlParameter("@WXName",SqlDbType.Text),
												   new SqlParameter("@ClassifyID",SqlDbType.Text),
                                                   new SqlParameter("@IfIssue",SqlDbType.Int,8), 
                                                   new SqlParameter("@ID",SqlDbType.Int,8),
                                                   new SqlParameter("@WXID",SqlDbType.VarChar)};
            SQLHelper.CacheParameters(paramWXTK, paramCache);
        }
        SQLHelper.AddMyCommandParams(myCommand, paramCache);
        paramCache[0].Value = WXName;
        paramCache[1].Value = ClassifyID;
        paramCache[2].Value = IfIssue;
        paramCache[3].Direction = ParameterDirection.ReturnValue;
        paramCache[4].Value = WXID;
 

        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[3].Value;
    }
    #endregion

#region 根据完形填空题ID来获取完形填空题详细信息
    public DataSet getWXTKInfoAsId(int ID)
    //根据选择题ID来获取选择题详细信息
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_WXTK_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, "WXTKInfo");
        myConnection.Close();
        return ds;

    }
    #endregion

#region 删除完形填空题记录
    public int deleteWXTKInfo(string ID)
    //删除选择题记录
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_WXTK_delete", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@ID", SqlDbType.Int).Value = ID;

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