How To Change DataType of a DataColumn in a DataTable?

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 currently column.DataType.Name is "Double". I want it to become "Int32".

How do I achieve this?

You cannot change the DataType after the Datatable is filled with data. However, you can clone the Data table, change the column type and load data from previous data table to 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);
}

While it is true that you cannot change the type of the column after the DataTable is filled, you can change it after you call FillSchema, but before you call Fill. For example, say the 3rd column is the one you want to convert from double to Int32, you could use:

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

Old post, but I thought I'd weigh in, with a DataTable extension that can convert a single column at a time, to a given 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's name
            dc.ColumnName = columnName;
        }
    }
}

It can then be called like this:

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

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

I've taken a bit of a different approach. I needed to parse a datetime from an excel import that was in the OA date format. This methodology is simple enough to build from... in essence,

1. Add column of type you want

2. Rip through the rows converting the value

3. Delete the original column and rename to the new 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;
       }
   

Consider also altering the return type:

select cast(columnName as int) columnName from table

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)

if you want to change only a column.for example from string to int32 you can use Expression property:

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

Once a DataTable has been filled, you can't change the type of a 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 your original table to the new table:

DataTable dataTableClone = dataTable.Clone();

Here's a post with more details.

I combined the efficiency of Mark's solution - so I do not have to .Clone the entire DataTable - with generics and extensibility, so I can define my own conversion function. This is 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's name
	dc.ColumnName = columnName;
}

This way, usage is a lot more straightforward, while 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());

I created an extension function which allows changing the column type of a DataTable. Instead of cloning the entire table and importing all the data it just clones the column, parses 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 so:

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 above code changes all the decimal columns to doubles.

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" );

Puedes agregar una columna con tipo de dato distinto , luego copiar los datos y eliminar la columna anterior

TB.Columns.Add("columna1", GetType(Integer))    
TB.Select("id=id").ToList().ForEach(Sub(row) row("columna1") = row("columna2"))    
TB.Columns.Remove("columna2")