SqlServer批量插入数据

1、创建队列

//DataTable表头与要插入的数据库表列名一致
 public static ConcurrentDictionary<string, DataTable> dataTemp = new ConcurrentDictionary<string, DataTable>();

2、获取数据库连接和表名以及需要插入的数据DataTable

   if (dataTemp.Count > 0)
    {
        try
        {
            foreach (var item in dataTemp)
            {
               string key = item.Key.Split('-')[0];//获取数据库名
               string dbName = item.Key.Split('-')[1];//获取表名
               DataTable dtStr = new DataTable();
               dataTemp.Remove(item.Key, out dtStr);//获取值并移除

               //数据库连接
               string connectionString = "Data Source=" + dataSource + ";Database=" + key + ";User ID=" + userID + ";Password=" + password + ";";

               int count = SqlHelper.BulkInsert(connectionString, dbName, dtStr);
                if (count > 0)
                   Configs.log.Info(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ":入库成功" + count + "条");
            }
        }
        catch (Exception err)
        {
            Configs.log.Info(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ":入库失败" + err.Message);
        }
    }

3、批量插入数据，返回影响的记录数

    /// <summary>
    /// 批量插入数据，返回影响的记录数
    /// </summary>
    /// <param name="connectionString">数据库连接</param>
    /// <param name="tableName">表名</param>
    /// <param name="sqldt">数据表</param>
    /// <returns></returns>
    public static int BulkInsert(string connectionString,string tableName, DataTable sqldt)
    {
        int count = 0;
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = sqldt.Rows.Count;
            conn.Open();

            if (sqldt != null && sqldt.Rows.Count != 0)
            {
                bulkCopy.WriteToServer(sqldt);
                count = sqldt.Rows.Count;//执行条数
            }
        }
        return count;
    }