使用SQL Bulkcopy解决大量数据写入的问题

最新推荐文章于 2022-01-06 09:41:48 发布

ViperL1

最新推荐文章于 2022-01-06 09:41:48 发布

阅读量944

点赞数

分类专栏：工程实践环境搭建&故障排除

本文链接：https://blog.csdn.net/weixin_37878740/article/details/119704873

版权

工程实践同时被 2 个专栏收录

76 篇文章

订阅专栏

环境搭建&故障排除

20 篇文章

订阅专栏

本文介绍了两种提高数据库大批量数据处理效率的方法。首先，利用SQLBulkcopy进行数据导入，通过DataTable转换和映射优化，显著提升了数据插入速度。其次，展示了使用UpdateCommand参数更新数据的技巧，结合批处理和参数化查询，提高了更新操作的性能。这两种方法在处理大量数据时能有效减少资源消耗和时间成本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

思路一：对于大批量数据采用ADO.Net或者linq to sql进行一条条执行会极大影响效率，而采用SQL Bulkcopy将整个对象集导入可以极大的缩短时间。代码执行分为如下三步。

1.使用代码对数据集进行预处理，比如修改数据或者过滤数据

2.将数据集转换为DataTable（自适应算法）

public static DataTable ToDataTable<T>(IEnumerable<T> collection)
        {
            var props = typeof(T).GetProperties();
            var dt = new DataTable();
            dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
            if (collection.Count() > 0)
            {
                for (int i = 0; i < collection.Count(); i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in props)
                    {
                        object obj = pi.GetValue(collection.ElementAt(i), null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    dt.LoadDataRow(array, true);
                }
            }
            return dt;
        }

3.使用SQL Bulkcopy将DataTable导入数据库

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
        /// <summary>
        /// 批量导入
        /// </summary>
        /// <param name="connectionString">链接字符串</param>
        /// <param name="TableName">数据库名</param>
        /// <param name="dt">数据表</param>
        private void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlBulkCopy sqlbulkcopy = 
new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.WriteToServer(dt);
                    }
                    catch (System.Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

如果数据结构不同的话则需要额外更改映射

        /// <summary>
        /// 不同表之间复制数据
        /// </summary>
        private static void PerformBulkCopyDifferentSchema()
        {
            string connectionString = @"连接字符串";
            DataTable sourceData = new DataTable();
            // 源 
            using (SqlConnection sourceConnection = new SqlConnection(connectionString))
            {
                SqlCommand myCommand = new SqlCommand("SELECT TOP 5 * FROM Products_Archive", sourceConnection);
                sourceConnection.Open();
                SqlDataReader reader = myCommand.ExecuteReader();

                // 目的
                using (SqlConnection destinationConnection = new SqlConnection(connectionString))
                {
                    // 打开连接
                    destinationConnection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
                    {
                        bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
                        bulkCopy.ColumnMappings.Add("ProductName", "Name");
                        bulkCopy.ColumnMappings.Add("QuantityPerUnit", "Quantity");
                        bulkCopy.DestinationTableName = "Products_TopSelling";
                        bulkCopy.WriteToServer(reader);
                    }
                }
                reader.Close();
            }
        }

思路二：使用UpdateCommand参数将数据写入

DateTime begin = DateTime.Now;
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();

                    SqlDataAdapter sd = new SqlDataAdapter();
                    sd.SelectCommand = new SqlCommand("select top 200 * from Mans", conn);

                    DataSet dataset = new DataSet();
                    sd.Fill(dataset);
                    Random r = new Random(1000);
                    sd.UpdateCommand = new SqlCommand("SQL 语句, conn);
                    sd.UpdateCommand.Parameters.Add("@参数", SqlDbType.VarChar, 200, "参数");
                    sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
                    sd.UpdateBatchSize = 0;


                    for (int count = 0; count < dt.Rows.Count - 1; count++)
                    {
                        for (int i = 0; i < 200; i++, count++)
                        {
                            if (count > dt.Rows.Count - 1) break;
                            dataset.Tables[0].Rows[i].BeginEdit(); 
                            dataset.Tables[0].Rows[i]["参数"] = dt.Rows[count][0];//源数据表对应的位置
                            dataset.Tables[0].Rows[i].EndEdit();//终止发生在该行的编辑。
                        }
                        //Update:为指定 System.Data.DataTable 中每个已插入、已更新或已删除的行调用相应的 INSERT、UPDATE 或 DELETE 语句。
                        sd.Update(dataset.Tables[0]);
                    }


                    dataset.Tables[0].Clear();
                    sd.Dispose();
                    dataset.Dispose();
                    conn.Close();

                }
            }
            catch (Exception ex) { }
            TimeSpan ts = DateTime.Now - begin;//更新一万条数据2秒左右

外层一定要套一个空的catch语句，否则如果出现数据库中不存在的数据就会触发并发性错误终止程序，加上空catch则不会影响执行，同时需要在构造datatable的时候做一个存在性检查，这里使用select语句

 foreach (DataRow item in X.Rows)
                        {
                            string Find = "IDNo='{0}'";
                            Find = string.Format(Find, item.ItemArray[2].ToString().Trim());
                            if (basement.Select(Find).Length > 0)
                            {
                                string AreaZone = System.IO.Path.GetFileNameWithoutExtension(Path);
                                string Locations = item.ItemArray[0].ToString().Trim();

                                DataRow dr = dt.NewRow();
                                dr["MLocation"] = Locations;
                                dr["AreaZone"] = DataX.GetArea(Locations);
                                dr["AreaName"] = DataX.GetHome(Locations);
                                dr["IDNo"] = item.ItemArray[2].ToString().Trim();

                                dt.Rows.Add(dr);
                            }
                            Count++;
                            settext(Pbar, Count, lblValue, X.Rows.Count);
                        }