How can I make my data grid to view data from multiple related tables?

Let’s see how we can use Data Grid control to show multiple related tables. When two tables are related, one is called the parent table while the other is called the child table. The child table contains the primary key of parent table as a foreign key. For example in our ProgrammersHeaven database, table Author is the parent table of the Article table as the Article table contains ‘AuthorId’ as foreign key which is a primary key in the Author table.

In this example, we will use data grid to show the related records from article and author table. In order to specify the relationship between the two tables we need to use the DataRelation class as:

C# Version

	dgDetails.SetDataBinding(ds, "article")
	DataRelation relation = new DataRelation("ArtAuth", 
			ds.Tables["author"].Columns["authorId"], 
			ds.Tables["article"].Columns["authorId"] 
			);

VB.Net Version

Dim relation As New DataRelation("ArtAuth", _
    ds.Tables("author").Columns("authorId"), _
    ds.Tables("article").Columns("authorId") _
    )

Here the first argument of DataRelation constructor is the name for the new relation while second and third arguments are the columns of the tables which will be used to relate the two tables. After creating this relationship we need to add it to the Relations collection of the dataset.

C# Version

	ds.Relations.Add(relation);

VB.Net Version

	ds.Relations.Add(relation)

Hence the modified code for the Load Data button is:

C# Version

private void btnLoadData_Click(object sender, System.EventArgs e)
{
	string connectionString = "server=P-III; database=programmersheaven;" +
					  "uid=sa; pwd=;";
	SqlConnection conn = new SqlConnection(connectionString);
	string cmdString = "SELECT * FROM article";
	SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdString, conn);
	DataSet ds = new DataSet();
	dataAdapter.Fill(ds, "article");
	cmdString = "SELECT * FROM author";
	dataAdapter = new SqlDataAdapter(cmdString, conn);
	dataAdapter.Fill(ds, "author");
	DataRelation relation = new DataRelation("ArtAuth", 
			ds.Tables["author"].Columns["authorId"], 
			ds.Tables["article"].Columns["authorId"] 
			);
	ds.Relations.Add(relation);
	DataView dv = new DataView(ds.Tables["author"]);
	dgDetails.DataSource = dv;
}

VB.Net Version

    Private Sub btnLoadData_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles btnLoadData.Click
        Dim connectionString As String = "server=P-III; database=programmersheaven;" + _
                                         "uid=sa; pwd=;"
        Dim conn As New SqlConnection(connectionString)
        Dim cmdString As String = "SELECT * FROM article"
        Dim dataAdapter As New SqlDataAdapter(cmdString, conn)
        Dim ds As New DataSet()
        dataAdapter.Fill(ds, "article")
        cmdString = "SELECT * FROM author"
        dataAdapter = New SqlDataAdapter(cmdString, conn)
        dataAdapter.Fill(ds, "author")
        Dim relation As New DataRelation("ArtAuth", _
                    ds.Tables("author").Columns("authorId"), _
                    ds.Tables("article").Columns("authorId") _
                    )
        ds.Relations.Add(relation)

        Dim dv As New DataView(ds.Tables("author"))
        dgDetails.DataSource = dv
    End Sub

In the above code we first filled the dataset with the two tables, defined the relationship between them and then added it to the dataset. In the last two lines, we created an instance of DataView class by supplying the parent table in its constructor call and then set the DataSource property of data grid to this data view.

When we compile and execute this application, the data grid will show the records of parent table with ‘+’ button on the left of each record



When you press the ‘+’ button on the left of the record, it will expand to show the name of relationship as a link



Now when you click the relation name, the data grid will show all the related records in the child table



Still you can see the parent record at the top of all the rows of the child table. You can go back to the parent table using the back arrow button (ç) at the title bar of the data grid.

Back

 

Advertisement

Free Magazine