本文將解釋如何在SQL Server 2005中以盡可能最簡(jiǎn)單的方法創(chuàng)建基于.NET的CLR子例程。本文主要針對(duì)在服務(wù)器端使用SQL Server 2005的.NET開(kāi)發(fā)者。

一. 創(chuàng)建SQL Server 2005數(shù)據(jù)庫(kù)

這一節(jié)主要討論創(chuàng)建一個(gè)將用于本文中的數(shù)據(jù)庫(kù)和表格。詳見(jiàn)下列步驟：

· 執(zhí)行'Start->Programs->Microsoft SQL Server 2005->SQL Server Management Studio'，并使用必要的證書(shū)連接到你的SQL Server 2005實(shí)例。

· 一旦建立連接，使用'Object Explorer'打開(kāi)SQL Server 2005實(shí)例，然后右擊'databases'并選擇'New Database'(圖1)。圖1.然后，出現(xiàn)一個(gè)'New Database'對(duì)話框。輸入數(shù)據(jù)庫(kù)名'Sample'并點(diǎn)擊'OK'以便在同一個(gè)實(shí)例內(nèi)創(chuàng)建新的數(shù)據(jù)庫(kù)。· 一旦創(chuàng)建該數(shù)據(jù)庫(kù)，你一定能夠在'Object explorer'中看到相同的結(jié)果。· 使用'Object explorer'打開(kāi)'Sample'數(shù)據(jù)庫(kù)，右擊'tables'并選擇'new table'(圖2)。圖2.· 創(chuàng)建如下圖3所示的表格結(jié)構(gòu)，并用'emp'保存表格。圖3.· 一旦你創(chuàng)建了表格'emp'，打開(kāi)它(圖4)并使用下列數(shù)據(jù)進(jìn)行填充(圖5)。圖4.圖5.

二. 創(chuàng)建SQL Server 2005數(shù)據(jù)庫(kù).NET CLR存儲(chǔ)過(guò)程一旦你創(chuàng)建完數(shù)據(jù)庫(kù)和表格，接下來(lái)讓我們使用下列步驟在SQL Server 2005數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè).NET CLR存儲(chǔ)過(guò)程：· 轉(zhuǎn)到'Start -> Programs -> Microsoft Visual Studio 2005 -> Microsoft Visual Studio 2005'。· 在'New Project'對(duì)話框內(nèi)，從'Visual Basic'樹(shù)的'Project Type'下選擇'database'并選擇'SQL Server Project'作為模板，輸入名字'SampleAllInOne'。至此，該對(duì)話框應(yīng)該看起來(lái)如下圖6所示。圖6.· 一旦你點(diǎn)擊'OK'，將出現(xiàn)一個(gè)'New Database Reference'對(duì)話框。輸入你自己的實(shí)例名，并選擇數(shù)據(jù)庫(kù)'Sample'(在上一節(jié)創(chuàng)建的)，并且測(cè)試連接(圖7)。圖7· 一旦測(cè)試成功，點(diǎn)擊'OK'創(chuàng)建工程。· 然后系統(tǒng)出現(xiàn)一個(gè)對(duì)話框，它詢問(wèn)你是否啟動(dòng)CLR調(diào)試功能。請(qǐng)點(diǎn)擊'Yes'(圖8)。圖8· 使用'Solution explorer'，右擊'SampleAllInOne'工程，并點(diǎn)擊'Add->Stored Procedure'(以創(chuàng)建一個(gè)新的存儲(chǔ)過(guò)程)(圖9)。圖9· 之后，出現(xiàn)'Add New Item'對(duì)話框。在該對(duì)話框內(nèi)，選擇'Stored Procedure'作為模板并輸入名字'IncSalaries'，最后點(diǎn)擊'Add'(圖10)。圖10三. .NET CLR存儲(chǔ)過(guò)程編碼一旦創(chuàng)建'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上面是一個(gè)使用Visual Basic.NET 2005編寫(xiě)簡(jiǎn)單的基于CLR的存儲(chǔ)過(guò)程，你可以把它發(fā)布到SQL Server 2005中。該存儲(chǔ)過(guò)程名為'IncSalaries'，它只接收一個(gè)參數(shù)-'IncVal'。上面的存儲(chǔ)過(guò)程簡(jiǎn)單地使用參數(shù)中提供的值來(lái)提高表格'emp'中每一位雇員的工資值。四. .NET CLR存儲(chǔ)過(guò)程測(cè)試為了測(cè)試上面的存儲(chǔ)過(guò)程，請(qǐng)遵循下列步驟：· 使用'Solution Explorer'在'test scripts'中打開(kāi)'test.sql'(圖11)。圖11· 轉(zhuǎn)到最后一行，并如下修改代碼：--為運(yùn)行你的工程，請(qǐng)編輯你的工程的Test.sql文件。--這個(gè)文件位于Solution Explorer的'Test Scripts'文件夾下exec IncSalaries 50· 轉(zhuǎn)到'Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration'。· 在窗口內(nèi)，點(diǎn)擊'Surface Area Configuration for Features'(圖12)。圖12.· 啟動(dòng)CLR集成，如下所示(圖13)。圖13.· 按F5執(zhí)行整個(gè)方案。如果它的執(zhí)行中出現(xiàn)任何錯(cuò)誤，一切都會(huì)被顯示于輸出窗口中。· 如果存儲(chǔ)過(guò)程成功執(zhí)行，你應(yīng)該能夠看到所有的工資數(shù)增加了50。五. SQL Server 2005數(shù)據(jù)庫(kù)中的.NET CLR用戶定義函數(shù)現(xiàn)在，我們要在我們現(xiàn)有的方案上添加一個(gè)存儲(chǔ)函數(shù)。· 使用'Solution explorer'，右擊'SampleAllInOne'工程并轉(zhuǎn)到'Add->User-defined Function'(以創(chuàng)建一個(gè)新的存儲(chǔ)函數(shù))(圖14)。圖14· 之后，出現(xiàn)'Add New Item'對(duì)話框。在對(duì)話框內(nèi)，選擇'User defined function'作為模板，輸入名字'getNetSalary'，最后點(diǎn)擊'Add'(圖15)。圖15.一旦創(chuàng)建'getNetSalary.vb'，接下來(lái)按下列方式修改你的代碼：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然后，使用下列步驟測(cè)試上面的存儲(chǔ)過(guò)程：· 使用'Solution Explorer'在'test scripts'中打開(kāi)'test.sql'(見(jiàn)前圖11)。· 轉(zhuǎn)到并修改如下代碼：--為運(yùn)行你的工程，請(qǐng)編輯你的工程的test.sql文件。--這個(gè)文件位于Solution Explorer的'Test Scripts'文件夾下--exec IncSalaries 50select dbo.getNetSalary(empno) from sample.dbo.emp按F5執(zhí)行該函數(shù)，你應(yīng)該能夠看到相應(yīng)結(jié)果。六. 定義.NET CLR用戶定義聚合器現(xiàn)在，我們開(kāi)始在上一節(jié)基礎(chǔ)上在我們的現(xiàn)有方案上添加一個(gè)聚合函數(shù)。· 使用'Solution explorer'，右擊'SampleAllInOne'工程并轉(zhuǎn)到'Add->Aggregate'(以創(chuàng)建一個(gè)新的聚合函數(shù))(圖16)。圖16· 之后，出現(xiàn)'Add New Item'對(duì)話框。在該對(duì)話框內(nèi)，選擇'Aggregate'作為模板，輸入名字'NetSal'并點(diǎn)擊'Add'(圖17)。圖17一旦創(chuàng)建'NetSal.vb'，接下來(lái)以下列方式修改你的代碼：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為了測(cè)試上面的存儲(chǔ)過(guò)程，遵循下列步驟:· 使用'Solution Explorer'，然后在'test scripts'中打開(kāi)'test.sql'。· 轉(zhuǎn)到最后一行并修改如下代碼：--為運(yùn)行你的工程，請(qǐng)編輯你的工程的test.sql文件。--這個(gè)文件位于Solution Explorer的'Test Scripts'文件夾下--exec IncSalaries 50--select dbo.getNetSalary(empno) from sample.dbo.empselect dbo.NetSal(sal) from sample.dbo.emp按F5執(zhí)行該函數(shù)，你應(yīng)該能夠觀察到相應(yīng)的結(jié)果。