📄 userdb.cs

📁 计算机学院网站及管理系统
💻 CS
字号:
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Security;
using System.Security.Cryptography;
using System.Text;

namespace ComputerWeb
{
	/// <summary>
	/// Summary description for UserDB.
	/// </summary>
	public class UserDB
	{
		public SqlDataReader GetUserLogin(String sUserName,String sPassword)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetUserLogin",myConnection);

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

            //创建访问数据库的参数
			SqlParameter parameterUserName = new SqlParameter("@UserName",SqlDbType.VarChar,50);
			parameterUserName.Value = sUserName;
			myCommand.Parameters.Add(parameterUserName);

			SqlParameter parameterPassword = new SqlParameter("@Password",SqlDbType.VarChar,50);
			parameterPassword.Value = sPassword;
			myCommand.Parameters.Add(parameterPassword);
			
			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;
		}	
	
		public SqlDataReader GetUsers()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			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("10002",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}
		
		public SqlDataReader GetSingleUser(int nUserID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetSingleUser",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterUserID = new SqlParameter("@ID",SqlDbType.Int,4);
			parameterUserID.Value = nUserID;
			myCommand.Parameters.Add(parameterUserID);

			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;
		}	
	
		public int AddUser(String sUserName,String sPassword,int nRole,int nIsValid)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_AddUser",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterUserName = new SqlParameter("@UserName",SqlDbType.VarChar,50);
			parameterUserName.Value = sUserName;
			myCommand.Parameters.Add(parameterUserName);

			SqlParameter parameterPassword = new SqlParameter("@Password",SqlDbType.VarChar,50);
			parameterPassword.Value = sPassword;
			myCommand.Parameters.Add(parameterPassword);

			SqlParameter parameterRole = new SqlParameter("@Role",SqlDbType.Int,4);
			parameterRole.Value = nRole;
			myCommand.Parameters.Add(parameterRole);

			SqlParameter parameterIsValid = new SqlParameter("@IsValid",SqlDbType.Int,4);
			parameterIsValid.Value = nIsValid;
			myCommand.Parameters.Add(parameterIsValid);

			SqlParameter parameterID = new SqlParameter("@ID",SqlDbType.Int,4);
			parameterID.Direction = ParameterDirection.ReturnValue;
			myCommand.Parameters.Add(parameterID);

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

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

			return (int)parameterID.Value;
		}

		public void UpdateUserState(int nID,int nIsValid)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateUserState",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterID = new SqlParameter("@ID",SqlDbType.Int,4);
			parameterID.Value = nID;
			myCommand.Parameters.Add(parameterID);

			SqlParameter parameterIsValid = new SqlParameter("@IsValid",SqlDbType.Int,4);
			parameterIsValid.Value = nIsValid;
			myCommand.Parameters.Add(parameterIsValid);		

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

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

		public void DeleteUser(int nID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteUser",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterID = new SqlParameter("@ID",SqlDbType.Int,4);
			parameterID.Value = nID;
			myCommand.Parameters.Add(parameterID);

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

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

		public void UpdatePassword(int nID,String sPassword)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdatePassword",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterID = new SqlParameter("@ID",SqlDbType.Int,4);
			parameterID.Value = nID;
			myCommand.Parameters.Add(parameterID);

			SqlParameter parameterPassword = new SqlParameter("@Password",SqlDbType.VarChar,50);
			parameterPassword.Value = sPassword;
			myCommand.Parameters.Add(parameterPassword);		

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

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

		/// <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>
		public static bool IsValidAdmin(int nUserID)
		{
			bool isValid = false;

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

			while(recu.Read())
			{
				if((Int32.Parse(recu["Role"].ToString()) == 1)&&(Int32.Parse(recu["IsValid"].ToString())==1) )
				{
					isValid = true;
					break;
				}
			}
			recu.Close();

			return(isValid);
		}

		/// <summary>
		/// 判断用户是不是一般的管理员
		/// </summary>
		public static bool IsValidChannel(int nUserID)
		{
			bool isValid = false;

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

			while(recu.Read())
			{
				if((Int32.Parse(recu["Role"].ToString()) == 2)&&(Int32.Parse(recu["IsValid"].ToString())==1))
				{
					isValid = true;
					break;
				}
			}
			recu.Close();

			return(isValid);
		}
	}
}
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -