/// <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;
}
}
}
public List<UserOrderDiscount> GetOrderDiscounts(int userId)
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.StoreProcedure.usp_GetOrderDiscount, cn, true))
{
SqlParameter paramUserId = new SqlParameter(DbConstant.Parameter.UserId, userId);
cmd.Parameters.Add(paramUserId);
List<UserOrderDiscount> userPromoDiscounts = null;
cn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
userPromoDiscounts = new List<UserOrderDiscount>();
if (reader.HasRows)
{
while (reader.Read())
{
var data = new UserOrderDiscount
{
Discount = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.Discount]),
OrderNumber = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.OrderNumber]),
TemplateId = TypeConversionUtility.ToInteger(reader[DbConstant.DbColumn.TemplateId]),
Savings = TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.Savings]),
GlobalPromoCode= TypeConversionUtility.ToStringWithNull(reader[DbConstant.DbColumn.GlobalPromoCode])
};
userPromoDiscounts.Add(data);
}
}
}
return userPromoDiscounts;
}
}
}
/// <summary>
/// Add Update a Menu into the database.
/// </summary>
/// <param name="entity">object of Menu class to be added/updated.</param>
/// <returns>
/// Returns true if add is successful, else false.
/// </returns>
public bool AddUpdateMenu(MenuNav entity)
{
using (var cn = new SqlConnection(_connectionString))
{
bool isAddUpdated = false;
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.Menu.StoreProcedure.usp_AddUpdateMenu, cn, true))
{
SqlParameter paramId = new SqlParameter(DbConstant.Menu.DbParameter.Id, entity.Id);
cmd.Parameters.Add(paramId);
SqlParameter paramMenu = new SqlParameter(DbConstant.Menu.DbParameter.MenuName, entity.MenuName);
cmd.Parameters.Add(paramMenu);
SqlParameter paramDisplayOrder = new SqlParameter(DbConstant.Menu.DbParameter.DisplayOrder, entity.DisplayOrder);
cmd.Parameters.Add(paramDisplayOrder);
SqlParameter paramIsActive = new SqlParameter(DbConstant.Menu.DbParameter.IsActive, entity.IsActive);
cmd.Parameters.Add(paramIsActive);
cn.Open();
var id = cmd.ExecuteScalar();
entity.Id = TypeConversionUtility.ToInteger(id);
isAddUpdated = entity.Id > 0;
}
return isAddUpdated;
}
}
/// <summary>
/// Get the Menus By Role.
/// </summary>
/// <param name="id"></param>
/// <returns>Collection of Menus.</returns>
public IList<MenuNav> GetMenusByRole(int? roleId = null)
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.Menu.StoreProcedure.usp_SelectRoleByMenu, cn, true))
{
SqlParameter paramRoleId = new SqlParameter(DbConstant.Menu.DbParameter.RoleId, roleId);
cmd.Parameters.Add(paramRoleId);
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.Role.DbColumn.Id]);
menuNav.MenuName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.Role.DbColumn.MenuName]);
listMenuNav.Add(menuNav);
}
}
cn.Close();
}
return listMenuNav;
}
}
}
/// <summary>
/// Get the Menus.
/// </summary>
/// <param name="id"></param>
/// <returns>Collection of Menus.</returns>
public IList <Role> GetMenusByRoleId(int?id = null)
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.Role.StoreProcedure.usp_SelectPermission, cn, true))
{
SqlParameter paramId = new SqlParameter(DbConstant.Role.DbParameter.Id, id);
cmd.Parameters.Add(paramId);
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.RoleId]);
role.MenuId = TypeConversionUtility.ToInteger(reader[DbConstant.Role.DbColumn.MenuId]);
role.SubMenuId = TypeConversionUtility.ToInteger(reader[DbConstant.Role.DbColumn.SubMenuId]);
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 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>
/// 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;
}
}
}
protected void rptList_OnItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == WebConstant.CommandName.Delete)
{
if (UserRepository.UserDelete(TypeConversionUtility.ToInteger(e.CommandArgument)))
{
PopulateUsers();
lblMsg.Text = "Record has been successfully deleted.";
pnlMsg.Visible = true;
}
}
}
protected void lkbSubmit_OnClick(object sender, EventArgs e)
{
try
{
var objUser = new clsUser();
if (!string.IsNullOrEmpty(Convert.ToString(Request.QueryString["Id"])))
{
objUser.UserId = TypeConversionUtility.ToInteger(Request.QueryString["Id"]);
}
else
{
objUser.UserId = 0;
}
objUser.UserName = TypeConversionUtility.ToStringWithNull(txtName.Text.Trim());
objUser.EmailId = TypeConversionUtility.ToStringWithNull(txtEmailId.Text.Trim());
objUser.LoginId = TypeConversionUtility.ToStringWithNull(txtLoginId.Text.Trim());
objUser.LoginPwd = TypeConversionUtility.ToStringWithNull(txtPwd.Text.Trim());
objUser.Status = TypeConversionUtility.ToInteger(ddlStatus.SelectedValue);
objUser.TypeId = Convert.ToInt32(ddUserLevel.SelectedValue);
if (UserRepository.CheckUserExits(objUser.UserId, objUser.LoginId))
{
lblMsg.Text = "Login Id already exist. Try using different login id.";
pnlMsg.Visible = true;
}
else if (UserRepository.UserAddUpdate(objUser)) // Add/Update Login Details
{
if (objUser.UserId == 0)
{
pnlMsg.Visible = true;
lblMsg.Text = "Record has been successfully inserted.";
lblMsg.CssClass = "success";
txtName.Text = "";
txtEmailId.Text = "";
txtPwd.Text = "";
txtLoginId.Text = "";
ddlStatus.SelectedValue = "0";
}
else
{
Response.Redirect("userList.aspx?msg=Record has been successfully updated.");
}
}
}
catch (Exception ex)
{
lblMsg.Text = string.Format("<p class=\"error\">{0}</p>", ex);
pnlMsg.Visible = true;
}
}
/// <summary>
/// Add Update a SubMenu into the database.
/// </summary>
/// <param name="entity">object of SubMenu class to be added/updated.</param>
/// <returns>
/// Returns true if add is successful, else false.
/// </returns>
public bool AddUpdateSubMenu(SubMenu entity)
{
using (var cn = new SqlConnection(_connectionString))
{
bool isAddUpdated = false;
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.SubMenu.StoreProcedure.usp_AddUpdateSubMenu, cn, true))
{
SqlParameter paramId = new SqlParameter(DbConstant.SubMenu.DbParameter.Id, entity.Id);
cmd.Parameters.Add(paramId);
SqlParameter paramSubMenu = new SqlParameter(DbConstant.SubMenu.DbParameter.SubMenuName, entity.SubMenuName);
cmd.Parameters.Add(paramSubMenu);
SqlParameter paramMenuLinku = new SqlParameter(DbConstant.SubMenu.DbParameter.MenuLink, entity.MenuLink);
cmd.Parameters.Add(paramMenuLinku);
SqlParameter paramMenuId = new SqlParameter(DbConstant.SubMenu.DbParameter.MenuId, entity.MenuId);
cmd.Parameters.Add(paramMenuId);
SqlParameter paramDisplayOrder = new SqlParameter(DbConstant.SubMenu.DbParameter.DisplayOrder, entity.DisplayOrder);
cmd.Parameters.Add(paramDisplayOrder);
SqlParameter paramListPageHeading = new SqlParameter(DbConstant.SubMenu.DbParameter.ListPageHeading, entity.ListPageHeading);
cmd.Parameters.Add(paramListPageHeading);
SqlParameter paramListPageDescription = new SqlParameter(DbConstant.SubMenu.DbParameter.ListPageDescription, entity.ListPageDescription);
cmd.Parameters.Add(paramListPageDescription);
SqlParameter paramListAddPageHeading = new SqlParameter(DbConstant.SubMenu.DbParameter.AddPageHeading, entity.AddPageHeading);
cmd.Parameters.Add(paramListAddPageHeading);
SqlParameter paramAddPageDescription = new SqlParameter(DbConstant.SubMenu.DbParameter.AddPageDescription, entity.AddPageDescription);
cmd.Parameters.Add(paramAddPageDescription);
SqlParameter paramIsAddPage = new SqlParameter(DbConstant.SubMenu.DbParameter.IsAddPage, entity.IsAddPage);
cmd.Parameters.Add(paramIsAddPage);
SqlParameter paramIsActive = new SqlParameter(DbConstant.SubMenu.DbParameter.IsActive, entity.IsActive);
cmd.Parameters.Add(paramIsActive);
cn.Open();
var id = cmd.ExecuteScalar();
entity.Id = TypeConversionUtility.ToInteger(id);
isAddUpdated = entity.Id > 0;
}
return isAddUpdated;
}
}
/// <summary>
/// Checks if the User Email is available for registration.
/// </summary>
/// <param name="email">User Email to be checked</param>
/// <returns>
/// Returns true if User Email is available, else false.
/// </returns>
public bool CheckUserEmailAvailability(string email, int userId)
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.StoreProcedure.usp_CheckUserEmailAvailability, cn, true))
{
SqlParameter paramEmail = new SqlParameter(DbConstant.Parameter.Email, email);
cmd.Parameters.Add(paramEmail);
SqlParameter paramUserId = new SqlParameter(DbConstant.Parameter.UserId, userId);
cmd.Parameters.Add(paramUserId);
cn.Open();
int results = TypeConversionUtility.ToInteger(cmd.ExecuteScalar());
return results <= 0;
}
}
}
/// <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>
/// Add Update a Role into the database.
/// </summary>
/// <param name="entity">object of Role class to be added/updated.</param>
/// <returns>
/// Returns true if add is successful, else false.
/// </returns>
public bool AddUpdateRole(Role entity)
{
using (var cn = new SqlConnection(_connectionString))
{
bool isAddUpdated = false;
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.Role.StoreProcedure.usp_AddUpdateUserRole, cn, true))
{
SqlParameter paramId = new SqlParameter(DbConstant.Role.DbParameter.Id, entity.Id);
cmd.Parameters.Add(paramId);
SqlParameter paramRole = new SqlParameter(DbConstant.Role.DbParameter.RoleName, entity.RoleName);
cmd.Parameters.Add(paramRole);
SqlParameter paramIsActive = new SqlParameter(DbConstant.Role.DbParameter.IsActive, entity.IsActive);
cmd.Parameters.Add(paramIsActive);
cn.Open();
var id = cmd.ExecuteScalar();
entity.Id = TypeConversionUtility.ToInteger(id);
isAddUpdated = entity.Id > 0;
}
return(isAddUpdated);
}
}
/// <summary>
/// Deletes an Menu from the database based on Menu Id.
/// </summary>
/// <param name="dtMenuId"></param>
/// <returns>Returns true if delete is successful, else false.</returns>
/// <remarks>Deletes based on primary key(s).</remarks>
public bool DeleteMenu(DataTable dtMenuId)
{
int effectedRows = 0;
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.Menu.StoreProcedure.usp_DeleteMenu, cn, true))
{
SqlParameter paramdtMenu = new SqlParameter(DbConstant.DbTypeParameter.EntityIdIntTableType, SqlDbType.Structured);
paramdtMenu.Value = dtMenuId;
paramdtMenu.TypeName = DbConstant.DbTypeName.EntityIdIntTableType;
cmd.Parameters.Add(paramdtMenu);
SqlParameter paramdIsDeleted = new SqlParameter(DbConstant.DbParameters.IsDeleted, SqlDbType.Int);
paramdIsDeleted.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramdIsDeleted);
cn.Open();
cmd.ExecuteNonQuery();
effectedRows = TypeConversionUtility.ToInteger(cmd.Parameters[DbConstant.DbParameters.IsDeleted].Value);
}
}
return effectedRows > 0;
}
/// <summary>
/// Get the Sub Menus by Role.
/// </summary>
/// <param name="id"></param>
/// <param name="roleId"></param>
/// <returns>Collection of Sub Menus.</returns>
public IList<SubMenu> GetSubMenusByRole(int? id = null, int ? roleId = null)
{
using (SqlConnection cn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.SubMenu.StoreProcedure.usp_SelectSubMenuByRole, cn, true))
{
SqlParameter paramId = new SqlParameter(DbConstant.Role.DbParameter.Id, id);
cmd.Parameters.Add(paramId);
SqlParameter paramRoleId = new SqlParameter(DbConstant.Menu.DbParameter.RoleId, roleId);
cmd.Parameters.Add(paramRoleId);
IList<SubMenu> listSubMenu = null;
cn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
listSubMenu = new List<SubMenu>();
if (reader.HasRows)
{
while (reader.Read())
{
SubMenu subMenu = new SubMenu();
subMenu.Id = TypeConversionUtility.ToInteger(reader[DbConstant.SubMenu.DbColumn.Id]);
subMenu.MenuId = TypeConversionUtility.ToInteger(reader[DbConstant.SubMenu.DbColumn.MenuId]);
subMenu.MenuName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.MenuName]);
subMenu.SubMenuName = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.SubMenuName]);
subMenu.MenuLink = TypeConversionUtility.ToStringWithNull(reader[DbConstant.SubMenu.DbColumn.MenuLink]);
listSubMenu.Add(subMenu);
}
}
cn.Close();
}
return listSubMenu;
}
}
}
/// <summary>
/// Add Update a User into the database.
/// </summary>
/// <param name="entity">object of User class to be added/updated.</param>
/// <returns>Returns true if add is successful, else false.</returns>
public bool AddUpdateUser(User entity)
{
using (var cn = new SqlConnection(_connectionString))
{
bool isAddUpdated = false;
using (SqlCommand cmd = SqlHelper.GetSqlCommand(DbConstant.StoreProcedure.usp_AddUpdateUser, cn, true))
{
SqlParameter paramUserId = new SqlParameter(DbConstant.Parameter.Id, entity.Id);
cmd.Parameters.Add(paramUserId);
SqlParameter paramFirstName = new SqlParameter(DbConstant.Parameter.FirstName, entity.FirstName);
cmd.Parameters.Add(paramFirstName);
SqlParameter paramLastName = new SqlParameter(DbConstant.Parameter.LastName, entity.LastName);
cmd.Parameters.Add(paramLastName);
SqlParameter paramEmailAddress = new SqlParameter(DbConstant.Parameter.Email, entity.Email);
cmd.Parameters.Add(paramEmailAddress);
SqlParameter paramStatus = new SqlParameter(DbConstant.Parameter.Status, (byte)entity.Status);
cmd.Parameters.Add(paramStatus);
SqlParameter paramUserType = new SqlParameter(DbConstant.Parameter.UserType, (byte)entity.UserType);
cmd.Parameters.Add(paramUserType);
SqlParameter paramPassword = new SqlParameter(DbConstant.Parameter.Password, entity.Password);
cmd.Parameters.Add(paramPassword);
SqlParameter paramProfileImage = new SqlParameter(DbConstant.Parameter.ProfileImage, entity.ProfileImage);
cmd.Parameters.Add(paramProfileImage);
SqlParameter paramPhoneNumber = new SqlParameter(DbConstant.Parameter.PhoneNumber, entity.PhoneNumber);
cmd.Parameters.Add(paramPhoneNumber);
SqlParameter paramSchoolName = new SqlParameter(DbConstant.Parameter.SchoolName, entity.SchoolName);
cmd.Parameters.Add(paramSchoolName);
SqlParameter paramAccountType = new SqlParameter(DbConstant.Parameter.AccountType, entity.AccountType.Id);
cmd.Parameters.Add(paramAccountType);
SqlParameter paramPhoneNumberType = new SqlParameter(DbConstant.Parameter.PhoneNumberType, entity.PhoneNumberType);
cmd.Parameters.Add(paramPhoneNumberType);
SqlParameter paramAddress1 = new SqlParameter(DbConstant.Parameter.Address1, entity.Address1);
cmd.Parameters.Add(paramAddress1);
SqlParameter paramAddress2 = new SqlParameter(DbConstant.Parameter.Address2, entity.Address2);
cmd.Parameters.Add(paramAddress2);
SqlParameter paramCity = new SqlParameter(DbConstant.Parameter.City, entity.City);
cmd.Parameters.Add(paramCity);
SqlParameter paramZipCode = new SqlParameter(DbConstant.Parameter.ZipCode, entity.ZipCode);
cmd.Parameters.Add(paramZipCode);
SqlParameter paramSchoolDistric = new SqlParameter(DbConstant.Parameter.SchoolDistrict, entity.SchoolDistrict);
cmd.Parameters.Add(paramSchoolDistric);
SqlParameter paramApprovedVendor = new SqlParameter(DbConstant.Parameter.ApprovedVendor, entity.ApprovedVendor);
cmd.Parameters.Add(paramApprovedVendor);
SqlParameter paramIsTaxExempt = new SqlParameter(DbConstant.Parameter.IsTaxExempt, entity.IsTaxExempt);
cmd.Parameters.Add(paramIsTaxExempt);
SqlParameter paramTaxExempt = new SqlParameter(DbConstant.Parameter.TaxExempt, entity.TaxExempt);
cmd.Parameters.Add(paramTaxExempt);
SqlParameter paramTaxExemptPath = new SqlParameter(DbConstant.Parameter.TaxExemptFile, entity.TaxExemptFile);
cmd.Parameters.Add(paramTaxExemptPath);
SqlParameter paramAccountCategory = new SqlParameter(DbConstant.Parameter.AccountCategory, entity.AccountCategory.Id);
cmd.Parameters.Add(paramAccountCategory);
if (entity.AccountCategory.IsOther)
{
SqlParameter paramCategoryName = new SqlParameter(DbConstant.Parameter.CategoryName, entity.AccountCategory.CategoryName);
cmd.Parameters.Add(paramCategoryName);
}
SqlParameter paramSchoolCampusId = new SqlParameter(DbConstant.Parameter.SchoolCampusId, entity.SchoolCampus.Id);
cmd.Parameters.Add(paramSchoolCampusId);
if (entity.SchoolCampus.IsOther)
{
SqlParameter paramCampusName = new SqlParameter(DbConstant.Parameter.CampusName, entity.SchoolCampus.CampusName);
cmd.Parameters.Add(paramCampusName);
}
SqlParameter paramStateId = new SqlParameter(DbConstant.Parameter.StateId, entity.State.Id);
cmd.Parameters.Add(paramStateId);
if (entity.State.IsOther)
{
SqlParameter paramStateName = new SqlParameter(DbConstant.Parameter.StateName, entity.State.StateName);
cmd.Parameters.Add(paramStateName);
}
SqlParameter paramSchoolTypeId = new SqlParameter(DbConstant.Parameter.SchoolTypeId, entity.SchoolType.Id);
cmd.Parameters.Add(paramSchoolTypeId);
if (entity.SchoolType.IsOther)
{
SqlParameter paramSchoolTypeName = new SqlParameter(DbConstant.Parameter.SchoolTypeName, entity.SchoolType.SchoolTypeName);
cmd.Parameters.Add(paramSchoolTypeName);
}
SqlParameter paramIsSchoolDistrictExempt = new SqlParameter(DbConstant.Parameter.IsSchoolDistrictExempt, entity.IsSchoolDistrictExempt);
cmd.Parameters.Add(paramIsSchoolDistrictExempt);
SqlParameter paramIsNotifications = new SqlParameter(DbConstant.Parameter.IsNotifications, entity.IsNotifications);
cmd.Parameters.Add(paramIsNotifications);
SqlParameter paramCellPhone = new SqlParameter(DbConstant.Parameter.CellPhone, entity.CellPhone);
cmd.Parameters.Add(paramCellPhone);
cn.Open();
var id = cmd.ExecuteScalar();
entity.Id = TypeConversionUtility.ToInteger(id);
isAddUpdated = entity.Id > 0;
}
return isAddUpdated;
}
}
/// <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;
}
}
}
private void PopulateUsers()
{
try
{
int startPage = !String.IsNullOrEmpty(Request.QueryString[WebConstant.QueryString.PagerQueryString])
? TypeConversionUtility.ToInteger(Request.QueryString[WebConstant.QueryString.PagerQueryString])
: 1;
int pageLength =
!String.IsNullOrEmpty(Request.QueryString[WebConstant.QueryString.PageLengthQueryString])
? TypeConversionUtility.ToInteger(
Request.QueryString[WebConstant.QueryString.PageLengthQueryString])
: WebConstant.PageLength;
int noOfPages = 0;
int totalRecords = 0;
var users = UserRepository.UserListPaged(null, startPage, pageLength, out noOfPages, out totalRecords);
rptList.DataSource = users;
rptList.DataBind();
#region "Paging"
litPageState.Text = "Showing " + startPage + " of " + noOfPages + " (out of " + totalRecords + " items)";
if (totalRecords > users.Count)
{
ArgosPager argosPager = new ArgosPager();
argosPager.OutputFirstAndLastLinks = true;
argosPager.OutputPageStats = false;
argosPager.OutputPageJumper = false;
argosPager.EnableGoToPage = false;
argosPager.OutputNextPrevLinks = true;
argosPager.NavigateNextText = ">";
argosPager.NavigatePreviousText = "<";
PagerBuilder.BuildPager(startPage, pageLength, noOfPages, totalRecords, false, litPager,
WebConstant.PagerTextBoxId, WebConstant.QueryString.PagerQueryString, argosPager);
// Binding Paging Css on Pager Literal Control
string str = litPager.Text;
str = str.Replace(" ", "");
litPager.Text = str;
if (startPage != noOfPages)
{
int i = noOfPages / startPage;
if (i > 0 && startPage != 1)
{
IsLast = 2;
}
else
{
IsLast = 0;
}
}
else
{
IsLast = 1;
}
}
else
{
litPager.Text = "";
}
#endregion
}
catch (Exception ex)
{
lblMsg.Text = string.Format("<p class=\"error\">{0}</p>", ex);
pnlMsg.Visible = true;
}
}