June 10, 2003

Adding Relationships to DataTables

Adding Relationships to DataTables We know that a DataSet is an in-memory replica of database. It can contain multiple DataTables just like a database. In addition you can also set relationship between the DataTables and navigate through the relationship. This article shows you how. By: Bipin Joshi Email : webmaster@dotnetbips.com 
1:     using System;

2:     using System.Data;

3:     using System.Data.SqlClient;

4:     

5:     namespace ADONETSamples

6:     {

7:     class Sample

8:     {

9:     static void Main(string[] args)

10:    {

11:    //declare connection,datadapter and dataset

12:    SqlConnection cnn;

13:    SqlDataAdapter da;

14:    DataSet ds;

15:    

16:    //create connection

17:    cnn=new SqlConnection("connection_string_here");

18:    da=new SqlDataAdapter();

19:    ds=new DataSet();

20:    

21:    //set selectcommand property

22:    da.SelectCommand=

23:    new SqlCommand("select * from customers",cnn);

24:    

25:    //populate the dataset

26:    da.Fill(ds,"customers");

27:    da.SelectCommand.CommandText=

28:    "select * from orders";

29:    da.Fill(ds,"orders");

30:    

31:    //declare relationship

32:    DataRelation rel=

33:    new DataRelation("custorders",

34:    ds.Tables[0].Columns["customerid"],

35:    ds.Tables[1].Columns["customerid"]);

36:    ds.Relations.Add(rel);

37:    

38:    //display values of customers datatable

39:    foreach(DataRow r in ds.Tables[0].Rows)

40:    {

41:       Console.WriteLine(r["customerid"]);

42:       DataRow[] childrows=r.GetChildRows("custorders");

43:       foreach(DataRow cr in childrows)

44:       {

45:          Console.WriteLine("\t" + cr["orderid"]);

46:       }

47:    }

48:    }

49:    }

50:    }

51:    

52:


Posted by sachauncey at June 10, 2003 01:51 AM