datatable高效写入mysql_把dataTable表批量的写入数据库

connectionStr：链接字符串；dataTableName数据库中的表；sourceDataTable dataTable的名称

public static void SqlBulkCopyByDataTable(string connectionStr, string dataTableName, DataTable sourceDataTable, int batchSize = 100000)

{

using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionStr))

{

using (System.Data.SqlClient.SqlBulkCopy sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(connectionStr, System.Data.SqlClient.SqlBulkCopyOptions.UseInternalTransaction))

{

try

{

sqlBulkCopy.DestinationTableName = dataTableName;

sqlBulkCopy.BatchSize = batchSize;

for (int i = 0; i < sourceDataTable.Columns.Count; i++)

{

sqlBulkCopy.ColumnMappings.Add(sourceDataTable.Columns[i].ColumnName, sourceDataTable.Columns[i].ColumnName);

}

sqlBulkCopy.WriteToServer(sourceDataTable);

}

catch (Exception ex)

{

throw ex;

}

}

}

}