user.cs

企业内部信息交流系统

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace infoWeb.WebModules.Accounts.Data
{	
	/// <summary>
	/// Summary description for User.
	/// </summary>	
	public class User : infoWeb.WebModules.Data.DbObject
	{
		public User(string newConnectionString): base(newConnectionString)
		{	}

		public int Create(string emailAddress,
			byte[] password,
			string firstName,
			string lastName,
			string address1,
			string address2,
			string city,
			string state,
			string zipCode,
			string homePhone,
			string country)
		{
			int rowsAffected;
			SqlParameter[] 
				parameters = {
								 new SqlParameter("@EmailAddress", SqlDbType.VarChar, 255),
								 new SqlParameter("@Password", SqlDbType.Binary, 20),
								 new SqlParameter("@FirstName", SqlDbType.VarChar, 30),
								 new SqlParameter("@LastName", SqlDbType.VarChar, 50),
								 new SqlParameter("@Address1", SqlDbType.VarChar, 80),
								 new SqlParameter("@Address2", SqlDbType.VarChar, 80),
								 new SqlParameter("@City", SqlDbType.VarChar, 40),
								 new SqlParameter("@State", SqlDbType.VarChar, 2),								 
								 new SqlParameter("@ZipCode", SqlDbType.VarChar, 10),
								 new SqlParameter("@HomePhone", SqlDbType.VarChar, 14),
								 new SqlParameter("@Country", SqlDbType.VarChar, 50),
								 new SqlParameter("@UserID", SqlDbType.Int, 4)
							 };

			parameters[0].Value = emailAddress;
			parameters[1].Value = password;
			parameters[2].Value = firstName;
			parameters[3].Value = lastName;
			parameters[4].Value = address1;
			parameters[5].Value = address2;
			parameters[6].Value = city;
			parameters[7].Value = state;
			parameters[8].Value = zipCode;
			parameters[9].Value = homePhone;
			parameters[10].Value = country;
			parameters[11].Direction = ParameterDirection.Output;
	
			try 
			{
				RunProcedure("sp_Accounts_CreateUser", parameters, out rowsAffected);
			}
			catch ( SqlException e )
			{
				// 2601 is the number returned when the primary key
				// is violated. We know if we're violating the primary
				// key that the e-mail address is already on file.
				// Its cheaper than actually searching for that address before
				// inserting.
				if (e.Number == 2601) 
				{
					return (int)infoWeb.WebModules.Accounts.ProcResultCodes.AccountAlreadyOnFile;
				}
				else 
				{
					throw new AppException("An error occurred while executing the Accounts_CreateUser stored procedure",
						e );
				}
			}

			return (int)parameters[11].Value;
		}

		public DataRow Retrieve(int userID)
		{
			SqlParameter[] parameters = { new SqlParameter("@UserID", SqlDbType.Int, 4) };
			parameters[0].Value = userID;

			using (DataSet users = RunProcedure( "sp_Accounts_GetUserDetails", parameters, "Users" ))
			{
				return users.Tables[0].Rows[0];
			}			
		}

		public DataRow Retrieve(string emailAddress)
		{
			SqlParameter[] parameters = { new SqlParameter("@EmailAddress", SqlDbType.VarChar, 255) };
			parameters[0].Value = emailAddress;

			using (DataSet users = RunProcedure( "sp_Accounts_GetUserDetailsByEmail", parameters, "Users" ))
			{
				if (users.Tables[0].Rows.Count == 0)
				{
					throw new AppException("No user found on file for email:" + emailAddress);
				}
				else
					return users.Tables[0].Rows[0];
			}
		}

		public bool Update(int userID,
			string emailAddress,
			byte[] password,
			string firstName,
			string lastName,
			string address1,
			string address2,
			string city,
			string state,
			string zipCode,
			string homePhone,
			string country)
		{
			int rowsAffected;
			SqlParameter[] parameters = {
				new SqlParameter("@EmailAddress", SqlDbType.VarChar, 255),
				new SqlParameter("@Password", SqlDbType.Binary, 20),
				new SqlParameter("@FirstName", SqlDbType.VarChar, 30),
				new SqlParameter("@LastName", SqlDbType.VarChar, 50),
				new SqlParameter("@Address1", SqlDbType.VarChar, 80),
				new SqlParameter("@Address2", SqlDbType.VarChar, 80),
				new SqlParameter("@City", SqlDbType.VarChar, 40),
				new SqlParameter("@State", SqlDbType.VarChar, 2),				
				new SqlParameter("@ZipCode", SqlDbType.VarChar, 10),
				new SqlParameter("@HomePhone", SqlDbType.VarChar, 14),
				new SqlParameter("@Country", SqlDbType.VarChar, 50),
				new SqlParameter("@UserID", SqlDbType.Int, 4)
			};

			parameters[0].Value = emailAddress;
			parameters[1].Value = password;
			parameters[2].Value = firstName;
			parameters[3].Value = lastName;
			parameters[4].Value = address1;
			parameters[5].Value = address2;
			parameters[6].Value = city;
			parameters[7].Value = state;
			parameters[8].Value = zipCode;
			parameters[9].Value = homePhone;
			parameters[10].Value = country;
			parameters[11].Value = userID;

			RunProcedure("sp_Accounts_UpdateUser", parameters, out rowsAffected);
			return (rowsAffected == 1);
		}

		public bool Delete(int userID)
		{
			SqlParameter[] parameters = { new SqlParameter("@UserID", SqlDbType.Int, 4) };
			int rowsAffected;

			parameters[0].Value = userID;

			RunProcedure( "sp_Accounts_DeleteUser", parameters, out rowsAffected );
			return (rowsAffected == 1);
		}

		public int ValidateLogin(string emailAddress, byte[] encPassword)
		{
			int rowsAffected;
			SqlParameter[] parameters = 
			  {
				  new SqlParameter("@EmailAddress", SqlDbType.VarChar, 255),
				  new SqlParameter("@EncryptedPassword", SqlDbType.Binary, 20)
			  };

			parameters[0].Value = emailAddress;
			parameters[1].Value = encPassword;

			return RunProcedure("sp_Accounts_ValidateLogin", parameters, out rowsAffected);
		}

		public int TestPassword(int userID, byte[] encPassword)
		{
			int rowsAffected;
			SqlParameter[] parameters = 
			{
				new SqlParameter("@UserID", SqlDbType.Int, 4),
				new SqlParameter("@EncryptedPassword", SqlDbType.Binary, 20)
			};

			parameters[0].Value = userID;
			parameters[1].Value = encPassword;

			return RunProcedure("sp_Accounts_TestPassword", parameters, out rowsAffected);
		}

		public ArrayList GetUserRoles( int userID )
		{
			ArrayList roles = new ArrayList();
			SqlParameter[] parameters = { new SqlParameter("@UserID", SqlDbType.Int, 4) };
			parameters[0].Value = userID;

			SqlDataReader tmpReader = RunProcedure("sp_Accounts_GetUserRoles", parameters);
			while (tmpReader.Read())
			{
				roles.Add( tmpReader.GetString(1) );
			}			
			Connection.Close();
			return roles;
		}

		public ArrayList GetEffectivePermissionList( int userID )
		{
			ArrayList permissions = new ArrayList();
			SqlParameter[] parameters = { new SqlParameter("@UserID", SqlDbType.Int, 4) };
			parameters[0].Value = userID;

			SqlDataReader tmpReader = RunProcedure("sp_Accounts_GetEffectivePermissionList", parameters);
			while (tmpReader.Read())
			{
				permissions.Add( tmpReader.GetInt32(0) );
			}			
			Connection.Close();
			return permissions;
		}

		public DataSet GetUserList()
		{
			return RunProcedure("sp_Accounts_GetUsers", new IDataParameter[]{}, "Users");			
		}

		public bool AddRole(int userId, int roleId)
		{
			int rowsAffected;
			SqlParameter[] parameters = {
				new SqlParameter("@UserID", SqlDbType.Int, 4),
				new SqlParameter("@RoleID", SqlDbType.Int, 4)
			};
			parameters[0].Value = userId;
			parameters[1].Value = roleId;

			RunProcedure("sp_Accounts_AddUserToRole", parameters, out rowsAffected);
			return (rowsAffected == 1);
		}

		public bool RemoveRole(int userId, int roleId)
		{
			int rowsAffected;
			SqlParameter[] parameters = {
				new SqlParameter("@UserID", SqlDbType.Int, 4),
				new SqlParameter("@RoleID", SqlDbType.Int,4 )
			};
			parameters[0].Value = userId;
			parameters[1].Value = roleId;

			RunProcedure("sp_Accounts_RemoveUserFromRole", parameters, out rowsAffected);
			return (rowsAffected == 1);
		}

	}
}