userdb.cs

用ASP.NET（C#）、SQL Server2000数据库开发在线考试系统源代码

using System;
using System.Data;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Security.Cryptography;

namespace ExamineSystem
{
	/// <summary>
	/// Summary description for UserDB.
	/// </summary>
	public class UserDB
	{
		private const string paramGetSingleOrDeleteUser = "UserID";
		private const string paramGetUserLogin          = "UserName_Password";
		private const string paramAddUser               = "UserName_Password_RoleID_State_UserID";
		private const string paramUpdateUserRole        = "UserID_RoleID";
		private const string paramUpdateUserPassword    = "UserID_Password";
		private const string paramUpdateUserState       = "UserID_State";

		public SqlDataReader GetUsers()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetUsers",myConnection);

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

			SqlDataReader dr = null;

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

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

			//返回 dr
			return dr;
		}

		public SqlDataReader GetSingleUser(int nUserID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetSingleUser",myConnection);

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

			//添加储存过程的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramGetSingleOrDeleteUser);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{
												   new SqlParameter("@UserID",SqlDbType.Int,4)};
				SQLHelper.CacheParameters(paramGetSingleOrDeleteUser,paramCache);
			}	
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = nUserID;

			SqlDataReader dr = null;

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

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

			//返回 dr
			return dr;
		}

		public int AddUser(String sUserName,String sPassword,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("@UserName",SqlDbType.VarChar),
												   new SqlParameter("@Password",SqlDbType.VarChar),
												   new SqlParameter("@Email",SqlDbType.VarChar),
												   new SqlParameter("@UserID",SqlDbType.Int,4)};
				SQLHelper.CacheParameters(paramAddUser,paramCache);
			}	
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = sUserName;
			paramCache[1].Value = sPassword;
			paramCache[2].Value = sEmail;
			paramCache[3].Direction = ParameterDirection.ReturnValue;

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

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

			return (int)paramCache[3].Value;
		}

		public void UpdateUserPassword(int nUserID,String sPassword)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_UpdatePassword",myConnection);

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

			//创建访问数据库的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramUpdateUserPassword);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{
												   new SqlParameter("@UserID",SqlDbType.Int,4),
												   new SqlParameter("@Password",SqlDbType.VarChar)};
				SQLHelper.CacheParameters(paramUpdateUserPassword,paramCache);
			}	
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = nUserID;
			paramCache[1].Value = sPassword;

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

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

		public void UpdateUserState(int nUserID,int nState)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateUserState",myConnection);

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

			//创建访问数据库的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramUpdateUserState);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{
												   new SqlParameter("@UserID",SqlDbType.Int,4),
												   new SqlParameter("@State",SqlDbType.Int)};
				SQLHelper.CacheParameters(paramUpdateUserState,paramCache);
			}	
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = nUserID;
			paramCache[1].Value = nState;

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

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

		public void UpdateUserRole(int nUserID,int nRoleID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateUserRole",myConnection);

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

			//创建访问数据库的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramUpdateUserRole);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{
												   new SqlParameter("@UserID",SqlDbType.Int,4),
												   new SqlParameter("@RoleID",SqlDbType.Int)};
				SQLHelper.CacheParameters(paramUpdateUserRole,paramCache);
			}	
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = nUserID;
			paramCache[1].Value = nRoleID;

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

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

		public void DeleteUser(int nUserID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteUser",myConnection);

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

			//创建访问数据库的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramGetSingleOrDeleteUser);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{
												   new SqlParameter("@UserID",SqlDbType.Int,4)};
				SQLHelper.CacheParameters(paramGetSingleOrDeleteUser,paramCache);
			}	
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = nUserID;

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

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

		public SqlDataReader GetUserLogin(String sUserName,String sPassword)
		{
			//定义数据库的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("@UserName",SqlDbType.VarChar),
												   new SqlParameter("@Password",SqlDbType.VarChar)};
				SQLHelper.CacheParameters(paramGetUserLogin,paramCache);