探讨SQL Server 2005.NET CLR编程

核心提示： ◆之后，出现"Add New Item"对话框，探讨SQL Server 2005.NET CLR编程(5)，在对话框内，选择"User defined function"作为模板，接下来按下列方式修改你的代码：Imports SystemImpor

◆之后，出现"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

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