The data base likes to keep data in relational tables and the middle tier likes to present the data in object graphs where the details are stored within the patient object, so everything is close at hand. So how do you transform the data from tables to graphs? I use Linq2DataSets.
The Stored Procedure
Suppose you are asked to load an object graph with the following stored procedure (using Northwind, my favorite database of all times):
-- This procedure returns 2 results sets the first includes all of the Orders and
-- the second all of the Order Details (regardless of order).
CREATE PROCEDURE dbo.GetAllOrders
AS
SELECT o.OrderID, o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate,
o.ShippedDate, o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, o.ShipCity,
o.ShipRegion, o.ShipPostalCode, o.ShipCountry
FROM [Orders] o
SELECT od.OrderID, od.ProductID, od.UnitPrice, od.Quantity, od.Discount
FROM [Order Details] od
In real life, I would probably have parameters to limit the result sets to the data that I really want. For the purpose of demonstration, it is helpful to process a lot of data.
Getting the data into the Object Graphs
Now, suppose you want to load this data into Object Graphs. In my example, I have an Order class that includes a list of its Order Details (a real production SP would probably include parameters to filter the data returned; you don’t want to load all the orders for a real company).
The SP returns the two lists that you will have to stitch together yourself in code:
/// <summary> /// Loads all the orders into a list of Order objects /// </summary> public static ListGetAllOrders(OrderDS ds) { return new List ( from orders in ds.Orders select new Order( orders.OrderID, orders.CustomerID, orders.EmployeeID, orders.OrderDate, orders.RequiredDate, !orders.IsShippedDateNull() ? orders.ShippedDate : (DateTime?)null, orders.ShipVia, orders.Freight, orders.ShipName, orders.ShipAddress, orders.ShipCity, !orders.IsShipRegionNull() ? orders.ShipRegion : string.Empty, !orders.IsShipPostalCodeNull() ? orders.ShipPostalCode : string.Empty, orders.ShipCountry, getOrderDetails(orders.OrderID, ds))); } /// <summary> /// Loads the order details associated with a given order into a list of /// OrderDetal objects /// </summary> public static List getOrderDetails(int orderId, OrderDS ds) { return new List ( from orderDetails in ds.OrderDetails where orderDetails.OrderID == orderId select new OrderDetail( orderDetails.OrderID, orderDetails.ProductID, orderDetails.UnitPrice, orderDetails.Quantity, orderDetails.Discount)); }
From the above code, I would like to point out
- GetAllOrders() calls getOrderDetails() from within the linq query. When you call the Orders constructor, you can call any code you would call in any other constructor.
- Even though I’m in the middle of a Linq query in GetAllOrders(), I can create a second Linq query in getOrderDetails using the same DataSet.
- Since we are using DataSets and other classic ADO.NET objects, we need to call IsFooNullable() to avoid an InvalidCastException.
- When I’m passing null to the Order constructor, I need to cast it to the correct type.
Appendix
Here are the classes that I loaded the in the code above
public class Order
{
public int OrderID { get; set; }
public string CustomerID { get; set; }
public int EmployeeID { get; set; }
public DateTime OrderDate { get; set; }
public DateTime RequiredDate { get; set; }
public DateTime? ShippedDate { get; set; }
public int ShipVia { get; set; }
public decimal Freight { get; set; }
public string ShipName { get; set; }
public string ShipAddress { get; set; }
public string ShipCity { get; set; }
public string ShipRegion { get; set; }
public string ShipPostalCode { get; set; }
public string ShipCountry { get; set; }
public List Details { get; set; }
public Order(int orderID, string customerID, int employeeID, DateTime orderDate,
DateTime requiredDate, DateTime? shippedDate, int shipVia, decimal freight,
string shipName, string shipAddress, string shipCity, string shipRegion,
string shipPostalCode, string shipCountry, List details)
{
this.OrderID = orderID;
this.CustomerID = customerID;
this.EmployeeID = employeeID;
this.OrderDate = orderDate;
this.RequiredDate = requiredDate;
this.ShippedDate = shippedDate;
this.ShipVia = shipVia;
this.Freight = freight;
this.ShipName = shipName;
this.ShipAddress = shipAddress;
this.ShipCity = shipCity;
this.ShipRegion = shipRegion;
this.ShipPostalCode = shipPostalCode;
this.ShipCountry = shipCountry;
this.Details = details;
}
}
public class OrderDetail
{
public int OrderID { get; set; }
public int ProductID { get; set; }
public decimal UnitPrice { get; set; }
public int Quantity { get; set; }
public float Discount { get; set; }
public OrderDetail(int orderID, int productID, decimal unitPrice, int quantity,
float discount)
{
this.OrderID = orderID;
this.ProductID = productID;
this.UnitPrice = unitPrice;
this.Quantity = quantity;
this.Discount = discount;
}
}
And GetDataSet() the function that loads the DataSet
/// <summary>
/// Loads OrderDS using the SP GetAllOrders
/// </summary>
public static OrderDS GetDataSet()
{
var ds = new OrderDS();
SqlConnection conn = new SqlConnection(CONNECTION_STRING);
using (SqlCommand cmd = new SqlCommand("GetAllOrders", conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataAdapter DA = new SqlDataAdapter(cmd);
DA.TableMappings.Add("Table", "Orders");
DA.TableMappings.Add("Table1", "OrderDetails");
DA.Fill(ds);
}
return ds;
}
Since I’m too lazy to do graphics on this blog, I will leave it to the reader to make the actual DataSet.
