ADO.NET Series - Adding Relationships to DataTables

ADO.NET Series - Adding Relationships to DataTables

Introduction

We know that a DataSet is an in-memory replica of database. It can contain multiple DataTables just like a database. In addition you can also set relationship between the DataTables and navigate through the relationship. This article shows you how.

Example

using System;
using System.Data;
using System.Data.SqlClient;

namespace ADONETSamples
{
class Sample
{
static void Main(string[] args)
{
//declare connection,datadapter and dataset
SqlConnection cnn;
SqlDataAdapter da;
DataSet ds;

//create connection
cnn=new SqlConnection("connection_string_here");
da=new SqlDataAdapter();
ds=new DataSet();

//set selectcommand property
da.SelectCommand=
new SqlCommand("select * from customers",cnn);

//populate the dataset
da.Fill(ds,"customers");
da.SelectCommand.CommandText=
"select * from orders";
da.Fill(ds,"orders");

//declare relationship
DataRelation rel=
new DataRelation("custorders",
ds.Tables[0].Columns["customerid"],
ds.Tables[1].Columns["customerid"]);
ds.Relations.Add(rel);

//display values of customers datatable
foreach(DataRow r in ds.Tables[0].Rows)
{
   Console.WriteLine(r["customerid"]);
   DataRow[] childrows=r.GetChildRows("custorders");
   foreach(DataRow cr in childrows)
   {
      Console.WriteLine("\t" + cr["orderid"]);
   }
}
}
}
}

Let's examine the code:

• We create Connection, DataAdapter and DataSet objects.
• We then set SelectCommand property of DataAdapter to a new SqlCommand instance
• This command uses the connection we created above. Its CommandText property is set to "select * from customers"
• We then fill the DataSet with first DataTable called customers
• We then change the CommandText of this command to "select * from orders"
• We fill the DataSet with second DataTable called orders
• In order to set relationship between two tables we need to have a common key field between them. In our case customerid is such a field.
• We then create an instance of DataRelation class that represents a relation between tables
• We pass relation name, parent DataColumn and child DataColumn via its constructor.
• We then add this DataRelation instance to the Relations collection of DataSet object.
• In order to iterate through the parent and child rows we use for eachloop as shown.
• There are two for eachloops. The outer loop scans through the parent DataRows and the inner loop iterates through the child DataRows.
• The GetChildRows() method of DataRow class accepts the relation name for which the child rows are to be retrieved. It returns array of detail rows for the given parent row.

Summary

DataSet allows you to set relations between its DataTables. This is very much similar to setting relationships between database tables. The DataRelation class represents a single database relationship. You can add one or more such relationships to the Relations collection of DataSet. Once the relation is set you can use GetChildRows() method of individual DataRow of parent table to get its detail rows.