How to change the data type of a DataColumn in a DataTable?

Question

I have:

DataTable Table = new DataTable; SqlConnection = new System.Data.SqlClient.SqlConnection("Data Source=" + ServerName + ";Initial Catalog=" + DatabaseName + ";Integrated Security=SSPI; Connect Timeout=120"); SqlDataAdapter adapter = new SqlDataAdapter("Select * from " + TableName, Connection); adapter.FillSchema(Table, SchemaType.Source); adapter.Fill(Table); DataColumn column = DataTable.Columns[0]; 

What I want to do is:

Assume column.DataType.Name is currently set to "Double". I want it to become "Int32".

How can I achieve this?

Answer 1

You cannot change the DataType after populating the Datatable with data. However, you can clone the data table, change the column type, and load data from the previous data table into the cloned table, as shown below.

 DataTable dtCloned = dt.Clone(); dtCloned.Columns[0].DataType = typeof(Int32); foreach (DataRow row in dt.Rows) { dtCloned.ImportRow(row); } 

Answer 2

Although it is true that you cannot change the type of a column after filling out the DataTable , you can change it after calling FillSchema , but before calling Fill . For example, let's say that the third column is the one you want to convert from double to Int32 , you can use:

 adapter.FillSchema(table, SchemaType.Source); table.Columns[2].DataType = typeof (Int32); adapter.Fill(table); 

Answer 3

An old post, but I thought I would weigh it, with a DataTable extension that can convert one column at a time to a specific type:

 public static class DataTableExt { public static void ConvertColumnType(this DataTable dt, string columnName, Type newType) { using (DataColumn dc = new DataColumn(columnName + "_new", newType)) { // Add the new column which has the new type, and move it to the ordinal of the old column int ordinal = dt.Columns[columnName].Ordinal; dt.Columns.Add(dc); dc.SetOrdinal(ordinal); // Get and convert the values of the old column, and insert them into the new foreach (DataRow dr in dt.Rows) dr[dc.ColumnName] = Convert.ChangeType(dr[columnName], newType); // Remove the old column dt.Columns.Remove(columnName); // Give the new column the old column name dc.ColumnName = columnName; } } } 

Then it can be called as follows:

 MyTable.ConvertColumnType("MyColumnName", typeof(int)); 

Of course, using whatever type you want, as long as each value in the column can actually be converted to a new type.

Answer 4

Consider also changing the type of the return value:

 select cast(columnName as int) columnName from table 

Answer 5

 Dim tblReady1 As DataTable = tblReady.Clone() '' convert all the columns type to String For Each col As DataColumn In tblReady1.Columns col.DataType = GetType(String) Next tblReady1.Load(tblReady.CreateDataReader) 

Answer 6

I took a slightly different approach. I needed to parse the datetime from an excel import that was in the OA date format. This methodology is simple enough to build from ... essentially

• Add the type column you want
• Scroll through strings that convert a value

• Delete the original column and rename it to the new one to match the old

   private void ChangeColumnType(System.Data.DataTable dt, string p, Type type){ dt.Columns.Add(p + "_new", type); foreach (System.Data.DataRow dr in dt.Rows) { // Will need switch Case for others if Date is not the only one. dr[p + "_new"] =DateTime.FromOADate(double.Parse(dr[p].ToString())); // dr[p].ToString(); } dt.Columns.Remove(p); dt.Columns[p + "_new"].ColumnName = p; } 

Answer 7

Once a DataTable is populated, you cannot change the type of the column.

Your best option in this scenario is to add an Int32 column to the DataTable before filling it:

 dataTable = new DataTable("Contact"); dataColumn = new DataColumn("Id"); dataColumn.DataType = typeof(Int32); dataTable.Columns.Add(dataColumn); 

Then you can clone the data from the source table into a new table:

 DataTable dataTableClone = dataTable.Clone(); 

Here is a post with more details .

Answer 8

if you want to change only the column. For example, from a string in int32 you can use an expression.

 DataColumn col = new DataColumn("col_int" , typeof(int)); table.columns.Add(col) col.Expression = "table_exist_col_string"; // digit string convert to int 

Answer 9

I created an extension function that allows you to change the type of a DataTable column. Instead of cloning the entire table and importing all the data, it simply clones the column, analyzes the value, and then deletes the original.

  /// <summary> /// Changes the datatype of a column. More specifically it creates a new one and transfers the data to it /// </summary> /// <param name="column">The source column</param> /// <param name="type">The target type</param> /// <param name="parser">A lambda function for converting the value</param> public static void ChangeType(this DataColumn column, Type type, Func<object, object> parser) { //no table? just switch the type if (column.Table == null) { column.DataType = type; return; } //clone our table DataTable clonedtable = column.Table.Clone(); //get our cloned column DataColumn clonedcolumn = clonedtable.Columns[column.ColumnName]; //remove from our cloned table clonedtable.Columns.Remove(clonedcolumn); //change the data type clonedcolumn.DataType = type; //change our name clonedcolumn.ColumnName = Guid.NewGuid().ToString(); //add our cloned column column.Table.Columns.Add(clonedcolumn); //interpret our rows foreach (DataRow drRow in column.Table.Rows) { drRow[clonedcolumn] = parser(drRow[column]); } //remove our original column column.Table.Columns.Remove(column); //change our name clonedcolumn.ColumnName = column.ColumnName; } } 

You can use it like this:

 List<DataColumn> lsColumns = dtData.Columns .Cast<DataColumn>() .Where(i => i.DataType == typeof(decimal)) .ToList() //loop through each of our decimal columns foreach(DataColumn column in lsColumns) { //change to double column.ChangeType(typeof(double),(value) => { double output = 0; double.TryParse(value.ToString(), out output); return output; }); } 

The code above changes all decimal columns to two.

Answer 10

I combined the effectiveness of the Mark solution - so I don’t need to. .Clone entire DataTable - with generalizations and extensibility so that I can define my own transform function . Here's what I ended up with:

 /// <summary> /// Converts a column in a DataTable to another type using a user-defined converter function. /// </summary> /// <param name="dt">The source table.</param> /// <param name="columnName">The name of the column to convert.</param> /// <param name="valueConverter">Converter function that converts existing values to the new type.</param> /// <typeparam name="TTargetType">The target column type.</typeparam> public static void ConvertColumnTypeTo<TTargetType>(this DataTable dt, string columnName, Func<object, TTargetType> valueConverter) { var newType = typeof(TTargetType); DataColumn dc = new DataColumn(columnName + "_new", newType); // Add the new column which has the new type, and move it to the ordinal of the old column int ordinal = dt.Columns[columnName].Ordinal; dt.Columns.Add(dc); dc.SetOrdinal(ordinal); // Get and convert the values of the old column, and insert them into the new foreach (DataRow dr in dt.Rows) { dr[dc.ColumnName] = valueConverter(dr[columnName]); } // Remove the old column dt.Columns.Remove(columnName); // Give the new column the old column name dc.ColumnName = columnName; } 

Thus, usage is much simpler, but also customizable:

 DataTable someDt = CreateSomeDataTable(); // Assume ColumnName is an int column which we want to convert to a string one. someDt.ConvertColumnTypeTo<string>('ColumnName', raw => raw.ToString()); 

Answer 11

 DataTable DT = ... // Rename column to OLD: DT.Columns["ID"].ColumnName = "ID_OLD"; // Add column with new type: DT.Columns.Add( "ID", typeof(int) ); // copy data from old column to new column with new type: foreach( DataRow DR in DT.Rows ) { DR["ID"] = Convert.ToInt32( DR["ID_OLD"] ); } // remove "OLD" column DT.Columns.Remove( "ID_OLD" );