How to make relation between tables which are in same dataset?

I have one dataset in which there are 40 tables. Now I wa开发者_Go百科nt to make a relation between these tables and show important data in grid. How do i do this?

---

If you're creating a typed dataset, it's easiest to create the relations in Visual Studio's dataset designer. Just right-click on the table in the designer, select Add->Relation, and specify the relation.

If you need to specify the relation in code, you can do it like this:

dataSet.Relations.Add(dataSet.Tables["Customers"].Columns["customerId"],
    dataSet.Tables["Orders"].Columns["customerId"]);

Read all about it in MSDN here.

---

That is a large number of DataTables to have in a DataSet.

The first thing I would consider would be to reduce the number of DataTables (and eliminate the need for Relations) by populating the DataTables with queries that JOIN the database tables. For example, instead of having one DataTable for Product Category and another for Product Detail, it might be possible to combine the data for both database tables into one DataTable. Similarly, for Customer, Customer Address and Customer Phone, retrieve all of the data in one DataTable by using one query that does a JOIN on all three database tables.

Once you have minimized the number of DataTables in the DataSet, you can add Relations between DataTables if they have matching columns (even if the columns have different names). For example, there might be an Orders DataTable with a CustomerID column that matches the ID column in the Customers DataTable.

Here is the code to add a Relation to the DataSet for that situation. Assume that we have a DataSet dst containing two DataTables Customers and Orders.

DataColumn customerColumn, orderColumn;
customerColumn = dst.Tables["Customers"].Columns["ID"];
orderColumn = dst.Tables["Orders"].Columns["CustomerID"];
DataRelation dr = new DataRelation("CustomerOrders", customerColumn, orderColumn);
dst.Relations.Add(dr); 

---

ds.Relations.Add("Products_Category",
    ds.Tables("Categories").Columns("CategoryID"),
    ds.Tables("Products").Columns("CategoryID"));

---

Did you try something like:

ds.Relations.Add("Products_Category",
    ds.Tables("Categories").Columns("CategoryID"),
    ds.Tables("Products").Columns("CategoryID"));

---

private void CreateRelation() 
   {
       // Get the DataColumn objects from two DataTable objects 
       // in a DataSet. Code to get the DataSet not shown here.
       DataColumn parentColumn = 
       DataSet1.Tables["Customers"].Columns["CustID"];
       DataColumn childColumn = 
       DataSet1.Tables["Orders"].Columns["CustID"];
       // Create DataRelation.
       DataRelation relCustOrder;
       relCustOrder = new DataRelation("CustomersOrders", 
       parentColumn, childColumn);
       // Add the relation to the DataSet.
       DataSet1.Relations.Add(relCustOrder);
    }