public static Product UpdateProduct(Product product) { Product result = null; try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { database.Configuration.ProxyCreationEnabled = false; Product productToUpdate = database.Products.FirstOrDefault(p => p.ProductID == product.ProductID); productToUpdate.ProductNumber = product.ProductNumber; productToUpdate.Name = product.Name; productToUpdate.Color = product.Color; productToUpdate.ListPrice = product.ListPrice; productToUpdate.ProductDescription = product.ProductDescription; productToUpdate.ProductCategoryID = product.ProductCategoryID; productToUpdate.Quantity = product.Quantity; productToUpdate.IsActive = product.IsActive; database.SaveChanges(); result = productToUpdate; //result = database.Products // .Include(c=>c.ProductCategory) // .Include(so=>so.SalesOrderItems) // .FirstOrDefault(c => c.ProductID == productToUpdate.ProductID); } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Iterate through catagories", SystemReason = "Exception reading catagories", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(result); }
public static List <ProductCategory> CategoriesList() { List <ProductCategory> categoriesList = new List <ProductCategory>(); try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { database.Configuration.ProxyCreationEnabled = false; var categories = from category in database.ProductCategories select category; foreach (var cat in categories) { var q = from category in database.ProductCategories .Include(c => c.Products) where category.ProductCategoryID == cat.ProductCategoryID select category.Products.Count; cat.ProductCount = q.FirstOrDefault(); } categoriesList = categories.ToList(); } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Iterate through categories", SystemReason = "Exception reading categories", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(categoriesList); }
public static List <SalesOrder> GetSalesOrderList(string userName, int take, int skip) { List <SalesOrder> salesOrderList = new List <SalesOrder>(); try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { database.Configuration.ProxyCreationEnabled = false; var customer = database.Customers.FirstOrDefault(c => c.Username == userName); if (customer != null) { var salesorders = from salesorder in database.SalesOrders .Include(so => so.SalesOrderItems.Select(soi => soi.Product.ProductCategory)) .Where(so => so.CustomerID == customer.CustomerID) orderby salesorder.SalesOrderID ascending select salesorder; salesOrderList = salesorders.Skip(skip).Take(take).ToList(); } } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Iterate through product history", SystemReason = "Exception reading product history", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(salesOrderList); }
public static bool Authenicate(string username, string password) { bool result = false; try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { Customer customer = database.Customers.FirstOrDefault(c => c.Username == username); if (customer == null) { return(false); } byte[] salt = Convert.FromBase64String(customer.PasswordSalt); byte[] passwordBytes = Encoding.UTF8.GetBytes(password); byte[] hash = Convert.FromBase64String(customer.PasswordHash); if (CompareByteArrays(hash, GeneratedSaltedHash(passwordBytes, salt))) { return(true); } } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Authenicating user", SystemReason = "Exception authenicating user", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(result); }
public static string[] GetUserRoles(string username) { List <string> result = new List <string>(); try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { Customer customer = database.Customers.FirstOrDefault(c => c.Username == username); if (customer != null) { result.Add(Role.User.ToString()); if (customer.IsAdmin) { result.Add(Role.Admin.ToString()); } } } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Authorizing user", SystemReason = "Exception authorizing user", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(result.ToArray <string>()); }
public static int GetSalesOrderListCount(string userName) { int salesOrderListCount = 0; try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { var customer = database.Customers.FirstOrDefault(c => c.Username == userName); if (customer != null) { var salesorders = from salesorder in database.SalesOrders //.Include(so => so.SalesOrderItems.Select(soi => soi.Product.ProductCategory)) .Where(so => so.CustomerID == customer.CustomerID) select salesorder; salesOrderListCount = salesorders.Count(); } } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Iterate through product history", SystemReason = "Exception reading product history", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(salesOrderListCount); }
//public static void CreateInitialUsers() //{ // CreateUser(new CustomerData() // { // Username = "******", // FirstName = "Fred", // LastName = "Flintstone" // }, "Pa$$w0rd"); // CreateUser(new CustomerData() // { // Username = "******", // FirstName = "Bert", // LastName = "Andernie" // }, "Pa$$w0rd"); // Authenicate("Fred", "Pa$$w0rd"); //} private static void CreateUser(CustomerData customer, string password) { try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { byte[] salt = CreateSalt(32); byte[] passwordBytes = Encoding.UTF8.GetBytes(password); Customer customerToAdd = new Customer() { Username = customer.Username, FirstName = customer.FirstName, LastName = customer.LastName, IsAdmin = false, PasswordSalt = Convert.ToBase64String(salt), PasswordHash = Convert.ToBase64String(GeneratedSaltedHash(passwordBytes, salt)), }; database.Customers.Add(customerToAdd); database.SaveChanges(); } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Authenicating user", SystemReason = "Exception authenicating user", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } }
public static bool RemoveProduct(int productID) { bool result = false; try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { Product productToRemove = database.Products.FirstOrDefault(c => c.ProductID == productID); if (productToRemove != null) { database.Products.Remove(productToRemove); //productToRemove.MarkAsDeleted(); database.SaveChanges(); result = true; } } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Removing product", SystemReason = "Removing product", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(result); }
public static int AddProduct(Product product) { int result = 0; try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { database.Configuration.ProxyCreationEnabled = false; product.ModifiedDate = DateTime.Now; product.rowguid = new Guid(); database.Products.Add(product); //database.Products.AddObject(product); database.SaveChanges(); Product newProduct = database.Products.FirstOrDefault(c => c.ProductNumber == product.ProductNumber); result = (newProduct == null) ? 0 : newProduct.ProductID; } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Adding product", SystemReason = "Exception adding product", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(result); }
public static bool RemoveCategory(int categoryID) { bool result = false; try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { database.Configuration.ProxyCreationEnabled = false; ProductCategory categoryToRemove = database.ProductCategories.FirstOrDefault(c => c.ProductCategoryID == categoryID); if (categoryToRemove != null) { database.ProductCategories.Remove(categoryToRemove); database.SaveChanges(); result = true; } } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Removing category", SystemReason = "Removing category", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(result); }
public static int AddCategory(ProductCategory category) { int result = 0; try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { database.Configuration.ProxyCreationEnabled = false; category.ModifiedDate = DateTime.Now; category.rowguid = new Guid(); database.ProductCategories.Add(category); database.SaveChanges(); ProductCategory newCategory = database.ProductCategories.FirstOrDefault(c => c.ProductCategoryID == category.ProductCategoryID); result = (newCategory == null) ? 0 : newCategory.ProductCategoryID; } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Adding category", SystemReason = "Exception adding category", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(result); }
//public static int ProductListCount() //{ // int productsListCount = 0; // try // { // using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) // { // var products = from product in database.Products // .Include(p => p.ProductCategory) // orderby product.ProductID // select product; // productsListCount = products.Count(); // } // } // catch (Exception e) // { // if (e.InnerException is System.Data.SqlClient.SqlException) // { // DatabaseFault dbf = new DatabaseFault // { // DbOperation = "Connect to database", // DbReason = "Exception accessing database", // DbMessage = e.InnerException.Message // }; // throw new FaultException<DatabaseFault>(dbf); // } // else // { // SystemFault sf = new SystemFault // { // SystemOperation = "Counting products", // SystemReason = "Exception count products", // SystemMessage = e.Message // }; // throw new FaultException<SystemFault>(sf); // } // } // return productsListCount; //} public static Product GetProduct(int productID) { Product productToGet = null; try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { database.Configuration.ProxyCreationEnabled = false; var products = from product in database.Products .Include(p => p.ProductCategory) where product.ProductID == productID select product; productToGet = products.FirstOrDefault(); } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Getting product", SystemReason = "Exception getting product", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(productToGet); }
public static ProductCategory GetCategory(int categoryID) { ProductCategory categoryToGet = new ProductCategory(); try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { database.Configuration.ProxyCreationEnabled = false; var categories = from category in database.ProductCategories where category.ProductCategoryID == categoryID select category; categoryToGet = categories.FirstOrDefault(); } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Iterate through categories", SystemReason = "Exception reading categories", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(categoryToGet); }
public static bool IsUserAdmin(string username) { bool result = false; try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { Customer customer = database.Customers.FirstOrDefault(c => c.Username == username); if (customer != null && customer.IsAdmin) { result = true; } } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Is Admin", SystemReason = "Exception authorizing user", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(result); }
public static List <SalesHistoryData> GetSalesHistory(string userName, int take, int skip) { List <SalesHistoryData> salesHistoryList = new List <SalesHistoryData>(); List <SalesOrder> salesOrderList = new List <SalesOrder>(); try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { database.Configuration.ProxyCreationEnabled = false; var customer = database.Customers.FirstOrDefault(c => c.Username == userName); if (customer != null) { var salesorders = from salesorder in database.SalesOrders .Include(so => so.SalesOrderItems.Select(soi => soi.Product.ProductCategory)) .Where(so => so.CustomerID == customer.CustomerID) orderby salesorder.SalesOrderID ascending select salesorder; salesOrderList = salesorders.Skip(skip).Take(take).ToList(); foreach (var salesOrder in salesOrderList) { foreach (var salesOrderItem in salesOrder.SalesOrderItems.OrderBy(item => item.OrderBy)) { salesHistoryList.Add(new SalesHistoryData() { SalesOrderNumber = salesOrder.SalesOrderID, OrderDate = salesOrder.OrderDate, ProductNumber = salesOrderItem.Product.ProductNumber, ProductName = salesOrderItem.Product.Name, Quantity = salesOrderItem.Quantity, PerItemCost = salesOrderItem.Product.ListPrice, ItemSubTotal = salesOrderItem.Quantity * salesOrderItem.Product.ListPrice, OrderTotal = salesOrder.SalesOrderItems.Sum(item => item.Quantity * item.Product.ListPrice), SortOrder = salesOrderItem.OrderBy }); } } } } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Iterate through product history", SystemReason = "Exception reading product history", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(salesHistoryList); }
public static void SaveSalesOrder(List <ShoppingCartItem> shoppingCart, string username) { try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { database.Configuration.ProxyCreationEnabled = false; Customer customer = database.Customers.First(c => c.Username == username); SalesOrder salesOrder = new SalesOrder() { CustomerID = customer.CustomerID, Customer = customer, SalesOrderID = 0, SalesOrderItems = new List <SalesOrderItem>(), OrderDate = DateTime.Now }; foreach (ShoppingCartItem item in shoppingCart) { Product matchingProduct = database.Products.FirstOrDefault(p => p.ProductNumber == item.ProductNumber); if (matchingProduct == null) { throw new FaultException <SystemFault>(new SystemFault { SystemOperation = "Checking out", SystemReason = "Shopping cart", SystemMessage = String.Format("Product {0} does not exist.", item.ProductNumber) }); } SalesOrderItem salesOrderItem = new SalesOrderItem() { SalesOrderID = 0, ProductID = matchingProduct.ProductID, ActualCost = matchingProduct.ListPrice, OrderBy = item.OrderBy, ModifiedDate = DateTime.Now, Quantity = item.Quantity, }; salesOrder.SalesOrderItems.Add(salesOrderItem); } database.SalesOrders.Add(salesOrder); database.SaveChanges(); } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Checking out", SystemReason = "Exception checking out shopping cart", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } }
public static ProductCategory UpdateCategory(ProductCategory category) { ProductCategory result = null; try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { database.Configuration.ProxyCreationEnabled = false; ProductCategory categoryToUpdate = database.ProductCategories.FirstOrDefault(c => c.ProductCategoryID == category.ProductCategoryID); if (categoryToUpdate == null) { categoryToUpdate = new ProductCategory() { ProductCategoryID = 0, Name = category.Name, IsActive = category.IsActive, ModifiedDate = DateTime.Now, rowguid = Guid.NewGuid() }; database.ProductCategories.Add(categoryToUpdate); } else { categoryToUpdate.Name = category.Name; categoryToUpdate.IsActive = category.IsActive; categoryToUpdate.ModifiedDate = DateTime.Now; categoryToUpdate.rowguid = Guid.NewGuid(); } database.SaveChanges(); result = database.ProductCategories.FirstOrDefault(c => c.rowguid == categoryToUpdate.rowguid); } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Updating category", SystemReason = "Updating category", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(result); }
public static List <ProductData> ProductList() // (int take, int skip) { List <ProductData> productsList = new List <ProductData>(); try { using (BicycleWorldDataModelContainer database = new BicycleWorldDataModelContainer()) { var products = from product in database.Products .Include(p => p.ProductCategory) .Include(so => so.SalesOrderItems) orderby product.ProductID select new ProductData() { ProductNumber = product.ProductNumber, ProductID = product.ProductID, CategoryID = product.ProductCategory.ProductCategoryID, CategoryName = product.ProductCategory.Name, Color = product.Color, ListPrice = product.ListPrice, Name = product.Name, ProductDescription = product.ProductDescription, Quantity = product.Quantity, SalesOrderCount = product.SalesOrderItems.Count, IsActive = product.IsActive, IsCategoryActive = product.ProductCategory.IsActive }; //select product; //productsList = products.Skip(skip).Take(take).ToList(); productsList = products.ToList(); } } catch (Exception e) { if (e.InnerException is System.Data.SqlClient.SqlException) { DatabaseFault dbf = new DatabaseFault { DbOperation = "Connect to database", DbReason = "Exception accessing database", DbMessage = e.InnerException.Message }; throw new FaultException <DatabaseFault>(dbf); } else { SystemFault sf = new SystemFault { SystemOperation = "Iterate through products", SystemReason = "Exception reading products", SystemMessage = e.Message }; throw new FaultException <SystemFault>(sf); } } return(productsList); }