userdb.cs

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

			}	
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = sUserName;
			paramCache[1].Value = sPassword;
			
			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("10002",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}	

		/// <summary>
		/// 判定管理员的权限，若为超级管理员，返回True;否则为false;
		/// </summary>
		public static bool IsAuthorityUser(int nUserID)
		{
			bool isAuthority = false;

			UserDB user = new UserDB();
			SqlDataReader recu = user.GetSingleUser(nUserID);

			while(recu.Read())
			{
				if(recu["RoleID"].ToString() == "0")
				{
					isAuthority = true;
					break;
				}
			}
			recu.Close();

			return(isAuthority);
		}

		/// <summary>
		/// 判定管理员的权限，若为普通管理员，返回True;否则为false;
		/// </summary>
		public static bool IsNormalAuthorityUser(int nUserID)
		{
			bool isNormalAuthority = false;

			UserDB user = new UserDB();
			SqlDataReader recu = user.GetSingleUser(nUserID);

			while(recu.Read())
			{
				if(recu["RoleID"].ToString() == "3")
				{
					isNormalAuthority = true;
					break;
				}
			}
			recu.Close();

			return(isNormalAuthority);
		}
		/// <summary>
		/// 判定教师的权限，若为教师，返回True;否则为false;
		/// </summary>
		public static bool IsTeacherUser(int nUserID)
		{
			bool isTeacher = false;

			UserDB user = new UserDB();
			SqlDataReader recu = user.GetSingleUser(nUserID);

			while(recu.Read())
			{
				if(recu["RoleID"].ToString() == "2")
				{
					isTeacher = true;
					break;
				}
			}
			recu.Close();

			return(isTeacher);
		}
		/// <summary>
		/// 判定管理的权限，若为管理权限，返回True;否则为false;
		/// </summary>
		public static bool IsAdminUser(int nUserID)
		{
			bool isAdmin = true;

			UserDB user = new UserDB();
			SqlDataReader recu = user.GetSingleUser(nUserID);

			while(recu.Read())
			{
				if(recu["RoleID"].ToString() == "1")
				{
					isAdmin = false;
					break;
				}
			}
			recu.Close();

			return(isAdmin);
		}

		/// <summary>
		/// 用户加密函数
		/// </summary>
		public static String Encrypt(string password) 
		{
			Byte[] clearBytes = new UnicodeEncoding().GetBytes(password);
			Byte[] hashedBytes = ((HashAlgorithm) CryptoConfig.CreateFromName("MD5")).ComputeHash(clearBytes);

			return BitConverter.ToString(hashedBytes);
		}		
	}

	/// <summary>
	/// Summary description for RoleDB.
	/// </summary>
	public class RoleDB
	{
		private const string paramGetSingleOrDeleteRole = "RoleID";
		private const string paramAddOrUpdateRole       = "RoleName_RoleID";
		private const string paramUpdateRole            = "UpdateRoleName_RoleID";

		public SqlDataReader GetRoles()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetRoles",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 GetSingleRole(int nRoleID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetSingleRole",myConnection);

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

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

			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 AddRole(String sRoleName)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_AddRole",myConnection);

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

			//创建访问数据库的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramAddOrUpdateRole);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{
												   new SqlParameter("@RoleID",SqlDbType.Int,4),
												   new SqlParameter("@RoleName",SqlDbType.VarChar)};
				SQLHelper.CacheParameters(paramAddOrUpdateRole,paramCache);
			}	
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Direction = ParameterDirection.ReturnValue;
			paramCache[1].Value     = sRoleName;

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

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

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

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

			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 DeleteRole(int nRoleID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteRole",myConnection);

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

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

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