ADO/ADO.NET - Join 2 datatables in a Dataset

I have 2 differet datatables in 2 Datasets. How would I merge them togther. 
First table has Order Details and Other table has customer Info. I want to merge them together on OrderID and create a single list. How would I do that? 
Thanks in Advance

Hi Yogesh,
In ADO.NET, as far as I know, there's no way to perform joins.  The best way to do it is from the database side.  But if you cannot do it from there, you have to loop through your Order Details table and for each one, filter out from your Customer Info table given the Order ID and get the detail from the resulting array of data rows.

DataSet.Merge.
Here are the details:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatasetclassmergetopic3.asp
 to Add new columns to the OrderDetails table (that are present int he Customer Table) use MissingSchemaAction.Add
There are some limitations and you may want to read through DataSet,Merge before using it..but it is one cool way of achieving waht you want.

:)

to join the two tables withing SAME dataset.
After you fill both tables into the same dataset, ds, you can do the following:
ds.Relations.Add("MyDataRelation", _
   ds.Tables("OrderDetails").Columns("OrderID"), _
   ds.Tables("CustomerInfo").Columns("OrderID"))
Please note that you should fill the 2 datatables into 1 single dataset to do this.

Hazel,
THanks for info. So when I use relation oblect does it takes care of 1 to many issue also? 
thanks

you need to put the table and key on the "one" side as the second parameter (i.e. after the Relation Name), and put the table and key on the "many" side as the third parameter.

Hazel,
Thanks  a lot for suggestions. I have created a relation as you suggesed. Now How would I display results from both parent and child dataTables in single datagrid?

The outer Datagrid will be binded to the "1" table in the 1-many relation.
Then you need to call the CreateChildView method of the selected DataItem in the ItemDataBound event of your outer datagrid:
   sub OuterGrid_ItemDataBound(sender as object, e as DataGridItemEventArgs)
        dim oDGrid as DataGrid
        dim oDrv as DataRowView
        if (e.Item.ItemType = ListItemType.Item) or (e.Item.ItemType = ListItemType.AlternatingItem) then
            oDGrid = CType(e.Item.FindControl("InsideGrid"), DataGrid)
            oDrv = e.Item.DataItem
            'Bind item repeater
            oDGrid.DataSource = oDrv.CreateChildView("PKID")
            oDGrid.DataBind
        end if
    end sub
This will load data from both the parent and child table when the page is loaded.
Instead of using the nested datagrid, you can also hide the datagrid that is used for displaying the "child" data, and only call its databind() method when users click on a button (i.e. move the code to the ItemCommand event) on a datagrid row that contains the "parent" data.

Hazel Many thanks!  Used follwing loop to navigate thru master & detail records.  
DataTableCollection tablesCol ; 
DataTable t;
DataRow R;
DataRow[] Details;
tablesCol = yaDS.Tables;
t = tablesCol[1];
int Cntr = 0, Cntr2 = 0;
string tst=""; 
foreach ( DataRow r in t.Rows)
{
	Details = yogiDS.Tables["MasterList"].Rows[Cntr].GetChildRows("yaRelat");
	foreach (DataRow DetailRow in Details)
	{
		// display or process an order
		Response.Write("Master " + r[0].ToString() + r[1].ToString() );
		Response.Write("Detail " + DetailRow[3].ToString() + DetailRow[5].ToString() + DetailRow[7].ToString() + DetailRow[9].ToString() + "<br>" );
	}
	Cntr += 1;
}