/// <summary>
/// Get the SubMenu.
/// </summary>
/// <returns>Collection of SubMenu.</returns>
public IList<SubMenu> GetSubMenus()
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.SubMenu.StoreProcedure.usp_SelectSubMenu, cn, true))
{
IList<SubMenu> SubMenus = null;
cn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
SubMenus = new List<SubMenu>();
if (reader.HasRows)
{
while (reader.Read())
{
SubMenu SubMenu = new SubMenu();
SubMenu.Id = TypeConversionUtility.ToInteger(reader[DbConstant.SubMenu.DbColumn.Id]);
SubMenu.MenuName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.MenuName]);
SubMenu.SubMenuName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.SubMenuName]);
SubMenu.MenuId = TypeConversionUtility.ToInteger(reader[DbConstant.SubMenu.DbColumn.MenuId]);
SubMenu.DisplayOrder = TypeConversionUtility.ToInteger(reader[DbConstant.SubMenu.DbColumn.DisplayOrder]);
SubMenu.IsActive = TypeConversionUtility.ToBoolean(reader[DbConstant.SubMenu.DbColumn.IsActive]);
SubMenu.CreatedDate = TypeConversionUtility.ToDateTime(reader[DbConstant.SubMenu.DbColumn.CreatedDate]);
SubMenus.Add(SubMenu);
}
}
cn.Close();
}
return SubMenus;
}
}
}
/// <summary>
/// Get the Role.
/// </summary>
/// <returns>Collection of Role.</returns>
public IList <Role> GetRoles()
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.Role.StoreProcedure.usp_SelectUserRole, cn, true))
{
IList <Role> listRole = null;
cn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
listRole = new List <Role>();
if (reader.HasRows)
{
while (reader.Read())
{
Role Role = new Role();
Role.Id = TypeConversionUtility.ToInteger(reader[DbConstant.Role.DbColumn.Id]);
Role.RoleName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.Role.DbColumn.RoleName]);
Role.IsActive = TypeConversionUtility.ToBoolean(reader[DbConstant.Role.DbColumn.IsActive]);
Role.CreatedDate = TypeConversionUtility.ToDateTime(reader[DbConstant.Role.DbColumn.CreatedDate]);
listRole.Add(Role);
}
}
cn.Close();
}
return(listRole);
}
}
}
/// <summary>
/// Get the Menu.
/// </summary>
/// <returns>Collection of Menu.</returns>
public IList<MenuNav> GetMenus()
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.Menu.StoreProcedure.usp_SelectMenu, cn, true))
{
IList<MenuNav> listMenuNav = null;
cn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
listMenuNav = new List<MenuNav>();
if (reader.HasRows)
{
while (reader.Read())
{
MenuNav menuNav = new MenuNav();
menuNav.Id = TypeConversionUtility.ToInteger(reader[DbConstant.Menu.DbColumn.Id]);
menuNav.MenuName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.Menu.DbColumn.MenuName]);
menuNav.DisplayOrder = TypeConversionUtility.ToInteger(reader[DbConstant.Menu.DbColumn.DisplayOrder]);
menuNav.IsActive = TypeConversionUtility.ToBoolean(reader[DbConstant.Menu.DbColumn.IsActive]);
menuNav.CreatedDate = TypeConversionUtility.ToDateTime(reader[DbConstant.Menu.DbColumn.CreatedDate]);
listMenuNav.Add(menuNav);
}
}
cn.Close();
}
return listMenuNav;
}
}
}
/// <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 SubMenu.
/// </summary>
/// <returns>Details of SubMenu.</returns>
public SubMenu GetSubMenu(int id,bool ? isAddPage)
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.SubMenu.StoreProcedure.usp_SelectSubMenu, cn, true))
{
SqlParameter paramId = new SqlParameter(DbConstant.SubMenu.DbParameter.Id, id);
cmd.Parameters.Add(paramId);
SqlParameter paramIsAddPage = new SqlParameter(DbConstant.SubMenu.DbParameter.IsAddPage, isAddPage);
cmd.Parameters.Add(paramIsAddPage);
SubMenu subMenus = null;
cn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
subMenus = new SubMenu();
if (reader.HasRows)
{
while (reader.Read())
{
subMenus.Id = TypeConversionUtility.ToInteger(reader[DbConstant.SubMenu.DbColumn.Id]);
subMenus.MenuName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.MenuName]);
subMenus.MenuLink = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.MenuLink]);
subMenus.SubMenuName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.SubMenuName]);
subMenus.ListPageHeading = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.ListPageHeading]);
subMenus.ListPageDescription = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.ListPageDescription]);
subMenus.AddPageHeading = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.AddPageHeading]);
subMenus.AddPageDescription = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.AddPageDescription]);
subMenus.IsAddPage = TypeConversionUtility.ToBoolean(reader[DbConstant.SubMenu.DbColumn.IsAddPage]);
subMenus.MenuId = TypeConversionUtility.ToInteger(reader[DbConstant.SubMenu.DbColumn.MenuId]);
subMenus.DisplayOrder = TypeConversionUtility.ToInteger(reader[DbConstant.SubMenu.DbColumn.DisplayOrder]);
subMenus.IsActive = TypeConversionUtility.ToBoolean(reader[DbConstant.SubMenu.DbColumn.IsActive]);
subMenus.CreatedDate = TypeConversionUtility.ToDateTime(reader[DbConstant.SubMenu.DbColumn.CreatedDate]);
}
}
cn.Close();
}
return subMenus;
}
}
}
/// <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;
}
}
}