探讨SQL Server 2005.NET CLR编程

图1.

图2.

图3.

图4.

图5.

图1.

图2.

图3.

图4.

图5.

　　三. .NET CLR存储过程编码

　　一旦创建"IncSalaries.vb"，以下列方法修改你的代码，如下所示：

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub IncSalaries(ByVal IncVal As SqlDouble) 　'在此添加你的代码 　Using cn As New SqlConnection("context connection=true") 　Dim cmd As New SqlCommand("update sample.dbo.emp set 　sal = sal + " & IncVal.ToString， cn) 　cmd.Connection.Open() 　cmd.ExecuteNonQuery() 　End Using End Sub End Class

　　上面是一个使用Visual Basic.NET 2005编写简单的基于CLR的存储过程，你可以把它发布到SQL Server 2005中。该存储过程名为"IncSalaries"，它只接收一个参数-"IncVal"。上面的存储过程简单地使用参数中提供的值来提高表格"emp"中每一位雇员的工资值。

　　四. .NET CLR存储过程测试

　　为了测试上面的存储过程，请遵循下列步骤：

　　· 使用"Solution Explorer"在"test scripts"中打开"test.sql"(图11)。

图11

　　· 转到最后一行，并如下修改代码：

--为运行你的工程，请编辑你的工程的Test.sql文件。 --这个文件位于Solution Explorer的"Test Scripts"文件夹下 exec IncSalaries 50

　　· 转到"Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration"。

　　· 在窗口内，点击"Surface Area Configuration for Features"(图12)。

图12.

　　· 启动CLR集成，如下所示(图13)。

图13.

　　· 按F5执行整个方案。如果它的执行中出现任何错误，一切都会被显示于输出窗口中。

　　· 如果存储过程成功执行，你应该能够看到所有的工资数增加了50。

　　五. SQL Server 2005数据库中的.NET CLR用户定义函数

　　现在，我们要在我们现有的方案上添加一个存储函数。

　　· 使用"Solution explorer"，右击"SampleAllInOne"工程并转到"Add->User-defined Function"(以创建一个新的存储函数)(图14)。

图14

　　· 之后，出现"Add New Item"对话框。在对话框内，选择"User defined function"作为模板，输入名字"getNetSalary"，最后点击"Add"(图15)。

图15.

　　一旦创建"getNetSalary.vb"，接下来按下列方式修改你的代码：

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server PartialPublic Class UserDefinedFunctions <Microsoft.SqlServer.Server.SqlFunction (DataAccess:=DataAccessKind.Read)> _ Public Shared Function getNetSalary(ByVal empno As SqlString) As SqlDouble 　'在此添加你的代码 　Dim sal As Double 　Using cn As New SqlConnection("context connection=true") 　Dim cmd As New SqlCommand("select sal from 　sample.dbo.emp where empno='" & empno.ToString & "'"， cn) 　cmd.Connection.Open() 　sal = CType(cmd.ExecuteScalar， Double) 　cmd.Dispose() 　End Using 　Dim hra As Double = sal * 10 / 100 　Dim ta As Double = 200 　Dim gross As Double = sal + hra + ta 　Dim epf As Double = sal * 5 / 100 　Dim net As Double = gross - epf 　Return net End Function End Class

　　然后，使用下列步骤测试上面的存储过程：

　　· 使用"Solution Explorer"在"test scripts"中打开"test.sql"(见前图11)。

　　· 转到并修改如下代码：

--为运行你的工程，请编辑你的工程的test.sql文件。 --这个文件位于Solution Explorer的"Test Scripts"文件夹下 --exec IncSalaries 50 select dbo.getNetSalary(empno) from sample.dbo.emp

　　按F5执行该函数，你应该能够看到相应结果。

　　六. 定义.NET CLR用户定义聚合器

　　现在，我们开始在上一节基础上在我们的现有方案上添加一个聚合函数。

　　· 使用"Solution explorer"，右击"SampleAllInOne"工程并转到"Add->Aggregate"(以创建一个新的聚合函数)(图16)。

图16

　　· 之后，出现"Add New Item"对话框。在该对话框内，选择"Aggregate"作为模板，输入名字"NetSal"并点击"Add"(图17)。

图17

　　一旦创建"NetSal.vb"，接下来以下列方式修改你的代码：

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server <Serializable()> _ <Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)> _ PublicStructure NetSal Public SumNetSal As SqlDouble Public Sub Init() '在此加入你的代码 SumNetSal = 0 End Sub Public Sub Accumulate(ByVal value As SqlDouble) 　'在此加入你的代码 　Dim sal As Double = CType(value， Double) 　Dim hra As Double = sal * 10 / 100 　Dim ta As Double = 200 　Dim gross As Double = sal + hra + ta 　Dim epf As Double = sal * 5 / 100 　Dim net As Double = gross - epf 　SumNetSal += net End Sub Public Sub Merge(ByVal obj As NetSal) 　'在此加入你的代码 　SumNetSal += obj.SumNetSal End Sub Public Function Terminate() As SqlDouble 　'在此加入你的代码 　Return SumNetSal End Function EndStructure

　　为了测试上面的存储过程，遵循下列步骤:

　　· 使用"Solution Explorer"，然后在"test scripts"中打开"test.sql"。

　　· 转到最后一行并修改如下代码：

--为运行你的工程，请编辑你的工程的test.sql文件。 --这个文件位于Solution Explorer的"Test Scripts"文件夹下 --exec IncSalaries 50 --select dbo.getNetSalary(empno) from sample.dbo.emp select dbo.NetSal(sal) from sample.dbo.emp

　　按F5执行该函数，你应该能够观察到相应的结果。