userdb.cs

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

            myConnection.Close();
        }


    }
    #endregion

#region 获取分类详细信息
    public DataSet getClassifyInfoAsId(int ID)
    //获取分类详细信息
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Classify_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, "ClassifyInfo");
        myConnection.Close();
        return ds;
    }
    #endregion

#region 更新分类的信息,返回1表示更新成功,返回0则更新失败
    public int updateClassifyInfo(string ID, string classifyId, string classifyName)
    //更新分类的信息,返回1表示更新成功,返回0则更新失败
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_classifyInfo_update", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("ID", SqlDbType.Int).Value = ID;
        myCommand.Parameters.Add("@ClassifyID", SqlDbType.Int).Value = classifyId;
        myCommand.Parameters.Add("@ClassifyName", SqlDbType.VarChar, 50).Value = classifyName;
        
        //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 根据ID来获取角色详细信息
    public DataSet getRoleInfoAsId(int ID)
    //根据ID来获取角色详细信息
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Role_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, "RoleInfo");
        myConnection.Close();
        return ds;

    }
    #endregion

#region 更新角色的信息,返回1表示更新成功,返回0则更新失败
    public int updateRoleInfo(string ID, string roleId, string roleName)
    //更新角色的的信息,返回1表示更新成功,返回0则更新失败
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_roleInfo_update", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("ID", SqlDbType.Int).Value = ID;
        myCommand.Parameters.Add("@RoleID", SqlDbType.Int).Value = roleId;
        myCommand.Parameters.Add("@RoleName", SqlDbType.VarChar, 50).Value = roleName;
        //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 取得考生的信息
    public DataSet getStudentAllInfo()
    //取得考生的信息
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_student_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, "StudentInfo");
        myConnection.Close();
        return ds;
    }
#endregion

#region 在Users表中删除学生记录,成功返回1,失败返回0
    public int deleteStudentInfo(string UserID)
    //在Users表中删除学生记录,成功返回1,失败返回0
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Student_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 ifStuExist(string UserID)
    //判断是否存在此学生!
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_student_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 取得试题分类的信息
    public DataSet getClassifyAllInfo()
    //取得试题分类的信息
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Classify_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, "ClassifyInfo");
        myConnection.Close();
        return ds;
    }
#endregion

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

#region 添加试题分类
    private const string paramAddClassify = "ClassifyID_ClassifyName";
    public int AddClassify(String ClassifyID, String ClassifyName)
    {
        //定义数据库的Connection and Command 
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_AddClassify", myConnection);

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

        //创建访问数据库的参数
        SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramAddClassify);
        if (paramCache == null)
        {
            paramCache = new SqlParameter[]{
												   new SqlParameter("@ClassifyID",SqlDbType.VarChar),
												   new SqlParameter("@ClassifyName",SqlDbType.VarChar),
                                                   new SqlParameter("@ID",SqlDbType.Int,8)};
            SQLHelper.CacheParameters(paramAddClassify, paramCache);
        }
        SQLHelper.AddMyCommandParams(myCommand, paramCache);
        paramCache[0].Value = ClassifyID;
        paramCache[1].Value = ClassifyName;
        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 在Classify表中删除试题分类记录,成功返回1,失败返回0
    public int deleteClassifyInfo(string ClassifyID)
    //在Classify表中删除试题分类记录,成功返回1,失败返回0
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Classify_delete", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@ClassifyID", SqlDbType.VarChar, 50).Value = ClassifyID;

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

        }
        finally
        {
            myConnection.Close();
        }

    }
    #endregion

#region 取得角色的信息
    public DataSet getRoleAllInfo()
    //取得角色的信息
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Role_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, "RoleInfo");
        myConnection.Close();
        return ds;
    }
#endregion

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

#region 添加角色
    private const string paramAddRole = "RoleID_RoleName";
    public int AddRole(String RoleID, String RoleName)
    {
        //定义数据库的Connection and Command 
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_AddRole", myConnection);

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

        //创建访问数据库的参数
        SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramAddRole);