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:

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

    7. 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.

    Get the 2 Tables from the DB 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.

    Page_Load
    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.

    GetDataSetFromCache
    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.

    CacheDataSet
    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.

    CacheCallback
    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).

    LoadOrdersList
    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.

    btnReload_Click
    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).

    btnGo_Click
    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.

    LoadOrderDetailsGrid
    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();
    }

    This isn't the only way to solve the requirements, of course. But it does handle the caching, ADO.NET and the hierarchical requirements.