/// <summary>
/// Gets the user based on User ID.
/// </summary>
/// <param name="userId">The User ID to be loaded.</param>
/// <param name="emailAddress">The email address.</param>
/// <param name="password">The password.</param>
/// <param name="status">The status.</param>
/// <returns>Object of <see cref="User" />.</returns>
public User GetUser(int? userId, string emailAddress, string password, Enums.Status status)
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.StoreProcedure.usp_GetUser, cn, true))
{
SqlParameter paramUserId = new SqlParameter(DbConstant.Parameter.Id, userId);
cmd.Parameters.Add(paramUserId);
SqlParameter paramEmailAddress = new SqlParameter(DbConstant.Parameter.Email, emailAddress);
cmd.Parameters.Add(paramEmailAddress);
SqlParameter paramPassword = new SqlParameter(DbConstant.Parameter.Password, password);
cmd.Parameters.Add(paramPassword);
if (status != Enums.Status.NotDefined)
{
SqlParameter paramStatus = new SqlParameter(DbConstant.Parameter.Status, status);
cmd.Parameters.Add(paramStatus);
}
User user = null;
cn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (reader.HasRows)
{
if (reader.Read())
{
user = new User();
user.Id = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.Id]);
user.FirstName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.FirstName]);
user.LastName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.LastName]);
user.Status = (Enums.Status)TypeConversionUtility.ToByte(reader[DbConstant.DbColumn.Status]);
user.UserType = (Enums.UserType)TypeConversionUtility.ToByte(reader[DbConstant.DbColumn.UserType]);
user.Email = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.Email]);
user.Password = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.Password]);
user.ProfileImage = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.ProfileImage]);
user.PhoneNumber = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.PhoneNumber]);
user.SchoolName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.SchoolName]);
user.DateCreated = TypeConversionUtility.ToDateTime(reader[DbConstant.DbColumn.DateCreated]);
user.PromoCode = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.PromoCode]);
user.SYId = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.SYId]);
user.IsGiftFree = TypeConversionUtility.ToBoolean(reader[DbConstant.DbColumn.IsGiftFree]);
user.IsTaxExempt = TypeConversionUtility.ToBoolean(reader[DbConstant.DbColumn.IsTaxExempt]);
user.RoleId = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.RoleId]);
}
}
}
return user;
}
}
}
/// <summary>
/// Get the Users Paged.
/// </summary>
/// <param name="name"></param>
/// <param name="startPage">Page number at which to start reading.</param>
/// <param name="pageLength">Number of records to be displayed per page.</param>
/// <param name="noOfPages">Out parameter which returns the number of pages.</param>
/// <param name="totalRecords">Out parameter which returns the total records.</param>
/// <param name="email"></param>
/// <returns>Collection of User.</returns>
public IList<User> GetUsers(string email, string name, int startPage, int pageLength, out int noOfPages, out int totalRecords)
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.StoreProcedure.usp_GetUsersPaged, cn, true))
{
SqlParameter paramName = new SqlParameter(DbConstant.Parameter.Name, name);
cmd.Parameters.Add(paramName);
SqlParameter paramEmailAddress = new SqlParameter(DbConstant.Parameter.Email, email);
cmd.Parameters.Add(paramEmailAddress);
SqlParameter parStart = new SqlParameter(DbConstant.Parameter.StartPage, startPage);
cmd.Parameters.Add(parStart);
SqlParameter parPageLength = new SqlParameter(DbConstant.Parameter.PageLength, pageLength);
cmd.Parameters.Add(parPageLength);
SqlParameter parNoOfPage = new SqlParameter(DbConstant.Parameter.NoOfPages, noOfPages = 0);
parNoOfPage.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parNoOfPage);
SqlParameter partotalRecords = new SqlParameter(DbConstant.Parameter.TotalRecords, totalRecords = 0);
partotalRecords.Direction = ParameterDirection.Output;
cmd.Parameters.Add(partotalRecords);
IList<User> users = null;
cn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
users = new List<User>();
if (reader.HasRows)
{
while (reader.Read())
{
var user = new User();
user.Id = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.Id]);
user.FirstName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.FirstName]);
user.LastName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.LastName]);
user.Password = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.Password]);
user.Status = (Enums.Status)TypeConversionUtility.ToByte(reader[DbConstant.DbColumn.Status]);
user.UserType = (Enums.UserType)TypeConversionUtility.ToByte(reader[DbConstant.DbColumn.UserType]);
user.Email = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.Email]);
user.ProfileImage = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.ProfileImage]);
user.PhoneNumber = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.PhoneNumber]);
user.SchoolName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.SchoolName]);
user.DateCreated = TypeConversionUtility.ToDateTime(reader[DbConstant.DbColumn.DateCreated]);
user.AccountType.Id = TypeConversionUtility.ToByte(reader[DbConstant.DbColumn.AccountType]);
user.AccountCategory.CategoryName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.CategoryName]);
user.SYId = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.SYId]);
users.Add(user);
}
}
cn.Close();
if (reader.IsClosed)
{
noOfPages = TypeConversionUtility.ToInteger(parNoOfPage.Value);
totalRecords = TypeConversionUtility.ToInteger(partotalRecords.Value);
}
}
return users;
}
}
}
/// <summary>
/// Gets the user based on User ID.
/// </summary>
/// <param name="userId">The User ID to be loaded.</param>
/// <param name="emailAddress">The email address.</param>
/// <param name="password">The password.</param>
/// <param name="status">The status.</param>
/// <returns>Object of <see cref="User" />.</returns>
public User GetUserProfile(int? userId, string emailAddress, string password, Enums.Status status)
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.StoreProcedure.usp_GetUserProfile, cn, true))
{
SqlParameter paramUserId = new SqlParameter(DbConstant.Parameter.Id, userId);
cmd.Parameters.Add(paramUserId);
User user = null;
cn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (reader.HasRows)
{
if (reader.Read())
{
user = new User();
user.Id = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.Id]);
user.FirstName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.FirstName]);
user.LastName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.LastName]);
user.Status = (Enums.Status)TypeConversionUtility.ToByte(reader[DbConstant.DbColumn.Status]);
user.UserType = (Enums.UserType)TypeConversionUtility.ToByte(reader[DbConstant.DbColumn.UserType]);
user.Email = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.Email]);
user.Password = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.Password]);
user.ProfileImage = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.ProfileImage]);
user.PhoneNumber = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.PhoneNumber]);
user.SchoolName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.SchoolName]);
user.DateCreated = TypeConversionUtility.ToDateTime(reader[DbConstant.DbColumn.DateCreated]);
user.PromoCode = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.PromoCode]);
user.SYId = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.SYId]);
user.AccountType.Id = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.AccountType]);
user.PromoType = (Enums.PromoType)TypeConversionUtility.ToByte(reader[DbConstant.DbColumn.PromoType]);
user.IsGiftFree = TypeConversionUtility.ToBoolean(reader[DbConstant.DbColumn.IsGiftFree]);
user.PhoneNumberType = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.PhoneNumberType]);
user.Address1 = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.Address1]);
user.Address2 = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.Address2]);
user.City = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.City]);
user.ZipCode = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.ZipCode]);
user.SchoolDistrict = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.SchoolDistrict]);
user.ApprovedVendor = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.ApprovedVendor]);
user.IsTaxExempt = TypeConversionUtility.ToBoolean(reader[DbConstant.DbColumn.IsTaxExempt]);
user.TaxExempt = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.TaxExempt]);
user.TaxExemptFile = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.TaxExemptFile]);
user.AccountCategory.Id = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.AccountCategory]);
user.AccountCategory.CategoryName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.CategoryName]);
user.AccountCategory.IsOther = TypeConversionUtility.ToBoolean(reader[DbConstant.DbColumn.IsOtherCategoryName]);
user.SchoolCampus.Id = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.SchoolCampusId]);
user.SchoolCampus.CampusName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.CampusName]);
user.SchoolCampus.IsOther = TypeConversionUtility.ToBoolean(reader[DbConstant.DbColumn.IsOtherCampus]);
user.SchoolType.Id = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.SchoolTypeId]);
user.SchoolType.IsOther = TypeConversionUtility.ToBoolean(reader[DbConstant.DbColumn.IsOtherSchoolType]);
user.SchoolType.SchoolTypeName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.SchoolTypeName]);
user.State.Id = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.StateId]);
user.State.StateName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.StateName]);
user.State.StateCode = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.StateCode]);
user.State.IsOther = TypeConversionUtility.ToBoolean(reader[DbConstant.DbColumn.IsOtherState]);
user.IsSchoolDistrictExempt = TypeConversionUtility.ToBoolean(reader[DbConstant.DbColumn.IsSchoolDistrictExempt]);
user.IsActiveCategory = TypeConversionUtility.ToBoolean(reader[DbConstant.DbColumn.IsActiveCategory]);
user.IsProfile = true;
user.IsNotifications = TypeConversionUtility.ToBoolean(reader[DbConstant.DbColumn.IsNotifications]);
user.CellPhone = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.CellPhone]);
}
}
}
return user;
}
}
}