我有:
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];
我想做的是：
假设当前column.DataType.Name是"Double"，我希望变成"Int32"。
我该怎么做？
在Datatable填充数据后，您无法更改DataType。但是，您可以克隆Data表，更改列类型并从先前的数据表加载数据到克隆表中，如下所示。
DataTable dtCloned = dt.Clone();
dtCloned.Columns[0].DataType = typeof(Int32);
foreach (DataRow row in dt.Rows)
{
dtCloned.ImportRow(row);
}
虽然在填充了DataTable之后无法更改列的类型，但是您可以在调用Fill之前调用FillSchema来更改它。例如，假设第3列是您想要从double转换为Int32的列，您可以使用以下代码：
adapter.FillSchema(table, SchemaType.Source);
table.Columns[2].DataType = typeof (Int32);
adapter.Fill(table);
Oracle.MangedDataAccess.Client.OracleDataAdapter 上进行了测试，它可以正常工作。谢谢。 - 3per虽然这是一篇旧帖子，但我想发表我的观点。这里有一个 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;
}
}
}
然后可以这样调用：
MyTable.ConvertColumnType("MyColumnName", typeof(int));
public static void ConvertColumnType<T>(this DataTable dt, string columnName, TnewType) where T : Type, IConvertible - T.M.dr[dc.ColumnName] = dr[columnName] == DBNull.Value ? DBNull.Value : Convert.ChangeType(dr[columnName], newType); - miboper我采取了不同的方法。我需要解析一个在Excel导入中以OA日期格式呈现的日期时间。这种方法很容易建立......实质上，
删除原始列并将新列重命名为与旧列相匹配
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;
}
还要考虑更改返回类型：
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)
dataTable = new DataTable("Contact");
dataColumn = new DataColumn("Id");
dataColumn.DataType = typeof(Int32);
dataTable.Columns.Add(dataColumn);
DataTable dataTableClone = dataTable.Clone();
如果你想仅更改一个列，例如从字符串到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
.Clone - 与泛型和可扩展性，使得我可以定义自己的转换函数。这就是我最终得到的：/// <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;
}
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());
我创建了一个扩展函数，可以更改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;
}
}
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;
});
}