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