public IEnumerable <Orders> GetAll() //查尋全部資料
{
SqlConnection connection = new SqlConnection(
SqlConnectionString.ConnectionString());
var sql = "SELECT * FROM Orders";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
var orders = new List <Orders>();
var properties = typeof(Orders).GetProperties();
while (reader.Read())
{
var order = new Orders();
order = DbReaderModelBinder <Orders> .Bind(reader);
orders.Add(order);
}
reader.Close();
return(orders);
}
public IEnumerable <Orders> GetOrderDate(string OrderDate)
{
SqlConnection connection = new SqlConnection(
SqlConnectionString.ConnectionString());
var sql = "select * FROM Orders WHERE CONVERT(VARCHAR(25), OrderDate, 126) LIKE @OrderDate";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@OrderDate", OrderDate);
connection.Open();
var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
var properties = typeof(Orders).GetProperties();
var Orders = new List <Orders>();
while (reader.Read())
{
var Order = new Orders();
Order = DbReaderModelBinder <Orders> .Bind(reader);
Orders.Add(Order);
}
reader.Close();
return(Orders);
}
public int GetCartOrderID(int MemberID)
{
SqlConnection connection = new SqlConnection(source.connectcloud);
var sql = "SELECT OrderID FROM Orders WHERE MemberID = @MemberID and Cart = 0";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@MemberID", MemberID);
var orders = new Orders();
connection.Open();
//查詢是否有購物車存在 cart=0
var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
orders = DbReaderModelBinder <Orders> .Bind(reader);
}
reader.Close();
//如果有購物車存在,回傳OrderID
if (orders.OrderID > 0)
{
connection.Close();
return(orders.OrderID);
}
//如果沒有購物車存在 新增一筆
else
{
//var now = DateTime.Now.ToString("MM-dd-yyyy");
var sql2 = "INSERT INTO Orders( MemberID , Cart ) Values(@MemberID , @Cart)";
SqlCommand command2 = new SqlCommand(sql2, connection);
connection.Open();
command2.Parameters.AddWithValue("@MemberID", MemberID);
//command.Parameters.AddWithValue("@Time", now);
command2.Parameters.AddWithValue("@Cart", 0);
var q = command2.ExecuteNonQuery();
//檢查是否有成功新增購物車 , 如果有 回傳OrderID
if (q > 0)
{
var reader2 = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader2.Read())
{
orders = DbReaderModelBinder <Orders> .Bind(reader2);
}
reader2.Close();
connection.Close();
return(orders.OrderID);
}
else
{
connection.Close();
throw new Exception("新增購物車失敗,請聯絡客服.");
}
}
}
public Orders FindOrderID(string MemberID)
{
var command = Command("dbo.FindOrderID");
command.Parameters.Add(new SqlParameter("@MemberID", MemberID));
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
return(DbReaderModelBinder <Orders> .Bind(reader));
}
return(null);
}
public ShoppingCart SearchRepeatCart(string memberID, int formatID)
{
var command = Command("dbo.SearchRepeatInCart");
command.Parameters.Add(new SqlParameter("@memberID", memberID));
command.Parameters.Add(new SqlParameter("@formatID", formatID));
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
return(DbReaderModelBinder <ShoppingCart> .Bind(reader));
}
return(null);
}
public IEnumerable <GetProductOrderModel> GetProductOrder()
{
var command = Command("dbo.GetProductOrder");
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
var GetProductOrders = new List <GetProductOrderModel>();
while (reader.Read())
{
var GetProductOrder = new GetProductOrderModel();
GetProductOrder = DbReaderModelBinder <GetProductOrderModel> .Bind(reader);
GetProductOrders.Add(GetProductOrder);
}
command.Connection.Close();
return(GetProductOrders);
}
public IEnumerable <FindProductsByCategoryModel> FindProductsByCategory()
{
var command = Command("dbo.FindProductsByCategory");
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
var FindProductsByCategories = new List <FindProductsByCategoryModel>();
while (reader.Read())
{
var FindProductsByCategory = new FindProductsByCategoryModel();
FindProductsByCategory = DbReaderModelBinder <FindProductsByCategoryModel> .Bind(reader);
FindProductsByCategories.Add(FindProductsByCategory);
}
command.Connection.Close();
return(FindProductsByCategories);
}
public IEnumerable <GetHowLongHireDateModel> GetHowLongHireDate()
{
var command = Command("dbo.GetHowLongHireDate");
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
var GetHowLongHireDates = new List <GetHowLongHireDateModel>();
while (reader.Read())
{
var GetHowLongHireDate = new GetHowLongHireDateModel();
GetHowLongHireDate = DbReaderModelBinder <GetHowLongHireDateModel> .Bind(reader);
GetHowLongHireDates.Add(GetHowLongHireDate);
}
command.Connection.Close();
return(GetHowLongHireDates);
}
public IEnumerable <SearchProductName> Search(string productname)
{
var command = Command("dbo.Search");
command.Parameters.Add(new SqlParameter("@productname", productname));
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
var SearchProductName = new List <SearchProductName>();
while (reader.Read())
{
var Search = new SearchProductName();
Search = DbReaderModelBinder <SearchProductName> .Bind(reader);
SearchProductName.Add(Search);
}
command.Connection.Close();
return(SearchProductName);
}
public IEnumerable <FindProductFormatByProductID> GetFormatByProductID(int productid)
{
var command = Command("dbo.FindProductFormatByProductID");
command.Parameters.Add(new SqlParameter("@productid", productid));
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
var GetFormatByProductID = new List <FindProductFormatByProductID>();
while (reader.Read())
{
var GetProductFormat = new FindProductFormatByProductID();
GetProductFormat = DbReaderModelBinder <FindProductFormatByProductID> .Bind(reader);
GetFormatByProductID.Add(GetProductFormat);
}
command.Connection.Close();
return(GetFormatByProductID);
}
public IEnumerable <ColorFilter> ColorFilters(string Color)
{
var command = Command("dbo.ColorFilter");
command.Parameters.Add(new SqlParameter("@Color", Color));
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
var colorfilter = new List <ColorFilter>();
while (reader.Read())
{
var colorfilters = new ColorFilter();
colorfilters = DbReaderModelBinder <ColorFilter> .Bind(reader);
colorfilter.Add(colorfilters);
}
command.Connection.Close();
return(colorfilter);
}
public IEnumerable <FindOrderdetaiByOrderIDModel> FindOrderdetaiByOrderID(int orderid)
{
var command = Command("dbo.FindOrderdetaiByOrderID");
command.Parameters.Add(new SqlParameter("@orderid", orderid));
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
var FindOrderdetaiByOrderIDs = new List <FindOrderdetaiByOrderIDModel>();
while (reader.Read())
{
var FindOrderdetaiByOrderID = new FindOrderdetaiByOrderIDModel();
FindOrderdetaiByOrderID = DbReaderModelBinder <FindOrderdetaiByOrderIDModel> .Bind(reader);
FindOrderdetaiByOrderIDs.Add(FindOrderdetaiByOrderID);
}
command.Connection.Close();
return(FindOrderdetaiByOrderIDs);
}
public IEnumerable <GetBuyerOrderModel> GetBuyerOrder(string memberID)
{
var command = Command("dbo.GetBuyerOrder");
command.Parameters.Add(new SqlParameter("@memberID", memberID));
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
var GetBuyerOrders = new List <GetBuyerOrderModel>();
while (reader.Read())
{
var GetBuyerOrder = new GetBuyerOrderModel();
GetBuyerOrder = DbReaderModelBinder <GetBuyerOrderModel> .Bind(reader);
GetBuyerOrders.Add(GetBuyerOrder);
}
command.Connection.Close();
return(GetBuyerOrders);
}
public IEnumerable <FindIndexProducts> FindMoneyBetween(decimal lower, decimal higher)
{
var command = Command("dbo.FindMoneyBetween");
command.Parameters.Add(new SqlParameter("@lower", lower));
command.Parameters.Add(new SqlParameter("@higher", higher));
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
var FindMoneyBetween = new List <FindIndexProducts>();
while (reader.Read())
{
var GetBetween = new FindIndexProducts();
GetBetween = DbReaderModelBinder <FindIndexProducts> .Bind(reader);
FindMoneyBetween.Add(GetBetween);
}
command.Connection.Close();
return(FindMoneyBetween);
}
public IEnumerable <FindFormatIDByProductIDCS> GetFormatIDByProductIDCS(int productid, string size, string color)
{
var command = Command("dbo.FindFormatIDByProductIDCS");
command.Parameters.Add(new SqlParameter("@productid", productid));
command.Parameters.Add(new SqlParameter("@color", color));
command.Parameters.Add(new SqlParameter("@size", size));
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
var GetFormatIDByProductIDCS = new List <FindFormatIDByProductIDCS>();
while (reader.Read())
{
var GetProductFormatID = new FindFormatIDByProductIDCS();
GetProductFormatID = DbReaderModelBinder <FindFormatIDByProductIDCS> .Bind(reader);
GetFormatIDByProductIDCS.Add(GetProductFormatID);
}
command.Connection.Close();
return(GetFormatIDByProductIDCS);
}
public IEnumerable <Orders> GetAll() //ok
//()內不用給直 因為傳整個表格
{
SqlConnection connection = new SqlConnection(source.connectcloud);
var sql = "SELECT * FROM Orders";
SqlCommand command = new SqlCommand(sql, connection);
var list = new List <Orders>();
var orders = new Orders();
connection.Open();
var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
orders = DbReaderModelBinder <Orders> .Bind(reader);
list.Add(orders);
}
reader.Close();
connection.Close();
return(list);
}
public IEnumerable <Orders> GetByOrderID(int OrderID) //ok
{
SqlConnection connection = new SqlConnection(source.connectcloud);
var sql = "SELECT * FROM Orders Where OrderID = @OrderID";
var list = new List <Orders>();
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@OrderID", OrderID);
connection.Open();
var orders = new Orders();
var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
orders = DbReaderModelBinder <Orders> .Bind(reader);
list.Add(orders);
}
reader.Close();
connection.Close();
return(list); //?
}
public IEnumerable <Products> _GetAll()
{
SqlConnection connection = new SqlConnection(source.connectcloud);
var sql = "SELECT * FROM Products";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
var reader = command.ExecuteReader();
var list = new List <Products>();
Products products = new Products();
while (reader.Read())
{
products = DbReaderModelBinder <Products> .Bind(reader);
list.Add(products);
}
reader.Close();
connection.Close();
return(list);
}
public IEnumerable <OrderDetail> GetAll() //()內不用給直 因為傳整個表格
{
SqlConnection connection = new SqlConnection(source.connectcloud);
var sql = "SELECT * FROM OrderDetail";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
var reader = command.ExecuteReader();
var list = new List <OrderDetail>();
var orderDetail = new OrderDetail();
while (reader.Read())
{
orderDetail = DbReaderModelBinder <OrderDetail> .Bind(reader);
list.Add(orderDetail);
}
reader.Close();
connection.Close();
return(list);
}
/// <summary>
/// Get All Discount
/// </summary>
/// <returns></returns>
public IEnumerable <Discounts> GetAll()
{
SqlConnection connection = new SqlConnection(source.connectcloud);
var sql = "SELECT * FROM Discounts";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
var list = new List <Discounts>();
while (reader.Read())
{
var discount = new Discounts();
discount = DbReaderModelBinder <Discounts> .Bind(reader);
list.Add(discount);
}
reader.Close();
connection.Close();
return(list);
}
public IEnumerable <Discounts> OrderByDiscountDESC()
{
//找到所有產品，並用折扣排序
SqlConnection connection = new SqlConnection(source.connectcloud);
var sql = "SELECT p.ProductID, p.Category, p.Name, p.UnitPrice FROM Products p INNER JOIN Discounts d ON d.ProductID = p.ProductID WHERE d.ProductID = p.ProductID ORDER BY d.Discount DESC";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
var list = new List <Discounts>();
while (reader.Read())
{
var discount = new Discounts();
discount = DbReaderModelBinder <Discounts> .Bind(reader);
list.Add(discount);
}
reader.Close();
connection.Close();
return(list);
}
