Map datatable A to datatable B

  • Thread starter Thread starter moondaddy
  • Start date Start date
M

moondaddy

Using c# 3.5, I have a dataset (not strongly typed) where all the table
column names are a, b, c, etc. like the sample below. I need to rename the
table columns into something more user friendly such as ID, Name, StartDate,
etc... Also, this is something that will happen over and over with many
different datasets and some of them will be rather large such as 40 column
and 1000 rows. therefore, I want to find the most efficient way to do this.

1) Is there a way to simply rename the table columns when the datatable
already has data in it?
2) Is it possible to create a new dataset and map the columns such as col.a
= col.ID, col.b = col.Name, etc.. and then do a import or merge?
3) or do I have to create the new dataset and manually loop through each
column of each row of the first dataset and set each value into the new
dataset?
4) or is there another better way?

Thanks!

<ds>
<tbClient>
<a>1</a>
<b>test1</b>
<c>01/01/2008</c>
<d>66 Treescape Circle</d>
<e>1</e>
<f>123.000</f>
<g>1</g>
</tbClient>
</ds>
 
Hello Moondaddy,
1)Is there a way to simply rename the table columns when the datatable
Click to expand...
already has data in it?

Datacolumn.columnname indicates the name of column. You can set it to new
column name after your datatable has already filled with data.
For example:
ds.Tables["tbclient"].Columns["a"]="ID";
2) Is it possible to create a new dataset and map the columns such as
Click to expand...
col.a = col.ID, col.b = col.Name, etc.. and then do a import or merge?

Yes, you can copy the structure of the original DataSet, including all
DataTable schemas, relations, and constraints without data by
DataSet.Clone() method. After that, you can rename each column.
But, I don't think it's necessary. You can rename the column in the origin
dataset directly.
3) or do I have to create the new dataset and manually loop through each
Click to expand...
column of each row of the first dataset and set each value into the new
dataset?

Please refer to the first and second answer. I don't think it necessary for
you to create a new dataset instance. You can make change on the original
dataset directly.
4) or is there another better way?
Click to expand...

The answer depends on how you fill date into dataset. If you are using
DBDataAdapter, DataAdapter.TableMappings property addresses this issue.
DataAdatper.TableMappings is a collection that provides the master mapping
between the returned records and the DataSet.
For example:
System.Data.SqlClient.SqlDataAdapter sda = new
System.Data.SqlClient.SqlDataAdapter();
sda.TableMappings.Add("tbClient","newtbClient");
sda.TableMappings["tbClient"].ColumnMappings.Add("a", "id");
sda.TableMappings["tbClient"].ColumnMappings.Add("b", "Name");
sda.TableMappings["tbClient"].ColumnMappings.Add("c", "StartDate");
....
sda.Fill(ds,"tbClient");

http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.tabl
emappings(VS.85).aspx
[DataAdapter.TableMappings Property]

Hope this helps. If there is anything unclear, please feel free to update
here again. We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thanks. Renaming the columns works good. fast, easy, and low cost.

WenYuan Wang said:
Hello Moondaddy,
1)Is there a way to simply rename the table columns when the datatable
Click to expand...
already has data in it?

Datacolumn.columnname indicates the name of column. You can set it to new
column name after your datatable has already filled with data.
For example:
ds.Tables["tbclient"].Columns["a"]="ID";
2) Is it possible to create a new dataset and map the columns such as
Click to expand...
col.a = col.ID, col.b = col.Name, etc.. and then do a import or merge?

Yes, you can copy the structure of the original DataSet, including all
DataTable schemas, relations, and constraints without data by
DataSet.Clone() method. After that, you can rename each column.
But, I don't think it's necessary. You can rename the column in the origin
dataset directly.
3) or do I have to create the new dataset and manually loop through each
Click to expand...
column of each row of the first dataset and set each value into the new
dataset?

Please refer to the first and second answer. I don't think it necessary
for
you to create a new dataset instance. You can make change on the original
dataset directly.
4) or is there another better way?
Click to expand...

The answer depends on how you fill date into dataset. If you are using
DBDataAdapter, DataAdapter.TableMappings property addresses this issue.
DataAdatper.TableMappings is a collection that provides the master mapping
between the returned records and the DataSet.
For example:
System.Data.SqlClient.SqlDataAdapter sda = new
System.Data.SqlClient.SqlDataAdapter();
sda.TableMappings.Add("tbClient","newtbClient");
sda.TableMappings["tbClient"].ColumnMappings.Add("a", "id");
sda.TableMappings["tbClient"].ColumnMappings.Add("b", "Name");
sda.TableMappings["tbClient"].ColumnMappings.Add("c", "StartDate");
...
sda.Fill(ds,"tbClient");

http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.tabl
emappings(VS.85).aspx
[DataAdapter.TableMappings Property]

Hope this helps. If there is anything unclear, please feel free to update
here again. We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
Click to expand...
 
You are welcome, moondaddy.

Have a great day,
Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top