asked on

Adding dataTables to DataSet

I have two DataTables InvoiceDetail and InvoiceHeader. I want to combine InvoiceHeader to InvoiceDetail. Both of them have different number of columns and rows.

On combining end result will go by InvoiceDetail.

I am able to achieve this merger in SQL by below syntax - CrossJoin

SELECT  * FROM
  InvoiceDetail t1
CROSS JOIN
  InvoiceHeader t2

Open in new window

How to achieve in C# by writing CrossJoin for DataTables ? SQL Syntax returns exact expected results. Only drawback is that , i have to export the data to sql and then perform this feature.

Giuseppe Pizzuto

Create a DataTable for each Table and add them ot the dataset,
then create a DataRelation joining the 2 tables and add to the dataset

chokka

ASKER

To give an Example : All are string datatype.

datatable1

col1 col2 col3
1 SAM ABC
2 JAM DEF

datatable2

deptcolumn
Sales

DataTable datatable1 = new DataTable();
datatable1 .Columns.Add("col1", typeof(string));
datatable1 .Columns.Add("col2", typeof(string));
datatable1 .Columns.Add("col3", typeof(string));

DataTable datatable2 = new DataTable();
datatable1 .Columns.Add("deptcolumn", typeof(string));

Can you please make a DataRelation for this example.

From SQL Syntax - Crossjoin , We will get an output as

col1 col2 col3 deptcolumn
1 SAM ABC sales
2 JAM DEF sales

Giuseppe Pizzuto

have a look here:

http://msdn.microsoft.com/en-us/library/d6s958d6.aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1

chokka

ASKER

My understanding about DataRelation is both datatables should share a common column name.

From the link

customerOrders.Tables["Customers"].Columns["CustomerID"],
customerOrders.Tables["Orders"].Columns["CustomerID"]);

As in the previous example, CustomerID relates the Customers table to the Orders table.

In my scenario there is no relation between two datatables.

chokka

ASKER

This is just an arraylist or collection of data which i want to insert into another collection which already has data.

ASKER CERTIFIED SOLUTION

Giuseppe Pizzuto

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

chokka

ASKER

Great gpizzuto, thanks. Let me test this syntax

chokka

ASKER

I am able to put both datatables inside Linq Collection Object - query

How to load a dataset or load a database from this object ? I need to check the results from "Watch window " before i load the database.

 var query =
                    from dt1 in dtInvoiceHeader.AsEnumerable()
                    from dt2 in dtInvoiceDetail.AsEnumerable()
                    select new
                    {
                       dt1,dt2
                    };

Open in new window

chokka

ASKER

Thanks