MS SQL Server 2008 新特性 - User-Defined Table Type 实现将.NET DataTable作为参数传入数据库

       在MS SQL Server 2008之前,我们无法直接在.NET程式中直接将一个DataTable当作参数传入到存储过程.

现在在MS SQL Server 2008可以完全实现这个功能了!其中完成这个功能的主要对象叫 User-Defined Table Type 自定义表类型

以下将展示一个Demo.

      主要实现功能:在.NET 中生成一个DataTable,将这个Datatable作为参数传入到MS SQL Server 2008 

中的事先已经生成好的使用User-Defined Table Type作为参数的存储过程中.然后查询这个传入的Datatable,返回结果展示在页面上.

不多说,直接上Demo:

T-SQL 脚本:

IF EXISTS ( SELECT  A = 1
            FROM    sys.objects
            WHERE   name = 'GetUserInfo_TEST '
                    AND type = 'P' ) 
    DROP PROCEDURE dbo.GetUserInfo_TEST
 
go
 
IF EXISTS ( SELECT  A = 1
            FROM    sys.table_types
            WHERE   name = 'UserInfo_TEST '
                    AND is_user_defined = 1 ) 
    DROP TYPE dbo.UserInfo_TEST


go

CREATE TYPE dbo.UserInfo_TEST AS TABLE
(
Code  CHAR(8)  NOT NULL ,
Name  CHAR(50) NULL ,
PRIMARY KEY ( EmpCode)
)


go


CREATE PROCEDURE dbo.GetUserInfo_TEST
    (
      @User AS dbo.UserInfo_TEST READONLY
    )
AS 
    BEGIN
	
        SELECT  *
        FROM    @User
	
    END

go

VB.NET代码:(有兴趣的看官可以转为C#)

   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim dr As SqlDataReader
        Dim cmd As SqlCommand
        Dim dt As DataTable = New DataTable()
        dt.Columns.Add(New DataColumn("Code", Type.GetType("System.String")))
        dt.Columns.Add(New DataColumn("Name", Type.GetType("System.String")))

        Dim row As DataRow = dt.NewRow
        row(0) = "110"
        row(1) = "Police"
        dt.Rows.Add(row)
        Dim conn As SqlConnection = New SqlConnection("Data Source=MyDS;Initial Catalog=MyDB;Trusted_Connection=True;MultipleActiveResultSets=True")
        Using (conn)
            cmd = New SqlCommand()
            cmd.Connection = conn
            If conn.State = ConnectionState.Closed Then conn.Open()
            cmd.CommandText = "dbo.GetUserInfo_TEST"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@User", SqlDbType.Structured)
            cmd.Parameters(0).Value = dt
            dr = cmd.ExecuteReader
            While dr.Read
                Response.Write(dr(0).ToString)
            End While
        End Using

    End Sub

注意这个功能并非万能,各位使用中需注意使用场景:
当数据量过大时,这种方式并不适用,请使用sql bulk insert的方式插入到数据库再另作操作.

另外在使用中Datable不是强类型,与User Defined Table Type (其实是Structure) 中成员的对应关系处理不当容易引发数据类型/长度不匹配的错误.所以在传入参数时要注意传入数据的合法性验证.