public OrderObject GetOrder(int id) { OrderObject order = new OrderObject(); try { db.OpenConnection(); string sql = @"Select tbl_Order.ID AS[orderID], tbl_User.Firstname AS[firstName], tbl_User.Lastname AS[lastName], tbl_User.Pricegroup AS[priceGroup], " + @"tbl_User.Company AS[company], tbl_User.ID AS [usrID], tbl_User.Adress AS[adress], tbl_User.Postalcode AS[postalCode], tbl_User.City AS[city], " + @"tbl_User.Pricegroup AS[priceGroup], tbl_User.Telephone AS[telephone], tbl_User.Mobilephone AS[mobile], tbl_User.Email AS[email], " + @"tbl_Product_Attribute.PriceB2B AS[priceB2B], tbl_Product_Attribute.PriceB2C AS[priceB2C], tbl_Product_Attribute.ArticleNumber AS[ArtNr], " + @"[tbl_Order_Product-Attribute].Quantity AS[quantity], tbl_Product.Name AS[productName], tbl_User.Pricegroup AS[priceGroup], tbl_Carrier.Carrier AS [carrier], " + @"tbl_Carrier.[Service] AS [carrierSevice], tbl_Carrier.Price AS[carrierPrice], tbl_User.Pricegroup AS[priceGroup], " + @"tbl_Payment.Provider AS [payment], tbl_Payment.[Service] AS [paymentService], tbl_payment.Price AS[paymentPrice], " + @"tbl_Product_Attribute.AttributeID1 AS[attribute1], tbl_Product_Attribute.AttributeID2 AS[attribute2], tbl_Product_Attribute.AttributeID3 AS[attribute3], tbl_Product_Attribute.AttributeID4 AS[attribute4], tbl_Order.Date " + @"From[tbl_Order_Product-Attribute] " + @"INNER JOIN tbl_Order " + @"ON[tbl_Order_Product-Attribute].OrderID = tbl_Order.ID " + @"INNER JOIN tbl_Product_Attribute " + @"ON[tbl_Order_Product-Attribute].ProductAttributeID = tbl_Product_Attribute.ID " + @"INNER JOIN tbl_User " + @"ON tbl_Order.UserID = tbl_User.ID " + @"INNER JOIN tbl_Carrier " + @"ON tbl_Order.CarrierID = tbl_Carrier.ID " + @"INNER JOIN tbl_Payment " + @"ON tbl_Order.PaymentID = tbl_Payment.ID " + @"INNER JOIN tbl_Product " + @"ON tbl_Product_Attribute.ProductID = tbl_Product.ID " + $"WHERE orderID = '{id}'"; SqlCommand myCommand = new SqlCommand(sql, db._connection); using (SqlDataReader myDataReader = myCommand.ExecuteReader()) { while (myDataReader.Read()) { decimal num = -1; order.orderId = int.Parse(myDataReader["orderID"].ToString()); order.priceGroup = int.Parse(myDataReader["priceGroup"].ToString()); order.company = myDataReader["company"].ToString(); order.firstName = myDataReader["firstName"].ToString(); order.lastName = myDataReader["lastName"].ToString(); order.adress = myDataReader["adress"].ToString(); order.postalCode = myDataReader["postalCode"].ToString(); order.city = myDataReader["city"].ToString(); order.date = myDataReader["Date"].ToString(); order.userID = int.Parse(myDataReader["usrID"].ToString()); order.telephone = myDataReader["telephone"].ToString(); order.mobile = myDataReader["mobile"].ToString(); order.email = myDataReader["email"].ToString(); ProductObject prod = new ProductObject(); prod.artNr = myDataReader["ArtNr"].ToString(); prod.name = myDataReader["productName"].ToString(); prod.quantity = int.Parse(myDataReader["quantity"].ToString()); decimal.TryParse(myDataReader["priceB2B"].ToString(), out num); prod.priceB2B = num; decimal.TryParse(myDataReader["priceB2C"].ToString(), out num); prod.priceB2C = num; order.carrier = myDataReader["carrier"].ToString(); order.carrierService = myDataReader["carrierSevice"].ToString(); decimal.TryParse(myDataReader["carrierPrice"].ToString(), out num); order.carrierPrice = num; order.payment = myDataReader["payment"].ToString(); order.paymentService = myDataReader["paymentService"].ToString(); decimal.TryParse(myDataReader["paymentPrice"].ToString(), out num); order.paymentPrice = num; int atr = -1; int.TryParse(myDataReader["attribute1"].ToString(), out atr); prod.attribute1 = atr; int.TryParse(myDataReader["attribute2"].ToString(), out atr); prod.attribute2 = atr; int.TryParse(myDataReader["attribute3"].ToString(), out atr); prod.attribute3 = atr; int.TryParse(myDataReader["attribute4"].ToString(), out atr); prod.attribute4 = atr; order.AddProduct(prod); } } return(order); } catch { return(null); } finally { db.CloseConnection(); } }
public void AddProduct(ProductObject prod) { this.products.Add(prod); }