ADO.NET巧妙批量插入数据

在导入量比较大的Excel到数据库的时候，常规采用的方法是获取到Excel中的数据，然后一条一条的插入到数据库，那么有没有不用循环直接传入数据到数据库里呢？答案是肯定的，本文就介绍在ADO.NET批量插入到SQL Server的巧妙方法。.

这个需要借助SQL Server的自定义类型和存储过程。SQL Server除了自带的类型，如int、varchar、char、bit等类型，还可以自定义类型，自定义类型分为自定义数据类型和自定义表类型，我们今天主要使用自定义表类型，自定义表类型有点像一个只读的表，它可以作为参数，使用SQL查询操作这个参数。本文讲述通过ADO.NET传递一个表类型到数据库，通过存储过程查询这个table类型参数。

实例

    需求是这样的，需要导入一个excel表格到数据库的客户表，导入成功后返回受影响的行数。步骤如下：

1、新建自定义表类型

    在sql server中新建一个自定义表类型，这个表类型包含两个字段，Id和客户编码，创建方法如下：

CREATE TYPE [dbo].[CustomerTable] AS TABLE(  Id int,  [CustomerCode] [varchar](50) NOT NULL)

2、新建一个插入的存储过程

存储过程主要处理传递过来的表类型，然后批量插入到数据库，如下：

CREATE PROC PR_CreateCustomer @Customer CustomerTable READONLY //自定义表类型，这必须加READONLYASBEGIN  INSERT INTO  Customer (CustomerId,CustomerCode) SELECT Id,CustomerCode FROM @CustomerEND

在存储过程中使用一句INSERT INTO SELECT就可以了，跟循环说再见了。

3、在.NET程序里使用ADO.NET执行存储过程

首先导入Excel表格，并转换成datatable，这里就不列出代码；然后用ADO.NET执行新建的存储过程，并且传入自定义表。代码如下：

SqlParameter P = new SqlParameter("@Customer", SqlDbType.Structured);P.Value = dataTable;//这里是个DataTable,里面保护Id和CustomerCode两个字段P.TypeName = "CustomerTable";//这里必须指定类型。int rel= SqlHelper.ExecuteNonQuery(new CommonConfig().ConnectionString, CommandType.StoredProcedure, "PR_CreateCustomer", P);//返回受影响的行数。如果大于0表示添加成功。

EF Core不支持存储过程，如果用EF的话也可以使用执行SQL的方法来执行存储过程。

SqlParameter P = new SqlParameter("@Customer", SqlDbType.Structured);P.Value = dataTable;P.TypeName = "CustomerTable";var rel = entity.Database.ExecuteSqlCommand("EXEC [dbo].[PR_CreateCustomer] @SalesKpidata", P);

上面代码分别介绍了ADO.NET和EF Core使用table类型巧妙地批量插入，大家可以亲自尝试一下。

结语

    本文讲述了不使用循环来插入数据，从上案例可以看出没有一处地方循环，非常方便，插入效率也非常高。这个方式也有局限性，如果项目中不建议使用存储过程的话就不适合了；另外mysql等其它数据库没有试过，不知道是否支持。 希望本文对大家学习和工作有一定参考价值，同时欢迎大家留言讨论，谢谢大家的支持。