浙江恒生长运网络科技有限公司 苟安廷
用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操作结合起来就完美了,我们的设想业务逻辑顺序是这样的:
- 在软件里面准备准备好多个DataTable,这些存放了业务相关数据,可能需要插入正式表,也会做进一步加工
- 在数据库里面创建临时表,用于存放DataTable中的数据
- 用SqlBulkCopy将DataTable中的数据发到数据库临时表中
- 在数据库中进行必要的处理,并更新到正式表
由于临时表在断开连接后,就会被自动释放(关于临时表的生命周期请参考相关资料,简单说,断开后自动删除、同一个连接内可以共享),没办法和SqlBulkCopy “共享”,正因为临时表是连接断开后才会被自动释放的,因此,最自然的方式就是连接先不要断开,而是和SqlBulkCopy共享一个连接,那么,问题自然而然就解决了。
测试流程如下:
- 我们在数据库中准备一张表:
CREATE TABLE 测试表(UserName NVARCHAR(50),Age INT)
- 软件里面定义一个DataTable,结构和测试表完全一样,并模拟100条数据
如果字段名不一样,需要通过映射关系进行对应: sqlBulkCopy.ColumnMappings.Add(DataTable中的字段名,数据库表中的字段名))
- 创建共享连接
- 通过普通sql语句,创建临时表结构,注意,where 1=2永远不成立,故只会创建一个空的临时表,和正式表结构完全一样
- 通过SqlBulkCopy把数据传到临时表中
- 在数据库中,进行二次加工,最后更新到正式库
- 关闭连接
完整代码如下:
//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协同,发挥各自的长处。