CODEWITHSUNDEEP
c#datatable
Rocshy
Rocshy

Reputation: 3509

Copy datatable as columns in another datatable

I have a couple of DataTable's and I need to copy them into another DataTable. For example, as you can see in the attached picture, I need to take all the data from the Source Table 1 and copy it in the first 2 columns of the Dest Table, copy Source Table 2 in the next 2 columns, and so on. How can this be easily achieved?

enter image description here

Edit: I have to read several excel files (I am storing each file in a datatable) and I won't know exactly how many source table I will have, so this has to be done dynamically somehow.

Upvotes: 6

Views: 6204

Answers (5)

Alex Filipovici
Alex Filipovici

Reputation: 32541

You may first create the destination table, add it's columns (by summing the number of columns in all the input tables) and then add it's rows by concatenating the individual arrays of values for each row in the input tables.

Of course, the rows in the resulting DataTable will contain the values as they appear in the top-down direction for each input table (aligned on top). It also means that the number of resulting rows is the number of rows in the largest input table.

First we'll initialize and populate a List<DataTable> variable, then we'll perform the join, using this variable as a method parameter:

#region table collection initialization
List<DataTable> dts = new List<DataTable>();
var dt = new DataTable();
dt.Columns.Add("Test0", typeof(string));
dt.Rows.Add(1);
dt.Rows.Add(2);
dts.Add(dt);

dt = new DataTable();
dt.Columns.Add("Test1", typeof(int));
dt.Rows.Add(2);
dts.Add(dt);

dt = new DataTable();
dt.Columns.Add("Test3", typeof(int));
dt.Columns.Add("Test4");
dt.Columns.Add("Test5", typeof(int));
dt.Rows.Add(3, "a", 1);
dt.Rows.Add(4);
dt.Rows.Add(5, "a");
dt.Rows.Add(null, "a");
dts.Add(dt);

dt = new DataTable();
dt.Columns.Add("Test6", typeof(DateTime));
dt.Columns.Add("Test7", typeof(int));
dt.Rows.Add(DateTime.Now);
dts.Add(dt);
#endregion

// sample method usage
var result = GetJoinedTable(dts);

Let's create the GetJoinedTablemethod which will return into the result variable the resulting joined table:

private DataTable GetJoinedTable(List<DataTable> dts)
{
    var dest = new DataTable();

    //will be used if you have non-unique column names
    int counter = 0;

    foreach (var column in dts
        .SelectMany<DataTable, DataColumn>(t =>
            t.Columns.Cast<DataColumn>()))
    {
        dest.Columns.Add(column.ColumnName, column.DataType);

        // if you have non-unique column names use the following instead
        //dest.Columns.Add(String.Format("column_{0}", counter++), 
        //    column.DataType);
    }

    List<object> rowItems;

    for (int i = 0; i < dts.Max(t => t.Rows.Count); i++)
    {
        rowItems = new List<object>();
        for (int j = 0; j < dts.Count; j++)
        {
            if (dts[j].Rows.Count > i)
            {
                var r = dts[j].Rows[i].ItemArray
                    .Select((v, index) =>
                        (v == null || v == System.DBNull.Value)
                            ? GetDefault(dts[j].Columns[index].DataType) : v);
                rowItems.AddRange(r);
            }
            else
            {
                for (int c = 0; c < dts[j].Columns.Count; c++)
                {
                    rowItems.Add(GetDefault(dts[j].Columns[c].DataType));
                }
            }
        }
        dest.Rows.Add(rowItems.ToArray());
    }

    return dest;
}

You'll also need to add the following method, which returns the appropriate default column value, based on the DataType property of the column:

object GetDefault(Type t)
{
    if (t.IsValueType)
    {
        return Activator.CreateInstance(t);
    }
    else
    {
        return null;
    }
}

Upvotes: 0

Vincent Guo
Vincent Guo

Reputation: 36

You should to find the relationship between that source table. For example, they have the same id, you can copy them like this

insert into destTable( 
select s1.col1, s1.col2, s2.col3, s2.col4, s3.col5, s3.col6, s4.col7, s4.col8 
from sourcetable1 s1, sourcetable2 s2, sourcetable3 s3, sourcetable4 s4 
where s1.id = s2.id and s2.id = s3.id and s3.id = s4.id)

Upvotes: 1

Amir
Amir

Reputation: 352

You can use Merge method which provided by framework , for usage and extra info see Microsoft Datatable Merge

Upvotes: 1

Freelancer
Freelancer

Reputation: 9064

You can fire queries like:

select col1,col2 into sourcetable1 from destTable
union
select col3,col4 into sourcetable2 from destTable
union
select col5,col6 into sourcetable3 from destTable
union
select col7,col8 into sourcetable4 from destTable

Or, can follow technique mentioned over here.

In case of data rows, it can be:

foreach (DataRow row in DestTable)
{
    SourceTable1.ImportRow(row);
}

Upvotes: 0

Alex
Alex

Reputation: 8937

Assuming that your source tables have the same structure you can use Table.Copy() to create your dest table and then copy data in loop:

List<DataTable> sourceTables = getYourSourceTablesMethod();
if (sourceTables.Length>0)
{
    DataTable destTable = sourceTables[0].Copy();  

    for (int i = 1; i < sourceTables; i++) 
    {
       foreach (DataRow drow in sourceTables[i].Rows) 
       destTable.Rows.Add(drow.ItemArray);
    }
}

Upvotes: 6

Related Questions