SqlConnection sqlConn = new SqlConnection("Data Source=192.168.1.11;Initial Catalog=databasename;User Id=sa;Password=psd;");
sqlConn.Open();
SqlTransaction trans = sqlConn.BeginTransaction();
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.CheckConstraints, trans);
bulkCopy.DestinationTableName = tname;
bulkCopy.BulkCopyTimeout = 100;
bulkCopy.BatchSize = 50000;
try
{
if (dt != null && dt.Rows.Count != 0)
bulkCopy.WriteToServer(dt);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (trans != null)
{
trans.Dispose();
}
if (bulkCopy != null)
{
bulkCopy.Close();
}
if (sqlConn != null)
{
sqlConn.Dispose();
}
}
实际项目中根据要求获取datatable花费时间较多。
经测试待插入表106列,
导入13W条记录,一条记录105列,耗时15秒;
导入26W条记录,一条记录105列,BatchSize=30000-->耗时53秒,BatchSize=50000-->耗时32秒,BatchSize=80000-->耗时34秒,BatchSize=100000-->耗时57秒;
导入105W条记录,一条记录105列,耗时292秒;
导入105W条记录,一条记录3列,耗时131秒。