Brendan Tompkins [MVP]

Recently, I was tasked with creating a reporting application that did the following:

1. Allow the user to choose Columns in one DataTable in a DataSet to group by.
2. Render a grouped report from the flat table data.

So, for example, if the DataTable looked like this:

Department EmployeeID Product Count
Purchasing 3322       Apples  1
Purchasing 3322       Oranges 1
Purchasing 3311       Oranges 2
HR         1222       Apples  1
HR         1111       Apples  3

I had to generate a rollup report that looked like this:

Department: Purchasing
  Employee: 3322
    Product: Apples  Count: 1
    Product: Oranges Count: 2
                     Total  3
  Employee: 3311
    Product: Oranges Count: 2
                     Total: 2
          Purchasing Total: 5

Department: HR
  Employee: 1222
    Product: Apples  Count: 1
                     Total: 1
  Employee: 1111
    Product: Apples  Count: 3
                     Total: 3
                  HR Total: 4
               Grand Total: 9

You can see that the solution was going to involve recursion of some sort.  The trouble was, recursion on a flat DataTable is a nightmare!  If you've ever tried it, you know what I mean. Forget using foreach, and bone up on arrays if you're going down this route.

Well, I wrote the code to do this recursion, but I wasn't happy with what I had come up with.  It was ugly code, and hard to follow.  I was complaining to a friend of mine about the complexity, and drawing on a white board.  I drew a picture of an ADO DataSet with DataTables related with DataRelations, and said “If I only had this, this would be easy”...  Then it hit me.  I needed to generate a multi-table DataSet from a flat, one-table DataSet.  I could then use the GetChildRows() method of each data row to find out if I needed to recurse. I could also use a foreach to iterate through the DataRows. 

What I came up with is the following method, that I thought I'd share.  What it does is take a flat DataSet with one DataTable and a string array of ColumnNames that are to be used to generate a DataSet with multiple, related dataTables.  It returns the root table name, which you'll need to start your recursion:

public static string GetNestedDataSet(DataSet flatDs, ref DataSet nestedDs, string [] columnNames)
{
    string rootTableName = null;
    nestedDs = flatDs.Copy();
    DataTable dataTable = nestedDs.Tables[0];

    // Store the previous iteration's previous columns
    DataColumn [] prevUniqueColumns = null;

    foreach(string columnName in columnNames) 
    {
        // Add the table
        DataTable tbl = nestedDs.Tables.Add(columnName + "Table");
        if(rootTableName == null) rootTableName = tbl.TableName;

        // Store the current iteration's columns
        ArrayList currentColumns = new ArrayList();
        if(prevUniqueColumns != null)
        {
            // Add all the previous columns
            foreach(DataColumn col in prevUniqueColumns)
            {
                currentColumns.Add(new DataColumn(col.ColumnName));
            }

            // Add the current Columns
            DataColumn [] foriegnKeyCols = (DataColumn []) currentColumns.ToArray(typeof(DataColumn));
            tbl.Columns.AddRange(foriegnKeyCols);

            // Add the parent relations
            tbl.ParentRelations.Add(prevUniqueColumns, foriegnKeyCols);
        }

        // Now add the new column
        currentColumns.Add(tbl.Columns.Add(columnName)); 

        // Get the key columns for the current table
        DataColumn [] primaryKeyCols = (DataColumn []) currentColumns.ToArray(typeof(DataColumn));

        // Make everything unique
        tbl.Constraints.Add(tbl.TableName + "_unique", primaryKeyCols, false);

        // Store columns for next time...
        prevUniqueColumns = primaryKeyCols;

        // Now add the data...
        foreach(DataRow row in nestedDs.Tables[0].Rows)
        {
                try
                {
                        ArrayList al = new ArrayList();
                        foreach(DataColumn c in primaryKeyCols) al.Add(row[c.ColumnName]);
                        tbl.Rows.Add((object[]) al.ToArray()); 
                }
                catch(System.Data.ConstraintException){}
        }
    }
    
    // Finally, add the table relationship to the base table
    if(prevUniqueColumns != null)
    {
        // Add the current Columns
        DataColumn [] foriegnKeyCols = new DataColumn [prevUniqueColumns.Length];
        for(int i = 0; i<prevUniqueColumns.Length; i++)
        {
                foriegnKeyCols[i] = dataTable.Columns[prevUniqueColumns[i].ColumnName];
        }
        dataTable.ParentRelations.Add("BaseRelation", prevUniqueColumns, foriegnKeyCols);
    }

    // This happens when we have no rollup columns
    if(rootTableName == null) rootTableName = dataTable.TableName;
    return rootTableName;
}

Now, to use this, do the following:

DataSet nestedDataSet = null;
string rootTableName = GetNestedDataSet(MyFlatDataSet, ref nestedDataSet, string [] {“Department“, “EmployeeID“, “Product“});

Here's an example (warning: Air Code) of the recursive routine to do the rendering:

private int RenderGroup(DataRow parentRow)
{
      // Render out something here, like parentRow[0]

     // Now get the children rows, if any and recurse
      DataRelation childRelation = (parentRow.Table.ChildRelations.Count > 0) ? parentRow.Table.ChildRelations[0] : null;
      if(childRelation != null)
         foreach(DataRow row in parentRow.GetChildRows(childRelation))
            RenderGroup(row, showDetail, container);
}

That's it!  I put this method in my Utils class for ease of use.   I hope that posting this will save someone else the agony of trying to write recursive routines against a flat DataTable.

-Brendan