Home > .Net > Data Relation using Data Set in .Net

Data Relation using Data Set in .Net

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.

Advertisements
Categories: .Net Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: