使用DataTable作为存储过程的参数

曾经终败给现在 2022-01-06 06:37 260阅读 0赞

最近工作中写了几个存储过程，需要向存 储过程中传递字符串，因为SQL Server 2000中没有内置类似于 split 的函数，只好自己处理，将前台数据集中的一列用逗号拆分存到一个List中，再转化为字符串传给存储过程，很是麻烦。今天看了下SQL Server 2008的新特性，发现有表变量的使用，及其将DataTable作为参数的用法，就尝试了一下，简单谈谈心得。

一、测试环境

1、Windows Server 2008 R2 DataCenter

2、Visual Studio 2008 Team System With SP1

3、SQL Server 2008 Enterprise Edition With SP1

由于是SQL Server 2008新特性，所以只能用2008。

二、测试概述

测试项目很简单，就是添加新用户

![2010-01-25-11-16-09.jpg][]

三、准备数据

1、建立数据库、表、类型、存储过程

代码

<table style="border:1px dotted #cccccc;table-layout:fixed;"> 
 <tbody> 
 <tr> 
 <td>　　 1&nbsp;IF&nbsp;NOT&nbsp;EXISTS(SELECT&nbsp;*&nbsp;FROM&nbsp;dbo.sysobjects&nbsp;WHERE&nbsp;id&nbsp;=&nbsp;OBJECT_ID('Users')&nbsp;AND&nbsp;OBJECTPROPERTY(id,&nbsp;N'IsUserTable')&nbsp;=&nbsp;1) 　　2&nbsp;BEGIN 　　3&nbsp;CREATE&nbsp;TABLE&nbsp;dbo.Users 　　4&nbsp;( 　　 5&nbsp;UserID&nbsp;INT&nbsp;IDENTITY(-1,&nbsp;-1)&nbsp;NOT&nbsp;NULL, 　　 6&nbsp;UserName&nbsp;VARCHAR(20)&nbsp;NOT&nbsp;NULL, 　　7&nbsp;UserPass&nbsp;VARCHAR(20)&nbsp;NOT&nbsp;NULL, 　 　8&nbsp;Sex&nbsp;BIT&nbsp;NULL, 　　9&nbsp;Age&nbsp;SMALLINT&nbsp;NULL, 　　 10&nbsp;CONSTRAINT&nbsp;PK_Users_UserID&nbsp;PRIMARY&nbsp;KEY(UserID) 　　11&nbsp;) 　　 12&nbsp;END 　　 13&nbsp;IF&nbsp;NOT&nbsp;EXISTS(SELECT&nbsp;*&nbsp;FROM&nbsp;sys.table_types&nbsp;WHERE&nbsp;name&nbsp;=&nbsp;'UserTable'&nbsp;AND&nbsp;is_user_defined&nbsp;=&nbsp;1) 　　14&nbsp;BEGIN 　　15&nbsp;CREATE&nbsp;TYPE&nbsp;UserTable&nbsp;AS&nbsp;TABLE 　　16&nbsp;( 　 　17&nbsp;UserName&nbsp;VARCHAR(20)&nbsp;NOT&nbsp;NULL, 　　 18&nbsp;UserPass&nbsp;VARCHAR(20)&nbsp;NOT&nbsp;NULL, 　　19&nbsp;Sex&nbsp;BIT&nbsp;NULL, 　　 20&nbsp;Age&nbsp;SMALLINT&nbsp;NULL 　　21&nbsp;) 　　22&nbsp;END 　　23&nbsp;GO 　　24 　　 代码 　　 1&nbsp;IF&nbsp;EXISTS(SELECT&nbsp;*&nbsp;FROM&nbsp;dbo.sysobjects&nbsp;WHERE&nbsp;id&nbsp;=&nbsp;OBJECT_ID('sp_InsertSingleUser')&nbsp;AND&nbsp;OBJECTPROPERTY(id,&nbsp;N'IsProcedure')&nbsp;=&nbsp;1) 　　2&nbsp;BEGIN 　　3&nbsp;DROP&nbsp;PROCEDURE&nbsp;dbo.sp_InsertSingleUser 　　 4&nbsp;END 　　5&nbsp;GO 　　6&nbsp;CREATE&nbsp;PROCEDURE&nbsp;dbo.sp_InsertSingleUser 　　 7&nbsp;( 　　8&nbsp;@User&nbsp;UserTable&nbsp;READONLY 　　9&nbsp;) 　　10&nbsp;AS 　　11 　 　12&nbsp;SET&nbsp;XACT_ABORT&nbsp;ON 　　13&nbsp;BEGIN&nbsp;TRANSACTION 　　14 　　 15&nbsp;INSERT&nbsp;INTO&nbsp;dbo.Users(UserName,&nbsp;UserPass,&nbsp;Sex,&nbsp;Age) 　　 16&nbsp;SELECT&nbsp;UserName,&nbsp;UserPass,&nbsp;Sex,&nbsp;Age&nbsp;FROM&nbsp;@User 　　17 　　 18&nbsp;COMMIT&nbsp;TRANSACTION 　　19&nbsp;SET&nbsp;XACT_ABORT&nbsp;OFF 　　20&nbsp;GO</td> 
 </tr> 
 </tbody> 
</table>

前台搭建好表单，后台主要是一个函数：

代码

<table style="border:1px dotted #cccccc;table-layout:fixed;"> 
 <tbody> 
 <tr> 
 <td>　　 1&nbsp;public&nbsp;void&nbsp;fnInsertSingleUser(DataTable&nbsp;v_dt) 　　2&nbsp;{ 　　3&nbsp;try 　 　4&nbsp;{ 　　5&nbsp;SqlConnection&nbsp;cn&nbsp;=&nbsp;new&nbsp;SqlConnection(CONN); 　　 6&nbsp;SqlCommand&nbsp;cmd&nbsp;=&nbsp;cn.CreateCommand(); 　　 7&nbsp;cmd.CommandType&nbsp;=&nbsp;CommandType.StoredProcedure; 　　 8&nbsp;cmd.CommandText&nbsp;=&nbsp;@"sp_InsertSingleUser"; 　　 9&nbsp;SqlParameter&nbsp;p&nbsp;=&nbsp;cmd.Parameters.AddWithValue("@User",&nbsp;v_dt); 　　10 　 　11&nbsp;DataSet&nbsp;ds&nbsp;=&nbsp;new&nbsp;DataSet(); 　　 12&nbsp;SqlDataAdapter&nbsp;da&nbsp;=&nbsp;new&nbsp;SqlDataAdapter(cmd); 　　13&nbsp;da.Fill(ds); 　 　14&nbsp;} 　　15&nbsp;catch&nbsp;(Exception&nbsp;ex) 　　16&nbsp;{ 　　17&nbsp;throw&nbsp;ex; 　　 18&nbsp;} 　　19&nbsp;}</td> 
 </tr> 
 </tbody> 
</table>

点击【添加】按钮时调用存储过程。测试是完成了，也很简单，传递一个DataTable做参数确实很方便吧，能够轻松完成原先需要很多编码的工作。关于 表变量还是有些道道的，如创建时判断其是否存在的语句，删除表变量前需要先删除引用表变量的存储过程等。一般开发我大多会选择用临时表，处理起来比较方 便，表变量可以作为存储过程参数确实是一个独特的优势，希望在SQL Server的未来版本中能够继续增强对表变量和临时表的支持，尤其是早日支持临时表调试。

使用DataTable作为存 储过程的参数

原文出 处：http://www.cnblogs.com/brooks-dotnet/archive/2010/01/24/1655419.html

转载于:https://www.cnblogs.com/zhangchenliang/archive/2010/01/29/1659533.html

[2010-01-25-11-16-09.jpg]: http://ttcma.techtarget.com.cn/UpLoad/ArticlePic/2010-01-25-11-16-09.jpg