//Given a Product object, update the Products table on Product_ID
//Returns a boolean
public bool Update_Product_By_Product_ID(DataModel.Product product_in)
{
var ret = true;
using (var context = new DataModel.WarehouseContext())
{
// Get and update Product
var product = context.Products.SingleOrDefault(p => p.Product_ID == product_in.Product_ID);
if (product != null)
{
product.Product_Name = product_in.Product_Name;
product.Product_Price = product_in.Product_Price;
product.Product_UPC = product_in.Product_UPC;
product.CategoryRefID = product_in.CategoryRefID;
//Apply change
context.Products.Attach(product);
context.Entry(product).State = System.Data.Entity.EntityState.Modified;
//Check execution of transaction - we expect 1 change to have occurred
var execution_result = context.SaveChanges();
if (execution_result != 1)
{
ret = false;
}
}
else
{
ret = false;
}
}
return(ret);
}
//Given a UPC code, delete Product from Products table
//Returns a boolean
public bool Delete_Product_By_UPC(long product_UPC, ref string msg)
{
var ret = true;
using (var context = new DataModel.WarehouseContext())
{
// Delete Product from the database using product UPC
DataModel.Product product = new DataModel.Product {
Product_UPC = product_UPC
};
context.Products.Attach(product);
context.Products.Remove(product);
//Check execution of transaction - we expect 1 change to have occurred
var execution_result = context.SaveChanges();
if (execution_result != 1)
{
msg = "Product was not deleted";
ret = false;
}
else
{
msg = "Product deleted";
}
}
return(ret);
}
/// <summary>
/// MSCS 701-702 Topics in Math Sts, & Comp Sci
/// Service Oriented Architecture (SOA)
/// Spring 2019
/// Omar Waller, Andrew Jacobson
///
/// Description: This class contains queries to the Products table in the database configured in the app.config file. .
/// </summary>
//Given a Product object, add to Products table
//Returns a boolean
public bool Create(DataModel.Product product_in, ref string msg)
{
var ret = true;
using (var context = new DataModel.WarehouseContext())
{
// Create and save a new Product
var newProduct = new DataModel.Product();
newProduct.Product_Name = product_in.Product_Name;
newProduct.Product_Price = product_in.Product_Price;
newProduct.Product_UPC = product_in.Product_UPC;
newProduct.CategoryRefID = product_in.CategoryRefID;
//Add the Product
context.Products.Add(newProduct);
//Check execution of transaction - we expect 1 change to have occurred
if (context.SaveChanges() != 1)
{
msg = "Issue adding product";
ret = false;
}
else
{
msg = "Product created";
}
}
return(ret);
}
// This method updates a Category record in the Warehouse database and returns true if the operation was successful.
public bool Update_Category_By_ID(DataModel.Category category)
{
using (var context = new DataModel.WarehouseContext())
{
// Get and update Category
bool success = false;
var cat = context.Categories.SingleOrDefault(c => c.Category_ID == category.Category_ID);
if (cat != null)
{
cat.Category_Name = category.Category_Name;
cat.Category_Description = category.Category_Description;
//Apply change
context.Categories.Attach(cat);
context.Entry(cat).State = System.Data.Entity.EntityState.Modified;
}
//Check execution of transaction - we expect 1 change to have occurred
var execution_result = context.SaveChanges();
if (execution_result != 1)
{
success = false;
}
return(success);
}
}
//This method returns a list of warehouses given a UPC number.
public List <DataModel.Inventory> Get_Inventories_By_Warehouse_Name(string warehouse_name, ref string msg)
{
List <DataModel.Inventory> inventory_list = new List <DataModel.Inventory>();
using (var context = new DataModel.WarehouseContext())
{
try
{
var inventories = context.Inventories.Where(i => i.WarehouseID.Warehouse_Name == warehouse_name);
if (inventories != null)
{
inventory_list = inventories.ToList();
msg = "Inventories found - DAL";
return(inventory_list);
}
else
{
msg = "Warehouse not found";
}
}
catch (Exception ex)
{
throw new InvalidOperationException("No item found", ex);
}
}
return(inventory_list);
}
//Given an ID, get Product from Products table
//Returns Product object
public DataModel.Product Get_Product_By_ID(int product_ID)
{
//Create Product object
DataModel.Product product = new DataModel.Product();
using (var context = new DataModel.WarehouseContext())
{
try
{
// Get Product from database
var product_Qs = context.Products.SingleOrDefault(p => p.Product_ID == product_ID);
if (product_Qs != null)
{
product.Product_ID = product_Qs.Product_ID;
product.Product_Name = product_Qs.Product_Name;
product.Product_Price = product_Qs.Product_Price;
product.Product_UPC = product_Qs.Product_UPC;
product.CategoryRefID = product_Qs.CategoryRefID;
return(product);
}
}
catch (Exception ex)
{
throw new InvalidOperationException("No item found", ex);
}
}
return(product);
}
//Return a list of all Warehouses in the database.
public List <DataModel.Warehouse> Get_All_Warehouses()
{
List <DataModel.Warehouse> warehouse_list = new List <DataModel.Warehouse>();
using (var context = new DataModel.WarehouseContext())
{
try
{
var warehouses = context.Warehouses;
if (warehouses != null)
{
warehouse_list = warehouses.ToList();
return(warehouse_list);
}
}
catch (Exception ex)
{
throw new InvalidOperationException("No item found", ex);
}
}
return(warehouse_list);
}
/// <summary>
/// MSCS 701-702 Topics in Math Sts, & Comp Sci
/// Service Oriented Architecture (SOA)
/// Spring 2019
/// Omar Waller
///
/// Description: This class contains CRUD and Get queries to the Inventory table in the Warehouse database configured
/// in the app.config file.
/// </summary>
// This method creates a Inventory record in the Warehouse database and returns true if the operation was successful.
public bool Create(DataModel.Inventory inventory, ref string msg)
{
using (var context = new DataModel.WarehouseContext())
{
// Create and save a new Products
DataModel.Inventory newInventory = new DataModel.Inventory();
bool success = false;
newInventory.Product_Quantity = inventory.Product_Quantity;
newInventory.Inventory_ID = inventory.Inventory_ID;
newInventory.Product_ID = inventory.Product_ID;
newInventory.Warehouse_ID = inventory.Warehouse_ID;
context.Inventories.Add(newInventory);
//Check execution of transaction - we expect 1 change to have occurred
var execution_result = context.SaveChanges();
if (execution_result != 1)
{
msg = "Inventory not created in DB - DAL";
}
else
{
msg = "Inventory object successfully created.";
success = true;
}
return(success);
}
}
/// <summary>
/// MSCS 701-702 Topics in Math Sts, & Comp Sci
/// Service Oriented Architecture (SOA)
/// Spring 2019
/// Omar Waller, Andrew Jacobson
///
/// Description: This class contains CRUD and Get queries to the Warehouse table in the Warehouse database configured
/// in the app.config file.
/// </summary>
//Given a Warehouse object, add to Warehouses table
//Returns a boolean
public bool Create(DataModel.Warehouse warehouse_in, ref string msg)
{
var ret = true;
using (var context = new DataModel.WarehouseContext())
{
// Create and save a new Warehouse
var newWarehouse = new DataModel.Warehouse();
newWarehouse.Warehouse_Name = warehouse_in.Warehouse_Name;
newWarehouse.Street = warehouse_in.Street;
newWarehouse.City = warehouse_in.City;
newWarehouse.State = warehouse_in.State;
newWarehouse.Zipcode = warehouse_in.Zipcode;
//Add the Warehouse
context.Warehouses.Add(newWarehouse);
//Check execution of transaction - we expect 1 change to have occurred
var execution_result = context.SaveChanges();
if (execution_result != 1)
{
msg = "Warehouse was not added";
ret = false;
}
else
{
msg = "Warehouse added";
}
}
return(ret);
}
//Given an ID, delete Warehouse from Warehouses
//Returns a boolean
public bool Delete_Warehouse_By_ID(int warehouse_id, ref string msg)
{
var ret = true;
using (var context = new DataModel.WarehouseContext())
{
// Delete Warehouse from the database using warehouse ID
var warehouse = new DataModel.Warehouse {
Warehouse_ID = warehouse_id
};
context.Warehouses.Attach(warehouse);
context.Warehouses.Remove(warehouse);
//Check execution of transaction - we expect 1 change to have occurred
var execution_result = context.SaveChanges();
if (execution_result != 1)
{
msg = "Warehouse was not deleted";
ret = false;
}
else
{
msg = "Warehouse deleted";
}
}
return(ret);
}
//Given a name, get Warehouse from Warehouses table
//Returns a Warehouse object
public DataModel.Warehouse Get_Warehouse_By_Name(string warehouse_name)
{
//Create Warehouse object
DataModel.Warehouse warehouse = new DataModel.Warehouse();
using (var context = new DataModel.WarehouseContext())
{
try
{
//Get Warehouse from database
var warehouse_Qs = context.Warehouses.SingleOrDefault(w => w.Warehouse_Name == warehouse_name);
//Translate query result to Warehouse object
if (warehouse_Qs != null)
{
warehouse.Warehouse_ID = warehouse_Qs.Warehouse_ID;
warehouse.Warehouse_Name = warehouse_Qs.Warehouse_Name;
warehouse.Street = warehouse_Qs.Street;
warehouse.City = warehouse_Qs.City;
warehouse.State = warehouse_Qs.State;
warehouse.Zipcode = warehouse_Qs.Zipcode;
return(warehouse);
}
}
catch (Exception ex)
{
throw new InvalidOperationException("No item found", ex);
}
}
return(warehouse);
}
public List <DataModel.Category> Get_All_Categories()
{
List <DataModel.Category> all_categories = new List <DataModel.Category>();
using (var context = new DataModel.WarehouseContext())
{
// Get product
var all_cat = context.Categories;
if (all_cat != null)
{
all_categories = all_cat.ToList();
return(all_categories);
}
}
return(all_categories);
}
// This method retrieves a Category record in the Warehouse database using the category ID.
public DataModel.Category Get_Category_By_ID(int category_id)
{
DataModel.Category category = new DataModel.Category();
using (var context = new DataModel.WarehouseContext())
{
// Get product
var cat = context.Categories.SingleOrDefault(c => c.Category_ID == category_id);
if (cat != null)
{
category.Category_ID = cat.Category_ID;
category.Category_Name = cat.Category_Name;
category.Category_Description = cat.Category_Description;
return(category);
}
}
return(category);
}
//Given a Warehouse object, update the Warehouses table on Warehouse_ID
//Returns a boolean
public bool Update(DataModel.Warehouse warehouse_in, ref string msg)
{
var ret = true;
using (var context = new DataModel.WarehouseContext())
{
// Get and update Warehouse
var warehouse = context.Warehouses.First(w => w.Warehouse_ID == warehouse_in.Warehouse_ID);
if (warehouse != null)
{
warehouse.Warehouse_Name = warehouse_in.Warehouse_Name;
warehouse.Street = warehouse_in.Street;
warehouse.City = warehouse_in.City;
warehouse.State = warehouse_in.State;
warehouse.Zipcode = warehouse_in.Zipcode;
//Apply change
context.Warehouses.Attach(warehouse);
context.Entry(warehouse).State = System.Data.Entity.EntityState.Modified;
//Check execution of transaction - we expect 1 change to have occurred
var execution_result = context.SaveChanges();
if (execution_result != 1)
{
msg = "Warehouse was not updated";
ret = false;
}
else
{
msg = "Warehouse updated";
}
}
else
{
msg = "No Warehouse found with the provided ID";
ret = false;
}
}
return(ret);
}
// This method deletes a Category record in the Warehouse database and returns true if the operation was successful.
public bool Delete_Category_By_ID(int category_id)
{
using (var context = new DataModel.WarehouseContext())
{
// Delete the category using category ID
bool success = false;
DataModel.Category category = new DataModel.Category {
Category_ID = category_id
};
context.Categories.Attach(category);
context.Categories.Remove(category);
//Check execution of transaction - we expect 1 change to have occurred
var execution_result = context.SaveChanges();
if (execution_result != 1)
{
success = false;
}
return(success);
}
}
public List <DataModel.Product> Get_All_Products_By_Warehouse_ID(ref string msg)
{
List <DataModel.Product> product_list = new List <DataModel.Product>();
using (var context = new DataModel.WarehouseContext())
{
// Get all products in DB
var products = context.Products;
if (products != null)
{
msg = "Found products";
product_list = products.ToList();
return(product_list);
}
else
{
msg = "No products found";
}
}
return(product_list);
}
// This method creates a Category record in the Warehouse database and returns true if the operation was successful.
public bool Create(DataModel.Category category)
{
using (var context = new DataModel.WarehouseContext())
{
// Create and save a new Category
DataModel.Category newCategory = new DataModel.Category();
bool success = false;
newCategory.Category_Description = category.Category_Description;
newCategory.Category_Name = category.Category_Name;
context.Categories.Add(newCategory);
//Check execution of transaction - we expect 1 change to have occurred
var execution_result = context.SaveChanges();
if (execution_result == 1)
{
return(true);
}
return(success);
}
}
