DataSet:
The DataSet class represents a memory-resident database. It is a container for the DataTable and DataRelation classes.DataRelationIt is a Class to establish relation with more than one data in the dataset.
DataRelation class constructor takes three arguments to establish relationship with other table.
1. Strdatarelationname(String type).
2. Parent table columan name.
3. Child table columan name.
To get all data againsed parent table column name you have to use GetChildRows method, which takes one
arguments( Strdatarelationname). and after getting all fields value of child table you can go further with your own code whatever you want.
Here below code gives you example to relate two table using particular field of table.
DataSet ds = null;
protected void Page_Load(object sender, EventArgs e)
{
ds = new DataSet();
ds.Tables.Add(MakeAuthorData());
ds.Tables.Add(MakeBookData());
DataRelation relation = new DataRelation("Book2Author", ds.Tables["Authors"].Columns["AuthorId"], ds.Tables["Books"].Columns["AuthorId"]);
ds.Relations.Add(relation);
grdAuthors.DataSource = ds.Tables["Authors"].DefaultView;
grdAuthors.DataBind();
grdBooks.DataSource = ds.Tables["Books"].DefaultView;
grdBooks.DataBind();
foreach (DataRow artistRow in ds.Tables["Authors"].Rows)
{
string fname = (string)artistRow["FirstName"];
string lname = (string)artistRow["LastName"];
labReport.Text += "" + fname + " " + lname;
foreach (DataRow bookRow in artistRow.GetChildRows(relation)) //Get child table fields
{
string title = (string)bookRow["Title"];
labReport.Text += "" + title;
}
}
}
private DataTable MakeAuthorData()
{
DataTable table = new DataTable();
table.TableName = "Authors";
DataColumn idCol = new DataColumn();
idCol.ColumnName = "AuthorId";
idCol.DataType = typeof(Int32);
idCol.AllowDBNull = false; idCol.Unique = true;
DataColumn firstNameCol = new DataColumn("FirstName", typeof(string));
DataColumn lastNameCol = new DataColumn("LastName", typeof(string));
table.Columns.Add(idCol);
table.Columns.Add(firstNameCol);
table.Columns.Add(lastNameCol);
DataRow r1 = table.NewRow();
r1[0] = 10;
r1[1] = "A";
r1[2] = "a";
table.Rows.Add(r1);
DataRow r2 = table.NewRow();
r2[0] = 20;
r2["FirstName"] = "B";
r2["LastName"] = "b";
table.Rows.Add(r2);
DataRow r3 = table.NewRow();
r3[0] = 30; r3["FirstName"] = "C";
r3["LastName"] = "c";
table.Rows.Add(r3);
DataRow r4 = table.NewRow();
r4[0] = 40; r4["FirstName"] = "D";
r4["LastName"] = "d";
table.Rows.Add(r4);
return table;
}
private DataTable MakeBookData()
{
DataTable table = new DataTable();
table.TableName = "Books";
DataColumn idCol = new DataColumn();
idCol.ColumnName = "Id";
idCol.DataType = typeof(Int32);
idCol.AllowDBNull = false;
idCol.Unique = true;
idCol.AutoIncrement = true;
DataColumn authorCol = new DataColumn("AuthorId", typeof(Int32));
DataColumn nameCol = new DataColumn("Title", typeof(string));
DataColumn priceCol = new DataColumn("Price", typeof(double));
table.Columns.Add(idCol);
table.Columns.Add(authorCol);
table.Columns.Add(nameCol);
table.Columns.Add(priceCol);
DataRow r1 = table.NewRow();
r1[1] = 30;
r1[2] = "Java";
r1[3] = 49.99;
table.Rows.Add(r1);
DataRow r2 = table.NewRow();
r2[1] = 10;
r2[2] = "C#";
r2[3] = 19.99;
table.Rows.Add(r2);
DataRow r3 = table.NewRow();
r3[1] = 40; r3[2] = "Javascript";
r3[3] = 24.99;
table.Rows.Add(r3);
DataRow r4 = table.NewRow();
r4[1] = 40; r4[2] = "Oracle";
r4[3] = 24.99;
table.Rows.Add(r4);
return table;
}
}
Happy Coding.