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;

}

Steps to connect with Database…

Step 1. Create a connection string.
SqlConnection SqlCon=new SqlConnecyion(“Connectionstring”);

Step 2. Open the connection.

SqlCon.Open();

Step 3. Pass a query through command object

SqlCommand SqlCom;

SqlCom=SqlCon.CreateCommand;

SqlCom.CommandText=”sqlQuries” ;

Step 4. Read the using datareader.

SqlDatareader dr= SqlCom.ExcuteReader();
Step 5. Display the data using any display control.

Like DataGrid or GridView or DropDownBox Datalist etc

Step 6. Close the connection
SqlCon.Close()