userroledb.cs

本系统以采用一种结合B/S和Web的方式开发的中小企业办公自动化系统为例讨论办公自动化系统的设计与实现。

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

namespace OfficeAuto.Components
{
	/// <summary>
	/// UserRoleDB 的摘要说明。
	/// </summary>
	public class StaffDB
	{
		/// <summary>
		/// 获取所有用户基本信息
		/// </summary>		
		/// <returns>返回DataReader</returns>
		public SqlDataReader GetStaffs() 
		{
			SqlDataReader dataReader = null;
			Database data = new Database();
		
			try 
			{	
				///获取用户信息
				data.RunProc("Pr_GetStaffs",out dataReader);
				return dataReader;
			}
			catch (Exception ex) 
			{
				SystemError.SystemLog(ex.ToString());
				throw new Exception("人员信息读取出错!",ex);
			}			
		}

		public SqlDataReader GetStaffByOStatus(int nStatus)
		{
			Database db = new Database();
			SqlDataReader dr;
			SqlParameter[] prams = {
									   db.CreateInParam("@Status",SqlDbType.Int,4,nStatus)
								   };
				db.RunProc("Pr_GetStaffByStatus",prams,out dr);
			
			//
			return(dr);
		}


		/// <summary>
		/// 获取用户基本信息
		/// </summary>
		/// <param name="StaffID">用户ID</param>
		/// <returns>返回DataReader</returns>
		public SqlDataReader GetStaffInfo(int StaffID) 
		{
			SqlDataReader dataReader = null;
			Database data = new Database();
			SqlParameter[] prams = {
									   data.CreateInParam("@StaffID", SqlDbType.Int, 4, StaffID),
									   
			};
			try 
			{	
				///获取用户信息
				data.RunProc("Pr_GetStaffInfo",prams,out dataReader);
				return dataReader;
			}
			catch (Exception ex) 
			{
				SystemError.SystemLog(ex.ToString());
				throw new Exception("人员信息读取出错!",ex);
			}			
		}

		/// <summary>
		/// 人员更新
		/// </summary>
		/// <param name="StaffID">人员ID</param>
		/// <returns>返回是否成功</returns>
		public int UpdateInfo(long StaffID,string RealName,int Sex,string Birthday,string Password,string Email,string Phone,string Mobile,long PositionID,int Caste) 
		{
			Database db = new Database();	
			SqlParameter[] prams = {
									   db.CreateInParam("@StaffID",SqlDbType.Int,4,StaffID),
									   db.CreateInParam("@RealName",SqlDbType.VarChar,50,RealName),
									   db.CreateInParam("@Sex",SqlDbType.Bit ,1,Sex),	
									   db.CreateInParam("@Birthday",SqlDbType.DateTime ,8,Birthday),
									   db.CreateInParam("@Password",SqlDbType.VarChar,255,Password),
									   db.CreateInParam("@Email",SqlDbType.VarChar,500,Email),
									   db.CreateInParam("@Phone",SqlDbType.VarChar,50,Phone),						
									   db.CreateInParam("@Mobile",SqlDbType.VarChar,50,Mobile),																					   
									   db.CreateInParam("@PositionID",SqlDbType.Int,4,PositionID),
									   db.CreateInParam("@Caste",SqlDbType.Int,4,Caste)									
								   };
			return db.RunProc("Pr_UpdateStaffInfo",prams);			
		}

		public void UpdateUserPassword(int StaffID,string sPassword)
		{
			Database db = new Database();
			SqlParameter[] param = {
									   db.CreateInParam("@StaffID",SqlDbType.Int,4,StaffID),
									   db.CreateInParam("@Password",SqlDbType.VarChar,255,sPassword)
								   };
			db.RunProc("Pr_UpdateUserPassword",param);
		}

		public int AddStaff(string sStaffName,string sPassword,string sRealName,int nSex,string sEmail,
			string sPhone,string sMobile,DateTime dBirthday,int nPositionID)
		{
			int nStaffId = 0;
			Database db = new Database();
			SqlParameter[] param = {
									   db.CreateInParam("@StaffName",SqlDbType.VarChar,300,sStaffName),
									   db.CreateInParam("@Password",SqlDbType.VarChar,300,sPassword),
									   db.CreateInParam("@RealName",SqlDbType.VarChar,300,sRealName),
									   db.CreateInParam("@Sex",SqlDbType.Int,4,nSex),
									   db.CreateInParam("@Email",SqlDbType.VarChar,300,sEmail),
									   db.CreateInParam("@Phone",SqlDbType.VarChar,50,sPhone),
									   db.CreateInParam("@Mobile",SqlDbType.VarChar,50,sMobile),
									   db.CreateInParam("@Birthday",SqlDbType.DateTime,8,dBirthday),
									   db.CreateInParam("@PositionID",SqlDbType.Int,4,nPositionID)
								   };
			nStaffId = db.RunProc("Pr_AddStaff",param);

			///返回添加新员工的ID
			return(nStaffId);
		}

		/// <summary>
		/// 获取用户基本信息
		/// </summary>
		/// <param name="StaffID">用户ID</param>
		/// <returns>返回DataReader</returns>
		public void DeleteStaff(int StaffID) 
		{		
			Database data = new Database();
			SqlParameter[] prams = {
									   data.CreateInParam("@StaffID", SqlDbType.Int, 4, StaffID),
									   
			};
			try 
			{	
				///获取用户信息
				data.RunProc("Pr_DeleteStaff",prams);
			}
			catch (Exception ex) 
			{
				SystemError.SystemLog(ex.ToString());
				throw new Exception("人员信息读取出错!",ex);
			}			
		}

		
		/// <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 class RoleDB
	{
		/// <summary>
		/// 获取所有角色
		/// </summary>
		/// <returns></returns>
		public SqlDataReader GetRoles()
		{
			Database db = new Database();
			SqlDataReader dr = null;
			db.RunProc("Pr_GetRoles",out dr);
			return(dr);
		}

		/// <summary>
		/// 获取单个角色的信息
		/// </summary>
		/// <param name="nRoleID"></param>
		/// <returns></returns>
		public SqlDataReader GetSingleRole(int nRoleID)
		{
			Database db = new Database();
			SqlDataReader dr = null;

			SqlParameter[] param = {
									 db.CreateInParam("@RoleID",SqlDbType.Int,4,nRoleID)
								 };
			db.RunProc("Pr_GetSingleRole",param,out dr);
			return(dr);
		}

		/// <summary>
		/// 添加角色
		/// </summary>
		/// <param name="sRoleName"></param>
		/// <returns></returns>
		public int AddRole(string sRoleName)
		{
			Database db = new Database();
			SqlParameter[] param = {
									   db.CreateInParam("@RoleName",SqlDbType.VarChar,50,sRoleName)
								   };
			return(db.RunProc("Pr_AddRole",param));
		}

		/// <summary>
		/// 更新角色
		/// </summary>
		/// <param name="nRoleID"></param>
		/// <param name="sRoleName"></param>
		public void UpdateRole(int nRoleID,string sRoleName)
		{
			Database db = new Database();
			SqlParameter[] param = {
									   db.CreateInParam("@RoleID",SqlDbType.Int,4,nRoleID),
									   db.CreateInParam("@RoleName",SqlDbType.VarChar,50,sRoleName)
								   };
			db.RunProc("Pr_UpdateRole",param);
		}

		/// <summary>
		/// 删除角色
		/// </summary>
		/// <param name="nRoleID"></param>
		public void DeleteRole(int nRoleID)
		{
			Database db = new Database();
			SqlParameter[] param = {
									   db.CreateInParam("@RoleID",SqlDbType.Int,4,nRoleID)
								   };
			db.RunProc("Pr_DeleteRole",param);
		}
	}

	public class UserRoleDB
	{
		/// <summary>
		/// 添加用户和角色之间的对应关系
		/// </summary>
		/// <param name="nUserID">用户ID</param>
		/// <param name="nRoleID">角色ID</param>
		public void AddUserRole(int nUserID,int nRoleID)
		{
			Database db = new Database();
			SqlParameter[] param = {
									   db.CreateInParam("@RoleID",SqlDbType.Int,4,nRoleID),
									   db.CreateInParam("@UserID",SqlDbType.Int,4,nUserID)
								   };
			db.RunProc("Pr_AddUserRole",param);
		}

		/// <summary>
		/// 删除用户和角色之间的对应关系
		/// </summary>
		/// <param name="nUserID">用户ID</param>
		/// <param name="nRoleID">角色ID</param>
		public void DeleteUserRole(int nUserID,int nRoleID)
		{
			Database db = new Database();
			SqlParameter[] param = {
									   db.CreateInParam("@RoleID",SqlDbType.Int,4,nRoleID),
									   db.CreateInParam("@UserID",SqlDbType.Int,4,nUserID)
								   };
			db.RunProc("Pr_DeleteUserRole",param);
		}

		/// <summary>
		/// 根据用户获取该用户所拥有的角色
		/// </summary>
		/// <param name="nUserID">用户ID</param>
		/// <returns>DataReader对象</returns>
		public SqlDataReader GetRoleByUser(int nUserID)
		{
			Database db = new Database();
			SqlDataReader dr = null;

			SqlParameter[] param = {
									   db.CreateInParam("@UserID",SqlDbType.Int,4,nUserID)
								   };
			db.RunProc("Pr_GetRoleByUser",param,out dr);
			return(dr);
		}
	}
}