本文將解釋如何在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執行該函數，你應該能夠觀察到相應的結果。