public void Update(OrderDetails model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("UPDATE OrderDetails SET Quantity = @Quantity, Discount =@Discount WHERE OrderID = @OrderID AND ProductID = @ProductID", model);
}
}
public void Create(Categories model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("INSERT INTO Categories VALUES(@CategoryID, @CategoryName)", model);
}
}
public void Create(OrderDetails model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("INSERT INTO OrderDetails VALUES(@OrderID ,@ProductID,@Quantity,@Discount)", model);
}
}
public void Delete(int CategoryID)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("Delete From Categories WHERE CategoryID = @id", new { id = CategoryID });
}
}
public void Update(Categories model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("UPDATE Categories SET CategoryName = @CategoryName WHERE CategoryID = @CategoryID", model);
}
}
public void Update(Orders model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute(
"UPDATE Orders SET CustomerID = @CustomerID, OrderDate = @OrderDate, ShippedDate = @ShippedDate, PaymentMethodID = @PaymentMethodID, DeliveryMethodID = @DeliveryMethodID, Status = @Status WHERE OrderID = @OrderID", model);
}
}
public void Create(Orders model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("INSERT INTO Orders VALUES(@OrderID ,@CustomerID, @OrderDate, @ShippedDate, @PaymentMethodID, @DeliveryMethodID,@Status)",
model);
}
}
public IEnumerable <OrderDetails> GetAllOrderDeta()
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
var orderdeta = connection.Query <OrderDetails>("SELECT * FROM OrderDetails");
return(orderdeta);
}
}
public void Create(Messages model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("INSERT INTO Messages VALUES(@OrderID,@Message,@Reply,@Time,@ReplyTime)",
model);
}
}
public void Create(Cart model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("INSERT INTO Cart VALUES(@CustomerID,@ProductID,@Quantity)",
model);
}
}
//取得產品
public IEnumerable <ProductsItem> FindById_Home()
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
var products = connection.Query <ProductsItem>("SELECT ProductID, CategoryID, ProductName, UnitPrice, Path FROM Products ORDER BY ProductID DESC");
return(products);
}
}
//取得最新的產品ID
public string GetNewId()
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
var id = connection.Query <string>("SELECT TOP 1 ProductID FROM Products ORDER BY ProductID DESC");
return(id.FirstOrDefault());
}
}
public IEnumerable <ProductsItem> GetAll()
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
var products = connection.Query <ProductsItem>(
"SELECT * FROM Products");
return(products);
}
}
public void UpdatePassword(Customers model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute(
"UPDATE Customers SET " +
"CustomerPassword = @CustomerPassword WHERE CustomerID = @CustomerID", model);
}
}
public void Create(Products model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute(
"INSERT INTO Products VALUES(@ProductID, @CategoryID,@ProductName,@Stock,@UnitPrice,@Size,@Color,@Instructions,@Path)",
model);
}
}
public void Update(Cart model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute(
"UPDATE Cart SET " +
"ProductID = @ProductID, Quantity = @Quantity WHERE CustomerID = @CustomerID AND ProductID = @ProductID", model);
}
}
public void Update(Customers model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute(
"UPDATE Customers SET " +
"CustomerPassword = @CustomerPassword,CustomerName = @CustomerName,Telephone = @Telephone,Address = @Address,CustomerMail = @CustomerMail WHERE CustomerID = @CustomerID", model);
}
}
public int FindLastDeliveryMethodID()
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
var deliveryMethods = connection.Query <int>(
"select top 1 DeliveryMethodID from DeliveryMethods order by DeliveryMethodID desc");
return(deliveryMethods.FirstOrDefault());
}
}
public int FindLastPaymentMethodID()
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
var paymentMethods = connection.Query <int>(
"select top 1 PaymentMethodID from PaymentMethods order by PaymentMethodID desc");
return(paymentMethods.FirstOrDefault());
}
}
public int FindLastCategoryID()
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
var categories = connection.Query <int>(
"SELECT TOP 1 CategoryID FROM Categories ORDER BY CategoryID DESC");
return(categories.FirstOrDefault());
}
}
public IEnumerable <Messages> FindById_Admin(string Id)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
var list = connection.Query <Messages>(
"SELECT * FROM [Messages] WHERE OrderID = @Id", new { Id });
return(list);
}
}
public void DeleteById(string Id)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("DELETE Cart WHERE CustomerID = @customerId", new
{
customerId = Id
});
}
}
//取得新訂單的數量
public int GetNewOrderCount()
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
var count = connection.Query <int>(
"SELECT COUNT(*) FROM Orders WHERE Status = 0"
);
return(count.FirstOrDefault());
}
}
//取得會員數量
public int GetAllCount()
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
var count = connection.Query <int>(
"SELECT COUNT (*) FROM Customers"
);
return(count.FirstOrDefault());
}
}
public void Update(Products model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute(
"UPDATE Products SET " +
"CategoryID = @CategoryID,ProductName = @ProductName,Stock = @Stock,UnitPrice = @UnitPrice," +
"Size = @Size,Color = @Color,Instructions = @Instructions,Path = @Path WHERE ProductID = @ProductID",
model);
}
}
public void Delete(int PaymentMethodID)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("Delete From PaymentMethods WHERE PaymentMethodID = @id",
new
{
id = PaymentMethodID
});
}
}
public void Update(Messages model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute(
"UPDATE Messages SET " +
"Message = @Message,Reply = @Reply,ReplyTime = @ReplyTime " +
"WHERE OrderID = @OrderID AND Time = @Time",
model);
}
}
public void Delete(int DeliveryMethodID)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute(
"Delete From DeliveryMethods WHERE DeliveryMethodID = @id",
new
{
id = DeliveryMethodID
});
}
}
public void Update(PaymentMethods model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("UPDATE PaymentMethods SET PaymentMethod = @PaymentMethod WHERE PaymentMethodID = @id",
new
{
id = model.PaymentMethodID,
PaymentMethod = model.PaymentMethod
});
}
}
public void Create(PaymentMethods model)
{
using (var connection = new SqlConnection(SqlConnectionString.ConnectionString()))
{
connection.Execute("INSERT INTO PaymentMethods VALUES(@PaymentMethodID, @PaymentMethod)",
new
{
PaymentMethodID = model.PaymentMethodID,
PaymentMethod = model.PaymentMethod
});
}
}