//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);
}