public void Update(Product product)
{
using (IDbConnection db = new SqlConnection(MsSqlHelper.GetConnectionString()))
{
string query = @"UPDATE [dbo].[Products] SET [Title] = @Title, [Description] = @Description, [Price] = @Price WHERE [Id] = @Id";
db.Execute(query, product);
}
}
public int Create(Product product)
{
using (IDbConnection db = new SqlConnection(MsSqlHelper.GetConnectionString()))
{
string query = @"INSERT INTO [dbo].[Products]([Title], [Description], [Price]) VALUES (@Title, @Description, @Price); SELECT CAST(SCOPE_IDENTITY() as int);";
return(db.Query <int>(query, product).First());
}
}
public IEnumerable <Product> GetAll()
{
using (IDbConnection db = new SqlConnection(MsSqlHelper.GetConnectionString()))
{
string query = @"SELECT * FROM [dbo].[Products]";
return(db.Query <Product>(query));
}
}
public IEnumerable <Order> GetAll()
{
using (IDbConnection db = new SqlConnection(MsSqlHelper.GetConnectionString()))
{
string ordersQuery = @"SELECT * FROM [dbo].[Orders]";
var dbOrders = db.Query <dynamic>(ordersQuery);
return(dbOrders.Select(x => GetOrder(db, x)).Cast <Order>().ToList());
}
}
public void Delete(int orderId)
{
using (IDbConnection db = new SqlConnection(MsSqlHelper.GetConnectionString()))
{
string query = "DELETE FROM [dbo].[Orders] WHERE [Id] = @OrderId";
db.Execute(query, new
{
OrderId = orderId
});
}
}
public void Delete(int productId)
{
using (IDbConnection db = new SqlConnection(MsSqlHelper.GetConnectionString()))
{
string query = @"DELETE FROM [dbo].[Products] WHERE [Id] = @productId";
db.Execute(query, new
{
productId = productId
});
}
}
public Product GetById(int productId)
{
using (IDbConnection db = new SqlConnection(MsSqlHelper.GetConnectionString()))
{
string query = @"SELECT * FROM [dbo].[Products] WHERE [Id] = @productId";
return(db.Query <Product>(query, new
{
productId = productId
}).FirstOrDefault());
}
}
public Order GetById(int orderId)
{
using (IDbConnection db = new SqlConnection(MsSqlHelper.GetConnectionString()))
{
string ordersQuery = @"SELECT * FROM [dbo].[Orders] WHERE [Id] = @orderId";
var dbOrder = db.Query <dynamic>(ordersQuery, new
{
orderId = orderId
}).First();
return(GetOrder(db, dbOrder));
}
}
public int Create(Order order)
{
using (IDbConnection db = new SqlConnection(MsSqlHelper.GetConnectionString()))
{
string insertOrderQuery = @"INSERT INTO [dbo].[Orders]([CreationDate], [CustomerName], [CustomerPhone], [Status]) VALUES (@CreationDate, @CustomerName, @CustomerPhone, @Status); SELECT CAST(SCOPE_IDENTITY() as int);";
int orderId = db.Query <int>(insertOrderQuery, new
{
CreationDate = order.CreationDate,
CustomerName = order.Customer.Name,
CustomerPhone = order.Customer.Phone,
Status = order.Status
}).First();
InsertOrderItems(db, order.Items, orderId);
return(orderId);
}
}
public void Update(Order order)
{
using (IDbConnection db = new SqlConnection(MsSqlHelper.GetConnectionString()))
{
string updateOrderQuery = @"UPDATE [dbo].[Orders] SET [Status] = @Status WHERE [Id] = @OrderId";
db.Execute(updateOrderQuery, new
{
Status = order.Status,
OrderId = order.Id
});
string deleteItemsQuery = "DELETE FROM [dbo].[OrderItems] WHERE [OrderId] = @OrderId";
db.Execute(deleteItemsQuery, new
{
OrderId = order.Id
});
InsertOrderItems(db, order.Items, order.Id);
}
}