Tuesday, February 23, 2010

Loading Object Graphs with Linq2DataSets

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 List GetAllOrders(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.

Sunday, February 21, 2010

Generate Stored Procedures with data from SQL Server System Tables

Here’s an experiment I wrote over the weekend to create an Insert SP for a table using SQL Server System Tables.

This code generates code for a specific code pattern and won’t necessarily work for any table that is thrown at it. Supporting every possible situation can make the code really complex. If I am faced with a table that this can’t handle, I am face with the choice of expanding the program to handle the new situation or write the code by hand.

These SPs are the same except for:

  • The SP name – Based on Table Name
  • The Table Name
  • The Fields
  • Which Field is the Primary Key
  • Which Fields are Nullable

From the System Tables I use:

  • sysObjects.name: the name of the table
  • sysColumns.name: the column name
  • sysTypes.name: the type name
  • sysColumns.length: the length of the field
  • sysColumns.isnullable: is the field nullable?
  • sysColumns.status: used to determine if this is the primary key/auto-number column (look for 0x80)

The Code

using System;
using System.Data.SqlClient;
using System.Text;

namespace GenerateSP
{
    class Program
    {
        /// <summary>
        /// The Connection String
        /// </summary>
        private const string CONNECTION_STRING = @"Data Source=localhost\SQLEXPRESS;" + 
             "Initial Catalog=Northwind;Integrated Security=True";
        /// <summary>
        /// Number of list items per line in the generated procedure
        /// </summary>
        private const int ITEMS_PER_LINE = 5;
        /// <summary>
        /// Shows the Code Generation in action
        /// </summary>
        static void Main(string[] args)
        {
            Console.WriteLine(CreateInsertSp("Employees"));
            Console.ReadKey();
        }
        /// <summary>
        /// Creates an INSERT Stored Procedure for a given table as a string
        /// </summary>
        public static string CreateInsertSp(string tableName)
        {
            string identityField = string.Empty;
            // There are 3 places in the SP that refer to the field names
            // so I am using 3 string builders to generate those parts
            // of the SP;
            StringBuilder parametersSb = new StringBuilder();
            StringBuilder InsertValueListSb = new StringBuilder();
            StringBuilder InsertFieldListSb = new StringBuilder();
            using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))
            {
                int lineNumber = 0; // Used to 
                SqlDataReader reader = GetTableDefReader(tableName, conn);
                while (reader.Read())
                {
                    string colName = reader.GetString(0);
                    string typeName = reader.GetString(1);
                    int length = reader.GetInt32(2);
                    int isNullable = reader.GetInt32(3);
                    int status = reader.GetInt32(4);

                    AddToParameterSb(parametersSb, colName, typeName, length, 

                        isNullable, status);
                    if (status == 0x80)
                        identityField = colName;
                    else
                    {
                        lineNumber++;
                        AddInsertFieldListToSb(InsertFieldListSb, colName, lineNumber);
                        AddInsertValueListToSb(InsertValueListSb, colName, lineNumber);
                    }
                }
            }
            // Put everything together
            return
                string.Format("\nCREATE PROCEDURE [dbo].[Insert{0}]\n", tableName) +
                parametersSb.ToString() +
                string.Format("\nAS\n\nINSERT INTO [{0}] (", tableName) +
                InsertFieldListSb.ToString() +
                string.Format(")\nVALUES (") +
                InsertValueListSb.ToString() +
                string.Format(")\n\nSET @{0} = @@IDENTITY\n\nGO\n", identityField);
        }
        /// <summary>
        /// Gets a DataReader containing selected column information for all of the 
        /// columns in the indecated table from SQL Server System Tables 
        /// </summary>

        private static SqlDataReader GetTableDefReader(string tableName, 

            SqlConnection conn)
        {
            SqlDataReader reader;
            string getDataForTable =
                "SELECT c.name AS col_name, " +
                "   t.name AS type_name, " +
                "   CAST(c.length AS INT) AS length, " +
                "   c.isnullable, " +
                "   CAST(c.status AS INT) AS status " +
                "FROM " +
                "  sysObjects o " +
                "  JOIN sysColumns c ON c.id = o.id " +
                "  JOIN systypes t on c.xtype = t.xtype AND t.status = 0 " +
                "WHERE o.name = @tableName " +
                "ORDER BY c.colid ";
            using (SqlCommand cmd = new SqlCommand(getDataForTable, conn))
            {
                cmd.Parameters.AddWithValue("@tableName", tableName);
                conn.Open();
                reader = cmd.ExecuteReader();
            }
            return reader;
        }
        /// <summary>
        /// Adds the current field to the 
        /// </summary>
        private static void AddToParameterSb(StringBuilder sb, string colName, 
             string typeName, int length, int isNullable, int status)
        {
            if (sb.Length > 0)
                sb.Append(",\n");
            sb.AppendFormat("\t@{0} ", colName);
            switch (typeName.ToLower())
            {
                case "varchar":
                case "nvarchar":
                    string lenString = length.ToString();
                    if (length == -1)
                        lenString = "MAX";
                    sb.AppendFormat("{0}({1})", typeName, lenString);
                    break;
                default:
                    sb.Append(typeName);
                    break;
            }
            if (status == 0x80)
                sb.Append(" OUT");
            else if (isNullable != 0)
                sb.Append(" = NULL");
        }
        /// <summary>
        /// Adds the current field to the VALUES list for an INSERT query
        /// </summary>
        private static void AddInsertValueListToSb(StringBuilder sb, string colName, 
            int lineNumber)
        {
            AddListSeperatorToSb(sb, lineNumber);
            sb.AppendFormat("@{0}", colName);
        }
        /// <summary>
        /// Adds the current field to the Field list for an INSERT query
        /// </summary>

        private static void AddInsertFieldListToSb(StringBuilder sb, string colName, 
            int lineNumber)
        {
            AddListSeperatorToSb(sb, lineNumber);
            sb.AppendFormat("[{0}]", colName);
        }
        /// <summary>
        /// Adds the comma and or new line to the list StringBuilder
        /// </summary>
        /// <remarks>
        /// Shouldn't have a comma before the first item in the list.<br />
        /// Should only insert new ine after each ITEMS_PER_LINE items
        /// </remarks>
        private static void AddListSeperatorToSb(StringBuilder sb, int lineNumber)
        {
            if (sb.Length > 0)
                sb.Append(", ");
            if ((lineNumber % ITEMS_PER_LINE) == 0)
                sb.Append("\n\t");
        }
    }
}

I adapted this code for T4 Templates here