SqlBulkCopy与临时表、普通Sql操作配合使用

       浙江恒生长运网络科技有限公司 苟安廷

        用C#开发时，经常会配套使用Sql Server数据库，也经常希望将DataTable中的数据直接插入的数据库表中，但Sql Server数据库中并没有DataTable这种数据类型，要实现这个目的，最直观的方法就是拼Sql语句比如:

Insert into 数据库表(字段1,字段2….字段n)

Select ‘C1’,’C2’…’Cn’

union all

Select ‘C1’,’C2’…’Cn’

……

      也可以写个通用方法,将DataTable转换成XML格式的字符串,然后通过值类型参数(nvarchar(max))将xml传递到数据中,在数据库里面还原成临时表,经二次加工后再插入到正式表。

      以上两种方法都不方便，而且效率不高。

       说到这里，大家都马上想到了更专业的方法：利用SqlBulkCopy，操作简便，更关键的是性能杠杠的，经本人实测，插入4000多条记录时，速度比xml方式快15倍以上。但SqlBulkCopy也有其致命弱点，那就用途单一，只能用来插入数据，其他的简单sql操作都进行不了，比如创建临时表、做一些简单计算、调用存储过程等，因此，如果将SqlBulkCopy和普通Sql操作结合起来就完美了，我们的设想业务逻辑顺序是这样的：

1. 在软件里面准备准备好多个DataTable，这些存放了业务相关数据，可能需要插入正式表，也会做进一步加工
2. 在数据库里面创建临时表，用于存放DataTable中的数据
3. 用SqlBulkCopy将DataTable中的数据发到数据库临时表中
4. 在数据库中进行必要的处理，并更新到正式表

由于临时表在断开连接后，就会被自动释放(关于临时表的生命周期请参考相关资料,简单说,断开后自动删除、同一个连接内可以共享)，没办法和SqlBulkCopy “共享”，正因为临时表是连接断开后才会被自动释放的，因此，最自然的方式就是连接先不要断开，而是和SqlBulkCopy共享一个连接,那么,问题自然而然就解决了。

测试流程如下:

1. 我们在数据库中准备一张表：

CREATE TABLE 测试表(UserName NVARCHAR(50),Age INT)

1. 软件里面定义一个DataTable,结构和测试表完全一样,并模拟100条数据

如果字段名不一样,需要通过映射关系进行对应: sqlBulkCopy.ColumnMappings.Add(DataTable中的字段名,数据库表中的字段名))

1. 创建共享连接
2. 通过普通sql语句，创建临时表结构,注意,where 1=2永远不成立,故只会创建一个空的临时表,和正式表结构完全一样
3. 通过SqlBulkCopy把数据传到临时表中
4. 在数据库中,进行二次加工,最后更新到正式库
5. 关闭连接

     完整代码如下： 

            //1.模拟数据，注意表结构

            var tb=new DataTable();

            tb.Columns.Add("UserName", typeof(string));

            tb.Columns.Add("Age", typeof(int));

            for (var i = 0; i < 1000; i++)

                tb.Rows.Add($"姓名:{i}", 10 + i);

            //2.创建连接,供普通Sql操作和SqlBulkCopy共享,为简化代码,异常就不捕获了,实际生产时必须处理异常

            var connection = new SqlConnection(_connStr);

            connection.Open();

            //3.根据正式表复制临时表结构,注意where语句，可以一次性创建多个临时表

            var strSql = "select * into #temp from 测试表 WHERE 1=2";

            var command=new SqlCommand(strSql,connection);

            command.ExecuteNonQuery();

            //4.传输数据，可以重复执行，将每种业务数据都写入临时表

            var sqlBulkCopy = new SqlBulkCopy(connection)

            {

                DestinationTableName = "#temp",//目标表是临时表,而不是正式表

                BatchSize=tb.Rows.Count

            };

            sqlBulkCopy.WriteToServer(tb);

            //5.进行二次加工,当然,也可以调用存储过程(存储过程内部仍然可以使用刚才创建的临时表,注意,不要和存储过程自己定义的临时表重名了),比如,command.CommandText = "Exec myProcess @Name='张三'";

            command.CommandText = "insert into 测试表 select * from #temp";

            command.ExecuteNonQuery();

            //6.最后关闭连接

            connection.Close();

说白了，本的核心就是共享数据库连接，间接实现普通Sql操作和SqlBulkCopy协同，发挥各自的长处。