My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.

The easiest way to do this is with nested Repeater controls; one for the outer group (Customers, this case), and within that Repeater's ItemTemplate we'd have another Repeater control for the details (Products).

To use nested repeaters, you would return two separate result sets from SQL:

A "Customers" result set, listing one row per Customer to display on the report

A "Products" result set, listing one row per Product to display.

You would put both of those results into a single DataSet as DataTables, and then create a relation between the two (on the CustomerID column). Now, in our DataSet, we have a parent-child relation and each row in the parent Customers DataTable will have related child rows in the Products DataTable.

Then, in ASP.NET, we create nested repeaters: the outer repeater is bound to the Customers DataTable, and the inner repeater gets bound for each Customer row to the child rows in the Products DataTable. There are lots of good examples detailing this technique here and here, so I won't get into specifics on that in this space.

However -- what if you already have a stored procedure that returns this data in a single result set? The nested Repeater controls won't do you much good, since we need two separate (but related) DataTables. Or, what if it is inefficient to return these results separately in SQL? For example, suppose we want to output only the products for customers that have a total sales of greater than $200 for a specified time period. We'd need to process the sales transactions table twice in SQL to generate the list of Customers separate from the list of Products. Thus, our SQL becomes twice as inefficient as it needs to be to use this technique.

To accommodate those possibilities, I recently wrote a simple function in C# that takes a single DataTable and creates a related parent DataTable in the same DataSet based on specified parameters. This is useful if you only have a single result set from SQL (or any other data source) and you'd like to break that data up into two separate parent-child DataTables in order to use nested repeaters or any other type of parent-child processing on the data.

Then we can simply bind the outer Repeater to the Customers DataTable, and then bind the inner Repeater to a call to each DataRow's GetChildRows() method to get the related products. All from a single SQL result set.

Also, notice that the function removes the CustomerName column from the Products DataTable, but leaves the CustomerID column for the relation.

Here's the function:

/// <summary>/// Creates a parent DataTable within the DataSet using distinct rows from/// an existing "source" DataTable, based on the column(s) specified./// The source table then becomes the "child" table of the newly created /// parent. A DataRelation is also created between the parent table and the /// child table, using the column(s) specified./// </summary>/// <param name="sourceTable">
/// The source DataTable, which must be within a DataSet.
/// This source table will become the "child" table.
/// </param>/// <param name="parentTableName">
/// This name will be assigned to the parent table once it is created.
/// </param>/// <param name="relationColumns">
/// Specify the columns used to relate the parent table to the child table.
/// </param>/// <param name="additionalColumns">
/// Any additional column(s) in the source table that will be extracted to the
/// parent table. These columns will be removed from the source table.
/// </param>/// <param name="relationName">
/// The name of the relation that will be created between the parent and
/// the child table.
/// </param>/// public static void AddParentTable(DataTable sourceTable, string parentTableName,
string[] relationColumns, string[] additionalColumns, string relationName){ DataSet dataSet = sourceTable.DataSet;

if (dataSet == null) { throw new Exception("The source DataTable must be contained in a DataSet"); }

// generate the set of columns to use to create the Parent table: string[] cols = new string[relationColumns.Length+additionalColumns.Length]; relationColumns.CopyTo(cols,0); additionalColumns.CopyTo(cols,relationColumns.Length);

// remove the additional columns from the child table that were
// copied to the parent table: foreach (string s in additionalColumns) sourceTable.Columns.Remove(s);

// create the relation between the new parent table and the child table: DataColumn[] parentColumns = new DataColumn[relationColumns.Length]; DataColumn[] childColumns = new DataColumn[relationColumns.Length];

The parameters are all explained in the XML documentation. Feel free to make modifications or overloads of this function. Some good overloads might be one that accepts a single relation column name, or that accepts a parameter that determines whether or not columns are removed from the child table.

After that is executed, the DataSet now contains an additional "Customers" table, and a relation called "CustomerProducts" to the "Products" table. The relation is on "CustomerID", and the "Customers" table also includes the "CustomerName" column.

we can simply assign rpt1's DataSource to our DataSet, set its DataMember as "Customers", call DataBind(), and we are good to go:

rpt1.DataSource = ds;rpt1.DataMember = "Customers";rpt1.DataBind();

A couple of more notes:

The outer Repeater's DataItems are DataRowView objects, but the inner Repeater's DataItems are DataRows. This is because we called the DataRowView's DataRow's GetChildRows() method, and not the DataRowView's CreateChildView() method. I have found that this provides much better performance. For small results, you may wish to call CreateChildView().

Instead of databinding in the HTML markup, you may wish to bind the inner Repeaters during the outer Repeater's DataBinding() event. This keeps your HTML markup a little cleaner and shorter.

This function only works in .NET 2.0 or above, since it uses ToTable() method of the DataRowView class.