How can we copy the column data of one datatable to another,even if there is different column names of different datatable ?

Question

I have two datatables. First is

DataTable NameAdressPhones = new DataTable(); 

with Three columns Name,Adress and PhoneNo.But I want only two columns Name and Adress data so I am copy those columns (with data) to the new datatable

DataTable NameAdress = new DataTable(); 

For that I do

            foreach (DataRow sourcerow in NameAdressPhones.Rows)
            {
                DataRow destRow = NameAdress.NewRow();
                foreach (string colname in columns)
                {
                    destRow[colname] = sourcerow[colname];
                }
                NameAdress.Rows.Add(destRow);
            }

Now I clear every time the NameAdressPhones(first) datatable when there are new records in the table.And every time there will be same no of columns but column name will be different like Nm instead of Name,Add instead of Address.Now problem is second datatable have already column names Name and Address and now I want to copy the columns data of Nm and Add to the second datatabel but the column names are different to the second datatable.So even If there is different column names I want to copy Nm column data of first datatable to the column Name of second datatable and column Add data of first datatable to column Address of second datatable.

In short how can we copy column data from one datatable to another even if there is different column names of both datatable like Nm is the column name of first datatable and Name is the column name of second datatable then the data of the column Nm should be copied to the column Name.

Answer 1

Here's the simplest way:

foreach (DataRow sourcerow in NameAdressPhones.Rows)
{
    DataRow destRow = NameAdress.NewRow();
    destRow["Name"] = sourcerow["Nm"];
    destRow["Address"] = sourcerow["Add"];
    NameAdress.Rows.Add(destRow);
}

Automation is great when it's available. When it's not, you have to map source columns to destination columns in some manner.

If the columns are in the same order in both tables, you could just reference the values by ordinal instead of column name, but that's such a bad idea I'm not even going to post any code for it.

Answer 2

If I've understood your question right, then the way this is usually done is by using stored procedures. You have the same stored procedures in both databases, but the implementation is specific to the table schema of each database. This allows you the abstraction you need.

Answer 3

Use column index number rather than names:

destRow[0] = sourcerow[0]; // for column 0 = "Name" or "NM"