userdb.cs

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

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Data.SqlClient;
using System.Security.Cryptography;

/// <summary>
/// UserDB 的摘要说明
/// </summary>
public class UserDB
{
#region 验证用户 
    private const string paramGetUserLogin = "UserID_UserPassword_RoleID";
    public SqlDataReader GetUserLogin(String sUserID, String sPassword, String sRoleID)
    {
        //定义数据库的Connection and Command 
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_GetUserLogin", myConnection);

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

        //创建访问数据库的参数
        SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramGetUserLogin);
        if (paramCache == null)
        {
            paramCache = new SqlParameter[]{
												   new SqlParameter("@UserID",SqlDbType.VarChar),
												   new SqlParameter("@UserPassword",SqlDbType.VarChar),
                                                   new SqlParameter("@RoleID",SqlDbType.VarChar)};
            SQLHelper.CacheParameters(paramGetUserLogin, paramCache);
        }
        SQLHelper.AddMyCommandParams(myCommand, paramCache);
        paramCache[0].Value = sUserID;
        paramCache[1].Value = sPassword;
        paramCache[2].Value = sRoleID;

        SqlDataReader dr = null;

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

        try
        {
            //执行数据库的存储过程（访问数据库）
            dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            throw new GlobalDB.MyException("10002", ex.Message, ex);
        }

        //返回 dr
        return dr;
    }
#endregion

#region 添加用户
    private const string paramAddUser = "UserID_UserName_UserPassword_Email_RoleID";
    public int AddUser(String sUserID, String sUserName, String sUserPassword, String sEmail)
    {
        //定义数据库的Connection and Command 
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_AddUser", myConnection);

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

        //创建访问数据库的参数
        SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramAddUser);
        if (paramCache == null)
        {
            paramCache = new SqlParameter[]{
												   new SqlParameter("@UserID",SqlDbType.Int,8),
												   new SqlParameter("@UserName",SqlDbType.VarChar),
												   new SqlParameter("@UserPassword",SqlDbType.VarChar),
                                                   new SqlParameter("@Email",SqlDbType.VarChar),
                                                   new SqlParameter("@ID",SqlDbType.Int,8)};
            SQLHelper.CacheParameters(paramAddUser, 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;

        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 根据学生ID来获取学生详细信息
    public DataSet getStudentInfoAsId(int ID)
    //根据学生ID来获取学生详细信息
    {
         SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_Student_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, "StudentInfo");
        myConnection.Close();
        return ds;

    }
    #endregion

#region 判断教师的用户名和密码是否正确
    public int getTeacher(string teacherId, string teacherPwd)
    //判断教师的用户名和密码是否正确
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_teacher_select", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = teacherId;
        myCommand.Parameters.Add("@UserPassword", SqlDbType.VarChar, 50).Value = teacherPwd;
        myConnection.Open();
        SqlDataReader thisReader = myCommand.ExecuteReader();

        int count = 0;
        if (thisReader.Read())
        {
            count = 1;

        }

        thisReader.Close();
        myConnection.Close();
        return count;

    }
    #endregion

#region 更新教师的密码,返回1表示更新成功,返回0则更新失败
    public int updateTeacherPwd(string teacherId, string teacherPwd)
    //更新教师的密码,返回1表示更新成功,返回0则更新失败
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_teacher_update", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = teacherId;
        myCommand.Parameters.Add("@UserPassword", SqlDbType.VarChar, 50).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();
        }


    }
#endregion

#region 判断管理员的用户名和密码是否正确
    public int getAdmin(string adminId, string adminPwd)
    //判断管理员的用户名和密码是否正确

    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_administrator_select", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = adminId;
        myCommand.Parameters.Add("@UserPassword", SqlDbType.VarChar, 50).Value = adminPwd;
        myConnection.Open();
        SqlDataReader thisReader = myCommand.ExecuteReader();

        int count = 0;
        if (thisReader.Read())
        {
            count = 1;

        }

        thisReader.Close();
        myConnection.Close();
        return count;

    }
    #endregion

#region 更新管理员的的密码,返回1表示更新成功,返回0则更新失败
    public int updateAdminPwd(string adminId, string adminPwd)
    //更新管理员的的密码,返回1表示更新成功,返回0则更新失败
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_admin_update", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = adminId;
        myCommand.Parameters.Add("@UserPassword", SqlDbType.VarChar, 50).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();
        }


    }
    #endregion

#region 判断学生的用户名和密码是否正确
    public int getStudent(string studentId, string studentPwd)
    //判断学生的用户名和密码是否正确
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_student_select", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = studentId;
        myCommand.Parameters.Add("@UserPassword", SqlDbType.VarChar, 50).Value = studentPwd;
        myConnection.Open();
        SqlDataReader thisReader = myCommand.ExecuteReader();

        int count = 0;
        if (thisReader.Read())
        {
            count = 1;

        }

        thisReader.Close();
        myConnection.Close();
        return count;

    }
#endregion

#region 更新学生的密码,返回1表示更新成功,返回0则更新失败
    public int updateStudentPwd(string studentId, string studentPwd)
    //更新学生的的密码,返回1表示更新成功,返回0则更新失败
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_student_update", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = studentId;
        myCommand.Parameters.Add("@UserPassword", SqlDbType.VarChar, 50).Value = studentPwd;

        //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 更新学生的信息,返回1表示更新成功,返回0则更新失败
    public int updateStudentInfo(string ID, string studentId, string studentName, string studentPassword, string Email)
    //更新学生的的信息,返回1表示更新成功,返回0则更新失败
    {
        SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
        SqlCommand myCommand = new SqlCommand("Pr_studentInfo_update", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("ID", SqlDbType.Int).Value = ID;
        myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = studentId;
        myCommand.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = studentName;
        myCommand.Parameters.Add("@UserPassword",SqlDbType.VarChar, 50).Value = studentPassword;
        myCommand.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = Email;

        //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
        {