public override List <IOrderEntry> GetAll()
{
try
{
using (IDbConnection connection = SQLInfoCaptureContext.GetDataStore().CreateOpenConnection())
{
string query = "SELECT * FROM OrderEntries";
using (IDbCommand command = SQLInfoCaptureContext.GetDataStore().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
var result = new List <IOrderEntry>();
while (reader.Read())
{
IOrderEntry c = DataFactory.CreateOrderEntry(
DataFactory.CreateProduct(Convert.ToInt32(reader["ProductID"])),
Convert.ToInt32(reader["Quantity"]),
null,
DataFactory.CreateOrder(Convert.ToInt32(reader["OrderID"])),
Convert.ToInt32(reader["OrderEntryID"]));
result.Add(c);
}
return(result);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public override List <ICustomer> GetAll()
{
try
{
using (IDbConnection connection = SQLInfoCaptureContext.GetDataStore().CreateOpenConnection())
{
string query = "SELECT * FROM Customers";
using (IDbCommand command = SQLInfoCaptureContext.GetDataStore().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
List <ICustomer> customerList = new List <ICustomer>();
while (reader.Read())
{
ICustomer c = Data.DataFactory.CreateCustomer(
Convert.ToInt32(reader["CustomerID"]),
reader["FirstName"].ToString(),
reader["LastName"].ToString(),
reader["Email"].ToString(),
reader["Phone"].ToString()
);
customerList.Add(c);
}
return(customerList);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public override List <IOrder> GetAll()
{
try
{
using (IDbConnection connection = SQLInfoCaptureContext.GetDataStore().CreateOpenConnection())
{
string query = "SELECT * FROM Orders";
using (IDbCommand command = SQLInfoCaptureContext.GetDataStore().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
var result = new List <IOrder>();
while (reader.Read())
{
IOrder c = DataFactory.CreateOrder(
Convert.ToInt32(reader["OrderID"]),
customer: DataFactory.CreateCustomer(Convert.ToInt32(reader["CustomerID"])),
state: (OrderState)Convert.ToInt32(reader["OrderState"]),
prize: Convert.ToDouble(reader["Prize"]),
date: Convert.ToDateTime(reader["OrderDate"]));
result.Add(c);
}
return(result);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public override ICustomer Add(ICustomer entity)
{
using (IDbConnection connection = SQLInfoCaptureContext.GetDataStore().CreateOpenConnection())
{
using (IDbTransaction transaction = connection.BeginTransaction())
{
try
{
string query = "INSERT INTO Customers (FirstName, LastName, Email, Phone) VALUES "
+ "('" + entity.FirstName + "', '" + entity.LastName + "', '" + entity.Email + "', '" + entity.Phone + "')";
using (IDbCommand command = SQLInfoCaptureContext.GetDataStore().CreateCommand(query, connection))
{
command.Transaction = transaction;
if (command.ExecuteNonQuery() <= 0)
{
transaction.Rollback();
}
}
string queryID = "SELECT SCOPE_IDENTITY();";
using (IDbCommand commandID = SQLInfoCaptureContext.GetDataStore().CreateCommand(queryID, connection))
{
commandID.Transaction = transaction;
using (IDataReader reader = commandID.ExecuteReader())
{
if (reader.Read())
{
entity.CustomerID = Convert.ToInt32(reader[0]);
}
}
}
if (entity.CustomerID.Equals(-1))
{
transaction.Rollback();
throw new Exception("Customer inserted successfully, but could not retrieve ID afterwards. Rollback.");
}
else
{
transaction.Commit();
return(entity);
}
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
}
public override List <IProduct> GetAll()
{
try
{
using (IDbConnection connection = SQLInfoCaptureContext.GetDataStore().CreateOpenConnection())
{
string query = "SELECT * FROM Products";
using (IDbCommand command = SQLInfoCaptureContext.GetDataStore().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
var productsList = new List <IProduct>();
while (reader.Read())
{
IProduct c = DataFactory.CreateProduct(
Convert.ToInt32(reader["ProductID"]),
reader["Name"].ToString(),
reader["Category"].ToString(),
reader["Description"].ToString(),
Convert.ToDouble(reader["Prize"]),
reader["SellerName"].ToString(),
Convert.ToInt32(reader["Stack"])
);
productsList.Add(c);
}
return(productsList);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public override IOrder Add(IOrder entity)
{
using (IDbConnection connection = SQLInfoCaptureContext.GetDataStore().CreateOpenConnection())
{
using (IDbTransaction transaction = connection.BeginTransaction())
{
try
{
string query = "INSERT INTO Orders (CustomerID,OrderState,Prize) VALUES " + "(" + entity.Cutsomer.CustomerID + ", " + ((int)entity.OrderState) + ", " + entity.Prize + ")";
using (IDbCommand command = SQLInfoCaptureContext.GetDataStore().CreateCommand(query, connection))
{
command.Transaction = transaction;
if (command.ExecuteNonQuery() <= 0)
{
transaction.Rollback();
}
}
string queryID = "SELECT SCOPE_IDENTITY();";
using (IDbCommand commandID = SQLInfoCaptureContext.GetDataStore().CreateCommand(queryID, connection))
{
commandID.Transaction = transaction;
using (IDataReader reader = commandID.ExecuteReader())
{
if (reader.Read())
{
entity.OrderID = Convert.ToInt32(reader[0]);
}
}
}
if (entity.Entries.Count > 0)
{
query = "INSERT INTO OrderEntries (ProductID,Quantity,OrderID) ";
entity.Entries.ToList().ForEach(e => query += " SELECT " + e.Product.ProductID + ", " + e.Quantity + ", " + e.Order.OrderID + " UNION ALL ");
query = query.Remove(query.LastIndexOf("UNION ALL")) + ";";
using (IDbCommand command = SQLInfoCaptureContext.GetDataStore().CreateCommand(query, connection))
{
command.Transaction = transaction;
if (command.ExecuteNonQuery() <= 0)
{
transaction.Rollback();
}
}
var orders = entity.Entries.Select(e => Tuple.Create(e.Quantity, e.Product.ProductID));
query = "SELECT * FROM Products WHERE ProductID IN (";
orders.Select(t => t.Item2).ToList().ForEach(id => query += id + ",");
query = query.Remove(query.LastIndexOf(",")) + ");";
var remainingStack = new List <Tuple <int, int> >();
using (IDbCommand command = SQLInfoCaptureContext.GetDataStore().CreateCommand(query, connection))
{
command.Transaction = transaction;
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var id = Convert.ToInt32(reader["ProductID"]);
var stack = Convert.ToInt32(reader["Stack"]) - orders.FirstOrDefault(t => t.Item2 == id).Item1;
remainingStack.Add(Tuple.Create(id, stack));
}
}
}
foreach (var item in remainingStack)
{
query = "UPDATE Products Set Stack = " + item.Item2 + " WHERE ProductID = " + item.Item1 + ";";
using (IDbCommand command = SQLInfoCaptureContext.GetDataStore().CreateCommand(query, connection))
{
command.Transaction = transaction;
if (command.ExecuteNonQuery() <= 0)
{
transaction.Rollback();
}
}
}
}
if (entity.OrderID.Equals(-1))
{
transaction.Rollback();
throw new Exception("Customer inserted successfully, but could not retrieve ID afterwards. Rollback.");
}
else
{
transaction.Commit();
return(entity);
}
}
catch (Exception ex)
{
transaction.Rollback();
throw;
}
}
}
}
