C# 如何更改数据表中数据列的数据类型？

How To Change DataType of a DataColumn in a DataTable?

提问by rofans91

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.Nameis "Double". I want it to become "Int32".

假设当前column.DataType.Name是"Double"。我希望它成为"Int32"。

How do I achieve this?

我如何实现这一目标？

采纳答案by Akhil

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

回答by Josh Earl

Once a DataTablehas been filled, you can't change the type of a column.

一旦DataTable被填满，你不能改变一个列的类型。

Your best option in this scenario is to add an Int32column to the DataTablebefore filling it:

在这种情况下，您最好的选择是在填充之前添加一Int32列DataTable：

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.

这是一个包含更多详细信息的帖子。

回答by rsbarro

While it is true that you cannot change the type of the column after the DataTableis 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 doubleto Int32, you could use:

虽然您不能在DataTable填充后更改列的类型FillSchema，但您可以在调用之后但在调用之前更改它Fill。例如，假设第三列是您要从 转换double为 的列Int32，您可以使用：

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

回答by tsilb

Consider also altering the return type:

还考虑改变返回类型：

select cast(columnName as int) columnName from table

回答by Superna Parajuli

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)

回答by mehdi

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

如果你只想改变一个 column.for example 从 string 到 int32 你可以使用 Expression 属性：

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

回答by John Salewski

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,

我采取了一些不同的方法。我需要从 OA 日期格式的 excel 导入中解析日期时间。这种方法很简单，可以从……本质上构建，

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;
       }
   

1. 添加所需类型的列
2. 翻阅转换值的行

3. 删除原始列并重命名为新列以匹配旧列

   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;
       }
   

回答by Wes Hanney

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.

我创建了一个扩展函数，它允许更改 DataTable 的列类型。而不是克隆整个表并导入所有数据，它只是克隆列，解析值然后删除原始值。

    /// <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.

上面的代码将所有小数列更改为双精度。

回答by Altivo

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

回答by Marc Ferrold

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:

旧帖子，但我想我会权衡一下，使用 DataTable 扩展可以一次将一列转换为给定类型：

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.

当然可以使用任何您想要的类型，只要列中的每个值实际上都可以转换为新类型。