userdb.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 Service
{
	/// <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 void UpdateUserPassword(int nUserID,String sPassword,String sRealName,
			String sTelephone,String sAddress)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateUserPassword",myConnection);

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

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

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

			SqlParameter parameterRealName = new SqlParameter("@RealName",SqlDbType.VarChar,32);
			parameterRealName.Value = sRealName;
			myCommand.Parameters.Add(parameterRealName);

			SqlParameter parameterTelephone = new SqlParameter("@Telephone",SqlDbType.VarChar,50);
			parameterTelephone.Value = sTelephone;
			myCommand.Parameters.Add(parameterTelephone);

			SqlParameter parameterAddress = new SqlParameter("@Address",SqlDbType.VarChar,200);
			parameterAddress.Value = sAddress;
			myCommand.Parameters.Add(parameterAddress);
			
			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 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("@UserID",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,String sRealName,String sTelephone,
			String sAddress)
		{
			//定义数据库的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 parameterRealName = new SqlParameter("@RealName",SqlDbType.VarChar,32);
			parameterRealName.Value = sRealName;
			myCommand.Parameters.Add(parameterRealName);

			SqlParameter parameterTelephone = new SqlParameter("@Telephone",SqlDbType.VarChar,50);
			parameterTelephone.Value = sTelephone;
			myCommand.Parameters.Add(parameterTelephone);

			SqlParameter parameterAddress = new SqlParameter("@Address",SqlDbType.VarChar,200);
			parameterAddress.Value = sAddress;
			myCommand.Parameters.Add(parameterAddress);

			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 UpdateUserRole(int nUserID,int nRole)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateUserRole",myConnection);

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

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

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

			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 nUserID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteUser",myConnection);

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

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

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

		public static String GetUserLoginRole(int nUserID)
		{
			String RoleName = "Normal";

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

			while(recu.Read())
			{
				if(recu["RepairState"].ToString() == "1" && recu["MarkTable"].ToString() == "0")
				{
					RoleName = "ValidRepair";
				}

				if(recu["RepairState"].ToString() == "0" && recu["MarkTable"].ToString() == "1")
				{					
					RoleName = "ValidMark";
				}

				if(recu["RepairState"].ToString() == "1" && recu["MarkTable"].ToString() == "1")
				{
					//领导的权限
					RoleName = "ValidRepairMark";
				}
                
				SqlDataReader recr = role.GetDirecrtionByRole(Int32.Parse(recu["RoleID"].ToString()));

				if(recr.HasRows == true)
				{			
					//包含监控权限
					RoleName += "Monitor";
				}
				recr.Close();
			}

			recu.Close();

			if(nUserID == 2)
			{
				//系统管理员
				RoleName = "ValidAdmin";
			}

			if(nUserID == 1)
			{
				//超级管理员
				RoleName = "ValidRepairMarkMonitorAdmin";
			}

			return(RoleName);		
		}
	}

	public class RoleDB
	{
		public SqlDataReader GetRoles()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			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(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetSingleRole",myConnection);

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

			//添加储存过程的参数
			SqlParameter parameterRoleID = new SqlParameter("@RoleID",SqlDbType.Int,4);
			parameterRoleID.Value = nRoleID;
			myCommand.Parameters.Add(parameterRoleID);

			SqlDataReader dr = null;

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