public static bool UserHasRoleName(string userId, string roleName) { bool _ret = false; string sql = @"SELECT ur.*, r.[Name] FROM dbo.AspNetUserRoles ur, dbo.AspNetRoles r WHERE ur.RoleId = r.Id AND r.Name = @RoleName AND ur.UserId = @UserId" ; List <SqlParameter> parms = new List <SqlParameter>(); SQLData db = new SQLData(); try { parms.Add(new SqlParameter("@UserId", userId)); parms.Add(new SqlParameter("@RoleName", roleName)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); DataTable dt = db.Execute(sql, parms); _ret = (dt.Rows.Count > 0); } catch (Exception ex) { Logger.LogError("UserHasRoleName Exception", ex); } finally { db.Close(); } return(_ret); }
public static bool UpdateEmailConfirmed(string id, bool emailConfirmed) { bool _ret = false; List <SqlParameter> parms = new List <SqlParameter>(); SQLData db = new SQLData(); try { parms.Add(new SqlParameter("@Id", id)); parms.Add(new SqlParameter("@EmailConfirmed", emailConfirmed)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); int i = db.ExecuteNonQuery("UPDATE dbo.AspNetUsers SET [EmailConfirmed]=@EmailConfirmed WHERE [Id]=@Id", parms); _ret = (i == 1); } catch (Exception ex) { Logger.LogError("UpdateEmailConfirmed Exception", ex); } finally { db.Close(); } return(_ret); }
public AspNetRolesModel GetRole(string id) { SQLData db = new SQLData(); string sql = "SELECT * FROM dbo.AspNetRoles WHERE Id=@Id"; List <SqlParameter> parms = new List <SqlParameter>(); AspNetRolesModel model = new AspNetRolesModel(); try { db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); parms.Add(new SqlParameter("@Id", id)); DataTable dt = db.Execute(sql, parms); DataRow row = dt.Rows[0]; if (!row["Id"].ToString().Equals(string.Empty)) { model.Id = row["Id"].ToString(); } if (!row["Name"].ToString().Equals(string.Empty)) { model.Name = row["Name"].ToString(); } } catch (Exception ex) { Logger.LogError("GetRole exception: ", ex); } finally { db.Close(); } return(model); }
public AspNetRolesModel AddRole(string name) { SQLData db = new SQLData(); string sql = "INSERT INTO dbo.AspNetRoles (Id, Name, AccessLevel) VALUES(@Id, @Name, @AccessLevel)"; List <SqlParameter> parms = new List <SqlParameter>(); AspNetRolesModel model = new AspNetRolesModel(); try { model.Id = Guid.NewGuid().ToString(); model.Name = name; parms.Add(new SqlParameter("@Id", model.Id)); parms.Add(new SqlParameter("@Name", model.Name)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); db.ExecuteNonQuery(sql, parms); } catch (Exception ex) { Logger.LogError("AddRole Exception: ", ex); } finally { db.Close(); } return(model); }
public AspNetAccessLevelsModel AddAccessLevel(AspNetAccessLevelsModel model) { SQLData db = new SQLData(); string sql = "INSERT INTO dbo.AspNetAccessLevels (Id, Name, Description, AccessLevel) VALUES(@Id, @Name, @Description, @AccessLevel)"; List <SqlParameter> parms = new List <SqlParameter>(); try { if (string.IsNullOrEmpty(model.Description)) { model.Description = ""; } model.Id = Guid.NewGuid().ToString(); parms.Add(new SqlParameter("@Id", model.Id)); parms.Add(new SqlParameter("@Name", model.Name)); parms.Add(new SqlParameter("@Description", model.Description)); parms.Add(new SqlParameter("@AccessLevel", model.AccessLevel)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); db.ExecuteNonQuery(sql, parms); } catch (Exception ex) { Logger.LogError("AddAccessLevel Exception: ", ex); } finally { db.Close(); } return(model); }
public int UpdateAccessLevel(AspNetAccessLevelsModel model) { SQLData db = new SQLData(); string sql = "UPDATE dbo.AspNetAccessLevels SET Name=@Name, Description=@Description, AccessLevel=@AccessLevel WHERE Id=@Id"; List <SqlParameter> parms = new List <SqlParameter>(); int _ret = 0; try { parms.Add(new SqlParameter("@Id", model.Id)); parms.Add(new SqlParameter("@Name", model.Name)); parms.Add(new SqlParameter("@Description", model.Description)); parms.Add(new SqlParameter("@AccessLevel", model.AccessLevel)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); _ret = db.ExecuteNonQuery(sql, parms); } catch (Exception ex) { Logger.LogError("UpdateAccessLevel Exception: ", ex); } finally { db.Close(); } return(_ret); }
public static string GetSecurityStamp(string id) { string _ret = string.Empty; List <SqlParameter> parms = new List <SqlParameter>(); SQLData db = new SQLData(); try { parms.Add(new SqlParameter("@Id", id)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); DataTable dt = db.Execute("SELECT [SecurityStamp] FROM dbo.AspNetUsers WHERE [Id]=@Id", parms); if (dt.Rows.Count > 0) { _ret = dt.Rows[0]["SecurityStamp"].ToString(); } } catch (Exception ex) { Logger.LogError("GetSecurityStamp Exception", ex); } finally { db.Close(); } return(_ret); }
public void Fetch() { SQLData db = new SQLData(); List <People> list = new List <People>(); string sql = "SELECT * FROM Application.People"; try { if (queryModel.search != null) { sql = string.Format(@"{0} WHERE {1}", sql, queryModel.search); } if (db.Open(ConfigurationManager.ConnectionStrings["Model1"].ConnectionString)) { string sql2 = string.Format("SELECT COUNT(*) FROM ({0}) AS C1", sql); DataTable dt2 = db.Execute(sql2); TotalRecords = (int)dt2.Rows[0][0]; if (queryModel.orders != null) { StringBuilder sbOrders = new StringBuilder(" ORDER BY "); bool isFirst = true; foreach (Order order in queryModel.orders) { if (!isFirst) { sbOrders.Append(","); } sbOrders.AppendFormat("{0} {1}", (order.column).ToString(), order.dir); isFirst = false; } sql += sbOrders.ToString(); } if (queryModel.length > 0) { sql = string.Format(@"{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, queryModel.start, queryModel.length); } DataTable dt = db.Execute(sql); foreach (DataRow row in dt.Rows) { Add(new People(row)); } db.Close(); } } catch (Exception ex) { Logger.LogError("Fetch exception: ", ex); } }
public void DeleteUserRole(string userId, string roleName) { SQLData db = new SQLData(); List <SqlParameter> roleParms = new List <SqlParameter>(); roleParms.Add(new SqlParameter("@Name", roleName)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); DataTable dt = db.Execute("SELECT Id FROM dbo.AspNetRoles WHERE Name = @Name", roleParms); if (dt.Rows.Count > 0) { List <SqlParameter> userRoleParms = new List <SqlParameter>(); userRoleParms.Add(new SqlParameter("@UserId", userId)); userRoleParms.Add(new SqlParameter("@RoleId", dt.Rows[0]["Id"].ToString())); db.ExecuteNonQuery("DELETE FROM dbo.AspNetUserRoles WHERE UserId=@UserId AND RoleId=@RoleId", userRoleParms); } db.Close(); }
public void DeleteRole(string id) { SQLData db = new SQLData(); string sql = @"dbo.DeleteRole"; try { List <SqlParameter> parms = new List <SqlParameter>(); parms.Add(new SqlParameter("@RoleId", id)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); db.ExecuteNonQueryStoredProcedure(sql, parms); } catch (Exception ex) { Logger.LogError("DeleteRole exception: ", ex); } finally { db.Close(); } }
public static bool UserNameExists(string userName) { bool _ret = false; List <SqlParameter> parms = new List <SqlParameter>(); SQLData db = new SQLData(); try { parms.Add(new SqlParameter("@UserName", userName)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); DataTable dt = db.Execute("SELECT * FROM dbo.AspNetUsers WHERE UserName=@UserName"); _ret = (dt.Rows.Count > 0); } catch (Exception ex) { Logger.LogError("UserNameExists Exception", ex); } finally { db.Close(); } return(_ret); }
public int DeleteAccessLevel(string id) { SQLData db = new SQLData(); string sql = "DELETE FROM dbo.AspNetAccessLevels WHERE Id=@Id"; List <SqlParameter> parms = new List <SqlParameter>(); int _ret = 0; try { parms.Add(new SqlParameter("@Id", id)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); _ret = db.ExecuteNonQuery(sql, parms); } catch (Exception ex) { Logger.LogError("DeleteAccessLevel Exception: ", ex); } finally { db.Close(); } return(_ret); }
public void Fetch() { SQLData db = new SQLData(); List <PeopleModel> list = new List <PeopleModel>(); string sql = "SELECT * FROM Application.People"; try { if (queryModel.search != null) { sql = string.Format(@"{0} WHERE {1}", sql, queryModel.search); } if (db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString)) { string sql2 = string.Format("SELECT COUNT(*) FROM ({0}) AS C1", sql); DataTable dt2 = db.Execute(sql2); TotalRecords = (int)dt2.Rows[0][0]; if (queryModel.orders != null) { StringBuilder sbOrders = new StringBuilder(" ORDER BY "); bool isFirst = true; foreach (Order order in queryModel.orders) { if (!isFirst) { sbOrders.Append(","); } sbOrders.AppendFormat("{0} {1}", (order.column).ToString(), order.dir); isFirst = false; } sql += sbOrders.ToString(); } if (queryModel.length > 0) { sql = string.Format(@"{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, queryModel.start, queryModel.length); } DataTable dt = db.Execute(sql); foreach (DataRow row in dt.Rows) { PeopleModel model = new PeopleModel(); if (!row["PersonID"].ToString().Equals(string.Empty)) { model.PersonID = (int)row["PersonID"]; } if (!row["FullName"].ToString().Equals(string.Empty)) { model.FullName = row["FullName"].ToString(); } if (!row["PreferredName"].ToString().Equals(string.Empty)) { model.PreferredName = row["PreferredName"].ToString(); } if (!row["SearchName"].ToString().Equals(string.Empty)) { model.SearchName = row["SearchName"].ToString(); } if (!row["IsPermittedToLogon"].ToString().Equals(string.Empty)) { model.IsPermittedToLogon = (bool)row["IsPermittedToLogon"]; } if (!row["LogonName"].ToString().Equals(string.Empty)) { model.LogonName = row["LogonName"].ToString(); } if (!row["IsExternalLogonProvider"].ToString().Equals(string.Empty)) { model.IsExternalLogonProvider = (bool)row["IsExternalLogonProvider"]; } if (!row["HashedPassword"].ToString().Equals(string.Empty)) { model.HashedPassword = (byte[])row["HashedPassword"]; } if (!row["IsSystemUser"].ToString().Equals(string.Empty)) { model.IsSystemUser = (bool)row["IsSystemUser"]; } if (!row["IsEmployee"].ToString().Equals(string.Empty)) { model.IsEmployee = (bool)row["IsEmployee"]; } if (!row["IsSalesperson"].ToString().Equals(string.Empty)) { model.IsSalesperson = (bool)row["IsSalesperson"]; } if (!row["UserPreferences"].ToString().Equals(string.Empty)) { model.UserPreferences = row["UserPreferences"].ToString(); } if (!row["PhoneNumber"].ToString().Equals(string.Empty)) { model.PhoneNumber = row["PhoneNumber"].ToString(); } if (!row["FaxNumber"].ToString().Equals(string.Empty)) { model.FaxNumber = row["FaxNumber"].ToString(); } if (!row["EmailAddress"].ToString().Equals(string.Empty)) { model.EmailAddress = row["EmailAddress"].ToString(); } if (!row["Photo"].ToString().Equals(string.Empty)) { model.Photo = (byte[])row["Photo"]; } if (!row["CustomFields"].ToString().Equals(string.Empty)) { model.CustomFields = row["CustomFields"].ToString(); } if (!row["OtherLanguages"].ToString().Equals(string.Empty)) { model.OtherLanguages = row["OtherLanguages"].ToString(); } if (!row["LastEditedBy"].ToString().Equals(string.Empty)) { model.LastEditedBy = (int)row["LastEditedBy"]; } if (!row["ValidFrom"].ToString().Equals(string.Empty)) { model.ValidFrom = (DateTime)row["ValidFrom"]; } if (!row["ValidTo"].ToString().Equals(string.Empty)) { model.ValidTo = (DateTime)row["ValidTo"]; } Add(model); } db.Close(); } } catch (Exception ex) { Logger.LogError("Fetch exception: ", ex); } }
public void Fetch() { SQLData db = new SQLData(); List <AspNetUserRolesModel> list = new List <AspNetUserRolesModel>(); string sql = "SELECT * FROM dbo.AspNetUserRoles"; try { // If filtered, add WHERE clause to the sql string. if (queryModel.search != null) { sql = string.Format(@"{0} WHERE {1}", sql, queryModel.search); } // Open the connection. if (db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString)) { // Get the total record count. string sql2 = string.Format("SELECT COUNT(*) FROM ({0}) AS C1", sql); DataTable dt2 = db.Execute(sql2, queryModel.Parameters); TotalRecords = (int)dt2.Rows[0][0]; // Build order by clause if specified. if (queryModel.orders != null) { StringBuilder sbOrders = new StringBuilder(" ORDER BY "); bool isFirst = true; foreach (Order order in queryModel.orders) { if (!isFirst) { sbOrders.Append(","); } sbOrders.AppendFormat("{0} {1}", (order.column).ToString(), order.dir); isFirst = false; } sql += sbOrders.ToString(); } // If pagination values. if (queryModel.length > 0) { sql = string.Format(@"{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, queryModel.start, queryModel.length); } // Execute query. db.sqlCommand.Parameters.Clear(); DataTable dt = db.Execute(sql, queryModel.Parameters); // Populate model with values/rows. foreach (DataRow row in dt.Rows) { AspNetUserRolesModel model = new AspNetUserRolesModel(); if (!row["UserId"].ToString().Equals(string.Empty)) { model.UserId = row["UserId"].ToString(); } if (!row["RoleId"].ToString().Equals(string.Empty)) { model.RoleId = row["RoleId"].ToString(); } Add(model); } db.Close(); } } catch (Exception ex) { Logger.LogError("AspNetUserRoles Fetch exception: ", ex); } }
public void Fetch() { SQLData db = new SQLData(); List <SuppliersModel> list = new List <SuppliersModel>(); string sql = "select * from Purchasing.Suppliers"; try { if (queryModel.search != null) { sql = string.Format(@"{0} WHERE {1}", sql, queryModel.search); } if (db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString)) { string sql2 = string.Format("SELECT COUNT(*) FROM ({0}) AS C1", sql); DataTable dt2 = db.Execute(sql2); TotalRecords = (int)dt2.Rows[0][0]; if (queryModel.orders != null) { StringBuilder sbOrders = new StringBuilder(" ORDER BY "); bool isFirst = true; foreach (Order order in queryModel.orders) { if (!isFirst) { sbOrders.Append(","); } sbOrders.AppendFormat("{0} {1}", (order.column).ToString(), order.dir); isFirst = false; } sql += sbOrders.ToString(); } if (queryModel.length > 0) { sql = string.Format(@"{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, queryModel.start, queryModel.length); } DataTable dt = db.Execute(sql); foreach (DataRow row in dt.Rows) { SuppliersModel model = new SuppliersModel(); if (!row["SupplierID"].ToString().Equals(string.Empty)) { model.SupplierID = (Int32)row["SupplierID"]; } if (!row["SupplierName"].ToString().Equals(string.Empty)) { model.SupplierName = row["SupplierName"].ToString(); } if (!row["SupplierCategoryID"].ToString().Equals(string.Empty)) { model.SupplierCategoryID = (Int32)row["SupplierCategoryID"]; } if (!row["PrimaryContactPersonID"].ToString().Equals(string.Empty)) { model.PrimaryContactPersonID = (Int32)row["PrimaryContactPersonID"]; } if (!row["AlternateContactPersonID"].ToString().Equals(string.Empty)) { model.AlternateContactPersonID = (Int32)row["AlternateContactPersonID"]; } if (!row["DeliveryMethodID"].ToString().Equals(string.Empty)) { model.DeliveryMethodID = (Int32)row["DeliveryMethodID"]; } if (!row["DeliveryCityID"].ToString().Equals(string.Empty)) { model.DeliveryCityID = (Int32)row["DeliveryCityID"]; } if (!row["PostalCityID"].ToString().Equals(string.Empty)) { model.PostalCityID = (Int32)row["PostalCityID"]; } if (!row["SupplierReference"].ToString().Equals(string.Empty)) { model.SupplierReference = row["SupplierReference"].ToString(); } if (!row["BankAccountName"].ToString().Equals(string.Empty)) { model.BankAccountName = row["BankAccountName"].ToString(); } if (!row["BankAccountBranch"].ToString().Equals(string.Empty)) { model.BankAccountBranch = row["BankAccountBranch"].ToString(); } if (!row["BankAccountCode"].ToString().Equals(string.Empty)) { model.BankAccountCode = row["BankAccountCode"].ToString(); } if (!row["BankAccountNumber"].ToString().Equals(string.Empty)) { model.BankAccountNumber = row["BankAccountNumber"].ToString(); } if (!row["BankInternationalCode"].ToString().Equals(string.Empty)) { model.BankInternationalCode = row["BankInternationalCode"].ToString(); } if (!row["PaymentDays"].ToString().Equals(string.Empty)) { model.PaymentDays = (Int32)row["PaymentDays"]; } if (!row["InternalComments"].ToString().Equals(string.Empty)) { model.InternalComments = row["InternalComments"].ToString(); } if (!row["PhoneNumber"].ToString().Equals(string.Empty)) { model.PhoneNumber = row["PhoneNumber"].ToString(); } if (!row["FaxNumber"].ToString().Equals(string.Empty)) { model.FaxNumber = row["FaxNumber"].ToString(); } if (!row["WebsiteURL"].ToString().Equals(string.Empty)) { model.WebsiteURL = row["WebsiteURL"].ToString(); } if (!row["DeliveryAddressLine1"].ToString().Equals(string.Empty)) { model.DeliveryAddressLine1 = row["DeliveryAddressLine1"].ToString(); } if (!row["DeliveryAddressLine2"].ToString().Equals(string.Empty)) { model.DeliveryAddressLine2 = row["DeliveryAddressLine2"].ToString(); } if (!row["DeliveryPostalCode"].ToString().Equals(string.Empty)) { model.DeliveryPostalCode = row["DeliveryPostalCode"].ToString(); } if (!row["DeliveryLocation"].ToString().Equals(string.Empty)) { model.DeliveryLocation = row["DeliveryLocation"].ToString(); } if (!row["PostalAddressLine1"].ToString().Equals(string.Empty)) { model.PostalAddressLine1 = row["PostalAddressLine1"].ToString(); } if (!row["PostalAddressLine2"].ToString().Equals(string.Empty)) { model.PostalAddressLine2 = row["PostalAddressLine2"].ToString(); } if (!row["PostalPostalCode"].ToString().Equals(string.Empty)) { model.PostalPostalCode = row["PostalPostalCode"].ToString(); } if (!row["LastEditedBy"].ToString().Equals(string.Empty)) { model.LastEditedBy = (Int32)row["LastEditedBy"]; } if (!row["ValidFrom"].ToString().Equals(string.Empty)) { model.ValidFrom = (DateTime)row["ValidFrom"]; } if (!row["ValidTo"].ToString().Equals(string.Empty)) { model.ValidTo = (DateTime)row["ValidTo"]; } Add(model); } } } catch (Exception ex) { Logger.LogError("Suppliers Fetch exception: ", ex); } finally { db.Close(); } }
public void Fetch() { SQLData db = new SQLData(); List <CustomerCategoriesModel> list = new List <CustomerCategoriesModel>(); string sql = "SELECT * FROM Sales.CustomerCategories"; try { if (queryModel.search != null) { sql = string.Format(@"{0} WHERE {1}", sql, queryModel.search); } if (db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString)) { string sql2 = string.Format("SELECT COUNT(*) FROM ({0}) AS C1", sql); DataTable dt2 = db.Execute(sql2); TotalRecords = (int)dt2.Rows[0][0]; if (queryModel.orders != null) { StringBuilder sbOrders = new StringBuilder(" ORDER BY "); bool isFirst = true; foreach (Order order in queryModel.orders) { if (!isFirst) { sbOrders.Append(","); } sbOrders.AppendFormat("{0} {1}", (order.column).ToString(), order.dir); isFirst = false; } sql += sbOrders.ToString(); } if (queryModel.length > 0) { sql = string.Format(@"{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, queryModel.start, queryModel.length); } DataTable dt = db.Execute(sql); foreach (DataRow row in dt.Rows) { CustomerCategoriesModel model = new CustomerCategoriesModel(); if (!row["CustomerCategoryID"].ToString().Equals(string.Empty)) { model.CustomerCategoryID = (Int32)row["CustomerCategoryID"]; } if (!row["CustomerCategoryName"].ToString().Equals(string.Empty)) { model.CustomerCategoryName = row["CustomerCategoryName"].ToString(); } if (!row["LastEditedBy"].ToString().Equals(string.Empty)) { model.LastEditedBy = (Int32)row["LastEditedBy"]; } if (!row["ValidFrom"].ToString().Equals(string.Empty)) { model.ValidFrom = (DateTime)row["ValidFrom"]; } if (!row["ValidTo"].ToString().Equals(string.Empty)) { model.ValidTo = (DateTime)row["ValidTo"]; } Add(model); } } } catch (Exception ex) { Logger.LogError("CustomerCategories Fetch exception: ", ex); } finally { db.Close(); } }
public void Fetch() { SQLData db = new SQLData(); List <StockItemsModel> list = new List <StockItemsModel>(); string sql = "select * from Warehouse.StockItems"; try { if (queryModel.search != null) { sql = string.Format(@"{0} WHERE {1}", sql, queryModel.search); } if (db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString)) { string sql2 = string.Format("SELECT COUNT(*) FROM ({0}) AS C1", sql); DataTable dt2 = db.Execute(sql2); TotalRecords = (int)dt2.Rows[0][0]; if (queryModel.orders != null) { StringBuilder sbOrders = new StringBuilder(" ORDER BY "); bool isFirst = true; foreach (Order order in queryModel.orders) { if (!isFirst) { sbOrders.Append(","); } sbOrders.AppendFormat("{0} {1}", (order.column).ToString(), order.dir); isFirst = false; } sql += sbOrders.ToString(); } if (queryModel.length > 0) { sql = string.Format(@"{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, queryModel.start, queryModel.length); } DataTable dt = db.Execute(sql); foreach (DataRow row in dt.Rows) { StockItemsModel model = new StockItemsModel(); if (!row["StockItemID"].ToString().Equals(string.Empty)) { model.StockItemID = (Int32)row["StockItemID"]; } if (!row["StockItemName"].ToString().Equals(string.Empty)) { model.StockItemName = row["StockItemName"].ToString(); } if (!row["SupplierID"].ToString().Equals(string.Empty)) { model.SupplierID = (Int32)row["SupplierID"]; } if (!row["ColorID"].ToString().Equals(string.Empty)) { model.ColorID = (Int32)row["ColorID"]; } if (!row["UnitPackageID"].ToString().Equals(string.Empty)) { model.UnitPackageID = (Int32)row["UnitPackageID"]; } if (!row["OuterPackageID"].ToString().Equals(string.Empty)) { model.OuterPackageID = (Int32)row["OuterPackageID"]; } if (!row["Brand"].ToString().Equals(string.Empty)) { model.Brand = row["Brand"].ToString(); } if (!row["Size"].ToString().Equals(string.Empty)) { model.Size = row["Size"].ToString(); } if (!row["LeadTimeDays"].ToString().Equals(string.Empty)) { model.LeadTimeDays = (Int32)row["LeadTimeDays"]; } if (!row["QuantityPerOuter"].ToString().Equals(string.Empty)) { model.QuantityPerOuter = (Int32)row["QuantityPerOuter"]; } if (!row["IsChillerStock"].ToString().Equals(string.Empty)) { model.IsChillerStock = (Boolean)row["IsChillerStock"]; } if (!row["Barcode"].ToString().Equals(string.Empty)) { model.Barcode = row["Barcode"].ToString(); } if (!row["TaxRate"].ToString().Equals(string.Empty)) { model.TaxRate = (Decimal)row["TaxRate"]; } if (!row["UnitPrice"].ToString().Equals(string.Empty)) { model.UnitPrice = (Decimal)row["UnitPrice"]; } if (!row["RecommendedRetailPrice"].ToString().Equals(string.Empty)) { model.RecommendedRetailPrice = (Decimal)row["RecommendedRetailPrice"]; } if (!row["TypicalWeightPerUnit"].ToString().Equals(string.Empty)) { model.TypicalWeightPerUnit = (Decimal)row["TypicalWeightPerUnit"]; } if (!row["MarketingComments"].ToString().Equals(string.Empty)) { model.MarketingComments = row["MarketingComments"].ToString(); } if (!row["InternalComments"].ToString().Equals(string.Empty)) { model.InternalComments = row["InternalComments"].ToString(); } if (!row["Photo"].ToString().Equals(string.Empty)) { model.Photo = (byte[])row["Photo"]; } if (!row["CustomFields"].ToString().Equals(string.Empty)) { model.CustomFields = row["CustomFields"].ToString(); } if (!row["Tags"].ToString().Equals(string.Empty)) { model.Tags = row["Tags"].ToString(); } if (!row["SearchDetails"].ToString().Equals(string.Empty)) { model.SearchDetails = row["SearchDetails"].ToString(); } if (!row["LastEditedBy"].ToString().Equals(string.Empty)) { model.LastEditedBy = (Int32)row["LastEditedBy"]; } if (!row["ValidFrom"].ToString().Equals(string.Empty)) { model.ValidFrom = (DateTime)row["ValidFrom"]; } if (!row["ValidTo"].ToString().Equals(string.Empty)) { model.ValidTo = (DateTime)row["ValidTo"]; } Add(model); } } } catch (Exception ex) { Logger.LogError("StockItems Fetch exception: ", ex); } finally { db.Close(); } }
public void Fetch() { SQLData db = new SQLData(); List <AspNetUsersModel> list = new List <AspNetUsersModel>(); string sql = "SELECT * FROM dbo.AspNetUsers"; try { if (queryModel == null) { queryModel = new QueryModel(); } if (queryModel.search != null) { sql = string.Format(@"{0} WHERE {1}", sql, queryModel.search); } if (db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString)) { string sql2 = string.Format("SELECT COUNT(*) FROM ({0}) AS C1", sql); DataTable dt2 = db.Execute(sql2); TotalRecords = (int)dt2.Rows[0][0]; if (queryModel.orders != null) { StringBuilder sbOrders = new StringBuilder(" ORDER BY "); bool isFirst = true; foreach (Order order in queryModel.orders) { if (!isFirst) { sbOrders.Append(","); } sbOrders.AppendFormat("{0} {1}", (order.column).ToString(), order.dir); isFirst = false; } sql += sbOrders.ToString(); } if (queryModel.length > 0) { sql = string.Format(@"{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, queryModel.start, queryModel.length); } DataTable dt = db.Execute(sql); foreach (DataRow row in dt.Rows) { AspNetUsersModel model = new AspNetUsersModel(); if (!row["Id"].ToString().Equals(string.Empty)) { model.Id = row["Id"].ToString(); } if (!row["Email"].ToString().Equals(string.Empty)) { model.Email = row["Email"].ToString(); } if (!row["EmailConfirmed"].ToString().Equals(string.Empty)) { model.EmailConfirmed = (Boolean)row["EmailConfirmed"]; } if (!row["PasswordHash"].ToString().Equals(string.Empty)) { model.PasswordHash = row["PasswordHash"].ToString(); } if (!row["SecurityStamp"].ToString().Equals(string.Empty)) { model.SecurityStamp = row["SecurityStamp"].ToString(); } if (!row["PhoneNumber"].ToString().Equals(string.Empty)) { model.PhoneNumber = row["PhoneNumber"].ToString(); } if (!row["PhoneNumberConfirmed"].ToString().Equals(string.Empty)) { model.PhoneNumberConfirmed = (Boolean)row["PhoneNumberConfirmed"]; } if (!row["TwoFactorEnabled"].ToString().Equals(string.Empty)) { model.TwoFactorEnabled = (Boolean)row["TwoFactorEnabled"]; } if (!row["LockoutEndDateUtc"].ToString().Equals(string.Empty)) { model.LockoutEndDateUtc = (DateTime)row["LockoutEndDateUtc"]; } if (!row["LockoutEnabled"].ToString().Equals(string.Empty)) { model.LockoutEnabled = (Boolean)row["LockoutEnabled"]; } if (!row["AccessFailedCount"].ToString().Equals(string.Empty)) { model.AccessFailedCount = (Int32)row["AccessFailedCount"]; } if (!row["UserName"].ToString().Equals(string.Empty)) { model.UserName = row["UserName"].ToString(); } // Get Roles AspNetUserRoles dbRoles = new AspNetUserRoles(); dbRoles.queryModel = new QueryModel("UserId=@UserId"); dbRoles.queryModel.Parameters.Add(new SqlParameter("@UserId", model.Id)); dbRoles.Fetch(); model.Roles = dbRoles.Items; Add(model); } db.Close(); } } catch (Exception ex) { Logger.LogError("AspNetUsers Fetch exception: ", ex); } }
// Users public PeopleModel GetPerson(int personId) { SQLData db = new SQLData(); string sql = "SELECT * FROM Application.People WHERE PersonId=@PersonId"; List <SqlParameter> parms = new List <SqlParameter>(); PeopleModel model = new PeopleModel(); try { db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); parms.Add(new SqlParameter("@PersonId", personId)); DataTable dt = db.Execute(sql, parms); DataRow row = dt.Rows[0]; if (!row["PersonID"].ToString().Equals(string.Empty)) { model.PersonID = (int)row["PersonID"]; } if (!row["FullName"].ToString().Equals(string.Empty)) { model.FullName = row["FullName"].ToString(); } if (!row["PreferredName"].ToString().Equals(string.Empty)) { model.PreferredName = row["PreferredName"].ToString(); } if (!row["SearchName"].ToString().Equals(string.Empty)) { model.SearchName = row["SearchName"].ToString(); } if (!row["IsPermittedToLogon"].ToString().Equals(string.Empty)) { model.IsPermittedToLogon = (bool)row["IsPermittedToLogon"]; } if (!row["LogonName"].ToString().Equals(string.Empty)) { model.LogonName = row["LogonName"].ToString(); } if (!row["IsExternalLogonProvider"].ToString().Equals(string.Empty)) { model.IsExternalLogonProvider = (bool)row["IsExternalLogonProvider"]; } if (!row["HashedPassword"].ToString().Equals(string.Empty)) { model.HashedPassword = (byte[])row["HashedPassword"]; } if (!row["IsSystemUser"].ToString().Equals(string.Empty)) { model.IsSystemUser = (bool)row["IsSystemUser"]; } if (!row["IsEmployee"].ToString().Equals(string.Empty)) { model.IsEmployee = (bool)row["IsEmployee"]; } if (!row["IsSalesperson"].ToString().Equals(string.Empty)) { model.IsSalesperson = (bool)row["IsSalesperson"]; } if (!row["UserPreferences"].ToString().Equals(string.Empty)) { model.UserPreferences = row["UserPreferences"].ToString(); } if (!row["PhoneNumber"].ToString().Equals(string.Empty)) { model.PhoneNumber = row["PhoneNumber"].ToString(); } if (!row["FaxNumber"].ToString().Equals(string.Empty)) { model.FaxNumber = row["FaxNumber"].ToString(); } if (!row["EmailAddress"].ToString().Equals(string.Empty)) { model.EmailAddress = row["EmailAddress"].ToString(); } if (!row["Photo"].ToString().Equals(string.Empty)) { model.Photo = (byte[])row["Photo"]; } if (!row["CustomFields"].ToString().Equals(string.Empty)) { model.CustomFields = row["CustomFields"].ToString(); } if (!row["OtherLanguages"].ToString().Equals(string.Empty)) { model.OtherLanguages = row["OtherLanguages"].ToString(); } if (!row["LastEditedBy"].ToString().Equals(string.Empty)) { model.LastEditedBy = (int)row["LastEditedBy"]; } if (!row["ValidFrom"].ToString().Equals(string.Empty)) { model.ValidFrom = (DateTime)row["ValidFrom"]; } if (!row["ValidTo"].ToString().Equals(string.Empty)) { model.ValidTo = (DateTime)row["ValidTo"]; } } catch (Exception ex) { Logger.LogError("GetPerson exception: ", ex); } finally { db.Close(); } return(model); }
public int AddUser(AspNetNewUserModel model) { int _ret = 0; string userId = Guid.NewGuid().ToString(); SQLData db = new SQLData(); string sql = @"INSERT INTO dbo.AspNetUsers (Id, Email, EmailConfirmed, PasswordHash, PhoneNumber, PhoneNumberConfirmed, TwoFactorEnabled, LockoutEnabled, UserName, AccessFailedCount) VALUES(@Id, @Email, @EmailConfirmed, @PasswordHash, @PhoneNumber, @PhoneNumberConfirmed, @TwoFactorEnabled, @LockoutEnabled, @UserName, 0)" ; try { // Insert AspNetUsers table information. List <SqlParameter> parms = new List <SqlParameter>(); parms.Add(new SqlParameter("@Id", userId)); parms.Add(new SqlParameter("@Email", model.Email)); parms.Add(new SqlParameter("@EmailConfirmed", model.EmailConfirmed)); parms.Add(new SqlParameter("@PasswordHash", model.PasswordHash)); parms.Add(new SqlParameter("@PhoneNumber", model.PhoneNumber)); parms.Add(new SqlParameter("@PhoneNumberConfirmed", model.PhoneNumberConfirmed)); parms.Add(new SqlParameter("@TwoFactorEnabled", model.TwoFactorEnabled)); parms.Add(new SqlParameter("@LockoutEnabled", model.LockoutEnabled)); parms.Add(new SqlParameter("@UserName", model.UserName)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); db.ExecuteNonQuery(sql, parms); db.Close(); // Insert User roles. if (model.cbAdministrator) { InsertUserRole(userId, "Administrator"); } if (model.cbContractor) { InsertUserRole(userId, "Contractor"); } if (model.cbExecutive) { InsertUserRole(userId, "Executive"); } if (model.cbInventory) { InsertUserRole(userId, "Inventory"); } if (model.cbSales) { InsertUserRole(userId, "Sales"); } if (model.cbSupplier) { InsertUserRole(userId, "Supplier"); } if (model.cbUser) { InsertUserRole(userId, "User"); } if (model.cbVendor) { InsertUserRole(userId, "Vendor"); } } catch (Exception ex) { Logger.LogError("WWIDal.AddUser Exception", ex); } return(_ret); }
public int UpdateUser(AspNetEditUserModel model) { int _ret = 0; SQLData db = new SQLData(); StringBuilder sb = new StringBuilder(@"UPDATE dbo.AspNetUsers SET Email=@Email, EmailConfirmed=@EmailConfirmed, PhoneNumber=@PhoneNumber, PhoneNumberConfirmed=@PhoneNumberConfirmed, TwoFactorEnabled=@TwoFactorEnabled, LockoutEnabled=@LockoutEnabled, LockoutEndDateUtc=@LockoutEndDateUtc, AccessFailedCount=@AccessFailedCount" ); sb.Append(" WHERE Id=@Id"); try { List <SqlParameter> parms = new List <SqlParameter>(); parms.Add(new SqlParameter("@Email", model.Email)); parms.Add(new SqlParameter("@EmailConfirmed", model.EmailConfirmed)); parms.Add(new SqlParameter("@PhoneNumber", (string.IsNullOrEmpty(model.PhoneNumber)) ? "" : model.PhoneNumber)); parms.Add(new SqlParameter("@PhoneNumberConfirmed", model.PhoneNumberConfirmed)); parms.Add(new SqlParameter("@TwoFactorEnabled", model.TwoFactorEnabled)); parms.Add(new SqlParameter("@LockoutEnabled", model.LockoutEnabled)); parms.Add(new SqlParameter("@LockoutEndDateUtc", model.LockoutEndDateUtc)); parms.Add(new SqlParameter("@AccessFailedCount", model.AccessFailedCount)); parms.Add(new SqlParameter("@Id", model.Id)); db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString); _ret = db.ExecuteNonQuery(sb.ToString(), parms); string roleName = "Administrator"; bool userHasRoleName = WWIDal.UserHasRoleName(model.Id, roleName); if (model.cbAdministrator && !userHasRoleName) { InsertUserRole(model.Id, roleName); } else if (!model.cbAdministrator && userHasRoleName) { DeleteUserRole(model.Id, roleName); } roleName = "Contractor"; userHasRoleName = WWIDal.UserHasRoleName(model.Id, roleName); if (model.cbContractor && !userHasRoleName) { InsertUserRole(model.Id, roleName); } else if (!model.cbAdministrator && userHasRoleName) { DeleteUserRole(model.Id, roleName); } roleName = "Executive"; userHasRoleName = WWIDal.UserHasRoleName(model.Id, roleName); if (model.cbExecutive && !userHasRoleName) { InsertUserRole(model.Id, roleName); } else if (!model.cbExecutive && userHasRoleName) { DeleteUserRole(model.Id, roleName); } roleName = "Inventory"; userHasRoleName = WWIDal.UserHasRoleName(model.Id, roleName); if (model.cbInventory && !userHasRoleName) { InsertUserRole(model.Id, roleName); } else if (!model.cbInventory && userHasRoleName) { DeleteUserRole(model.Id, roleName); } roleName = "Sales"; userHasRoleName = WWIDal.UserHasRoleName(model.Id, roleName); if (model.cbSales && !userHasRoleName) { InsertUserRole(model.Id, roleName); } else if (!model.cbSales && userHasRoleName) { DeleteUserRole(model.Id, roleName); } roleName = "Supplier"; userHasRoleName = WWIDal.UserHasRoleName(model.Id, roleName); if (model.cbSupplier && !userHasRoleName) { InsertUserRole(model.Id, roleName); } else if (!model.cbSupplier && userHasRoleName) { DeleteUserRole(model.Id, roleName); } roleName = "User"; userHasRoleName = WWIDal.UserHasRoleName(model.Id, roleName); if (model.cbUser && !userHasRoleName) { InsertUserRole(model.Id, roleName); } else if (!model.cbUser && userHasRoleName) { DeleteUserRole(model.Id, roleName); } roleName = "Vendor"; userHasRoleName = WWIDal.UserHasRoleName(model.Id, roleName); if (model.cbVendor && !userHasRoleName) { InsertUserRole(model.Id, roleName); } else if (!model.cbVendor && userHasRoleName) { DeleteUserRole(model.Id, roleName); } } catch (Exception ex) { Logger.LogError("UpdateUser exception: ", ex); } finally { db.Close(); } return(_ret); }
public void Fetch() { SQLData db = new SQLData(); List <AspNetAccessLevelsModel> list = new List <AspNetAccessLevelsModel>(); string sql = "SELECT * FROM dbo.AspNetAccessLevels"; try { if (queryModel.search != null) { sql = string.Format(@"{0} WHERE {1}", sql, queryModel.search); } if (db.Open(ConfigurationManager.ConnectionStrings["WWI"].ConnectionString)) { string sql2 = string.Format("SELECT COUNT(*) FROM ({0}) AS C1", sql); DataTable dt2 = db.Execute(sql2); TotalRecords = (int)dt2.Rows[0][0]; if (queryModel.orders != null) { StringBuilder sbOrders = new StringBuilder(" ORDER BY "); bool isFirst = true; foreach (Order order in queryModel.orders) { if (!isFirst) { sbOrders.Append(","); } sbOrders.AppendFormat("{0} {1}", (order.column).ToString(), order.dir); isFirst = false; } sql += sbOrders.ToString(); } if (queryModel.length > 0) { sql = string.Format(@"{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, queryModel.start, queryModel.length); } DataTable dt = db.Execute(sql); foreach (DataRow row in dt.Rows) { AspNetAccessLevelsModel model = new AspNetAccessLevelsModel(); if (!row["Id"].ToString().Equals(string.Empty)) { model.Id = row["Id"].ToString(); } if (!row["Name"].ToString().Equals(string.Empty)) { model.Name = row["Name"].ToString(); } if (!row["Description"].ToString().Equals(string.Empty)) { model.Description = row["Description"].ToString(); } if (!row["AccessLevel"].ToString().Equals(string.Empty)) { model.AccessLevel = (Int32)row["AccessLevel"]; } Add(model); } db.Close(); } } catch (Exception ex) { Logger.LogError("AspNetAccessLevels Fetch exception: ", ex); } }