第二十一节:ADO层次上的海量数据处理方案(SqlBulkCopy类插入和更新)

一. 简介

1. 背景：

　　虽然前面EF的扩展插件Z.EntityFramework.Extensions，性能很快，而且也很方便，但是该插件要收费，使用免费版本的话，需要定期更新，如果不更新，将失效，非常麻烦，这个时候SqlBulkCopy类既免费又高效，显得非常合适了。

2. 使用步骤：

 　　①. 引入命名空间:using System.Data.SqlClient;

 　　②. 使用DataTable构造与目标数据库表结构相同的字段，并给其赋值。

 　　③. 使用SqlBulkCopy类，将内存表中的数据一次性插入到目标表中。(看下面的封装可知，可以自行设置插入块的大小)

　　补充：调用下面的封装这种形式必须内存表中的字段名和数据库表中的字段名一一对应。

二. 使用方式及其性能测试

 1.  涉及到的数据库结构：

 

2. 数据库连接字符串　

　　<add name="CodeFirstModel2" connectionString="data source=localhost;initial catalog=EFDB2;persist security info=True;user id=sa;password=123456;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />

3. 代码实践

　　有两种插入方式，一种是按部就班的每个字段 内存表和数据表进行映射，这个情况无须名称一致，只要映射正确即可。另外一种方式是，直接调用下面的封装方法即可，这种要求内存表中字段和数据库表中的字段名称必须完全一致，一一对应，这样就省去了方法一 中一一匹配映射的繁琐步骤了。

   public class SqlBulkCopyTest
    {
        public static void TestSqlBulkCopy()
        {
            //一. 构造DataTable结构并且给其赋值
            //1.定义与目标表的结构一致的内存表 排除自增id列  
            DataTable dtSource = new DataTable();
            //列名称如果和目标表设置为一样，则后面可以不用进行字段映射  
            dtSource.Columns.Add("id");
            dtSource.Columns.Add("t21");
            dtSource.Columns.Add("t22");
            //2. 向dt中增加4W条测试数据  
            DataRow dr;
            for (int i = 0; i <40000; i++)
            {
                // 创建与dt结构相同的DataRow对象  
                dr = dtSource.NewRow();
                dr["id"] = Guid.NewGuid().ToString("N");
                dr["t21"] = "Name" + i;
                dr["t22"] = "Address" + i;
                // 将dr追加到dt中  
                dtSource.Rows.Add(dr);
            }
            //二.将内存表dt中的4W条数据一次性插入到t_Data表中的相应列中 
            System.Diagnostics.Stopwatch st = new System.Diagnostics.Stopwatch();
            st.Start();
            string connStr = ConfigurationManager.ConnectionStrings["CodeFirstModel2"].ToString();

            #region 01-按部就班一一对应
            //{
            //    using (SqlBulkCopy copy = new SqlBulkCopy(connStr))
            //    {
            //        //1 指定数据插入目标表名称  
            //        copy.DestinationTableName = "TestTwo";

            //        //2 告诉SqlBulkCopy对象 内存表中的 字段和目标表中的字段 对应起来(这里有多个重载，也可以用索引对应)
            //        //前面是内存表，后面是目标表即数据库中的字段
            //        copy.ColumnMappings.Add("id", "id");
            //        copy.ColumnMappings.Add("t21", "t21");
            //        copy.ColumnMappings.Add("t22", "t22");

            //        //3 将内存表dt中的数据一次性批量插入到目标表中  
            //        copy.WriteToServer(dtSource);
            //    }
            //}

            #endregion

            #region 02-调用封装
            {
                AddByBluckCopy(connStr, dtSource, "TestTwo");
            }
            #endregion
            
            st.Stop();
            Console.WriteLine("数据插入成功，总耗时为:" + st.ElapsedMilliseconds + "毫秒");

        }

        /// <summary>
        /// 海量数据插入方法
        /// (调用该方法需要注意，DataTable中的字段名称必须和数据库中的字段名称一一对应)
        /// </summary>
        /// <param name="connectstring">数据连接字符串</param>
        /// <param name="table">内存表数据</param>
        /// <param name="tableName">目标数据的名称</param>
        public static void AddByBluckCopy(string connectstring,DataTable table, string tableName)
        {
            if (table != null && table.Rows.Count > 0)
            {
                using (SqlBulkCopy bulk = new SqlBulkCopy(connectstring))
                {
                    bulk.BatchSize = 1000;
                    bulk.BulkCopyTimeout = 100;
                    bulk.DestinationTableName = tableName;
                    bulk.WriteToServer(table);
                }
            }
        }
    }

4. 性能测试的结论

　　根据上述的数据测试可以看出来，SqlBulkCopy在处理大数据量插入上速度非常快，甚至比付费的插件Z.EntityFramework.Extensions都要快，所以值得参考和推荐。

　　既然SqlBulkCopy解决大数据量的插入问题，那么删除和更新怎么办呢？ 详见  第二十三节: EF性能篇(三)之基于开源组件 Z.EntityFrameWork.Plus.EF6解决EF性能问题

!

• 作       者 : Yaopengfei(姚鹏飞)
• 博客地址 : http://www.cnblogs.com/yaopengfei/
• 声     明1 : 本人才疏学浅，用郭德纲的话说“我是一个小学生”，如有错误，欢迎讨论，请勿谩骂^_^。
• 声     明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，如需代码请留下你的评论，加我QQ：604649488 （备注：评论的博客名）