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