When dealing with nested tables, we may require to display the child table rows as columns in the data grid. For example, from the "pubs" database we may want to display the Book Titles and Author Names side by side in a grid as shown below:

Since Book Titles and Author Names are stored in different tables, we need a nested query to retrieve the records. Though the DataGrid control is great, there is no direct way of binding nested queries to it. To display the child table rows as columns, we need to do a workaround using the GetChildRows method of DataRow object.

The following example shows you how to use the GetChildRows method and display child table rows as a column in DataGrid.

First we need to establish a connection to the database using the SqlConnection object.

SqlConnection CN = new SqlConnection("Server=MyServer;database=MyDataBase;uid=MyUser;pwd=MyPwd");
CN.Open();

Next, create an instance of DataAdapter object with a nested SQL query.

Since we need to display the Title, Price and Authors columns from the nested query we create a new DataTable object with these three columns.

DataTable objTable= new DataTable("Details");
objTable.Columns.Add("Title",typeof(string));
objTable.Columns.Add("Price",typeof(decimal));
objTable.Columns.Add("Authors",typeof(string));

Next step is to populate the DataSet with data using the Fill method of DataAdapter.

DataSet DS = new DataSet();
DA.Fill(DS,"Titles");

Then, create a Relation between the Title_ID columns of the Title and titleauthor tables as shown below:

DS.Relations.Add("Title_Authors",DS.Tables["Titles"].Columns["Title_ID"],DS.Tables["TitleAuthor"].Columns["Title_ID"]);

Now loop through the rows in the parent(Titles) table and fill values for the Title and Price column of the DataTable object we created.

foreach(DataRow TitleRow in DS.Tables["Titles"].Rows)
{
DR = objTable.NewRow();
DR["Title"] = TitleRow["Title"];
DR["Price"] = TitleRow["Price"];
}

Within the loop, use GetChildRows method of the DataRow to retrieve the Author's name and append it to the Author's column of the DataTable object. The GetChildRows method takes in the name of the Relation as argument.

foreach(DataRow TitleRow in DS.Tables["Titles"].Rows)
{
DR = objTable.NewRow();
DR["Title"] = TitleRow["Title"];
DR["Price"] = TitleRow["Price"];
Authors="";
foreach(DataRow AuthorRow in TitleRow.GetChildRows("Title_Authors"))
{
Authors += AuthorRow["Au_lname"].ToString() + " " + AuthorRow["Au_fname"].ToString() + ", " ;
}
DR["Authors"] = Authors.TrimEnd(',',' ');
objTable.Rows.Add(DR);
}

The final step is to bind the DataTable to the DataGrid.

DG1.DataSource=objTable;
DG1.DataBind();