Reputation: 788
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.
Upvotes: 0
Views: 4671
Reputation: 73761
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);
Upvotes: 1