//method to get the total charge for the orderID selected on the datagridview public static decimal GetTotalCharge(int orderID) { SqlConnection con = NorthWindDB.GetConnection(); string selectStatement = "SELECT sum(UnitPrice*Quantity*(1- Discount)) as totalcharge " + "FROM [Order Details] " + "WHERE OrderID = @OrderID"; SqlCommand selectCommand = new SqlCommand(selectStatement, con); selectCommand.Parameters.AddWithValue("@OrderID", orderID); try { con.Open(); // open database connection SqlDataReader reader = selectCommand.ExecuteReader(); // execute query if (reader.Read()) // read next record while there is one { return(Convert.ToDecimal(reader["totalcharge"])); } else { return(0m); } } catch (SqlException ex) { throw ex; } finally { con.Close(); //closes connection } }
//This is a method which gets the orderID by connecting to the SQL Server public static Orders getOrders(int orderID) { Orders order = null; SqlConnection con = NorthWindDB.GetConnection(); //gets connections from NortwindDB class string selectQuery = "SELECT OrderID, CustomerID, OrderDate, RequiredDate, ShippedDate " + "FROM Orders " + "WHERE OrderID = @OrderID"; SqlCommand selectCommand = new SqlCommand(selectQuery, con); selectCommand.Parameters.AddWithValue("@OrderID", orderID); try { con.Open(); //open the connection SqlDataReader reader = selectCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow); if (reader.Read()) //reads data { order = new Orders(); //create a new order object order.OrderID = (int)reader["OrderID"]; order.CustomerID = reader["CustomerID"].ToString(); if (DBNull.Value.Equals((DateTime)reader["OrderDate"])) { order.OrderDate = null; } //giving a null value else { order.OrderDate = (DateTime)reader["OrderDate"]; } if (DBNull.Value.Equals(reader["RequiredDate"])) { order.RequiredDate = null; } else { order.RequiredDate = (DateTime)reader["RequiredDate"]; } if (DBNull.Value.Equals(reader["ShippedDate"])) { order.ShippedDate = null; } else { order.ShippedDate = (DateTime)reader["ShippedDate"]; } //MessageBox.Show(reader["CustomerID"].ToString()); } } catch (SqlException ex) { throw ex; } finally { con.Close();//close the connection } return(order); }
//this is a boolean to update order and is passing 3 arguments public static bool UpdateOrder(int OrderID, DateTime oldShippedDate, DateTime?newShippedDate) { SqlConnection con = NorthWindDB.GetConnection(); string updateStmt = "UPDATE Orders SET " + "ShippedDate = @newShippedDate " + "WHERE OrderID = @OrderID " + "AND ShippedDate = @oldShippedDate"; //string updateStmt = "UPDATE Orders SET " + // "ShippedDate = @NewShippedDate " + // "WHERE ShippedDate = @oldShippedDate is null and @OldShippedDate is null " + // " AND OrderID = @OrderID"; SqlCommand updateCommand = new SqlCommand(updateStmt, con); //MessageBox.Show(oldShippedDate.ToString() + ":" + newShippedDate.ToString()); updateCommand.Parameters.AddWithValue("@newShippedDate", newShippedDate); updateCommand.Parameters.AddWithValue("@OrderID", OrderID); updateCommand.Parameters.AddWithValue("@oldShippedDate", oldShippedDate); try { con.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
public static List <OrderDetails> getOrderDetails(int orderID)//loads up the ordertails to a list and passes using orderID { List <OrderDetails> od = new List <OrderDetails>(); OrderDetails ordDet = null; SqlConnection con = NorthWindDB.GetConnection();//connection to the data source string selectQuery = "SELECT * FROM [Order Details] " + "WHERE OrderID = @OrderID"; SqlCommand selectCommand = new SqlCommand(selectQuery, con); selectCommand.Parameters.AddWithValue("@OrderID", orderID); //Console.WriteLine("Attempting OD fetch..."); try { con.Open(); SqlDataReader reader = selectCommand.ExecuteReader();// System.Data.CommandBehavior.SingleRow); while (reader.Read()) { // Console.WriteLine("Building Row..."); ordDet = new OrderDetails(); ordDet.OrderID = (int)reader["OrderID"]; ordDet.ProductID = (int)reader["ProductID"]; ordDet.UnitPrice = (decimal)reader["UnitPrice"]; ordDet.Quantity = Convert.ToInt32(reader["Quantity"]); ordDet.Discount = Convert.ToDecimal(reader["Discount"]); od.Add(ordDet); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } //Console.WriteLine("SetSize: "+od.Count); return(od); }