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); } }