//display only relevant order details public static DataTable GetDataTable(int orderId) { //define connection SqlConnection connection = NorthwindDB.GetConnection(); DataTable dt = new DataTable(); //empty DataTable object try { // open the connection connection.Open(); SqlDataAdapter sda = new SqlDataAdapter("select * from [Order Details] where OrderID =" + orderId, connection); sda.Fill(dt); } catch (Exception ex) { throw ex; // let the form handle it } finally { connection.Close(); } return(dt); }
// updates existing order record and returns bool success flag public static bool UpdateOrder(Order oldOrder, Order newOrder) { bool successful = false; SqlConnection connection = NorthwindDB.GetConnection(); //define update query command string updateString = "update Orders set " + "ShippedDate = @NewShippedDate " + "where " + // update succeeds only if record not changed by other users "CustomerID = @OldCustomerID and " + "OrderDate = @OldOrderDate and " + "RequiredDate = @OldRequiredDate and " + "(ShippedDate = @OldShippedDate or " + "ShippedDate IS NULL and @OldShippedDate IS NULL)"; SqlCommand updateCommand = new SqlCommand(updateString, connection); updateCommand.Parameters.AddWithValue("@OldCustomerID", oldOrder.CustomerID); updateCommand.Parameters.AddWithValue("@OldOrderDate", oldOrder.OrderDate); updateCommand.Parameters.AddWithValue("@OldRequiredDate", oldOrder.RequiredDate); if (oldOrder.ShippedDate != null)//not null { updateCommand.Parameters.AddWithValue("@OldShippedDate", oldOrder.ShippedDate); } else { updateCommand.Parameters.AddWithValue("@OldShippedDate", DBNull.Value); } if (newOrder.ShippedDate != null)//not null { updateCommand.Parameters.AddWithValue("@NewShippedDate", newOrder.ShippedDate); } else { updateCommand.Parameters.AddWithValue("@NewShippedDate", DBNull.Value); } try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count == 1)//updated a order from database { successful = true; } } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(successful); }
//retrieve order data from Orders table public static Order GetOrder(int OrderID) { Order order = null;//found Order //define connection SqlConnection connection = NorthwindDB.GetConnection(); //define the select query command string selectString = "select OrderID, CustomerID, OrderDate, RequiredDate, Shippeddate " + "from Orders " + "where OrderID = @OrderID"; SqlCommand selectCommand = new SqlCommand(selectString, connection); selectCommand.Parameters.AddWithValue("@OrderID", OrderID); try { // open the connection connection.Open(); // execute the query SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); // process the result if any if (reader.Read()) // if there is Order { order = new Order(); order.OrderID = (int)reader["OrderID"]; order.CustomerID = reader["CustomerID"].ToString(); order.OrderDate = GetNullableDatetime(reader, "OrderDate"); order.RequiredDate = GetNullableDatetime(reader, "RequiredDate"); order.ShippedDate = GetNullableDatetime(reader, "ShippedDate"); } } catch (Exception ex) { throw ex; // let the form handle it } finally { connection.Close(); // close connecto no matter what } return(order); }
//make a list to contain OrderId public static List <OrderId> GetOrderIds() { List <OrderId> orderIds = new List <OrderId>(); // make an empty list OrderId oi; // reference to new state object // create connection SqlConnection connection = NorthwindDB.GetConnection(); // create select command string selectString = "select OrderID from Orders order by OrderID"; SqlCommand selectCommand = new SqlCommand(selectString, connection); try { // open connection connection.Open(); // run the select command and process the results adding OrderID to the list SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read())// process next row { oi = new OrderId(); oi.OrderID = (int)reader["OrderID"]; orderIds.Add(oi); } reader.Close(); } catch (Exception ex) { throw ex; // throw it to the form to handle } finally { connection.Close(); } return(orderIds); }
//calculate Order Total public static decimal GetOrderTotal(int orderId) { //define connection SqlConnection connection = NorthwindDB.GetConnection(); //define a select query command string selectString = "select OrderID,sum(Unitprice*(1-Discount)*Quantity) as Order_Total from [Order Details] where OrderID = @OrderID " + "group by OrderID"; SqlCommand selectCommand = new SqlCommand(selectString, connection); selectCommand.Parameters.AddWithValue("@OrderID", orderId); try { // open the connection connection.Open(); // execute the query SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); // process the result if (reader.Read()) { return(Convert.ToDecimal(reader["Order_Total"])); } } catch (Exception ex) { throw ex; // let the form handle it } finally { connection.Close(); // close connect } return(0); }