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.


 

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

• Go to Toolbox
• Right click
  
  
   
• Click at Choose items
• Check the CheckBox of DataGrid and Click ok button
  

 

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



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

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.