merge two datatable into third as columns c#

mukesh salaria

12y23.9kVisual Basic .NET

Hi i have two data tables dt1 and dt2. I would like to merge them into third table which is dtfinal.

dt1 contain

----- ---

dt2 contain

name

-----------

muke

mike

joel

i want dtfinal output as

id name

--------------------

1 muke

2 mike

3 joel

how can i get this, please suggest

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

ash ash10y ago
what is table2?
mukesh salaria12y ago
i will try it tomorrow and let you know, if it works..Thanks for your efforts..
Ramesh Maruthi12y ago
DataTable finalTable = new DataTable();
foreach(DataColumn dc in tableLoad.Columns)
{
finalTable.Columns.Add(dc.ColumnName).DataType = dc.DataType;


}

for(int i = 0; i < tableLoad.Rows.Count; i++)
{
foreach(DataColumn dc in tableLoad.Columns)
{
string col = dc.ColumnName;
finalTable.Rows[i][col] = table2.Rows[i][col];
}

}

foreach(DataColumn dc in tableOrder.Columns)
{
finalTable.Columns.Add(dc.ColumnName).DataType = dc.DataType;


}

for(int i = 0; i < tableOrder.Rows.Count; i++)
{
foreach(DataColumn dc in tableOrder.Columns)
{
string col = dc.ColumnName;
finalTable.Rows[i][col] = table2.Rows[i][col];
}

}
mukesh salaria12y ago
already tried this, same result...
Ramesh Maruthi12y ago
finalTable = tableLoad.Copy();
finalTable.Merge(tableOrder);

Try like this once
mukesh salaria12y ago
Hi Ramesh, I format the code and uploaded .cs file as well. please check it now. if need anything let me know. Thanks in adnvace.
Ramesh Maruthi12y ago
Mukesh can you please upload the VS solution file instead of this clumsy lines of code
mukesh salaria12y ago
hey here my exact code

var _drcode = Request.QueryString["DRCODE"];
using (iDB2Connection conn = new iDB2Connection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) {
string sqlStatement = "SELECT DIODR# FROM LOAD where UPPER(DIDR1)=@drcode'"; iDB2Command cmd = new iDB2Command(sqlStatement, conn);
cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@drcode", _drcode.ToUpper());
try {
conn.Open();
iDB2DataAdapter adp = new iDB2DataAdapter(cmd);
DataTable finalTable = new DataTable();
DataTable tableLoad = new DataTable("tableLoadDetail");
tableLoad.Columns.Add("DIODR#");
adp.Fill(tableLoad);
DataTable tableOrder = new DataTable("tableOrderDetail"); tableOrder.Columns.Add("CUNAME");
for (int i = 0; i < tableLoad.Rows.Count; i++)
{
string sqlOderStatement = "SELECT CUSTMAST.CUNAME FROM CUSTMAST WHERE CUCODE IN (SELECT ORDER.ORCONS FROM ORDER where UPPER(ORODR#)=@orderid)";
iDB2Command cmdOrder = new iDB2Command(sqlOderStatement, conn); cmdOrder.CommandType = CommandType.Text;
cmdOrder.Parameters.Add("@orderid", tableLoad.Rows[i].ItemArray[0]);
iDB2DataAdapter adp2 = new iDB2DataAdapter(cmdOrder); adp2.Fill(tableOrder);
}
finalTable.Merge(tableLoad);
finalTable.Merge(tableOrder);
rptOrderId.DataSource = finalTable;
rptOrderId.DataBind();

and its show me output in one column like first records of first table and then append second table records to that column. help...
Vulpes12y ago
Try this:

using System;
using System.Data;

class Program
{
static void Main()
{
var dt1 = new DataTable();
dt1.Columns.Add("id", typeof(int));
dt1.Rows.Add(1);
dt1.Rows.Add(2);
dt1.Rows.Add(3);

var dt2 = new DataTable();
dt2.Columns.Add("name", typeof(string));
dt2.Rows.Add("muke");
dt2.Rows.Add("mike");
dt2.Rows.Add("joel");

var dtfinal = MergeDataTables(dt1, dt2);

// check it worked
foreach(DataColumn dc in dtfinal.Columns)
{
Console.Write("{0, -6}", dc.ColumnName);
}
Console.WriteLine("\n-----------------------");

foreach(DataRow dr in dtfinal.Rows)
{
Console.WriteLine("{0, -6}{1}", dr["id"], dr["name"]);
}

Console.ReadKey();
}

static DataTable MergeDataTables(DataTable table1, DataTable table2)
{
DataTable table3 = table1.Copy();
foreach(DataColumn dc in table2.Columns)
{
table3.Columns.Add(dc.ColumnName).DataType = dc.DataType;
}

for(int i = 0; i < table3.Rows.Count; i++)
{
foreach(DataColumn dc in table2.Columns)
{
string col = dc.ColumnName;
table3.Rows[i][col] = table2.Rows[i][col];
}
}

return table3;
}
}
Ramesh Maruthi12y ago
Hello Mukesh,

If you have the at least one similar column between the two tables you can use Linq, its very easy to merge the two tables and show it in third table...in this way

var collection = from t1 in dt1.AsEnumerable()
join t2 in dt2.AsEnumerable()
on t1["id"] equals t2["id"]
select new { Name = t1["id"], Group = t2["Name"] };
DataTable result = new DataTable("NameGroups");
result.Columns.Add("ID", typeof(string));
result.Columns.Add("Name", typeof(int));

foreach (var item in collection)
{
result.Rows.Add(item.ID, item.Name);
}

But according to your case there are no similar columns, so you can do in this way

DataTable table1 = new DataTable("IDs");
DataColumn idColumn = new DataColumn("id", typeof(System.Int32));
table1.Columns.Add(idColumn);

// Set the primary key column.
table1.PrimaryKey = new DataColumn[] { idColumn };

// Add Three rows.
DataRow row;
for (int i = 0; i <= 3; i++)
{
row = table1.NewRow();
row["id"] = i;
table1.Rows.Add(row);
}

// Accept changes.
table1.AcceptChanges();

DataTable table2 = new DataTable("Names")
table2.Columns.Add("Name", typeof(System.String));
row = table2.NewRow();
row["Name"] = "Muke";
table2.Rows.Add(row);

DataTable dt3 = dt1.Copy();
dt3.Merge(dt2);