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