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.
