Kriya and Meditation for Software / IT Professionals. Conducted by Bipin Joshi in Thane. Read more...
Learn ASP.NET MVC, ASP.NET Core and ASP.NET Design Patterns. Courses conducted by Bipin Joshi on weekends. Click here for more details.

<%@ Page %>

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.

Bipin Joshi is a software consultant, an author and a yoga mentor having 22+ years of experience in software development. He also conducts online courses in ASP.NET MVC / Core and Design Patterns. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Meditation and Mindfulness to interested individuals. To know more about him click here.

Get connected : Twitter  Facebook  Google+  LinkedIn

Posted On : 08 June 2003


Tags : ADO.NET Data Access SQL Server