public void Insert(Order ins)
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
string sql = @"INSERT INTO [Sales].[Orders]
([CustomerID],[EmployeeID],[ShipperID],[OrderDate],[ShippedDate],[RequiredDate],[ShipCountry],[ShipCity],[ShipRegion],[ShipPostalCode],[ShipAddress],[Freight])
VALUE
(@CustomerID,@EmployeeID,@ShipperID,@OrderDate,@ShippedDate,@RequiredDate,@ShipCountry,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipAddress,@Freight) SELECT SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@CustomerID", ins.CustomerID));
cmd.Parameters.Add(new SqlParameter("@EmployeeID", ins.EmployeeID));
cmd.Parameters.Add(new SqlParameter("@ShipperID", ins.ShipperID));
cmd.Parameters.Add(new SqlParameter("@OrderDate", ins.OrderDate));
cmd.Parameters.Add(new SqlParameter("@ShippedDate", ins.ShippedDate));
cmd.Parameters.Add(new SqlParameter("@RequiredDate", ins.RequiredDate));
cmd.Parameters.Add(new SqlParameter("@ShipName", ""));
cmd.Parameters.Add(new SqlParameter("@ShipCountry", ins.ShipCountry));
cmd.Parameters.Add(new SqlParameter("@ShipCity", ins.ShipCity));
cmd.Parameters.Add(new SqlParameter("@ShipRegion", ins.ShipRegion));
cmd.Parameters.Add(new SqlParameter("@ShipPostalCode", ins.ShipPostalCode));
cmd.Parameters.Add(new SqlParameter("@ShipAddress", ins.ShipAddress));
cmd.Parameters.Add(new SqlParameter("@Freight", ins.Freight));
conn.Open();
int orderId = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
public List <Orders> getAllData()
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
String sql = "SELECT * FROM [Sales].Orders";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Orders");
List <Orders> orderList = dataSet.Tables[0].AsEnumerable().Select(
dataRow => new Orders
{
OrderID = dataRow.Field <int>("OrderID"),
EmployeeID = dataRow.Field <int>("EmployeeID"),
OrderDate = dataRow.Field <DateTime>("OrderDate"),
RequiredDate = dataRow.Field <DateTime>("RequiredDate"),
ShippedDate = dataRow.Field <DateTime?>("ShippedDate"),
ShipperID = dataRow.Field <int>("ShipperID"),
Freight = dataRow.Field <Decimal>("Freight"),
ShipAddress = dataRow.Field <String>("ShipAddress"),
ShipCity = dataRow.Field <String>("ShipCity"),
ShipRegion = dataRow.Field <String>("ShipRegion"),
ShipPostalCode = dataRow.Field <String>("ShipPostalCode"),
ShipCountry = dataRow.Field <String>("ShipCountry"),
}).ToList();
return(orderList);
}
public Orders getOrderById(int OrderID)
{
List <Orders> orderAllData = this.getAllData();
Orders orderData = orderAllData.Single(m => m.OrderID == OrderID);
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
string sqlDetail = "select * from Sales.OrderDetails where OrderID = @Id";
SqlCommand cmdDetail = new SqlCommand(sqlDetail, conn);
cmdDetail.Parameters.Add(new SqlParameter("@Id", OrderID));
SqlDataAdapter adapterDetail = new SqlDataAdapter(cmdDetail);
DataSet dsDetail = new DataSet();
adapterDetail.Fill(dsDetail);
orderData.Details = new List <OrderDetail>();
foreach (DataRow detailRow in dsDetail.Tables[0].Rows)
{
orderData.Details.Add(new OrderDetail
{
OrderID = int.Parse(detailRow["OrderID"].ToString()),
ProductID = int.Parse(detailRow["ProductID"].ToString()),
UnitPrice = decimal.Parse(detailRow["UnitPrice"].ToString()),
Qty = int.Parse(detailRow["Qty"].ToString()),
});
}
return(orderData);
}
public Customer getData(int CustomerID)
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
List <Customer> customer = new List <Customer>();
customer = this.getAll();
Customer customerData = customer.Single(m => m.CustomerID == CustomerID);
return(customerData);
}
public Order getData(int OrderID)
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
List <Order> orderList = new List <Order>();
orderList = this.getAll();
Order orderData = orderList.Single(m => m.OrderID == OrderID);
return(orderData);
}
public List <Order> getAll()
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
//SQL
string sql = "Select * From [Sales].Orders";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Order");
List <Order> order = new List <Order>();
order = dataSet.Tables[0].AsEnumerable().Select(
dataRow => new Order()
{
OrderID = dataRow.Field <int>("OrderID"), //訂購編號
CustomerID = dataRow.Field <int>("CustomerID"), //顧客編號
EmployeeID = dataRow.Field <int>("EmployeeID"), //員工編號
ShipperID = dataRow.Field <int>("ShipperID"), //出貨編號
OrderDate = dataRow.Field <DateTime>("OrderDate"), //訂購日期
ShippedDate = dataRow.Field <DateTime?>("ShippedDate"), //出貨日期
RequiredDate = dataRow.Field <DateTime>("RequiredDate"), //需要日期
ShipCountry = dataRow.Field <String>("ShipCountry").ToString(), //出貨國家
ShipCity = dataRow.Field <String>("ShipCity"), //出貨城市
ShipRegion = dataRow.Field <String>("ShipRegion"), //出貨地區
ShipPostalCode = dataRow.Field <String>("ShipPostalCode"), //郵遞區號
ShipAddress = dataRow.Field <String>("ShipAddress"), //出貨地址
Freight = dataRow.Field <decimal>("Freight") //運費
}).ToList();
return(order);
}
public List <Employees> getAllData()
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
String sql = "SELECT * FROM [HR].Employees";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Employees");
List <Employees> employeesList = dataSet.Tables[0].AsEnumerable().Select(
dataRow => new Employees
{
EmployeeID = dataRow.Field <int>("EmployeeID"),
LastName = dataRow.Field <String>("LastName"),
FirstName = dataRow.Field <String>("FirstName")
}).ToList();
return(employeesList);
}
public List <Shippers> getAllData()
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
String sql = "SELECT * FROM [Sales].Shippers";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Shippers");
List <Shippers> ShippersList = dataSet.Tables[0].AsEnumerable().Select(
dataRow => new Shippers
{
ShipperID = dataRow.Field <int>("ShipperID"),
CompanyName = dataRow.Field <String>("CompanyName"),
Phone = dataRow.Field <String>("Phone")
}).ToList();
return(ShippersList);
}
public List <Products> getAllData()
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
String sql = "SELECT * FROM [Production].[Products]";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Products");
List <Products> productsList = dataSet.Tables[0].AsEnumerable().Select(
dataRow => new Products
{
ProductID = dataRow.Field <int>("ProductID"),
ProductName = dataRow.Field <String>("ProductName"),
UnitPrice = dataRow.Field <decimal>("UnitPrice")
}).ToList();
return(productsList);
}
public List <Customers> getAllData()
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
String sql = "SELECT * FROM [Sales].Customers";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Customers");
List <Customers> customersList = dataSet.Tables[0].AsEnumerable().Select(
dataRow => new Customers
{
CustomerID = dataRow.Field <int>("CustomerID"),
CompanyName = dataRow.Field <String>("CompanyName"),
ContactName = dataRow.Field <String>("ContactName"),
Address = dataRow.Field <String>("Address")
}).ToList();
return(customersList);
}
public List <Customer> getAll()
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
//SQL
string sql = "Select * From [Sales].Customer";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Customer");
List <Customer> customer = new List <Customer>();
customer = dataSet.Tables[0].AsEnumerable().Select(
dataRow => new Customer()
{
}).ToList();
return(customer);
}
public int DeleteOrder(int orderId)
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
string sql = @"
DELETE FROM [Sales].[OrderDetails]
WHERE OrderID = @OrderID
DELETE FROM [Sales].[Orders]
WHERE OrderID = @OrderID
";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@OrderID", orderId));
conn.Open();
// 開啟交易控管
SqlTransaction transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
try
{
cmd.ExecuteNonQuery();
// 全部動作做完後執行Commit
transaction.Commit();
}
catch (Exception)
{
// 有出問題則將此交易內的所有更動的資料Rollback
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
return(orderId);
}
public int InsertOrderReturnNewOrderId(Orders newOrder)
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
string sql = @"
INSERT INTO [Sales].[Orders]
([CustomerID]
,[EmployeeID]
,[OrderDate]
,[RequiredDate]
,[ShippedDate]
,[ShipperID]
,[Freight]
,[ShipName]
,[ShipAddress]
,[ShipCity]
,[ShipRegion]
,[ShipPostalCode]
,[ShipCountry])
VALUES
(@CustomerID
,@EmployeeID
,@OrderDate
,@RequiredDate
,@ShippedDate
,@ShipperID
,@Freight
,@ShipName
,@ShipAddress
,@ShipCity
,@ShipRegion
,@ShipPostalCode
,@ShipCountry)
SELECT SCOPE_IDENTITY()
";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@CustomerID", newOrder.CustomerID));
cmd.Parameters.Add(new SqlParameter("@EmployeeID", newOrder.EmployeeID));
cmd.Parameters.Add(new SqlParameter("@OrderDate", newOrder.OrderDate));
cmd.Parameters.Add(new SqlParameter("@RequiredDate", newOrder.RequiredDate));
cmd.Parameters.Add(new SqlParameter("@ShippedDate", newOrder.ShippedDate.HasValue ? newOrder.ShippedDate.Value.ToString("yyyy/MM/dd") : ""));
cmd.Parameters.Add(new SqlParameter("@ShipperID", newOrder.ShipperID));
cmd.Parameters.Add(new SqlParameter("@Freight", newOrder.Freight));
cmd.Parameters.Add(new SqlParameter("@ShipName", ""));
cmd.Parameters.Add(new SqlParameter("@ShipAddress", newOrder.ShipAddress));
cmd.Parameters.Add(new SqlParameter("@ShipCity", newOrder.ShipCity));
cmd.Parameters.Add(new SqlParameter("@ShipRegion", string.IsNullOrWhiteSpace(newOrder.ShipRegion) ? " " : newOrder.ShipRegion));
cmd.Parameters.Add(new SqlParameter("@ShipPostalCode", string.IsNullOrWhiteSpace(newOrder.ShipPostalCode) ? " " : newOrder.ShipPostalCode));
cmd.Parameters.Add(new SqlParameter("@ShipCountry", newOrder.ShipCountry));
int orderId;
conn.Open();
// 開啟交易控管
SqlTransaction transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
try
{
orderId = Convert.ToInt32(cmd.ExecuteScalar());
string sqlDetail = @"
INSERT INTO Sales.OrderDetails
([OrderID]
,[ProductID]
,[UnitPrice]
,[Qty]
,[Discount])
VALUES
(@OrderID
,@ProductID
,@UnitPrice
,@Qty
, 0)
";
foreach (var detail in newOrder.Details)
{
SqlCommand cmdDetail = new SqlCommand(sqlDetail, conn);
cmdDetail.Transaction = transaction;
cmdDetail.Parameters.Add(new SqlParameter("@OrderID", orderId));
cmdDetail.Parameters.Add(new SqlParameter("@ProductID", detail.ProductID));
cmdDetail.Parameters.Add(new SqlParameter("@UnitPrice", detail.UnitPrice));
cmdDetail.Parameters.Add(new SqlParameter("@Qty", detail.Qty));
cmdDetail.ExecuteNonQuery();
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
return(orderId);
}
public int UpdateOrder(Orders oldOrder)
{
DaoConnect daoConnect = new DaoConnect();
SqlConnection conn = daoConnect.SqlConnect();
string sql = @"
UPDATE [Sales].[Orders]
SET
[CustomerID] = @CustomerID
,[EmployeeID] = @EmployeeID
,[OrderDate] = @OrderDate
,[RequiredDate] = @RequiredDate
,[ShippedDate] = @ShippedDate
,[ShipperID] = @ShipperID
,[Freight] = @Freight
,[ShipName] = @ShipName
,[ShipAddress] = @ShipAddress
,[ShipCity] = @ShipCity
,[ShipRegion] = @ShipRegion
,[ShipPostalCode] = @ShipPostalCode
,[ShipCountry] = @ShipCountry
WHERE [OrderID] = @OrderID
DELETE FROM Sales.OrderDetails WHERE OrderID = @OrderID
";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@OrderID", oldOrder.OrderID));
cmd.Parameters.Add(new SqlParameter("@CustomerID", oldOrder.CustomerID));
cmd.Parameters.Add(new SqlParameter("@EmployeeID", oldOrder.EmployeeID));
cmd.Parameters.Add(new SqlParameter("@OrderDate", oldOrder.OrderDate));
cmd.Parameters.Add(new SqlParameter("@RequiredDate", oldOrder.RequiredDate));
cmd.Parameters.Add(new SqlParameter("@ShippedDate", oldOrder.ShippedDate.HasValue ? oldOrder.ShippedDate.Value.ToString("yyyy/MM/dd") : ""));
cmd.Parameters.Add(new SqlParameter("@ShipperID", oldOrder.ShipperID));
cmd.Parameters.Add(new SqlParameter("@Freight", oldOrder.Freight));
cmd.Parameters.Add(new SqlParameter("@ShipName", ""));
cmd.Parameters.Add(new SqlParameter("@ShipAddress", oldOrder.ShipAddress));
cmd.Parameters.Add(new SqlParameter("@ShipCity", oldOrder.ShipCity));
cmd.Parameters.Add(new SqlParameter("@ShipRegion", string.IsNullOrWhiteSpace(oldOrder.ShipRegion) ? oldOrder.ShipRegion : " "));
cmd.Parameters.Add(new SqlParameter("@ShipPostalCode", string.IsNullOrWhiteSpace(oldOrder.ShipPostalCode) ? oldOrder.ShipPostalCode : " "));
cmd.Parameters.Add(new SqlParameter("@ShipCountry", oldOrder.ShipCountry));
conn.Open();
cmd.ExecuteNonQuery();
int orderId;
// 開啟交易控管
SqlTransaction transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
try
{
orderId = Convert.ToInt32(oldOrder.OrderID);
if (oldOrder.Details != null)
{
string sqlDetail = @"
INSERT INTO Sales.OrderDetails
([OrderID]
,[ProductID]
,[UnitPrice]
,[Qty]
,[Discount])
VALUES
(@OrderID
,@ProductID
,@UnitPrice
,@Qty
, 0)
";
foreach (var detail in oldOrder.Details)
{
SqlCommand cmdDetail = new SqlCommand(sqlDetail, conn);
cmdDetail.Transaction = transaction;
cmdDetail.Parameters.Add(new SqlParameter("@OrderID", orderId));
cmdDetail.Parameters.Add(new SqlParameter("@ProductID", detail.ProductID));
cmdDetail.Parameters.Add(new SqlParameter("@UnitPrice", detail.UnitPrice));
cmdDetail.Parameters.Add(new SqlParameter("@Qty", detail.Qty));
cmdDetail.ExecuteNonQuery();
}
transaction.Commit();
}
}
catch
{
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
return(orderId);
}
