厦门安能建设公司网站,用数据库代码做家乡网站,应用公园制作app下载,企业网站建设公司 末路在SQL Server 2005或更早的版本中的中#xff0c;表变量是不能作为存储过程的参数的。当多行数据到SQL Server需要发送多行数据到SQL Server #xff0c;开发者要么每次发送一列记录#xff0c;或想出其他的变通方法#xff0c;以满足需求。虽然在.net 2.0中提供了个SQLBul…在SQL Server 2005或更早的版本中的中表变量是不能作为存储过程的参数的。当多行数据到SQL Server需要发送多行数据到SQL Server 开发者要么每次发送一列记录或想出其他的变通方法以满足需求。虽然在.net 2.0中提供了个SQLBulkCopy对象能够将多个数据行一次性传送给SQL Server但是多行数据仍然无法一次性传给存储过程。SQL Server 2008中的T-SQL功能新增了表值参数。利用这个新增特性我们可以很方便地通过T-SQL语句或者通过一个应用将一个表作为参数传给存储过程。1、用户自定义表类型当第一次看看新的表值参数我认为使用此功能有点复杂。有几个步骤。要做的第一件事是定义表型。在Management Studio 2008中的“Programmability”“Type”节点您可以看到“User-Defined Table Types(用户自定义表类型)”如图1所示 。图 1用户自定义表类型点击右键在弹出菜单中选择“新用户定义的表型... ” 会新建一个模板中的查询窗口如图2所示 。图2用户自定义表类型创建语句点击“Specify Values for Template Parameters(指定值为模板参数)”按钮将探出一个对话框如图3所示。图 3指定模板参数列的数值在填写在适当的数值之后点击确定按钮一个“CREATE TYPE”的声明取代了范本。这时你也可以手动增加一些列或者增加一些限制条件最后点击确定按钮。以下是最终的代码-- -- Create User-defined Table Type-- USE TestGO-- Create the data typeCREATE TYPE dbo.MyType AS TABLE(col1 int NOT NULL,col2 varchar(20) NULL,col3 datetime NULL,PRIMARY KEY (col1))GO在运行代码之后对象的定义就建立好了你可以在“User-Defined Table Type(用户自定义表类型”中查看属性如图4所示但没法修改它们。如果要修改的类型你只能将其删除然后按照修改后的属性再次创建它。图4查看用户自定义表类型的属性2、使用用户自定义的表类型如果打算在T-SQL代码中使用您还必须创建一个新类型的变量然后将具体的表的名称赋值给该变量。一旦赋值后您可以在其他的T-SQL语句中使用它。因为它是一个变量在批处理完成后它也自动失效结束生命周期。请注意下面的代码MyType是我们之前刚刚创建的数据类型。DECLARE MyTable MyTypeINSERT INTO MyTable(col1,col2,col3)VALUES (1,abc,1/1/2000),(2,def,1/1/2001),(3,ghi,1/1/2002),(4,jkl,1/1/2003),(5,mno,1/1/2004)SELECT * FROM MyTable在变量的有效范围内你可以象操作正常的表一样来操作这个变量如与另一个表象关联或者将变量中的记录填充到另一个表。对于表变量来说你无法修改表定义。正如前面提到的变量不能超出它的有效的范围。如果T-SQL脚本由多个批处理组成变量只有在批处理内才能创建并有效使用。3、使用变量作为参数到目前为止我们还没有看到经常表变量无法实现的功能。其好处是能够将变量作为参数传给存储过程。当然一个存储过程必须先建立使用新的类型作为其中的一个参数。下面这个例子通过代码创建一个常规表并对其填充记录。USE [Test]GOCREATE TABLE [dbo].[MyTable] ([col1] [int] NOT NULL PRIMARY KEY,[col2] [varchar](20) NULL,[col3] [datetime] NULL,[UserID] [varchar] (20) NOT NULL)GOCREATE PROC usp_AddRowsToMyTable MyTableParam MyType READONLY,UserID varchar(20) ASINSERT INTO MyTable([col1],[col2],[col3],[UserID])SELECT [col1],[col2],[col3],UserIDFROM MyTableParamGO请注意表值参数后面带了个READONLY参数。这是必需的不能在例程体中对表值参数执行诸如 UPDATE、DELETE 或 INSERT 这样的 DML 操作。最后我们对创建表值变量对变量进行赋值并调用存储过程。DECLARE MyTable MyTypeINSERT INTO MyTable(col1,col2,col3)VALUES (1,abc,1/1/2000),(2,def,1/1/2001),(3,ghi,1/1/2002),(4,jkl,1/1/2003),(5,mno,1/1/2004)EXEC usp_AddRowsToMyTable MyTableParam MyTable, UserID KathiSELECT * FROM MyTable为了让用户使用自定义表类型执行或控制权限必须是理所当然的。以下是授权命令GRANT EXECUTE ON TYPE::dbo.MyType TO TestUser;4、通过.net应用程序调用表值参数这一特性最大的亮点在于可以在.net应用中使用表值参数。为了做到这一点你必须要先安装.NET 3.5框架并确保应用程序中已经引用了 System.Data.SqlClient命名空间。创建表值参数时需要用到一些新的SQL数据类型(如DataTable、DataColumn等)。首先创建一个本地数据表并插入一些记录。肯定的是 DataTable中创建符合用户定义的表型的列计数和数据类型。Create a local tableDim table As New DataTable(temp)Dim col1 As New DataColumn(col1, System.Type.GetType(System.Int32))Dim col2 As New DataColumn(col2, System.Type.GetType(System.String))Dim col3 As New DataColumn(col3, System.Type.GetType(System.DateTime))table.Columns.Add(col1)table.Columns.Add(col2)table.Columns.Add(col3)Populate the tableFor i As Integer 20 To 30Dim vals(2) As Objectvals(0) ivals(1) Chr(i 90)vals(2) System.DateTime.Nowtable.Rows.Add(vals)Next我们在代码中采用存储过程创建一个命令对象并新增两个参数。代码如下图所示Create a command object that calls the stored procDim command As New SqlCommand(usp_AddRowsToMyTable, conn)command.CommandType CommandType.StoredProcedureCreate a parameter using the new typeDim param As SqlParameter command.Parameters.Add(MyTableParam, SqlDbType.Structured)command.Parameters.AddWithValue(UserID, Kathi)请注意 MyTableParam参数的数据类型(SqlDbType.Structured)这是.Net 3.5中新增的功能。最后将当地表赋值给表值参数并执行该命令。Set the value of the parameterparam.Value tableExecute the querycommand.ExecuteNonQuery()5、小结SQL Server 2008中新增的表值参数特性减少了应用程序与SQL Server服务器之间的交互提升了程序性能。
