数据库系统实验(3-9)

实验3 数据控制（安全性部分）

一、实验目的

熟悉通过SQL对数据进行安全性控制。

二、实验平台

SQL Server2005，及其交互查询工具（查询分析器）。

三、实验内容和要求

在实验2建立的数据库TEST的基础之上进行。

（一）授权与回收

【

背景知识：

在SQL Server中有三种特殊的用户：系统管理员、用户数据库所有者（建立相应数据库的数据库用户）DBO、一般用户。系统管理员对整个系统有操作权；用户数据库所有者对他所建立的数据库具有全部操作权利；一般用户对给定的数据库只有被授权的操作权限。

●建立SQL服务器用户名（登录帐号）

作为一个用户，为了使用SQL Server，他必须在SQL Server上有一个SQL服务器用户名（登录帐号）。这个帐号是系统管理员通过sp_addlogin来增加的。

sp_addlogin 登录名，口令，库名，语言，全名

例：建立用户zhang, 口令为zhangling(最低六位)，全名为Zhang ling

sp_addlogin zhang,zhangling,null,null,Zhang ling

GO

SELECT * FROM syslogins

GO

●增加数据库用户名

同样，为了使用SQL Server上的数据库，他必须在数据库中有一个用户名，这个用户名是数据库所有者通过sp_adduser来增加的。数据库用户名不必和用户帐户一样，而且在不同的数据库中用户名可以不同。多个用户可以有相同的SQL Server帐户。同样，多个SQL Server帐户可以有相同的数据库名。

sp_adduser 登录名，别名，组名

登录名为用户的SQL服务器登录名；别名为用户在数据库中的用户名；组名为用户所属的数据库用户组。

例：用户zhang增加为tele114的用户，别名为zhang1,属于用户组china

sp_addgroup china

GO

sp_adduser zhang,zhang1,china

GO

sp_helpuser

GO

】

实验步骤：

建立多个用户，为其赋予不同的权限，然后查看用户是否真正拥有被授予的权限。

（1）建立用户U1 、U2、U3、U4、U5、U6、U7。

sp_addlogin'U1','U1U1U1' ,'TEST' /*建立ＳＱＬ服务器用户名即登录账号U1~U7*/

GO

sp_addlogin'U2','U2U2U2' ,'TEST'

GO

sp_addlogin'U3','U3U3U3' ,'TEST'

GO

sp_addlogin'U4','U4U4U4' ,'TEST'

GO

sp_addlogin'U5','U5U5U5' ,'TEST'

GO

sp_addlogin'U6','U6U6U6' ,'TEST'

GO

sp_addlogin'U7','U7U7U7' ,'TEST'

GO

sp_adduser'U1','U1' /*建立数据库用户U1~U7*/

GO

sp_adduser'U2','U2'

GO

sp_adduser'U3','U3'

GO

sp_adduser'U4','U4'

GO

sp_adduser'U5','U5'

GO

sp_adduser'U6','U6'

GO

sp_adduser'U7','U7'

GO

（2）完成如下的授权语句

a)把查询Student表权限授给用户U1

GRANT SELECT

ON Student

TO U1;

b)把对Student表和Course表的全部权限授予用户U2和U3

GRANT ALL

ON Student

TO U2,U3;

GRANT ALL

ON Course

TO U2,U3;

c)把对表SC的查询权限授予所有用户

GRANT SELECT

ON SC

TO PUBLIC

d)把查询Student表和修改学生学号的权限授给用户U4

GRANT UPDATE(SNO),SELECT

ON Student

TO U4;

e)把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户

GRANT INSERT

ON SC

TO U5

WITH GRANT OPTION

f)此时，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限：

GRANT INSERT ON SC TO U6

WITH GRANT OPTION

g)U6还可以将此权限授予U7:

GRANT INSERT ON SC TO U7

执行完上面的7条授权语句之后，分别以不同用户（U1~U7）的身份登录数据库，进行相关的操作，检查它们是否具备上面的权限，例如：

（1）用户U4登录数据库TEST，更新Student表的学生学号。

UPDATE Student

SET SNO=1006

WHERE SNO=1001

显示更新一条记录，即用户U4拥有对Student表SNO的更新数据权限。

（2）用户U7登录数据库TEST，向SC表中插入一条数据记录。

INSERT INTO SC VALUES(1004,2,88)

显示插入一条记录，即用户U7拥有对SC表的插入数据权限。

以sa身份登录，完成如下的权限回收语句。在权限被回收之后，查看该用户是否真正丧失了对数据的相应权限。

h)把用户U4修改学生学号的权限收回

REVOKE UPDATE(SNO)

ON Student

FROM U4

i)收回所有用户对表SC的查询权限

REVOKE SELECT

ON SC

FROM PUBLIC

j)把用户U5对SC表的INSERT权限收回

REVOKE INSERT

ON SC

FROM U5 CASCADE

（将用户U5的权限收回时，必须级联收回，系统只收回直接或间接从U5处获得的权限）

查看用户的相应权限。

（1）用户U3重新登录数据库TEST。执行命令：

SELECT *

FROM SC;

执行失败。表明用户U3不再拥有此权限。证实用户U3丧失了对表SC的进行查询的权限。

（2）用户U6重新登录数据库TEST，向表SC中插入一条记录（1005,2,92）。执行命令：

INSERT INTO SC VALUES(1005,2,92)

执行失败。用户U6不再拥有此权限。证实用户U6丧失了从用户U5处所获得的对表SC插入数据的权限。

（二）数据库角色

（1）创建角色R1

sp_addrole R1

（2）为R1授权，使得R1拥有对Student表的SELECT、UPDATE、INSERT权限。

GRANT SELECT,UPDATE,INSERT

ON Student

TO R1

（3）将角色R1授予用户U1、U3、U7。

sp_addrolemember'R1','U1'

GO

sp_addrolemember'R1','U3'

GO

sp_addrolemember'R1','U7'

GO

（4）对R1的权限进行修改，增加对表Student的DELETE权限，并回收对Student 表的INSERT权限。

GRANT DELETE

ON Student

TO R1

REVOKE INSERT

ON Student

FROM R1

实验4 数据控制（完整性部分）

一、实验目的

熟悉通过SQL对数据进行完整性控制。

二、实验平台

SQL Server2005，及其交互查询工具（查询分析器）。

三、实验内容和要求

在实验2建立的数据库TEST的基础之上进行。

（一）~（ 五）见实验指导书

（六）

将实验指导书上的这部分语句

CREATE TRIGGER TRIBEFORE UPDATE OR INSERT ON TAB

FOR EACH ROW

AS BEGIN

IF new.col>100 AND new.col<1000 THEN

new.col:=50;

END IF;

IF new.col>1000 THEN

RAISE EXCEPTION 'New values can not be more than 1000';

END IF;

END;

替换为：

CREATE TRIGGER TRI

ON TAB

FOR UPDATE,INSERT

AS

DECLARE @col INT

IF(SELECT col FROM inserted)>1000

BEGIN

PRINT'New values can not be more than 1000'

ROLLBACK TRANSACTION

END

ELSE

BEGIN

UPDATE TAB SET col=50

WHERE(col BETWEEN 100 AND 1000)

END

实验5 数据库事务处理

一、实验目的

（1）、加深对事务概念的理解。

（2）、掌握SQL Server事务定义的方法。

（3）、学会使用检查点机制设置回滚点。

二、实验平台

SQL Server2005，及其交互查询工具（查询分析器）。

三、实验内容和要求

本实验在数据库Northwind的基础之上进行。

1、SQL Server提供三种事务模式：

（1）显式事务

是指由用户定义事务的开始和结束。它是以BEGIN TRANSACTION开始，以COMMIT TRANSACTION或ROLLBACK TRANSACTION语句结束。

（2）隐式事务

指当前事务提交或回滚后自动启动新的事务，即不需要使用BEGIN TRANSACTION 启动事务，而只需提交和回滚每个事务。

（3）自动定义事务

是指当一个SQL语句成功执行后，它被自动提交，而当执行出错时，则被自动回滚。

2、事务定义语句

SQL Server提供了以下事务定义语句。

（1）开始事务

格式：BEGIN TRANSACTION [transaction_name]

定义一个显式事务的开始。执行事务时，DBMS会根据系统设置的隔离级别，锁定其访问的资源直到事务结束。

（2）提交事务

格式：COMMIT TRANSACTION [transaction_name]或COMMIT WORK

使事务自开始以来对数据库的所有修改永久化，标记一个事务结束。

（3）回滚事务

格式：ROLLBACK TRANSACTION [transaction_name]或ROLLBACK WORK

使事务回滚到起点或指定的检查点处，也标志一个事务结束。不带事务名称和检查点名称的ROLLBACK操作是将数据库回滚到最远的BEGIN TRSACTION处。

（4）设置检查点

格式：SAVE TRANSACTION[checkpoint_name]

在事务内部设置检查点，以定义事务可以返回的位置。

（一）实验案例

假设某客户要求在一订单上追加购买一种商品，这时需分别在订单主表和订单明细表上更新相关信息，故可定义一个事务来完成。

1、假设某客户要求在订单11077上追加购买商品75（此处75是商品号）两件，

请定义一个事务insertorder1。

（1）、首先执行SQL语句：

SELECT*

FROM [Order Details]

WHERE OrderID=11077 AND ProductID=75

显示结果为：

SELECT*

FROM Orders

WHERE OrderID=11077

显示结果为：

（2）、定义一个事务insertorder1完成数据库的更新。

定义如下：

BEGIN TRANSACTION insertorder1 /*事务开始*/

UPDATE [Order Details] /*更新订单明细表中的订货数量*/

SET Quantity=Quantity+2

WHERE OrderID=11077 AND ProductID=75

IF@@error!=0

/*@@error是全局变量，用于测试SQL命令的执行情况，若不为0，则表示执行失败*/ BEGIN

PRINT'更新数量操作错误！'

RETURN

END

UPDATE Orders /*更新订单主表中的下单日期、货物要求日期、运输日期*/ SET

OrderDate=getdate(),RequiredDate=dateadd(d,25,getdate()),ShippedDate= dateadd(d,3,getdate())

FROM Orders

WHERE OrderID=11077

IF@@error!=0

BEGIN

ROLLBACK TRANSACTION insertorder1

PRINT'更新日期操作错误！'

RETURN

END

COMMIT TRANSACTION insertorder1

执行此部分语句。

（3）、执行SQL语句：

SELECT*

FROM [Order Details]

WHERE OrderID=11077 AND ProductID=75

显示结果为：

SELECT*

FROM Orders

WHERE OrderID=11077

显示结果为：

本题要求客户购买的商品信息既要在订单明细表中更新内容，也要修改该订单在订单主表上的相关日期信息，这两件事要么都做，要么都不做。

2、假设客户要求在订单11077上追加购买商品76（此处76是商品号）一件，请

定义一个事务insertorder2完成数据库的更新。要求订单明细表更新成功后设置一检查点before_insesrt_chk。

（1）、首先执行SQL语句：

SELECT*

FROM [Order Details]

WHERE OrderID=11077 AND ProductID=76

显示结果为：

SELECT*

FROM Orders

WHERE OrderID=11077

显示结果为：

（2）、定义一个事务insertorder2完成数据库的更新。

定义如下：

BEGIN TRANSACTION insertorder2

INSERT [Order Details] VALUES(11077,76,18.00,1,0)

IF@@error!=0

BEGIN

PRINT'插入操作错误！'

RETURN

END

SAVE TRANSACTION before_insert_chk

/*设置一个检查点，检查点命名为before_insert_chk */

UPDATE Orders

SET

OrderDate=getdate(),RequiredDate=dateadd(d,25,getdate()),ShippedDate= dateadd(d,3,getdate())

FROM Orders

WHERE OrderID=11077

IF@@error!=0

BEGIN

ROLLBACK TRANSACTION before_insert_chk

/*事务回滚到检查点before_insert_chk */

COMMIT TRANSACTION insertorder2 /*事务提交*/

PRINT'更新日期操作错误！'

RETURN

END

COMMIT TRANSACTION insertorder2 /*事务提交*/ 本题中，由于在订单明细表操作后设置了一个检查点before_insert_chk，这样即使是操作订单主表时发生了错误，事务对订单明细表的操作还是有效的。

（3）、执行SQL语句：

SELECT*

FROM [Order Details]

WHERE OrderID=11077 AND ProductID=76

显示结果为：

SELECT*

FROM Orders

WHERE OrderID=11077

显示结果为：

（二）选作题目：一新客户订购了75商品一件，请定义一事务完成数据库更新任务。

实验6 编写存储过程访问数据库

一、实验目的

熟悉使用存储过程来进行数据库应用程序的设计。

二、实验平台

SQL Server2005。

三、实验内容和要求

在实验2建立的数据库TEST的基础之上进行。编写存储过程，完成以下功能：

1、统计“离散数学”课程的成绩分布情况，即按照各分数段来统计人数；

2、统计任意一门课程的平均成绩；

3、将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。

以‘sa’身份登录SQL Server系统，执行

sp_addlogin'SYSTEM','MANAGER','TEST'

sp_adduser'SYSTEM','SYSTEM'

创建登录名'SYSTEM',然后以登录名'SYSTEM'的身份登录。

（一）、统计“离散数学”课程的成绩分布情况

1、创建存储过程

（1）创建需要的表结构

CREATE TABLE Rank(

division CHAR(20),

number INT)

（2）编写存储过程

CREATE PROCEDURE proStatisticMark(@sName CHAR(50))

AS

BEGIN

DECLARE@less60 int,@b60a70 int,@b70a80 int,@b80a90 int,@more90 int,@curcno char(4)

SET @less60=0

SET @b60a70=0

SET @b70a80=0

SET @b80a90=0

SET @more90=0

SET @curcno=''

SELECT @curcno=CNO

FROM Course

WHERE CNAME=@sName

IF @curcno=''

SELECT'输入错误，不存在此用户。'

SELECT @less60=count(*)

FROM SC

WHERE CNO=@curcno AND GRADE<60

SELECT @b60a70=count(*)

FROM SC

WHERE CNO=@curcno AND GRADE>=60 AND GRADE<70

SELECT @b70a80=count(*)

FROM SC

WHERE CNO=@curcno AND GRADE>=70 AND GRADE<80

SELECT @b80a90=count(*)

FROM SC

WHERE CNO=@curcno AND GRADE>=80 AND GRADE<90

SELECT @more90=count(*)

FROM SC

WHERE CNO=@curcno AND GRADE>=90

DELETE FROM Rank

INSERT INTO Rank VALUES('[0,60)',@less60)

INSERT INTO Rank VALUES('[60,70)',@b60a70)

INSERT INTO Rank VALUES('[70,80)',@b70a80)

INSERT INTO Rank VALUES('[80,90)',@b80a90)

INSERT INTO Rank VALUES('[90,100]',@more90)

END

2、执行存储过程

EXECUTE proStatisticMark '离散数学'

EXECUTE proStatisticMark '运筹学'

然后在表RANK中查看执行结果。

SELECT*

FROM Rank

（二）、统计任意一门课程的平均成绩

1、创建存储过程

（1）创建所需要的表结构。

CREATE TABLE Avggrade(

cname CHAR(50),

avgg NUMERIC(10,6))

（2）编写存储过程

CREATE PROCEDURE proCollectAvggrade

AS

BEGIN

DECLARE @curname CHAR(50),@curcno CHAR(4),@curavgg NUMERIC(10,6)

DECLARE curCourse CURSOR FOR

SELECT CNO,CNAME

FROM Course

OPEN curCourse

FETCH curCourse INTO @curcno,@curname

WHILE(@@FETCH_STATUS=0)

BEGIN

SELECT @curavgg=AVG(GRADE)

FROM SC

WHERE CNO=@curcno

GROUP BY CNO

INSERT INTO Avggrade VALUES(@curname,@curavgg)

FETCH curCourse INTO @curcno,@curname

END

CLOSE curCourse

DEALLOCATE curCourse

END

2、执行存储过程

EXECUTE proCollectAvggrade

然后在表Avggrade中查看结果。

SELECT*

FROM Avggrade

（三）在表ＳＣ中将学生选课成绩从百分制改为等级制（即Ａ、B、C、D、E）

1、创建存储过程

在SC中增设1列：

ALTER TABLE SC ADD newgrade CHAR(1)

存储过程：

CREATE PROCEDURE proChangeCritical

AS

BEGIN

DECLARE@chgrade CHAR(1),@cursno int,@curcno int,@curgrade int

DECLARE curSC CURSOR FOR

SELECT SNO,CNO,GRADE

FROM SC

OPEN curSC

FETCH curSC INTO @cursno,@curcno,@curgrade

WHILE(@@FETCH_STATUS=0)

BEGIN

IF @curgrade<60

SET @chgrade='E'

ELSE IF @curgrade<70

SET @chgrade='D'

ELSE IF @curgrade<80

SET @chgrade='C'

ELSE IF @curgrade<90

SET @chgrade='B'

ELSE IF @curgrade<=100

SET @chgrade='A'

UPDATE SC

SET newgrade=@chgrade

WHERE SNO=@cursno AND CNO=@curcno

FETCH curSC INTO @cursno,@curcno,@curgrade

END

CLOSE curSC

DEALLOCATE curSC

END

2、执行存储过程

EXECUTE proChangeCritical

查看结果：

SELECT*

FROM SC

（四）删除存储过程

DROP PROCEDURE proStatisticMark

请自行删除第二步、第三步建立的存储过程。

实验7 使用SQL Server中的CLR集成

一、实验目的

了解SQL Server中的CLR集成，了解使用C#开发存储过程的一般方法。

二、实验平台

SQL Server2005、VS2005。

三、实验内容和要求

通常，开发人员使用T-SQL来创建SQL Server的存储过程、函数和触发器。现在，SQL Server2005已经完全支持.Net共用语言运行时（CLR）。所以，开发人员可以使用.NET环境下的语言来开发SQL Server的存储过程、函数和触发器。SQL Server和CLR的集成带来很多好处，如实时编译、类型安全、增强的安全性以及增强的编程模型等。

1、启用CLR集成

在开始用C#写存储过程之前，必须要启用SQL Server的CLR集成特性。默认情况它是不启用的。打开SQL Server Management Studio并执行如下脚本。

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

这里，我们执行了系统存储过程“sp_configure”，为其提供的两个参数分别为：“clr enabled”和“1”。如果要停用CLR集成的话也是执行这个存储过程，只不过第二个参数要变为“0”。若要使新的设置产生效果，还得调用“RECONFIGURE”。

2、使用C#编写存储过程

打开Visual Studio，并从文件菜单中选择“新建项目”。在“新建项目”对话框中选择“Visual C#”下的“数据库”。然后选择“SQL Server项目”模板。

起好项目名称后就单击“确定”按钮。

然后，添加“数据库引用”。

然后单击“确定”按钮。如果选择了取消，也可以在“项目”–“属性”对话框中来选择数据库。

接下来，右键单击新建的这个项目，选择“添加”-“存储过程”。然后将会出现如下图所示的对话框：

选择“存储过程”模板，并起一个合适的名字，然后单击“添加”按钮。

添加完后会发现，实际上这是创建了一个已经导入了需要用到的命名空间的类。

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

System.Data.SqlTypes命名空间包含了很多不同的类型，可以用来代替SQL Server的数据类型。Microsoft.SqlServer.Server命名空间下的类负责SQL Server的CLR集成。

需要输入参数的没有返回值的存储过程

下面的例子告诉我们如何写一个执行了一些逻辑，但是却没有任何返回值和输出参数的存储过程。创建一个名为“ChangeCompanyName”的存储过程，它用来修改Customers表中CompanyName字段的值。这个存储过程需要两个参数：CustomerID（需要更改公司名称的客户的ID）和CompanyName（新的公司名称）。

删掉StoredProcedureMyFirst.cs文件中的如下代码：

public static void StoredProcedureMyFirst()

{

// 在此处放置代码

}

添加“ChangeCompanyName”存储过程的代码：

[SqlProcedure]

public static void ChangeCompanyName(SqlString CustomerID, SqlString CompanyName)

{

SqlConnection cnn = new SqlConnection("context connection=true");

cnn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = cnn;

https://www.360docs.net/doc/8214368933.html,mandText = "update customers set companyname=@p1 where customerid=@p2";

SqlParameter p1 = new SqlParameter("@p1", CompanyName);

SqlParameter p2 = new SqlParameter("@p2", CustomerID);

cmd.Parameters.Add(p1);

cmd.Parameters.Add(p2);

int i=cmd.ExecuteNonQuery();

cnn.Close();

SqlContext.Pipe.Send(i.ToString());

}

仔细看一下这个ChangeCompanyName()方法。它是一个静态方法并且没有返回值（void）。它需要两个名为CustomerID和CompanyName的参数。这两个参数的数据类型都是SqlString。SqlString可以用来代替SQL Server中的nvarchar数据类型。这个方法用了一个[SqlProcedure]属性来修饰。该属性用于标记ChangeCompanyName()方法是一个SQL Server 存储过程。

在方法内我们创建了一个SqlConnection对象，并设置其连接字符串为“context connection = true”。“上下文连接”可以让我们使用当前登录到数据库的用户作为登录数据库的验证信息。本例中，ChangeCompanyName()方法将会转换为存储过程，然后保存到Northwind数据库里。所以在这里的“上下文连接”指的就是Northwind数据库。这样就不需要再写任何关于登录数据库的验证信息了。

接下来是打开数据库连接。然后通过设置SqlCommand对象的Connection和CommandText属性，让其执行更新操作。同时，我们还需要设置两个参数。这样通过调用ExecuteNonQuery()方法就可以执行更新操作了。再接下来就是关闭连接。

最后，将ExecuteNonQuery()方法的返回值发送到客户端。

返回从表中读取的一条或多条记录的存储过程

我们在使用存储过程时，经常会SELECT一条或多条记录。可以采用两种方法来创建这样的存储过程。

a、首先我们创建一个名为GetAllCustomers()的方法，代码如下：

[SqlProcedure]

public static void GetAllCustomers()

{

SqlConnection cnn = new SqlConnection("context connection=true");

cnn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = cnn;

https://www.360docs.net/doc/8214368933.html,mandText = "select * from customers";

SqlDataReader reader = cmd.ExecuteReader();

SqlContext.Pipe.Send(reader);

reader.Close();

cnn.Close();

}

这个GetAllCustomers()方法用了一个[SqlProcedure]属性来修饰。在方法内创建一个SqlConnection和一个SqlCommand对象。然后使用ExecuteReader()方法来执行SELECT语句。接下来用Send()方法将取得的SqlDataReader数据发送到客户端。最后就是关闭SqlDataReader和SqlConnection。在这种方法中，是我们自己创建的SqlDataReader。

b、把这个任务交给SqlContext类去完成，代码如下：

[SqlProcedure]

public static void GetCustomerByID(SqlString CustomerID)

{

SqlConnection cnn = new SqlConnection

("context connection=true");

cnn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = cnn;

https://www.360docs.net/doc/8214368933.html,mandText = "select * from customers where customerid=@p1";

SqlParameter p1 = new SqlParameter("@p1", CustomerID);

cmd.Parameters.Add(p1);

SqlContext.Pipe.ExecuteAndSend(cmd);

cnn.Close();

}

GetCustomerByID()方法需要一个参数– CustomerID，它将从Customers表中返回某个customer的记录。ExecuteAndSend()方法接收一个SqlCommand对象作为参数，执行它就会返回数据集给客户端。

有输出参数的存储过程

我们在使用存储过程时，经常会通过输出参数返回一个经过计算的值。所以，现在让我们来看一看如何创建具有一个或多个输出参数的存储过程。

[SqlProcedure]

public static void GetCompanyName(SqlString CustomerID,out SqlString CompanyName)

{

SqlConnection cnn = new SqlConnection("context connection=true");

cnn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = cnn;

https://www.360docs.net/doc/8214368933.html,mandText = "select companyname from customers where customerid=@p1";

SqlParameter p1 = new SqlParameter("@p1", CustomerID);

cmd.Parameters.Add(p1);

object obj = cmd.ExecuteScalar();

cnn.Close();

CompanyName = obj.ToString();

}

这是一个名为GetCompanyName()的方法，它需要两个参数。第一个参数是CustomerID，它是一个输入参数；第二个参数是CompanyName，它是一个输出参数（用关键字out来指明）。这两个参数都是SqlString类型的。GetCompanyName()方法会接收一个CustomerID 参数，然后返回CompanyName（作为输出参数）。

该方法内的代码首先设置了SqlConnection和SqlCommand对象。然后，使用ExecuteScalar()方法来执行SELECT语句。ExecuteScalar()方法返回的值是一个object类型，它其实就是公司名称。最后将输出参数CompanyName设置为这个值。

返回一行或多行自定义数据的存储过程

我们在使用存储过程时，更多的还是从某些表中读取数据。但是，某些情况下我们需要的数据可能不在任何表里。例如，我们可能会基于某些计算来生成一个数据表格。因为它的数据不是从表中获得的，所以上面的方法就不再适用了。幸运的是，SQL Server的CLR 集成特性给我们提供了一个解决这个问题的方法。请看如下代码：

[SqlProcedure]

public static void GetCustomRow()

{

SqlMetaData[] metadata = new SqlMetaData[2];

metadata[0] = new SqlMetaData("CustomerID", SqlDbType.NVarChar, 50);

metadata[1] = new SqlMetaData("CompanyName", SqlDbType.NVarChar, 50);

SqlDataRecord record = new SqlDataRecord(metadata);

record.SetString(0, "ALFKI");

record.SetString(1, "Alfreds Futterkiste");

SqlContext.Pipe.Send(record);

}

GetCustomRow()方法不需要连接数据库，它会返回一条记录并发送给客户端。这个方法首先声明了一个SqlMetaData对象。当你要用到自定义列的时候，就可以使用这个SqlMetaData类。在我们的示例中，创建了两个类型为NVarChar，长度为50的列。然后创建了一个SqlDataRecord对象。SqlDataRecord类可以用来表示一个自定义行。它的构造函数需要一个SqlMetaData数组作为参数。SqlDataRecord对象的SetString()方法用来设置列的值。另外，还有许多不同的类似SetString()这样的方法，可以用来处理不同的数据类型。最后，调用Send()方法将SqlDataRecord对象发送到客户端。

上面的例子返回一行数据给调用者，如果要返回多行呢？请看下面的代码：

[SqlProcedure]

public static void GetMultipleCustomRows()

{

SqlMetaData[] metadata = new SqlMetaData[2];

metadata[0] = new SqlMetaData("CustomerID", SqlDbType.NVarChar, 50);

metadata[1] = new SqlMetaData("CompanyName", SqlDbType.NVarChar, 50);

SqlDataRecord record = new SqlDataRecord(metadata);

SqlContext.Pipe.SendResultsStart(record);

record.SetString(0, "ALFKI");

record.SetString(1, "Alfreds Futterkiste");

SqlContext.Pipe.SendResultsRow(record);

record.SetString(0, "ANATR");

record.SetString(1, "Ana Trujillo Emparedados y helados");

SqlContext.Pipe.SendResultsRow(record);

SqlContext.Pipe.SendResultsEnd();

}

GetMultipleCustomRows()方法将会返回多个SqlDataRecord对象到客户端。接下来创建自定义列和设置列的值都和之前的例子一样。但是，我们使用的是SendResutlsStart()方法来传输数据。SendResultsRow()方法也是发送一个SqlDataRecord对象到客户端，但是我们可以多次调用它，从而做到发送多条记录。最后，调用SendResultsEnd()方法用来标记已经完成数据传输操作。

3、部署存储过程到SQL Server数据库

自动方法

在“解决方案资源管理器”里，右键单击所建的项目，在出现的快捷菜单中选择“部署”，系统提示数据库连接：

单击“确定”按钮后，状态栏中提示“部署已成功”。

在SQL Server Management Studio查看Northwind数据库，出现下图所示情况。注意，在存储过程节点下出现了我们创建的所有方法（有“锁”图标的），并且在程序集节点下出现了我们的程序集。

【实验要求】：在SQL Server Management Studio中测试我们所创建的这些存储过程（至少选择3个），写出测试语句及测试结果。

手动方法

（首先在SQL Server Management Studio中将自动方法中创建的6个存储过程及SqlServerCLR程序集删除）

在VS2005中，单击“生成”菜单，选择“重新生成SqlServerCLR”。找到编译后的SqlServerCLR.dll文件，假设该文件的路径为e:\clr\SqlServerCLR.dll，接下来切换到SQL Server Management Studio，用以下方法注册程序集：

CREATE ASSEMBLY [SqlServerCLR]

FROM'e:\clr\SqlServerCLR.dll'

然后创建存储过程，例如：

CREATE PROCEDURE ChangeCompanyName

(

@CustomerID nchar(5),

@CompanyName nvarchar(40)

)

AS EXTERNAL NAME SqlServerCLR.StoredProcedures.ChangeCompanyName 和

CREATE PROCEDURE GetCompanyName

(

@CustomerID nchar(5),

@CompanyName nvarchar(40)OUTPUT

)