public static List <OrderId> GetOrderIds()
{
// empty list to store the retrieved values from database
List <OrderId> ids = new List <OrderId>();
OrderId id;
// create connection
using (SqlConnection connection = NorthwindCon.GetConnection())
{
// select query
string query = "SELECT orderid FROM orders";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
// run the command and process results
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// process next record from data reader
id = new OrderId();
id.orderId = (int)reader["orderid"];
ids.Add(id);
}
} // closes reader and recycles object
} // cmd object recycled
} // connection object recycled
return(ids);
}
public static Orders GetOrders(int OrderId)
{
Orders order = null;
using (SqlConnection connection = NorthwindCon.GetConnection())
{
// query with parameter selection to extract one order data
string query = "SELECT OrderID, CustomerID, OrderDate, RequiredDate, Shippeddate " +
"FROM Orders " +
"WHERE OrderID = @OrderID";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@OrderID", OrderId);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read()) // reading one row from the database based on given order id
{
order = new Orders();
order.OrderId = (int)reader["OrderID"];
order.CustomerID = reader["CustomerID"].ToString();
order.OrderDate = (reader["orderdate"] == DBNull.Value) ? null : (DateTime?)reader["orderdate"];
order.RequiredDate = (reader["RequiredDate"] == DBNull.Value) ? null : (DateTime?)reader["RequiredDate"];
order.ShippedDate = (reader["Shippeddate"] == DBNull.Value)? null : (DateTime?)reader["Shippeddate"];
}
}
}
}
return(order);
}
public static List <OrderDetails> GetOrderDetails(int OrderId)
{
List <OrderDetails> datadetail = new List <OrderDetails>(); // list to store orders table data
using (SqlConnection connection = NorthwindCon.GetConnection())
{
// query to extract columns from orders table
string query1 = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " +
"FROM [Order Details] " +
"WHERE OrderID = @OrderID";
using (SqlCommand cmd1 = new SqlCommand(query1, connection))
{
cmd1.Parameters.AddWithValue("@OrderID", OrderId);
connection.Open();
using (SqlDataReader reader = cmd1.ExecuteReader())
{
while (reader.Read()) // while loop to read all the rows associated with one order id
{
OrderDetails orderdata = new OrderDetails();
orderdata.OrderID = Convert.ToInt32(reader["OrderID"]);
orderdata.ProductID = Convert.ToInt32(reader["ProductID"]);
orderdata.UnitPrice = Convert.ToDouble(reader["UnitPrice"]);
orderdata.Quantity = Convert.ToInt16(reader["Quantity"]);
orderdata.Discount = Convert.ToDouble(reader["Discount"]);
datadetail.Add(orderdata);
}
}
}
}
return(datadetail); // returning the list with all the data
}
// Update method using same name but different signatures to accept null values
public static void UpdateShippingDate(int id)
{
using (SqlConnection connection = NorthwindCon.GetConnection())
{
string updatedate = "update orders set shippeddate = @newShippedDate Where OrderID = @OrderID"; // to identify record
using (SqlCommand cmd = new SqlCommand(updatedate, connection))
{
cmd.Parameters.AddWithValue("@newShippedDate", DBNull.Value);
cmd.Parameters.AddWithValue("@OrderID", id);
connection.Open();
cmd.ExecuteNonQuery();
}
}
}