Merging datacolumns from a datatable array with data

Question

0.00/5 (No votes)

See more:

I have a datatable array,

DataSet ds = // I gather data from a datasource

I initialize a DataTable[] from ds.Tables

DataTable[] tableArray = ds.Tables.Cast<DataTable>();

These tables all have a single row of data

Now is an alternate approach to what I have done possible, to merge the columns of these DataTables and also put data in the columns as required ?

Example Data

Let my DataSet ds have 2 tables dtA and dtB

Input

dtA

Col1 A
Col2 B

dtB

Col3 C
Col4 D

Output

dtMerged

Col1 A
Col2 B
Col3 C
Col4 D

What I have tried:

I have run a simple foreach loop on the columns and the rows to form a bigger datatable.
Also,

C#

DataTable bigTable = new DataTable();
Enumerable.Range(0, ds.Tables.Count).ToList().ForEach(i => bigTable.Merge(ds.Tables[i]));

DataTable bigTable2 = bigTable.Clone();
DataRow[] rowCollection = bigTable.Rows.Cast<DataRow>().ToArray();
DataRow newDR = bigTable2.NewRow();
Enumerable.Range(0, bigTable.Columns.Count).ToList().ForEach(j =>
{
    newDR[bigTable.Columns[j].ColumnName] = rowCollection.Select(dr => dr[j].ToString()).Last(s => !string.IsNullOrWhiteSpace(s));
});

Note: I have reasons not to execute a complex single query and fetch a single datatable.

Posted 25-May-17 20:44pm

Subhasis Chakraborty

Updated 26-May-17 0:56am

v6

Add a Solution

Comments

Maciej Los 26-May-17 4:04am

Sorry, but every table has a single row of data? Why?
Does the structure of every single table is the same?
What you mean by saying "merge"? Do you want to create only one table and "move" all data (comming from existsing tables) into it?
Show us an example data.

Subhasis Chakraborty 26-May-17 5:10am

I have added an example input and output.

Maciej Los 26-May-17 6:30am

Ok, i see the structure of tables, but there's no example data. Based on information you provided - i have no idea what relationship is between tables... You probably want to join data. As i mentioned you have to provide sample data.

CHill60 26-May-17 4:45am

"I have reasons not to execute a complex single query and fetch a single datatable." - and those reasons are? They are going to be obscure for sure.

Subhasis Chakraborty 26-May-17 5:11am

I don't have permissions to create/ add any more views in sql server.

CHill60 26-May-17 5:29am

Then don't create a view. How are you querying for all of these individual tables?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2017-05-26T00:56:00

Well, a word: "merge" in English may mean: connect, link, join, merge, mix, unite...

If i understand you well, you want to merge data, what is well known in programming language as join[^]

C#

var mergedData = from a in tableA.AsEnumerable()
        join b in tableB.AsEnumerable() on a.Field<int>("Key") equals b.Field<int>("ForeignKey")
        select new
       {
           A = a.Field<int>("Key"),
           B = a.Field<string>("FieldB"),
           C = b.Field<int>("ForeignKey"),
           D = b.Field<string>("FieldD")
       };

DataTable bigTable = new DataTable();
bigTable.Columns.AddRange(new DataColumn[]
	{
		new DataColumn("Key", typeof(int)),
		new DataColumn("FieldB", typeof(string)),
		new DataColumn("ForeignKey", typeof(int))
		new DataColumn("FieldD", typeof(string))
	});
bigTable = mergedData.CopyToDataTable();

For further details, please see:
Join Operators (LINQ to DataSet)[^]
Queries in LINQ to DataSet[^]
Creating a DataTable From a Query (LINQ to DataSet)[^]

In case you want to merge tables, which have the same structure, try this:

C#

//create "merged table" with destination structure
DataTable bigTable = new DataTable();
//all tables have to have the same structure!
bigTable.Columns.AddRange(new DataColumn[]
    {
        new DataColumn("EmpId", typeof(int)),
        new DataColumn("EmployeeName", typeof(string)),
        new DataColumn("DepartmentId", typeof(int))
    });
    //copy data into destination table ("merged table")
foreach(DataTable t in ds.Tables)
{
    foreach(DataRow r in t.Rows)
    {
        bigTable.ImportRow(r);
    }
}

Good luck!