SqlBulkCopy 批量插入数据库

话不多说直接上代码：

 1 /// <summary>
 2         /// 将批量数据导入Sqlserver
 3         /// </summary>
 4         /// <param name="sqldb">数据集</param>
 5         public static bool SqlBulkCopy(DataTable sourceTable, string targetTableName)
 6         {
 7             using (SqlConnection conn = GetDefaultDB())
 8             {
 9                 try
10                 {
11                     if (conn.State != ConnectionState.Open)
12                         conn.Open();
13                     if (sourceTable.Rows.Count > 0)
14                     {
15                         //数据批量导入sqlserver,创建实例
16                         using (SqlBulkCopy sqlbulk = new SqlBulkCopy(conn))
17                         {
18                             //目标数据库表名
19                             sqlbulk.DestinationTableName = targetTableName;
20                             //数据集字段索引与数据库字段索引映射
21                             for (int i = 0; i < sourceTable.Columns.Count; i++)
22                             {
23                                 sqlbulk.ColumnMappings.Add(i, i);
24                             }
25                             sqlbulk.BatchSize = 10000;
26                             sqlbulk.BulkCopyTimeout = 90;
27                             //导入
28                             sqlbulk.WriteToServer(sourceTable);
29                             sqlbulk.Close();
30                         }
31                     }
32                     return true;
33                 }
34                 catch
35                 {
36                     return false;
37                 }
38             }
39         }

View Code

/// <summary>
/// 将批量数据导入Sqlserver
/// </summary>
/// <param name="tempTable">数据源表</param>
/// <param name="tableName">目标表</param>
/// <param name="dic">列映射(key：数据源表字段，value：目标字段)</param>
/// <returns></returns>
public static bool SqlBulkCopy(DataTable tempTable, string tableName, IDictionary<string, string> dic)
{
    using (SqlConnection conn = GetDefaultDB())
    {
        try
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            if (tempTable.Rows.Count > 0)
            {
                //数据批量导入sqlserver,创建实例
                using (SqlBulkCopy sqlbulk = new SqlBulkCopy(conn))
                {
                    //目标数据库表名
                    sqlbulk.DestinationTableName = tableName;

                    //每一批次提交多少行到数据库，事务操作。(如果后面的批次数据异常，则该批次提交成功不会回滚，后面批次数据事务回滚插入不成功)
                    //少量数据不建议使用
                    //sqlbulk.BatchSize = 2;

                    //数据集字段索引与数据库字段索引映射
                    foreach (var item in dic)
                    {
                        sqlbulk.ColumnMappings.Add(item.Key, item.Value);
                    }
                    //导入
                    sqlbulk.WriteToServer(tempTable);
                    sqlbulk.Close();
                }
            }
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }
}

posted @ 2022-09-15 16:52 统哥阅读(45) 评论(0) 收藏举报

刷新页面返回顶部