探讨SQL Server 2005.NET CLR编程

本文将解释如何在SQL Server 2005中以尽可能最简单的方法创建基于.NET的CLR子例程。本文主要针对在服务器端使用SQL Server 2005的.NET开发者。

一. 创建SQL Server 2005数据库

这一节主要讨论创建一个将用于本文中的数据库和表格。详见下列步骤：

· 执行'Start->Programs->Microsoft SQL Server 2005->SQL Server Management Studio'，并使用必要的证书连接到你的SQL Server 2005实例。

· 一旦建立连接，使用'Object Explorer'打开SQL Server 2005实例，然后右击'databases'并选择'New Database'(图1)。图1.然后，出现一个'New Database'对话框。输入数据库名'Sample'并点击'OK'以便在同一个实例内创建新的数据库。· 一旦创建该数据库，你一定能够在'Object explorer'中看到相同的结果。· 使用'Object explorer'打开'Sample'数据库，右击'tables'并选择'new table'(图2)。图2.· 创建如下图3所示的表格结构，并用'emp'保存表格。图3.· 一旦你创建了表格'emp'，打开它(图4)并使用下列数据进行填充(图5)。图4.图5.

二. 创建SQL Server 2005数据库.NET CLR存储过程一旦你创建完数据库和表格，接下来让我们使用下列步骤在SQL Server 2005数据库中创建一个.NET CLR存储过程：· 转到'Start -> Programs -> Microsoft Visual Studio 2005 -> Microsoft Visual Studio 2005'。· 在'New Project'对话框内，从'Visual Basic'树的'Project Type'下选择'database'并选择'SQL Server Project'作为模板，输入名字'SampleAllInOne'。至此，该对话框应该看起来如下图6所示。图6.· 一旦你点击'OK'，将出现一个'New Database Reference'对话框。输入你自己的实例名，并选择数据库'Sample'(在上一节创建的)，并且测试连接(图7)。图7· 一旦测试成功，点击'OK'创建工程。· 然后系统出现一个对话框，它询问你是否启动CLR调试功能。请点击'Yes'(图8)。图8· 使用'Solution explorer'，右击'SampleAllInOne'工程，并点击'Add->Stored Procedure'(以创建一个新的存储过程)(图9)。图9· 之后，出现'Add New Item'对话框。在该对话框内，选择'Stored Procedure'作为模板并输入名字'IncSalaries'，最后点击'Add'(图10)。图10三. .NET CLR存储过程编码一旦创建'IncSalaries.vb'，以下列方法修改你的代码，如下所示：Imports SystemImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerPartial 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 UsingEnd SubEnd 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 SystemImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerPartialPublic 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 netEnd FunctionEnd Class然后，使用下列步骤测试上面的存储过程：· 使用'Solution Explorer'在'test scripts'中打开'test.sql'(见前图11)。· 转到并修改如下代码：--为运行你的工程，请编辑你的工程的test.sql文件。--这个文件位于Solution Explorer的'Test Scripts'文件夹下--exec IncSalaries 50select 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 SystemImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports Microsoft.SqlServer.Server<Serializable()> _<Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)> _PublicStructure NetSalPublic SumNetSal As SqlDoublePublic Sub Init()'在此加入你的代码SumNetSal = 0End SubPublic 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 += netEnd SubPublic Sub Merge(ByVal obj As NetSal)　'在此加入你的代码　SumNetSal += obj.SumNetSalEnd SubPublic Function Terminate() As SqlDouble　'在此加入你的代码　Return SumNetSalEnd FunctionEndStructure为了测试上面的存储过程，遵循下列步骤:· 使用'Solution Explorer'，然后在'test scripts'中打开'test.sql'。· 转到最后一行并修改如下代码：--为运行你的工程，请编辑你的工程的test.sql文件。--这个文件位于Solution Explorer的'Test Scripts'文件夹下--exec IncSalaries 50--select dbo.getNetSalary(empno) from sample.dbo.empselect dbo.NetSal(sal) from sample.dbo.emp按F5执行该函数，你应该能够观察到相应的结果。