Binding Children Rows to a DataGrid with ADO.NET DataRelations

Over the past few days I've had a few questions regarding a situation where there is a list of parent rows (let's say Orders) loaded into a dropdown list and a list of child rows (let's say Order Details) loaded into a grid. The idea being that when the user selects the Order from the dropdown list, the Order Details grid loads that Order's Order Details records without querying the database again. The DataSet contains all of the Orders and their Order Details rows and they are related by a DataRelation. In this situation you only want the Order Details for the selected Order to be bound to the DataGrid. (In this sample I wil focus on how to do this using the DataRowView.CreateChildView method.) Here is what the web page will look like:

So let's review the situation before going any further:

A single DataSet stores all Orders for a specific customer and all of those Orders' Order Details
The DataSet contains a DataRelation that relates the parent/child DataTables (Orders/Order Details)
The DataSet is read-only, for viewing purposes only.
The DataSet needs to be cached somewhere.
The data is not volatile
When an order is selected, only its Order Details should be displayed in the DataGrid

First, to load the DataSet there is a method called RefreshDataSetFromDB. This hits the database, grabs the Orders and the Order Details, throws them into 2 DataTable objects within the same DataSet and relates them via a DataRelation.

private DataSet RefreshDataSetFromDB()
{
    string sCn = "server=(local);database=northwind;integrated security=true;";
    DataSet ds = new DataSet();

    using (SqlConnection cn = new SqlConnection(sCn))
    {
        cn.Open();
        string sqlOrders = "SELECT OrderID FROM ORDERS WHERE CustomerID = 'ALFKI'";
        using (SqlCommand cmd = new SqlCommand(sqlOrders, cn))
        {
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(ds, "Orders");
            }
        }
        StringBuilder sb = new StringBuilder("");
        sb.Append("SELECT od.OrderID, p.ProductID, p.ProductName, od.UnitPrice, od.Quantity");
        sb.Append(" FROM [Order Details] od INNER JOIN Products p ON od.ProductID = p.ProductID");
        sb.Append(" WHERE OrderID IN (");
        sb.Append(" SELECT OrderID FROM Orders WHERE CustomerID = 'ALFKI')");
        string sqlOrderDetails = sb.ToString();
        using (SqlCommand cmd = new SqlCommand(sqlOrderDetails, cn))
        {
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(ds, "Order Details");
            }
        }
        ds.Relations.Add("OrdersToOrderDetails",
            ds.Tables["Orders"].Columns["OrderID"],
            ds.Tables["Order Details"].Columns["OrderID"]);
        cn.Close();
    }
    return ds;
}

Normally I'd be using stored procedures here, but for simplicity sake of this code sample I used inline SQL. I also would not hard code the CustomerID, instead I would use a parameter passed in from somewhere (a post from another form, a control on this page, outer space, who knows). Since I built my inline SQL statement, notice the use of the StringBuilder class to build the SQL statement for the Order Details. Once I have to concatenate 3 lines or more, I use the StringBuilder. If I concatenate string objects together instead, the fact that strings are immutable means that I keep creating and throwing the value away. What's my point: StringBuilder good. Concat bad.

I used the using statement quite a bit in the RefreshDataSetFromDB method. The using statement will clean up my resources when its block ends. Quite a nice feature in my opinion. And while I am at it, I would not hard code my connection string. Instead I would store it in a custom config file or the web.config.

Alright, that's relatively basic ADO.NET. To load the page I use the following code which grabs a DataSet. On the first call to the page I also load the Order rows into the dropdown list.

private void Page_Load(object sender, System.EventArgs e)
{
    // Get the DataSet
    DataSet ds = GetDataSetFromCache();
    if (!IsPostBack)
    {
        LoadOrdersList(ds);
    }
}

When I call GetDataSetFromCache, it the DataSet is not in Cache, then I go get the data from the database using the RefreshDataSetFromDB method. Then I stick the DataSet in the Cache.

private DataSet GetDataSetFromCache()
{
    // test // Cache.Remove("MyCachedDataSet");
    // Get the DataSet from Cache
    DataSet ds = (DataSet)Cache.Get("MyCachedDataSet");
    if (ds == null)
    {
        // Get the DataSet and load it into Cache
        ds = RefreshDataSetFromDB();
         CacheDataSet(ds);
    }
    return ds;
}

I cache the DataSet using the Cache.Insert method. Here, I am creating a key called MyCachedDataSet so I can grab the DataSet back out of Cache using this key. SInce the data does not change often, I am telling the Cache to store this data for 1 hour from now. You can play with this setting or the sliding expiration argument to get your desired effect. The final parameter tells the Cache what method to call when the DataSet is removed from the Cache. Here, I tell it to call the CacheCallback handler.

private void CacheDataSet(DataSet ds)
{
    Cache.Insert("MyCachedDataSet", ds, null,
        DateTime.Now.AddHours(1), Cache.NoSlidingExpiration,
        CacheItemPriority.Normal, new CacheItemRemovedCallback(CacheCallback));
}

The CacheCallBack handler will reload the data from the database, throw the DataSet back in Cache and the load the Orders dropdown list once again.

private void CacheCallback(string str, object obj, CacheItemRemovedReason reason)
{
    DataSet ds = RefreshDataSetFromDB();
    CacheDataSet(ds);
    LoadOrdersList(ds);
}

Here is how the Orders dropdown list is loaded (using data binding).

private void LoadOrdersList(DataSet ds)
{
    // Load the Orders
    DataView dv = new DataView(ds.Tables["Orders"]);
    ddlOrders.DataSource = dv;
    ddlOrders.DataTextField = "OrderID";
    ddlOrders.DataValueField = "OrderID";
    ddlOrders.DataBind();
    // Clear the Order Details grid
    dgOrderDetails.DataSource = null;
    dgOrderDetails.DataBind();
}

When the Reload button is clicked, the DataSet is removed from cache and then reloaded from the database. Then the DataSet is stored in Cache and the Orders dropdown list is reloaded.

private void btnReload_Click(object sender, System.EventArgs e)
{
    Cache.Remove("MyCachedDataSet");
    // Get the DataSet and load it into Cache
    DataSet ds = RefreshDataSetFromDB();
    CacheDataSet(ds);
        LoadOrdersList(ds);
}

Now here is where the DataGrid is loaded. The btnGo_Click handler calls the LoadOrderDetailsGrid method, which will grab the Order Details rows that are direct children of the currently selected Order (selected in the droopdown list).

private void btnGo_Click(object sender, System.EventArgs e)
{
LoadOrderDetailsGrid();
}

The method LoadOrderDetailsGrid loads the DataGrid with the approriate related child rows. To do this, it has to take the form of something that can be bound to a DataGrid. In thise case, the code creates a DataView using its overloaded method that accepts the DataTable, the filter criteria and the DataViewRowState. The key here is that the DataView will only contain the single row for the Order that has the selected OrderID. I could have used the Select method to pull out an array of DataRow object, but that does not bind directly to the grid. So instead, I create a DataRowView. The DataRowView is associated with the Orders DataTable's selected row. Next, the DataRowView's CreateChildView method uses the name of the DataRelation (OrdersToOrderDetails) to navigate from the selected Order row and down its children Order Details rows. This result can be bound to a DataGrid.

private void LoadOrderDetailsGrid()
{
    DataSet ds = GetDataSetFromCache();
    // Load the children rows
    DataView dv = new DataView(ds.Tables["Orders"], "OrderID = " + ddlOrders.SelectedValue, "", DataViewRowState.CurrentRows);
    DataRowView drv = dv[0];
    dgOrderDetails.DataSource = drv. CreateChildView("OrdersToOrderDetails");
    dgOrderDetails.DataBind();
}