/// <summary>
/// 单表插入
/// </summary>
/// <param name="dt"></param>
public static void BatchInsertBySqlBulkCopy(DataTable dt)
{
try
{
using (SqlBulkCopy sbc = new SqlBulkCopy(conn))
{
sbc.BatchSize = dt.Rows.Count;
sbc.BulkCopyTimeout = 10;
sbc.DestinationTableName = dt.TableName;
for (int i = 0; i < dt.Columns.Count; i++)
{
sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, i);
}
//全部写入数据库
sbc.WriteToServer(dt);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 多表插入
/// </summary>
/// <param name="dtList"></param>
/// <param name="isTruncate">导入数据之前是否清楚表中原有数据 1 ：清楚；0：不清除</param>
public static void BatchInsertBySqlBulkCopy(List<DataTable> dtList,int isTruncate)
{
try
{
string strconn = conn;
using (SqlConnection sqlconn = new SqlConnection(strconn))
{
sqlconn.Open();
using (SqlTransaction tran = sqlconn.BeginTransaction())
{
try
{
foreach (var dt in dtList)
{
if (isTruncate == 1)
{
string comText = string.Format(" truncate table {0} ", dt.TableName);
SqlCommand com = new SqlCommand(comText , sqlconn);
com.Transaction = tran;
com.ExecuteNonQuery();
}
using (SqlBulkCopy sbc = new SqlBulkCopy(sqlconn, SqlBulkCopyOptions.CheckConstraints, tran))
{
sbc.BatchSize = dt.Rows.Count;
sbc.BulkCopyTimeout = 10;
sbc.DestinationTableName = dt.TableName;
for (int i = 0; i < dt.Columns.Count; i++)
{
sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, i);
}
//全部写入数据库
sbc.WriteToServer(dt);
}
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}