SQL Server将DataTable传入存储过程（Table Value Parameter）

博主在做毕业设计的时候，需要用到事务处理和多次将数据写入不同的表中，但是 SQL Server 数据库是不支持数组类型变量的，想要实现数组的功能，可以通过 XML 和数据表的方法实现，但是实现方法非常繁琐。

于是寻找其他更方便的解决方案，就发现了从 SQL Server 2008 开始，数据库开始支持“用户定义表类型”。通过它，可以实现将 .NET 程序构造的 DataTable 直接作为参数传入存储过程。

博主在运用的时候，遇到了些许问题，现在将它记录下来，以备今后查询。

 

 

1.首先确保数据库为 SQL Server 2008 及以上版本

 

2.新建一个表类型

CREATE TYPE dbo.AppFlag AS TABLE

(

ApplicationID int

,Flag int

)

这样就建立了一个表类型，可以在【数据库】 -> 【可编程性】 -> 【类型】 -> 【用户定义表类型】中查看。

 

3.新建一个存储过程

CREATE PROCEDURE [dbo].[SetAppFlag]

(

@RoleID int

,@AppFlagTable AppFlag READONLY    --这里类型就用之前建立的表类型名称(AppFlag)，切记必须加上READONLY，否则报错

)

AS

--将传递来的DataTable写入临时表#AppFlag

IF OBJECT_ID(N'tempdb..#AppFlag',N'U') IS NOT NULL

DROP Table #AppFlag

SELECT * INTO #AppFlag FROM @AppFlagTable

之后在存储过程中就可以想干嘛干嘛了

 

4.在 .NET 程序中构建 DataTable 并传递到存储过程

在这一步博主算是遇到了些问题，调试了一段时间。

因为博主对数据库的操作是用一个通用的数据库操作类，这个类，使用的是 DbCommand 这个基类，DbCommand.Parameters 中，只支持 DbType 类型。而我们需要的是 SqlCommand 这个继承于 DbCommand 的子类中的 SqlCommand.Parameters 的 SqlDbType 类型。就是这个问题，博主遇到了很多奇葩的报错。

博主使用的是 VB.NET ，要 C# 的转换下就好

'之前构造DataTable没有什么值得注意的地方

Dim SqlC As New SqlClient.SqlCommand

Dim Conn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("db").ConnectionString)

Conn.Open()

SqlC.Connection = Conn

SqlC.CommandType = CommandType.StoredProcedure

SqlC.CommandText = "SetAppFlag"

SqlC.Parameters.Add("@RoleID", SqlDbType.Int)

'这里后面必须带上类型，否则报错

SqlC.Parameters(0).Value = RoleID

SqlC.Parameters.Add("@AppFlagTable", SqlDbType.Structured)

'在要传递的DataTable后面，跟上SqlDbType.Structured类型

'博主发现使用DbType.Object类型会报错，所以不能使用博主的这个数据库操作类

SqlC.Parameters(1).Value = _DataTable

SqlC.ExecuteNonQuery()

Conn.Close()

SqlC.Parameters.Clear()

这样就可以直接传递 DataTable 了，其实也没什么难的…

转载于:https://www.cnblogs.com/donchen/p/6899377.html