//Method1: retrieve all records from table [orders] public static List <Order> GetAllOrders() { List <Order> orderlist = new List <Order>(); //empty list Order order; //for reading //create sql connection SqlConnection connection = NorthwindDB.GetConnection(); //create select cmd string query = "select [orders].OrderID,[orders].CustomerID,[orders].OrderDate,[orders].RequiredDate,[orders].ShippedDate," + "sum([Order Details].UnitPrice*(1-[Order Details].Discount)*[Order Details].Quantity) as OrderTotal" + " from [orders] inner join [Order Details]" + " on [Order Details].OrderID=[Orders].OrderID" + " group by [orders].OrderID,[orders].CustomerID,[orders].OrderDate,[orders].RequiredDate,[orders].ShippedDate" ; SqlCommand cmd = new SqlCommand(query, connection); try { //open the connection connection.Open(); //run the select query SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // build order list to return while (reader.Read()) //if there is an order { order = new Order(); order.OrderID = (int)reader["OrderID"]; order.CustomerID = reader["CustomerID"].ToString(); order.OrderDate = string.Format("{0:MM/dd/yyyy}", reader["OrderDate"]); order.RequiredDate = string.Format("{0:MM/dd/yyyy}", reader["RequiredDate"]); order.ShippedDate = string.Format("{0:MM/dd/yyyy}", reader["ShippedDate"]); order.OrderTotal = Convert.ToDecimal(reader["OrderTotal"]); orderlist.Add(order); } reader.Close(); } catch (Exception ex) //error { throw ex; } finally //executes always { connection.Close(); } //return the list return(orderlist); }
//retrieve selected records from table OrderDetail public static List <OrderDetail> GetSelectedOrderDetail(int orderID) { List <OrderDetail> orderdetail_list = new List <OrderDetail>(); //empty list OrderDetail orderdetail; //for reading //create connection SqlConnection connection = NorthwindDB.GetConnection(); //create command for Select query to get the orderdetails string query = "select * from [Order Details] where OrderID=@OrderID"; SqlCommand cmd = new SqlCommand(query, connection); // supply parameter value cmd.Parameters.AddWithValue("@OrderID", orderID); //open the connection connection.Open(); //run the command SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); //add orderdetail data to the list while (reader.Read()) //while there still is data to read { orderdetail = new OrderDetail(); orderdetail.OrderID = (int)reader["OrderID"]; orderdetail.ProductID = (int)reader["ProductID"]; orderdetail.UnitPrice = (decimal)reader["UnitPrice"]; orderdetail.Quantity = Convert.ToInt32(reader["Quantity"]); orderdetail.Discount = Convert.ToDecimal(reader["Discount"]); orderdetail_list.Add(orderdetail); } return(orderdetail_list); }
//Method 2 Update order in table [orders] // return indicator of success public static bool UpdateOrder(Order oldOrder, Order newOrder) { bool success = false; //did not update //connection SqlConnection connection = NorthwindDB.GetConnection(); //update command (only update shippeddate) string updateStatement = "UPDATE [Orders] ShippedDate=@NewShippedDate" + "WHERE ShippedDate=@OldShippedDate"; SqlCommand cmd = new SqlCommand(updateStatement, connection); string newshipdate = String.Format("{0:d/M/yyyy HH:mm:ss}", newOrder.ShippedDate); cmd.Parameters.AddWithValue("@NewShippedDate", newshipdate); cmd.Parameters.AddWithValue("@OldShippedDate", oldOrder.ShippedDate); try { connection.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { success = true; //updated } } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(success); }