SQL Server 2005与IBM DB2 v8.2之对比——Visual Studio和.NET开发人员的数据库平台（二）

SQL Server 2005 CLR 存储过程

使用CLR数据库对象最大的优势就在于能够访问到数据库外部的资源。标准SQL和T-SQL语句能够获取和操作关系型数据，但它们不能够访问数据库外部的系统资源，例如文件系统或是注册表。CLR存储过程（或者是其他用.NET代码编写的服务器端对象）能够完全地访问.NET框架。因此，它们不但能访问数据库引擎中的关系型数据，还能访问外部的资源。下表显示了一个用C#编写的范例存储过程，此存储过程被命名为“usp_ReadTextFile”，它的功能是从存于文件系统中的某文本文件中读取数据然后将其转换成SqlString的数据类型返回。

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlServer;

using System.Data.SqlTypes;

using System.IO;

public partial class StoredProcedures

{

[SqlProcedure]

public static void usp_ReadTextFile(string sInputFile,

out SqlString sColumn)

{

string strContents;

sColumn = "";

try

{

StreamReader strReader = new StreamReader(sInputFile);

strContents = strReader.ReadToEnd();

strReader.Close();

sColumn = strContents;

}

catch (Exception ex)

{

SqlPipe sp = SqlContext.GetPipe();

sp.Send(ex.Message);

}

}

}

首先，我们检查一下using语句。这些语句使得程序在调用SQL Server .NET数据提供程序的命名空间中的类时，无需使用全名来引用。SQL Server .NET数据提供程序（在命名空间System.Data.SqlServer中）是用来连接SQL Server系统的。

 

 

接着，我们注意一下位于方法名称前面的[SqlProcedure]属性。这个属性的存在就告诉了.NET编译器和运行库这个方法将作为SQL Server存储过程使用。另外，你还会发现这个类的默认名称被设置为“StoredProcedures”。在这个类中包含了一个名为usp_ReadTextFile的静态方法，因为在此使用的是C#，所以这个方法必须被定义为静态的，如果你使用的是Visual Basic，这个方法就应该被定义为Shared。usp_ReadTextFile方法接收两个参数。第一个参数是输入参数，用于传递操作系统的文件名称；第二个参数是输出参数（用out关键字标志），这个参数将以SqlString数据类型的形式把文本文件的内容返回给调用者，SQLString数据类型与T-SQL varchar数据类型相对应。

 

 

考虑到有些读者可能不能读懂C#代码，我们在这先对这段代码的结构做个简要地描述：这段代码先打开一个文本文件，然后读取它的内容，接着将内容放到SqlString类型的输出变量中输出。在这个存储过程代码开始部分，先声明了一个String类型的变量用来存放文本文件的内容。接着，在Try-Catch结构中读取文件的内容，首先，创建了一个名为strReader的StreamReader类型的对象并将包含在sInputFile变量中的输入文件名传递给它，接着利用StreamReader的ReadtoEnd方法读取输入文件的全部内容并将其存入strContents变量中。在文件内容读取完毕以后，关闭StreamReader对象并将文件内容移至名为sColumn的输出变量中。

 

 

如果在读取文件内容的过程中发生了错误，Catch模块中的代码将被执行。在Catch模块中，利用了一个SqlPipe类型的对象实例将错误信息传送给这个存储过程的调用者。首先，利用SqlContext.GetPipe方法创建了一个SqlPipe类型的对象实例，然后使用SqlPipe对象的Send方法将Exception对象实例ex的Message属性的内容传送给调用存储过程usp_ReadTextFile的程序。

 

 

要将这个存储过程部署到 SQL Server 中，需要两个步骤：编译和安装。开发人员可以自己完成这些步骤，先将相应的 DLL 程序集移至合适的位置，再运行相应的 SQL Server 命令；或者他可以使用 Visual Studio 自动地完成这些工作。如果要自己去做，首先先将编译得到的 DLL 程序集拷贝到 SQL Server 能够访问到的文件系统中，然后先后运行 CREATE ASSEMBY 和 CREATE PROCEDURE 命令。然而，如果你是使用 Visual Studio 2005 来开发的，你仅仅需要先后选择“生成”和“部署解决方案”两个菜单选项就能将上述的 CLR 存储过程安装到 SQL Server 数据库中。在下一个部分中，我们将给读者展现使用 Visual Studio 2005 构建和部署 CLR 存储过程 usp_ReadTextFile 的具体步骤。

使用SQL Server和Visual Studio 2005构建存储过程

下面的表格中概括了使用Visual Studio 2005结合SQL Server 2005创建和部署一个CLR存储过程的所需步骤。

 

 

开发流程概述

步骤	简要说明	所用工具
1	创建新的Visual Studio SQL Server项目	Visual Studio 2005文件菜单
2	创建数据库连接	SQL Server项目模板
3	添加存储过程	存储过程项目模板
4	生成项目	Visual Studio 2005生成菜单
5	部署项目	Visual Studio 2005生成菜单
总共5步

 

 

集成工具

要使用 Visual Studio 2005 创建 CLR 存储过程，你得首先创建一个 SQL Server 项目。打开 Visual Studio 2005 ，选择“ File ” * “ New Project ”，然后就会跳出“ New Project ”对话框，在这个对话框中，你从已经安装的 Visual Studio 模板列表中选择 SQL Server 项目并给其命名。在这里所设定的名称将作为相应 Visual Studio 解决方案的名称，同时也是未来经过编译得到的程序集的名称。如果在你的项目中仅仅只包含一个存储过程，它也可以作为这个存储过程的名称，当然，如果你希望你的项目中包含多个存储过程或者还有其他类型的 CLR 数据库对象，你完全可以为其指定其他的名称。

如图6所示，点击“OK”按钮就会在Visual Studio 2005 IDE中创建一个新的SQL Server项目，同时，SQL Server项目模板会自动提示你设置SQL Server 2005数据库连接信息，Visual Studio将使用这里设置的连接信息来调试和部署你的解决方案。典型的用法是，最初这里设定的目标是用于开发的数据库；当经过测试和质量评价以后，相应的.NET项目就能被部署到生产环境中的SQL Server实例。

图7——设置SQL Server项目的数据库连接属性

在“Connection Properties”对话框中，你可以在“Select or enter a server name”下拉框中输入你的目标SQL Server实例名称。如果在你的开发环境中注册了多个SQL Server实例，当你点击下拉箭头时，所有已注册的实例名称都会被列出。如果你的目标系统不在下拉列表中，你也可以在下拉框中输入相应的实例名称或者是IP地址。

 

 

选择好目标实例之后，你接下来需要选择用来连接这个SQL Server实例的身份验证模式。选择“Windows NT Integrated Security”，系统会把你在Windows中使用的用户名和密码传递给SQL Server用以验证并连接。如果你的目标实例没有采用集成验证模式，你也可以在“User ID”和“Password”两个文本框中提供用以连接目标实例的用户信息。

接下来，选择将作为部署目标的数据库。点击下拉框，会显示所有你有权限访问的数据库。当然，这里供你选择的数据库都已经存在于SQL Server中了。

 

 

当你输入了所有必要的连接信息后，你可以点击“ Test Connection ”按钮来测试一下连接。如果所有的连接信息都是有效的，将会显示“ Successful Connection ”消息，否则你必须更正连接信息。点击“ OK ”按钮后，连接信息就会被写入 Visual Studio 项目中并出现 Visual Studio IDE 界面。由于连接信息是一直存于项目中的，所以最好使用集成安全，因为它并不存储实际的登录信息。如果你是显示地提供了用户名和密码，那你要保证你的项目的存储位置是安全的。

图8——新建的SQL Server项目

刚刚建立的SQL Server项目最初是一个空的解决方案。在“Solution Explorer”中，所显示的项目名称就是在“New Project”对话框中设置的。另外，适当的引用文件会被自动地添加到项目中。我们特别注意一下sqlaccess引用，它是包含SQL Server .NET Provider的系统DLL，也就是说，它是我们创建和运行.NET数据库对象所必需的。

 

 

要在 Visual Studio 2005 中创建一个 CLR 存储过程，选择 Visual Studio 2005 的“ Project ” * “ Add Stored Procedure ”。

图9——添加CLR存储过程

 

 

在图 9 中，你能再次领略到 SQL Server 和 Visual Studio 2005 集成的紧密程度。在 Visual Studio 的“ Project ”菜单项中包含了创建各种 .NET 数据库对象的选项。另外，你也可以右击“ Solution Explorer ”窗口中的项目名称，选择“ Add Item ”选项，这同样会出现创建各种 .NET 数据库对象的选项。无论哪种情况，接着都会显示如图 10 所示的“ Add New Item ”对话框。

图10——选择存储过程模板