Creating Master-Detail realationship

There are many times when your application needs to work with related tables. Although a dataset contains tables and columns as in a database, it does not inherently include a database’s ability to relate tables. However, you can create DataRelation objects that establish a relationship between a parent (master) and a child (detail) table based on a common key.

Relationships are created between matching columns in the parent and child tables. That is, the DataType value for both columns must be identical.

A DataRelation is used to relate two DataTable objects to each other through DataColumn objects. For example, in a Customer/Orders relationship, the Customers table is the parent and the Orders table is the child of the relationship. This is similar to a primary key/foreign key relationship.

It is important to understand the difference between a true join and the function of a DataRelation object. In a true join, records are taken from parent and child tables and put into a single, flat Recordset. When you use a DataRelation object, no new Recordset is created. Instead, the relation tracks the relationship between tables and keeps parent and child records in synch.

The sample code below shows a simple example of creating a master –detail relationship between two tables within a Dataset .We are making use of the sqlclient classes.

1]Drag and drop a datagrid control on a windows form.

2]Create appropiate data objects to access the data from data source.

• Sqlconnection
• SqlDataAdapter
• Dataset

3]Create a DataRelation object which expects the columns of the tables on which you want to set the relationship.

private void Form1_Load(object sender, System.EventArgs e)

{

//Creating a connection to the data source.

SqlConnection myconn=new SqlConnection(“data source=kedar;initial catalog=northwind;user id=sa;password=;”);

//Filling the dataset with appropiate tables.

SqlDataAdapter myda1=new SqlDataAdapter(“select * from orders”,myconn);

SqlDataAdapter myda2=new SqlDataAdapter(“select * from [order details] “,myconn);

DataSet myds=new DataSet();

myda1.Fill(myds,”orders”); myda1.Fill(myds,”orderdetails”);

//creating datacolumns on which you want to establish a relationship.

DataColumn parentcolumn =myds.Tables[“orders”].Columns[“orderid”] ;

DataColumn childcolumn =myds.Tables[“orderdetails”].Columns[“orderid”] ;

//Creating a datarelation object adding the columns on which relationship is set.

DataRelation mydr=new DataRelation(“childrecord”,parentcolumn,childcolumn);

//adding the relationship on the dataset.

myds.Relations.Add(mydr);

dataGrid1.DataSource =myds;

}