Adding Relationships to DataTables
Introduction
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.Example
using System; using System.Data; using System.Data.SqlClient; namespace ADONETSamples { class Sample { static void Main(string[] args) { //declare connection,datadapter and dataset SqlConnection cnn; SqlDataAdapter da; DataSet ds; //create connection cnn=new SqlConnection("connection_string_here"); da=new SqlDataAdapter(); ds=new DataSet(); //set selectcommand property da.SelectCommand= new SqlCommand("select * from customers",cnn); //populate the dataset da.Fill(ds,"customers"); da.SelectCommand.CommandText= "select * from orders"; da.Fill(ds,"orders"); //declare relationship DataRelation rel= new DataRelation("custorders", ds.Tables[0].Columns["customerid"], ds.Tables[1].Columns["customerid"]); ds.Relations.Add(rel); //display values of customers datatable foreach(DataRow r in ds.Tables[0].Rows) { Console.WriteLine(r["customerid"]); DataRow[] childrows=r.GetChildRows("custorders"); foreach(DataRow cr in childrows) { Console.WriteLine("\t" + cr["orderid"]); } } } } }Let's examine the code:
- We create Connection, DataAdapter and DataSet objects.
- We then set SelectCommand property of DataAdapter to a new SqlCommand instance
- This command uses the connection we created above. Its CommandText property is set to "select * from customers"
- We then fill the DataSet with first DataTable called customers
- We then change the CommandText of this command to "select * from orders"
- We fill the DataSet with second DataTable called orders
- In order to set relationship between two tables we need to have a common key field between them. In our case customerid is such a field.
- We then create an instance of DataRelation class that represents a relation between tables
- We pass relation name, parent DataColumn and child DataColumn via its constructor.
- We then add this DataRelation instance to the Relations collection of DataSet object.
- In order to iterate through the parent and child rows we use for eachloop as shown.
- There are two for eachloops. The outer loop scans through the parent DataRows and the inner loop iterates through the child DataRows.
- The GetChildRows() method of DataRow class accepts the relation name for which the child rows are to be retrieved. It returns array of detail rows for the given parent row.
Summary
DataSet allows you to set relations between its DataTables. This is very much similar to setting relationships between database tables. The DataRelation class represents a single database relationship. You can add one or more such relationships to the Relations collection of DataSet. Once the relation is set you can use GetChildRows() method of individual DataRow of parent table to get its detail rows.About the author
Name : |
Bipin Joshi |
Email : |
|
Profile : |
Bipin Joshi is the webmaster of DotNetBips.com. He is the founder of BinaryIntellect Consulting (www.binaryintellect.com) - a company providing training and consulting services on .NET framework. He conducts intensive training programs in Thane/Mumbai for developers. He is also a Microsoft MVP (ASP.NET) and a member of ASPInsiders. |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步