批量插入数据SqlBulkCopy
/// <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;
}
}
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步