public IEnumerable <T> ListByCriteria(TC criteria)
{
string sqlQuery = CreateSqlQuery(criteria);
DataTable dataTable;
using (var sqlSvc = new SqLiteService())
dataTable = sqlSvc.ExecuteQuery(sqlQuery);
var entitiesList = RetrieveEntities(dataTable);
return(entitiesList);
}
public override void Update(ProductDto product)
{
var existingEntity = ListByCriteria(new ProductCriteriaDto {
Id = product.Id
})
.FirstOrDefault();
if (existingEntity == null)
{
return;
}
using (var sqlSvc = new SqLiteService())
sqlSvc.ExecuteQuery($"UPDATE items SET dateCreated = '{product.DateCreated}', timeCreated = '{product.DateCreated}', itemName = '{product.Name}', itemCategory = '{product.Category}', fixedQuantity = '{product.FixedQuantity}', itemPrice = {product.Price} WHERE itemID = {product.Id};");
}
public override void Delete(ProductDto product)
{
var existingEntity = ListByCriteria(new ProductCriteriaDto {
Id = product.Id
})
.FirstOrDefault();
if (existingEntity == null)
{
return;
}
using (var sqlSvc = new SqLiteService())
sqlSvc.ExecuteQuery($"DELETE FROM items WHERE itemID = {existingEntity.Id};");
}
public override void Delete(CategoryDto category)
{
var existingEntity = ListByCriteria(new CategoryCriteriaDto {
Id = category.Id
})
.FirstOrDefault();
if (existingEntity == null)
{
return;
}
using (var sqlSvc = new SqLiteService())
sqlSvc.ExecuteQuery($"DELETE FROM itemCategories WHERE categoryID = {existingEntity.Id};");
}
public override void Delete(OrderDto order)
{
var existingEntity = ListByCriteria(new OrderCriteriaDto {
Id = order.Id
})
.FirstOrDefault();
if (existingEntity == null)
{
return;
}
using (var sqlSvc = new SqLiteService())
sqlSvc.ExecuteQuery($"DELETE FROM orderList WHERE orderID = {existingEntity.Id};");
}
public override void Update(CategoryDto category)
{
var existingEntity = ListByCriteria(new CategoryCriteriaDto {
Id = category.Id
})
.FirstOrDefault();
if (existingEntity == null)
{
return;
}
using (var sqlSvc = new SqLiteService())
sqlSvc.ExecuteQuery($"UPDATE itemCategories SET categoryName = '{category.Name}' WHERE categoryID = {existingEntity.Id};");
}
public override void Update(OrderDto order)
{
var existingEntity = ListByCriteria(new OrderCriteriaDto {
Id = order.Id, Table = order.Table
})
.FirstOrDefault();
if (existingEntity == null)
{
return;
}
using (var sqlSvc = new SqLiteService())
sqlSvc.ExecuteQuery($"UPDATE orderList SET orderDate = '{order.DateCreated.ToString("dd/MM/yyyy")}', orderTime = '{order.DateCreated.ToString("HH:mm:ss")}', orderSumPrice = {order.TotalAmount} WHERE orderID = {order.Id} AND orderTable = {order.Table};");
}
public override int Create(OrderDto order)
{
using (var sqlSvc = new SqLiteService())
sqlSvc.ExecuteQuery(@"INSERT INTO orderList (orderTable, orderDate, orderTime, orderSumPrice) VALUES" +
$"({order.Table}, '{order.DateCreated.ToString("dd/MM/yyyy")}', '{order.DateCreated.ToString("HH:mm:ss")}', {order.TotalAmount});");
var entitySaved = ListByCriteria(new OrderCriteriaDto
{
Table = order.Table,
TotalAmount = order.TotalAmount,
DateCreated = order.DateCreated
}).FirstOrDefault();
return(entitySaved?.Id ?? 0);
}
public override void Update(OrderItemDto orderItem)
{
var existingEntity = ListByCriteria(new OrderItemCriteriaDto {
Id = orderItem.Id, OrderId = orderItem.OrderId
})
.FirstOrDefault();
if (existingEntity == null)
{
return;
}
using (var sqlSvc = new SqLiteService())
sqlSvc.ExecuteQuery($"UPDATE orders SET orderedItemQuantity = '{orderItem.Quantity}', orderedItemName = '{orderItem.Name}'," +
$" orderedItemPrice = {orderItem.Price} WHERE orderedItemID = {orderItem.Id} AND orderID = {orderItem.OrderId};");
}
public override int Create(OrderItemDto orderItem)
{
using (var sqlSvc = new SqLiteService())
sqlSvc.ExecuteQuery(@"INSERT INTO orders (orderID, orderedItemID, orderedItemQuantity, orderedItemName, orderedItemPrice) VALUES " +
$"({orderItem.OrderId}, {orderItem.Id}, {orderItem.Quantity}, '{orderItem.Name}', {orderItem.Price});");
var entitySaved = ListByCriteria(new OrderItemCriteriaDto
{
OrderId = orderItem.OrderId,
Id = orderItem.Id,
Name = orderItem.Name,
Price = orderItem.Price,
Quantity = orderItem.Quantity
}).FirstOrDefault();
return(entitySaved?.Id ?? 0);
}
public override int Create(CategoryDto category)
{
var existingEntity = ListByCriteria(new CategoryCriteriaDto {
Name = category.Name
})
.FirstOrDefault();
if (existingEntity != null)
{
return(0);
}
using (SqLiteService sqlSvc = new SqLiteService())
sqlSvc.ExecuteQuery($"INSERT INTO itemCategories (categoryName) VALUES ('{category.Name}');");
var entitySaved = ListByCriteria(new CategoryCriteriaDto {
Name = category.Name
}).FirstOrDefault();
return(entitySaved?.Id ?? 0);
}
public override int Create(ProductDto product)
{
var existingEntity = ListByCriteria(new ProductCriteriaDto {
Name = product.Name, Category = product.Category, FixedQuantity = product.FixedQuantity
})
.FirstOrDefault();
if (existingEntity != null)
{
return(0);
}
using (var sqlSvc = new SqLiteService())
sqlSvc.ExecuteQuery(@"INSERT INTO items (dateCreated, timeCreated, itemName, itemCategory, fixedQuantity, itemPrice)" +
$" VALUES ('{product.DateCreated}', '{product.DateCreated}', '{product.Name}', '{product.Category}', '{product.FixedQuantity}', {product.Price});");
var entitySaved = ListByCriteria(new ProductCriteriaDto
{
Name = product.Name,
Category = product.Category
}).FirstOrDefault();
return(entitySaved?.Id ?? 0);
}