11.2VisualStudio2005内的SQLServer方案

 

 

首先查看一下 ADO.NET 2.0 提供用来创建 SQL Server 服务器端对象的相关类。

11.2.1 System.Data.SqlServer 命名空间
当用 .NET 开发 SQL Server 的内部对象时，最常使用的命名空间将会是存在 Sqlaccess.dll 程序文件内的 System.Data.SqlServer。若你曾经用 ADO.NET 开发数据库的应用程序，则部分对象是相通的。例如 SqlConnection、SqlCommand 等，微软的开发小组尽量让程序设计师在 SQL Server 内访问数据与在外部访问数据的程序编写方式相似，而不需要熟悉两套开发模式。以下稍微解释这些对象：

SqlContext：SqlContext 类让你可以取得 SQL Server 内部调用者的执行环境(Context)，主要是取得 SqlPipe 类实例，用此返回结果给前端调用该数据库对象的应用程序，或是 SqlTriggerContext 以提供编写触发器（Trigger）的相关数据。SqlContext 类在让我们所编写的程序集与 SQL Server 进行沟通时显得非常重要。使用该类的程序范例如程序代码列表11-1所示：

程序代码列表11-1 通过 SqlContext 取得用户当前的执行环境

Using cnn As New SqlConnection("Context Connection=true")

Using sqlCmd As New SqlCommand

'因为 Product 数据表属于不同的 Schema，所以需要加上 Schema 名称

cnn.Open()

sqlCmd.Connection = cnn

sqlCmd.CommandText = "SELECT ProductNumber FROM Production.Product"

Dim sqlRead As SqlDataReader = sqlCmd.ExecuteReader()

'因为是存储过程，要通过 SqlPipe 将运行结果接回原来 SQL Server 的输出

Dim sqlP As SqlPipe = SqlContext.Pipe

sqlP.Send(sqlRead)

End Using

End Using

程序代码列表11-1 仅是简单地取得 Production.Product 数据表内的 ProductNumber 字段，然后直接返回给前端。由于被调用的对象是在用户已经取得的连接环境内执行，若要传递数据给用户，就需要通过程序代码列表 11-1 的方式引用 SqlContext 类的 Pipe 属性，取得 SqlPipe 对象实例后，再通过 Send 方法将结果返回。

SqlConnection：让我们所编写的程序取得对程序集实例被 SQL Server 外部的前端应用程序调用时，该应用程序所在的数据库连接相关设置。这些连接设置并不是由我们用 .NET 所编写的存储过程或用户自定义函数的对象决定的，被调用的数据库内部对象需要与外部应用程序使用相同的连接环境。可以用以下的程序通过连接字符串设置如下，而通过这条连接再执行其他的 SQL 语法：

Using cnn As New SqlConnection("Context Connection=true")

SqlCommand：让你传送 T-SQL 命令到数据库服务器，参照前述程序代码列表 11-1 的范例，可以依照一般方式创建 SqlCommand 对象实例，并通过 SqlConnection 实例来执行取得产品编号的 T-SQL 语法。

SqlParameter：用以设置与访问 SqlCommand 对象内传递的参数。范例程序如程序代码列表11-2所示：

程序代码列表 11-2 通过 SqlCommand 和 SqlParameter 对象搭配SHA1 哈希算法为密码编码

<SqlProcedure(name:="WriteHashedPassword")> _

Public Shared Sub WriteHashedPassword(ByVal UserName As String, ByVal Password As String)

'将账号/密码经过 Sha1 Hash 口算法换算后，再存入数据表

Try

Using cnn As New SqlConnection("Context Connection=true")

cnn.Open()

Using sqlCmd As New SqlCommand

Dim sh1 As New SHA1Managed

Dim uEncode As New UnicodeEncoding

'将密码以 SHA1 计算哈希值，再以 Base64 编码。

Dim txtBytes As Byte() = uEncode.GetBytes(Password)

Dim hashedPassword As Byte() = sh1.ComputeHash(txtBytes)

Dim strHash As String = Convert.ToBase64String(hashedPassword)

Dim dml As String = "INSERT NamePass VALUES(@UserName,@HashAsString)"

With sqlCmd

.Connection = cnn

.CommandText = dml

'通过 SqlParameter 对象设置 SqlCommand 对象所包含 T-SQL 语法

'需要的参数

.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NVarChar, 50))

.Parameters(0).Value = UserName

.Parameters.Add(New SqlParameter("@HashAsString", SqlDbType.NVarChar, 100))

.Parameters(1).Value = strHash

sqlCmd.ExecuteNonQuery()

End With

End Using

cnn.Close()

End Using

Catch ex As Exception

Dim fs As New FileStream("C:\YukonCLR.log", FileMode.OpenOrCreate, FileAccess.Write)

Dim sw As New StreamWriter(fs)

sw.WriteLine(ex.ToString())

sw.Close()

End Try

End Sub

常常有朋友询问如何将用户输入的密码通过哈希(Hash)算法计算过后再放入数据库中，在以往笔者只能建议在应用程序端编写，现在相同的语法可以写成存储过程保存在 SQL Server 内，如程序代码列表11-2 所示。而一般的用户只需要简单地调用存储过程，就可以将密码通过 .NET Framework 提供的 SHA1 哈希算法编码后，再存入到数据表。

SqlPipe：将执行结果或信息送回前端应用程序，程序范例可以参照程序代码列表 11-1 最后两行。如果前端是通过 ADO 或 ADO.NET 对象访问 SQL Server，而我们编写的对象返回文字信息，则会被前端视为 Connection 对象的 InfoMessage 事件，范例如下。你也可以返回单一条记录或是整个数据集合。

Dim pipe As SqlPipe = SqlContext.Pipe

‘传送信息

pipe.Send(“完成事务”)

SqlDataReader：通过 SQL Server 提供的只能向前移动（forward-only）、只读（read-only）游标（Cursor）来访问数据。

Dim sdr As SqlDataReader = cmd.ExecuteReader()

While sdr.Read()

Debug.WriteLine(sdr("EmployeeID") & "-" & sdr(1))

End While

sdr.Close()

上述范例程序中通过 Read 函数逐条读出数据，直到数据记录读取完毕，Read 函数会返回 false，我们据此退出循环。

System.Transactions.Transaction：.NET Framework2.0新增了 System.Transactions 命名空间，在其内可提供新的事务管理架构与功能。若只就此处的 SQL Server 内部对象而言，你不太需要管理，因为当通过 SqlConnection 取得对象执行环境的连接时，若该连接已经有事务，则会自动加入，你可以通过 Transaction 的静态属性 Current 取得当前的事务状况，以此完成或恢复事务。范例程序如下：

Catch ex As Exception

Transaction.Current.Rollback

约略看完 ADO.NET 2.0 与创建 SQL Server 2005 对象相关的类后，我们接着来看一下，如何通过 Visual Studio 2005 建构对象。

11.2.2 通过Visual Studio 2005创建供SQL Server 2005用的Assembly
首先在 Visual Studio 2005 新建一个新的项目，类型为“SQL Server Project”，画面如图11-2所示：

在图11-2中，左上方的“项目类型（Project Type）”中选择某个语言后，点选其下的“Database”节点，然后在右上方的“模板(Template)”选择“SQL Server Project”，在下方的“名称(Name)”字段定义项目名称后，可以通过“浏览(Browse)”按钮选择项目存放的文件路径。

图11-2 打开创建SQL Server 2005对象的项目

Visual Studio 2005 接着会让你选择要加入对哪一个数据库的引用，画面如图11-3 所示：

由于需要 Visual Studio 2005 集成开发环境帮我们部署已开发好的数据库对象到 SQL Server 2005 实例的数据库内，所以相关的连接设置必须正确。若在图11-3 设置错误，可以在进入项目后，在“解决方案资源管理器”窗口内以鼠标右键点选项目名称，通过快捷选项“属性（Properties）”，或是鼠标双击项目下的“My Project”节点，打开如图11-4 的画面，在“数据库（Database）”页签重新设置连结：

图11-3 设置对某个 SQL Server 2005 实例内数据库的引用

图11-4 设置项目部署(Deploy)对象时，所连结的数据库以及各对象的执行权限

另外，Visual Basic.NET 默认的根命名空间(Root Namespace)和程序集名称(Assembly Name)将会与项目名称相同，在部署程序集到 SQL Server 2005 时将会用到这两个名称。若你想更改根命名空间或程序集名称可以进入“应用程序（Application）”页签修改，如图11-5画面所示：

图11-5 设置项目的程序集名称或根命名空间

接着在Visual Studio 2005的项目内选取工具选单上的“项目”-“添加新项目”，打开如图11-6的对话框，在对话框内选择“Stored Procedure”项目，或于“解决方案资源管理器”内使用快捷选项，选择“新建项目（New Item）”亦可。

在图11-6 中填入适当名称后，点选“添加（Add）”按钮，以添加定义“Stored Procedure”的模板。

在此，我们创建一个存储过程，内容如上文程序代码列表 11-2 的定义。整个项目的画面至此如图11-7 所示：

图11-6 通过添加新项目对话框选择创建存储过程的模板

图11-7 程序代码中所设置的StoredProcedure名称，在部署
后将会在SQL Server Management内找到

在各对象前可以加上 System.Data.Sql 命名空间下所提供的一组 Attribute 来告知该函数的用途，同时可以通过该 Attribute 的 name 属性（Property）来告知该函数或类型创建到 SQL Server 内对象的命名。通常若不指定名称，就沿用原 .NET 函数的名称当作数据库对象的名称。

Attribute 设置的范例如下：

<System.Data.Sql.SqlProcedure(name:="WriteHashedPassword")>

编译无误后，接着通过 Visual Studio 2005 提供的“部署（Deploy）”功能，将你所写好的程序部署到SQL Server 2005，快捷选项
通过 Visual Studio 2005 的“部署”选项将相关对象注册到 SQL Server 2005 内

部署完你的程序对象后，打开SQL Server Management Studio，在“对象资源管理器”窗口选择刚刚 Visual Studio 2005 所连接的数据库AdventureWorks，你可发现在“程序集(Assemblies)”这个目录底下多了一个节点YukonCLR。

在图11-9 中“可编程性（Programmability）”－“存储过程（Stored Procedures）”节点下，同时可以看到 Visual Studio 2005 遵循我们通过 SqlProcedure Attribute 告知的属性，将该 WriteHashedPassword 函数注册成存储过程。

图11-9 在 Management Studio 的“对象资源管理器”窗口查看部署到 SQL Server 2005 内的对象

11.2.3 通过T-SQL手动将该程序集放入到SQL Server
通过 T-SQL 语法亦可将该程序集放入到 SQL Server 2005，这时可以使用 SQL Server Management Studio 操作画面，通过主菜单“文件”－“新建”－“项目”选项打开一个新的项目后，类型为“SQL Server 脚本”，在下方的“名称”字段定义项目名称后，通过“浏览”选择项目存放的文件路径。

进入SQL Server Management Studio后，在“解决方案资源管理器”窗口内的方案上点选右键新建项目，新建一个空的 T-SQL 查询文件，如图11-10所示：

你也可以通过主菜单“视图”－“模板资源管理器”选项，从“模板资源管理器”中选择想要完成的工作，让你不至于从完全空白的环境开始编写 T-SQL。图11-11为 SQL Server Management Studio 的T-SQL 编写环境，我们可以手动将该组件注册到 SQL Server 2005，部署的 T-SQL 范例程序代码将在下一节介绍。

图11-10 新建项目

一切部署完毕后，若执行结果不如你的预期，这时可以通过 SQL Server 2005 与 Visual Studio 2005 的协作，完成程序代码调试的操作。

图11-11 Management Studio 提供的 T-SQL 编写与管理环境

11.2.4 调试
在任何平台与开发环境中，调试都是必要的一环。而 Visual Studio 2005 与 SQL Server 2005 也提供了集成的调试环境。不管你是通过 Tabular Data Stream（TDS）或是 HTTP/SOAP 协议与 SQL Server 2005 沟通，还是通过 T-SQL 或 .NET 程序语言调用 SQL Server 2005 的对象，都可以直接进入集成的调试环境。

在新建“SQL Server Project”项目时，Visual Studio 2005 会自动在项目之下创建一个 TestScripts 子目录，其内放置 Test.sql 文件，你可以在该文件中编写 T-SQL 语法，调用先前用 .NET 写成的 SQL 对象，以此启动调试的机制。

Test.sql 文件内已经预先提供了调用各种 SQL Server 对象的语法范例，你可以将批注去除掉，修改成调用你先前编写的对象。接着在需要调试的 .NET 程序代码上设置断点，通过主菜单“调试”－“开始调试”选项或 F5 快捷键就可以开始调试。这时 Visual Studio 2005 会自动附加调试环境到 SQL Server 应用程序(sqlserver.exe)，并帮你执行 Test.sql 内的 T-SQL 语法，以打开该 .NET 所编写的对象，当执行到断点所在的程序代码时，就进入到单步调试的模式。

若上述步骤有问题，你仍可以通过 Visual Studio 2005 主菜单“调试”－“附加到进程(Attach to Process)”选项打开如图11-12的“附加到进程”对话框：

图11-12 手动 Attach SqlServer.exe 程序，通过在 SqlServer 内执行我们自行编写的 .NET 对象，进入到单步调试的环境

如图11-12 附加完 SqlServer.exe 程序后，再通过“SQL Server Management Studio”执行 T-SQL 对该 .NET 对象的调用，依然可以执行到程序代码断点的位置，进入单步调试的模式。

集成开发环境就介绍到此，我们接着来看通过 .NET 程序语言如何建立 SQL Server 内的五种数据库对象。