/// <summary>
/// Return a list of all Carts
/// </summary>
/// <returns>
/// Return List of Cart
/// </returns>
public List <Cart> GetAllCarts()
{
var carts = new List <Cart>();
using (
var conn =
new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationDbContext"].ConnectionString))
{
conn.Open();
var cmd = new SqlCommand("SELECT * FROM Cart ", conn);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
// Build the Cart Object
var cart = ObjectBuilder.CreateCart(reader);
// Add the cart to the list
carts.Add(cart);
}
}
return(carts);
}
/// <summary>
/// Get a Warehouse
/// </summary>
/// <param name="id"></param>
/// <returns>
/// Warehouse if found, else null
/// </returns>
public Warehouse GetWarehouse(int id)
{
Warehouse warehouse = null;
using (
var conn =
new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationDbContext"].ConnectionString))
{
conn.Open();
var cmd = new SqlCommand("SELECT * FROM Warehouse, Product WHERE warehouseId = @id and Product.productId = warehouseProductId ", conn);
cmd.Parameters.AddWithValue("id", id);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
// Build the product
var product = ObjectBuilder.CreateProduct(reader);
// Build the Warehouse with a product
warehouse = ObjectBuilder.CreateWarehouse(reader, product);
}
}
return(warehouse);
}
/// <summary>
/// Get all Product similar to name
/// </summary>
/// <param name="input"></param>
/// <returns>
/// List of Product
/// </returns>
public List <Product> GetAllProductsByName(string input)
{
var products = new List <Product>();
using (
var conn =
new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationDbContext"].ConnectionString))
{
conn.Open();
var command = new SqlCommand("SELECT * FROM Product WHERE productName LIKE @name", conn);
command.Parameters.AddWithValue("name", input);
var reader = command.ExecuteReader();
while (reader.Read())
{
// Build the Product object
var product = ObjectBuilder.CreateProduct(reader);
// Add the Product to the list
products.Add(product);
}
}
return(products);
}
/// <summary>
/// Return a list of all Carts
/// </summary>
/// <returns>
/// Return List of Cart
/// </returns>
public List <Cart> GetAllCartsByPersonId(int personId)
{
var carts = new List <Cart>();
using (
var conn =
new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationDbContext"].ConnectionString))
{
conn.Open();
var cmd = new SqlCommand("SELECT * FROM Cart, PartOrder, Product, Shop, Chain WHERE partOrderProductId = productId AND cartId = partOrderCartId AND shopId = cartShopId AND shopChainId = chainId AND cartPersonId = @PersonId", conn);
cmd.Parameters.AddWithValue("PersonId", personId);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
// Build the Cart object with a list of PartOrders
var cart = ObjectBuilder.CreateCartWithPartOrders(reader);
// Add the cart to the list
carts.Add(cart);
}
}
return(carts);
}
///// <summary>
///// Add a Admin
///// </summary>
///// <param name="admin"></param>
///// <returns>
///// Id of Admin if added, else 0
///// </returns>
//public int AddAdmin(Admin admin)
//{
// var i = 0;
// using (
// var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationDbContext"].ConnectionString))
// {
// conn.Open();
// var cmd = conn.CreateCommand();
// // Set isolation level to ReadCommitted
// var transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
// cmd.Transaction = transaction;
// // Try to Insert the Admin
// try
// {
// cmd.CommandText = "DECLARE @DataID int; INSERT INTO Person(personName, personEmail, personType, personAddress) output inserted.personId VALUES(@name, @email, @personType, @address); SELECT @DataID = scope_identity(); INSERT INTO Administrator(administratorId) VALUES(@DataID);";
// cmd.Parameters.AddWithValue("name", admin.Name);
// cmd.Parameters.AddWithValue("email", admin.Email);
// cmd.Parameters.AddWithValue("personType", admin.PersonType);
// cmd.Parameters.AddWithValue("address", admin.Address);
// // get the id
// i = (int)cmd.ExecuteScalar();
// transaction.Commit();
// }
// catch (Exception)
// {
// // The transaction failed
// try
// {
// // Try rolling back
// transaction.Rollback();
// Console.WriteLine("Transaction was rolled back");
// }
// catch (SqlException)
// {
// // Rolling back failed
// Console.WriteLine("Transaction rollback failed");
// }
// }
// }
// return i;
//}
///// <summary>
///// Delete a Admintransaction
///// </summary>
///// <param name="id"></param>
///// <returns>
///// Return 1 if Admin is removed, else 0
///// </returns>
//public int DeleteAdmin(int id)
//{
// var i = 0;
// using (
// var conn =
// new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationDbContext"].ConnectionString))
// {
// conn.Open();
// var cmd = conn.CreateCommand();
// // Set the isolation level to ReadCommitted
// var transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
// cmd.Transaction = transaction;
// try
// {
// cmd.CommandText = "Delete from Administrator where administratorId = @id;Delete from Person where personId = @id";
// cmd.Parameters.AddWithValue("Id", id);
// i = cmd.ExecuteNonQuery();
// transaction.Commit();
// }
// catch (Exception)
// {
// // The transaction failed
// try
// {
// // Try rolling back
// transaction.Rollback();
// Console.WriteLine("Transaction was rolled back");
// }
// catch (SqlException)
// {
// // Rolling back failed
// Console.WriteLine("Transaction rollback failed");
// }
// }
// }
// return i;
//}
///// <summary>
///// Return a Admin by id
///// </summary>
///// <param name="id"></param>
///// <returns>
///// Return Admin if found, else null
///// </returns>
public Admin GetAdmin(int id)
{
Admin admin = null;
using (
var conn =
new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationDbContext"].ConnectionString))
{
conn.Open();
var cmd = new SqlCommand("SELECT * FROM Person LEFT JOIN Administrator ON Person.personId = Administrator.administratorId WHERE Person.personId = @Id", conn);
cmd.Parameters.AddWithValue("Id", id);
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
return(null);
}
while (reader.Read())
{
// Build the admin object
admin = ObjectBuilder.CreateAdmin(reader);
}
}
return(admin);
}
/// <summary>
/// Get all Warehouse by a shop id
/// </summary>
/// <param name="shopId"></param>
/// <returns>
/// List of Warehouse
/// </returns>
public List <Warehouse> GetAllWarehousesByShopId(int shopId)
{
var warehouses = new List <Warehouse>();
using (
var conn =
new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationDbContext"].ConnectionString))
{
conn.Open();
var cmd = new SqlCommand("SELECT * FROM Warehouse, Product WHERE Product.productId = warehouseProductId and warehouseShopId = @ShopId", conn);
cmd.Parameters.AddWithValue("ShopId", shopId);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
// Build the product object
var product = ObjectBuilder.CreateProduct(reader);
// Build the warehouse with a product
var warehouse = ObjectBuilder.CreateWarehouse(reader, product);
// Add the warehouse to the list
warehouses.Add(warehouse);
}
}
return(warehouses);
}