How to add column to datatable from another datatable

Question

I have two data tables dt1 and dt2, both share a common column. How to map the common column and add a new column with data to data table dt1.

DataTable dt1=new DataTable();
DataTable dt2=new DataTable();

sqlDataAdapter da1=new sqlDataAdapter("select col1,col2,col3,col4 from table",connection);
dataset ds1=new dataset();
da1.fill(ds);
dt1=ds.tables[0];

similarly for dt2 the select statement is "select col1,somecol from sometable" rest is the same as dt1.

for dt1 the output is: and the output for dt2

col1   col2  col3 col4             col1  somecol
1        2     3   4                1     true
2        5     6   ...              2     false..

i tried like below:

datatable dtTotal=new datatable();
dtTotal=dt1.clone();
foreach(datacolumn col in dt2.columns)
{
if(col.columnname=="somecol")
{
dtTotal.columns.add("somecol");
dtTotal.columns["somecol"].Datatype=col.Datatype;
}
}
foreach(datarow dr in dt1.rows)
{
dtTotal.importrows(dr);
}
//here a column is added but i don't understand how to import data into that column

I want to have a outpu like below:

col1 col2 col3 col4  somecol
1      2   3    4      true
2      5   6    7      false...

I cannot write a simple join while selecting the the data itself, because the dt2 data is coming from more complex calculations. so I have to do it at datatable level only.

if number of rows in dt1 doesnt match with number of rows in dt2 then dt2 should be added new rows with default value false.

Martin Parenteau · Accepted Answer · 2016-05-26 02:57:12Z

1

You can use the DataTable.Merge method. The command dt1.Merge(dt2) adds to dt1 the additional columns and the additional data records from dt2. The data from dt2 will overwrite the data from dt1 that share the same primary key value and the same column name.

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();

// Fill the data tables
...

// Set the default value for boolean column
dt2.Columns[4].DefaultValue = false;

// Set the primary keys
dt1.PrimaryKey = new DataColumn[] { dt1.Columns[0] }; // Use the appropriate column index
dt2.PrimaryKey = new DataColumn[] { dt2.Columns[0] }; // Use the appropriate column index

// Merge the two data tables in dt1
dt1.Merge(dt2);

edited May 26, 2016 at 2:57

answered May 25, 2016 at 0:38

Martin Parenteau

74k13 gold badges137 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add column to datatable from another datatable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related