您所在的位置： &
SQL Server 2008中使用DataTable作为存储过程的参数
SQL Server 2008中使用DataTable作为存储过程的参数
本文我们主要介绍了SQL Server 2008中使用DataTable作为存储过程的参数的相关知识，并通过一个一个实际的例子进行测试，希望能够对您有所帮助。
SQL Server 2008中使用DataTable作为存储过程的参数的相关知识是本文我们主要要介绍的内容，笔者在最近工作中写了几个存储过程，需要向存储过程中传递字符串，因为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。
二、测试概述
测试项目很简单，就是添加新用户
498)this.width=498;' onmousewheel = 'javascript:return big(this)' alt="SQL Server 2008中使用DataTable作为存储过程的参数" src="/files/uploadimg/1580.jpg" />
三、准备数据
建立数据库、表、类型、存储过程
IF&&EXISTS&(SELECT&*&FROM&sys.objects&WHERE&object_id&=&OBJECT_ID(N'[dbo].[Users]')&AND&type&in&(N'U')) &DROP&TABLE&[dbo].[Users] &CREATE&TABLE&[dbo].[Users]( &&[UserID]&[int]&IDENTITY(-1,-1)&NOT&NULL, &&[UserName]&[varchar](20)&NOT&NULL, &&[UserPass]&[varchar](20)&NOT&NULL, &&[Sex]&[bit]&NULL, &&[Age]&[smallint]&NULL, &&CONSTRAINT&[PK_Users_UserID]&PRIMARY&KEY&CLUSTERED& &( &[UserID]&ASC &)WITH&(PAD_INDEX&&=&OFF,&STATISTICS_NORECOMPUTE&&=&OFF,&IGNORE_DUP_KEY&=&OFF,&ALLOW_ROW_LOCKS&&=&ON,&ALLOW_PAGE_LOCKS&&=&ON)&ON&[PRIMARY] &)&ON&[PRIMARY] &&IF&&EXISTS&(SELECT&*&FROM&sys.types&st&JOIN&sys.schemas&ss&ON&st.schema_id&=&ss.schema_id&WHERE&st.name&=&N'UserTable'&AND&ss.name&=&N'dbo') &DROP&TYPE&[dbo].[UserTable] &CREATE&TYPE&[dbo].[UserTable]&AS&TABLE( &&[UserName]&[varchar](20)&NOT&NULL, &&[UserPass]&[varchar](20)&NOT&NULL, &&[Sex]&[bit]&NULL, &&[Age]&[smallint]&NULL &) &&IF&&EXISTS&(SELECT&*&FROM&sys.objects&WHERE&object_id&=&OBJECT_ID(N'[dbo].[sp_InsertSingleUser]')&AND&type&in&(N'P',&N'PC')) &DROP&PROCEDURE&[dbo].[sp_InsertSingleUser] &GO &CREATE&PROCEDURE&[dbo].[sp_InsertSingleUser]& &(& &@User&UserTable&READONLY& &)& &AS& &SET&XACT_ABORT&ON& &BEGIN&TRANSACTION& &INSERT&INTO&dbo.Users(UserName,&UserPass,&Sex,&Age)& &SELECT&UserName,&UserPass,&Sex,&Age&FROM&@User& &COMMIT&TRANSACTION& &前台搭建好表单，后台主要是一个函数： &&代码 &&public&void&fnInsertSingleUser(DataTable&v_dt)& &{& &try& &{& &SqlConnection&cn&=&new&SqlConnection(CONN);& &SqlCommand&cmd&=&cn.CreateCommand();& &mandType&=&CommandType.StoredP& &mandText&=&@&sp_InsertSingleUser&;& &SqlParameter&p&=&cmd.Parameters.AddWithValue(&@User&,&v_dt);& &&DataSet&ds&=&new&DataSet();& &SqlDataAdapter&da&=&new&SqlDataAdapter(cmd);& &da.Fill(ds);& &}& &catch&(Exception&ex)& &{& &throw&& &}& &}&
点击【添加】按钮时调用存储过程。测试是完成了，也很简单，传递一个DataTable做参数确实很方便吧，能够轻松完成原先需要很多编码的工 作。关于表变量还是有些道道的，如创建时判断其是否存在的语句，删除表变量前需要先删除引用表变量的存储过程等。
一般开发我大多会选择用临时表，处理起来 比较方便，表变量可以作为存储过程参数确实是一个独特的优势，希望在SQL Server的未来版本中能够继续增强对表变量和临时表的支持，尤其是早日支持临时表调试。
关于SQL Server 2008数据库中使用DataTable作为存储过程的参数的相关知识就介绍到这里了，希望本次的介绍能够对您有所收获！
【编辑推荐】
【责任编辑： TEL：（010）】
关于&&的更多文章
SQL Server 2008提供了全民啊行的空间支持，但同时空间分析是一
数据库产品
数据库综合
数据库新闻
维基百科将切换到另外一款开源数据库MariaDB
SQL Server 2008提供了全民啊行的空间支持，但同时空
你的SQL Server代码安全吗？请你与我一起跟随作者来探
为什么会发生死锁？如何利用SQL Server Profiler分析
在本书中，Thomas ERL呈现了第一部端对端的教程，提供了从基层开始的面向服务的建模与设计的逐步指导。通过逐步的、清晰生动的、
51CTO旗下网站导读：本文主要涉及EncryptByPassPhrase和DecryptByPassPhrase函数进行通行短语（PassPhrase）加密。
&&&&& 在SQL Server 2005和SQL Server
2008之前。如果希望加密敏感数据，如财务信息、工资或身份证号，必须借助外部应用程序或算法。SQL Server
2005引入内建数据加密的能力，使用证书、密钥和系统函数的组合来完成。
&&& 与数字证书类似。SQL Server 证书包括公钥和私钥这一对密钥，它们都用来加密和解密数据。SQL
Server也拥有创建非对称密钥和对称密钥对象的能力。非对称密钥（asymmetric
key）与证书相似，公钥用来加密数据库，私钥用来解密数据。非对称密钥和证书都提供了强大的加密强度。但在完成复杂的加密|解密过程中具有更多的性能开销。更适合对大量数据进行加密，且具有较低性能开销的解决方案是对称密钥（symmetric
key），它是对相同数据进行加密和解密的一个密钥。
&&& SQL Server允许将这些加密能力放到加密层次结构中。当安装了SQL
Server后，在数据库master中创建名为服务主密钥的服务器级别证书，并将其默绑定到SQL
Server服务账号登录名。服务主密钥用来加密所有其他数据库证书和创建在SQL
Server实例中的密钥。另外，你也可以在用户数据库中创建数据库主密钥（Database Master Key）,它可以用来加密数据库证书和密钥。
&&& 在SQL Server
2008中，微软引入了透明数据加密(TDE),它对整个数据库进行加密，而不需要修改任何访问它的应用程序。数据、日志文件和相关的数据库备份都是加密的。假如数据库被偷，如果没有数据库加密密钥（DEK）是不能访问数据的。本文及后面几篇文章将会举例说明。
&&& 在SQL Server 2008中，还引入了对可扩展密钥管理（EKM）的支持，也就意味着SQL
Server可以使用硬件安全模块（HSM）来存储和和管理加密密钥。HSM可以减少数据和实际的加密密钥耦合。
此部分内容共分六篇文章：
1、通过PassPhrase加密
3、非对称密钥加密
4、对称密钥加密
5、证书加密
6、透明数据加密
一、通过通行短语(PassPhrase)加密
对于不涉及证书及密钥的应急的数据加密，可以直接基于用户提供的密码来加密和解密数据。通行短语(PassPhrase)是允许存在空格的密码。这个PassPhrase不会存储在数据库中，因而也就意味着不会被使用存储的系统数据&破解&。同时，可以使用空格创建一个长的、易于记忆的句子来加密和解密敏感数据。
我们需要了解的一对函数是ENCRYPTBYPASSPHRASE（）和DECRYPTBYPASSPHRASE（）
&&& 这一对函数必须使用相同的参数。
&&& 我们看一个示例：
use DemoDB2008r2go-- Table used to store the encrypted data-- for the purposes of this recipeCREATE TABLE #SecretInfo(MySecret varbinary(max) NOT NULL)GOINSERT #SecretInfo (MySecret)SELECT EncryptByPassPhrase('My Password Used To Encrypt This String in 2008.','我把公司的服务器数据库密码记在那个绿色的小本子上了。')SELECT MySecret FROM #SecretInfo/*通常的查询结果：0xCCBC800C6B5CECE3F211FB975C2F8B681CC8301DDE74CF783C6A59316C2BAC08D3E2FDE01CD1D5984727DEE4003A3EFDDE505D1A4*/
/************想知道原文本的内容，使用以下语句：--------**********************/SELECT CAST(DecryptByPassPhrase('My Password Used To Encrypt This String in 2008.',MySecret) as varchar(max))FROM #SecretInfo
1、使用通行短语进行加密数据，不用担心sysadmin服务器角色成员读取数据（在后面文章会看到，服务器角色成员sysadmin拥有读取其他形式的加密数据的内在权限。)
2、假定没有将密码存储在表中或在任何模块（存储过程、触发器等）中使用密码，加密的数据将防止从数据库备份被偷窃或在SQL
Server实例中的数据库中渗透。如果通行短语没有正确共享，数据就可以被解密。
阅读(...) 评论()1.将SQL Server 2000的MDF数据库文件拷贝到SQL Server 2008安装目录\MSSQL10.MSSQLSERVER\MSSQL\DATA
2.运行SQL Server Management Studio登入SQL Server2008，使用附加数据库功能
3.如果只有MDF文件，没有LDF文件则在附加数据库窗体中使用删除功能删除找不到的LDF列表项
&点击确定后即将数据库从2000升级到2008了
阅读(...) 评论()ASP.NET向SQL Server导入文件主要用到FileUpload控件的FileBytes属性。该属性从FileUpload控件所指定的文件返回一个字节数组 。 1.数据库准备 为了方便大家能够理解，这里我
ASP.NET向SQL Server导入文件主要用到FileUpload控件的FileBytes属性。该属性从FileUpload控件所指定的文件返回一个字节数组 。 1.数据库准备 为了方便大家能够理解，这里我们只设计两个字段，一个是文件类型字段，字段名为FileType，另一个是存放文件内容字段，字段名为FileContent。创建数据库 ，数据库名为VarFile，语句如下： CREATE DATABASE VARFILE GO 创建表，表名为FileInOut,语句如下： USE VARFILE GO CREATE TABLE FILEINTOU ( FileType nvarchar(30) not null, FileContent varbinary(max) null ) 2.添加控件 运行VS2008并新建一个网站，在页面Default.aspx中添加一个FileUpload控件，ID 为FileUpload1.同时添加三个Button按钮，ID分别为fileUp和fileLoad。Text属性分别设置为“上传文件”和“下载文件”。 3.添加代码 (1)添加命名空间，因为和SQL Server数据库连接，所以添加using System.Data.Sqlclient和using System.Data命名空间。又因为要设置输出流的HTTP的字符集为"gb2312"字符编码，所以添加using System.Text命名空间。同时又因为要把导出文件强类型化为字符串，所以添加using System.Collections.Specialized命名空间。 (2)添加“上传文件”按钮的事件代码。当单击“上传文件”按钮后，获取FileUpload控件所选择的文件的文件类型以及文件的字节数组插入数据库中。切换到设计视图，双击“上传文件”按钮，添加"上传文件"按钮事件代码，代码如下： 复制代码 代码如下: protected void fileUp_Click(object sender,EventArgs e) { if(FileUpload1.FileName==string.Empty) { Response.Write("&script&altert(‘请选择要上传的文件')&/script&");
} string mailto:connstr=@%22Data Source=69F\SQL2008;Initial Catalog=VarFIntegrated Security=Ture"; //数据库连接字符串 string the Selected=FileUpload1.FileN //获取上传文件的后缀名 string extension=theSelected.Substring(theSelected.LastIndexOf(".")).ToLower(); if(CheckFileType(extension)) //如果有指定的文件类型 { string contentType=GetContentType(extension); string sqlstr="insert into FileInOut values(@FileType,@FileCount)"; //上传文件的SQL语句 string sqlstrclear="truncate table FileInOut"; //清空数据库SQL语句 SqlConnection con=new SqlConnection(connstr); //实例化数据库连接对象 SqlCommand cmd=new SqlCommand(sqlstr,con); //实例化上传文件SQL命令 SqlCommand cmdclear=new SqlCommand(sqlstrclear,con); //实例化清空数据库SQL命令 //定义问价类型参数 cmd.Parameters.Add(new SqlParameter("@FileType”,SlqDbType.NvarChar,30))； cmd.Parameters["@FileType"].Value=contentT //定义文件内容参数 cmd.Parameters.Add(new SqlParameter("@FileCount",SqlDbType.NVarChar,30)); //将文件转化为字节数组作为@FileCount的值 cmd.Parameters["@FileCount"].Value=FileUpload1.FileB con.Open(); cmdclear.ExecuteNonQuery(); //执行清空数据库命令 cmd.ExecuteNonQuery(); //执行上传文件命令 } }
(3)添加获取文件类型和获得文件导出方式的函数方法。首先查看所要上传文件类型是否在指定问价类型内，如果在，则可以直接导入文件，然后根据文件类型 获取此文件导出方式并存放在FileType字段中，代码如下： 复制代码 代码如下: public static bool CheckFileType(string type) { StringDictionary sd=new StringDictionary(); //实例化集合StringDictionary类 sd.Add(".doc","application/msword"); sd.Add(".ppt","application/vnd.ms-powerpoint"); sd.Add(".xsl","application/vnd.ms-excel"); sd.Add(".rtf","application/msword"); sd.Add(".html","text/html"); sd.Add(".htm","text/html"); sd.Add(".txt","text/plain"); sd.Add(".pdf","application/pdf"); return sd.ContainsKey(type); //确定StringDictionary是否包含特定键 } public static string GetContentType(string extension) //获取输出文件方式 {StringDictionary sd=new StringDictionary(); sd.Add(".doc","application/msword"); sd.Add(".ppt","application/vnd.ms-powerpoint"); sd.Add(".xsl","application/vnd.ms-excel"); sd.Add(".rtf","application/msword"); sd.Add(".html","text/html"); sd.Add(".htm","text/html"); sd.Add(".txt","text/plain"); sd.Add(".pdf","application/pdf"); return sd[extension]; //返回对应键的值 }
(4)上传文件，选择一个pdf文件，单击"上传文件"按钮后，打开数据库中的FileInOut表，如图所示可以看到。3682人阅读
&当一个用户会话（会话1）已经锁定了一个资源，而另一个会话（会话2）想要修改该资源，并且会话2也锁定了会话1想要修改的资源时，就会出现“死锁”(deadlocking)。在另一方释放资源前，会话1和会话2都不可能继续。所以，SQL Server会选择死锁中的一个会话作为“死锁牺牲品”。注意：死锁牺牲品的会话会被杀死，事务会被回滚。注意：死锁与正常的是两个经常被混淆的概念。发生死锁的一些原因：1、应用程序以不同的次序访问表。例如会话1先更新了客户然后更新了订单，而会话2先更新了订单然后更新了客户。这就增加了死锁的可能性。2、应用程序使用了长时间的事务，在一个事务中更新很多行或很多表。这样增加了行的“表面积”，从而导致死锁冲突。3、在一些情况下，SQL Server发出了一些行锁，之后它又决定将其升级为表锁。如果这些行在相同的数据页面中，并且两个会话希望同时在相同的页面升级锁粒度，就会产生死锁。一、使用 SQL Server Profiler 分析死锁&二、使用跟踪标志位找出死锁本文主要介绍使用DBCC TRACEON、DBCC TRACEOFF和DBCC TRACESTATUS命令来确保死锁被正确记录到SQL Server Management Studio SQL日志中。这些命令用来启用、关闭、和检查跟踪标志位的状态。■&DBCC TRACEON，启用跟踪标志位。用法：DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]详细参看 MSDN:■&DBCC TRACESTATUS，检查跟踪标志位状态。用法：DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] ) [ WITH NO_INFOMSGS ]详细参看 MSDN:■&DBCC TRACEOFF，关闭跟踪标志位。用法：DBCC TRACEOFF (trace# [ ,...n ] [ , -1 ] ) [ WITH NO_INFOMSGS ]详细参看 MSDN:下面我们模拟一个死锁：在第一个SQL查询窗口执行：use AdventureWorks
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE VendorID = 90
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE VendorID = 91
COMMIT TRAN
在第二个查询窗口执行：use AdventureWorks
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE VendorID = 91
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE VendorID = 90
COMMIT TRAN
等待几秒后，其中一个查询窗口会提示：/*
Msg 1205, Level 13, State 51, Line 9
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
*/ 此时，查看，SQL Server Management Studio的SQL 日志，发现死锁事件没有被记录。打开第三个查询窗口，执行：DBCC TRACEON (1222, -1)
DBCC TRACESTATUS
为了模拟另一个死锁，将重启动“胜利”的那个连接查询（没有被杀死的那个），然后重启死锁丢失的会话，几秒后又出现另一个死锁了。死锁发生后，停止另一个执行的查询。现在，SQL Server Management Studio的SQL 日志中包含了死锁事件的详细错误信息。包括相关的数据库和对象、锁定模式以及死锁中的SQL语句。在检查完毕后，关闭跟踪标志位：DBCC TRACEOFF (1222, -1)
DBCC TRACESTATUS
解析：&在本例中，我们使用跟踪标志位1222。跟踪标志位1222能把详细的死锁信息返回到SQL日志中，标志位－1表示跟踪标志位1222应该对所有SQL Server连接在全局中启用。三、设置死锁优先级我们也可以使用SET DEADLOCK_PRIORITY命令来增加一个查询会话被选为死锁牺牲品的可能性。此命令的语法如下：SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | &numeric-priority& | @deadlock_var | @deadlock_intvar }&numeric-priority& ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }例如，上例中，第一个查询窗口如果使用以下的死锁优先级命令，几乎可以肯定会被选为死锁牺牲品。（正常情况下，SQL Server会把它认为取消或回滚代价最小的连接作为默认的死锁牺牲品）：SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET DEADLOCK_PRIORITY LOW
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE VendorID = 2
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE VendorID = 1
COMMIT TRAN
解析：可以将优先级设为High或Normal,High表示除非另一个会话有相同的优先级，否则它不会被选为牺牲品。Normal是默认行为，如果另一个会话是High,它可能会被选中。如果另一个是Low,则它可以安全地不被选中。如果两个会话有相同的优先级，则回滚代价最小的事务会被选中。关于死锁的其他资源，可能会有补充：的SQL Server死锁总结，也总结的不错。&&邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。助人等于自助!&&
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1516476次
积分：21356
积分：21356
排名：第111名
原创：490篇
评论：1911条
文章：40篇
阅读：96274
文章：57篇
阅读：77805
文章：40篇
阅读：123157
(1)(4)(1)(1)(1)(2)(4)(4)(5)(2)(6)(18)(12)(3)(5)(1)(3)(4)(2)(11)(16)(15)(17)(5)(5)(4)(1)(6)(6)(16)(15)(13)(12)(8)(5)(9)(16)(11)(7)(3)(13)(5)(9)(8)(7)(4)(7)(8)(3)(1)(4)(9)(4)(2)(2)(1)(3)(3)(3)(1)(3)(2)(7)(2)(5)(4)(1)(1)(2)(8)(6)(6)(2)(2)(13)(3)(5)(3)(7)(1)(7)(15)(10)(1)