特别注意 sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
插入的时候列的顺序可以不一致,但名称和数据类型最好要保存一致。不一致时候,也能进行正确的转换,除了比如DataTime数据类型,不能插入一个无效的string时间字符串。
http://www.cnblogs.com/netuml/p/3719298.html
/// <summary>
///
/// </summary>
/// <param name="connectionString">目标连接字符</param>
/// <param name="TableName">目标表</param>
/// <param name="dt">源数据</param>
private void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlBulkCopy sqlbulkcopy =new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
{
try
{
sqlbulkcopy.DestinationTableName = TableName;
for (int i = 0; i < dt.Columns.Count; i++)
{
sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
sqlbulkcopy.WriteToServer(dt);
}
catch (System.Exception ex)
{
throw ex;
}
}
}
}