DataRelation class in ADO.NET


Introduction

The DataRelation is a class of disconnected architecture in the .NET framework. It is found in the System.Data namespace. It represents a relationship between database tables. It correlates tables on the basis of matching column.

Now we create an application using DataRelation. At first, create a database and table. I have created a database as STUDENT and two tables as AllStudent and StudentRecords. Insert some records into database.

Create Database And Table

create database STUDENT

use
student

create
table AllStudent
(

RollNo int constraint pk primary key,
F_Name varchar(30),
L_Name varchar(30),
City varchar(20)

)
 

create
table StudentRecords
(

RollNumber int constraint fk foreign key references AllStudent(RollNo),
Course varchar(20),
Duration int

)
 

Now insert records into table. I have inserted some records. I am giving screen - shot of records for better understanding of working of DataRelation.

datarelation class in .net
 

Now create a windows form application. Add a DataGrid control into Toolbox. Follow the given steps.

  • Go to Toolbox
  • Right click

    datarelation
     
  • Click at Choose items
  • Check the CheckBox of DataGrid and Click ok button

datarelation class in .net 

Now DataGrid control has added in the Toolbox. Create 2 dataGridViews, 1 DataGrid and 3 button controls. Arrange these into the given order.

datarelation class in .net

Write the following code

using System;
using
System.Collections.Generic;
using
System.ComponentModel;
using
System.Data;
using
System.Drawing;
using
System.Linq;
using
System.Text;
using
System.Windows.Forms;
using
System.Data.SqlClient;
namespace
datarelation
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlDataAdapter dad1, dad2;
       DataSet ds;
        DataRelation drel;
        string connstr = "Data Source=.;Initial Catalog=STUDENT;User ID=aa;password=aaaaaaa";
        private void btnRecodFromAllStudent_Click(object sender, EventArgs e)
        {
            dad1 = new SqlDataAdapter("select * from AllStudent", connstr);
            ds = new DataSet();
            dad1.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
        }
 
        private void btnstudentrecord_Click(object sender, EventArgs e)
        {
            dad2 = new SqlDataAdapter("select * from StudentRecords", connstr);
            ds = new DataSet();
            dad2.Fill(ds);
            dataGridView2.DataSource = ds.Tables[0];
       
        }
 
        private void btngettingrecordbydatarelation_Click(object sender, EventArgs e)
        {
            dad1 = new SqlDataAdapter("select * from AllStudent", connstr);
            ds = new DataSet();
            dad1.Fill(ds, "AllStudent");
            dad1.SelectCommand.CommandText ="select * from StudentRecords";
            dad1.Fill(ds, "StudentRecords");
            drel = new DataRelation("All", ds.Tables[0].Columns[0], ds.Tables[1].Columns[0]);
            ds.Relations.Add(drel);
            dataGrid1.DataSource = ds;
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
 
        }
    }
}
 
Run the application

Output



Click at all Buttons.

Output



Look at DataGrid records (At last). Now, click at + sign.


Click at both links to show records.

Records of StudentRecods table

datarelation class in .net

Records of AllStudent table


Now click At + sign of first row.


Click at All ( As looking in above figure). It will show records from both table. Note here, RollNo is primary key of "AllStudent" table also foreign key of "StudentRecords" table. So it shows all records from both table.

If you want to show record of a student whose RollNo is not in "StudentRecords", then it will show it as the below.