There are two types of user defined functions supported by
SQL Server: 1- Scalar-Valued function (SVF), which returns a single value, and
2- Table-valued function (TVF), which returns a set of rows. Here, we choose
for SVF, which returns a single value, such as a string, integer or bit value.
In Microsoft SQL Server 2005 we can create scalar-valued user-defined functions
in managed code using any Microsoft .NET Framework programming language. These
functions are accessible to Transact-SQL or other managed code.
The following are the codes for CLR-integrated user defined
SVF.
Listing 2
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class CLRFunctions
<Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read)> _
Public Shared Function CheckUsers(ByVal UserName As String, _
ByVal PassWord As String) _
As Boolean
Using conn As New SqlConnection("context connection=true")
conn.Open()
Dim sCmdString As String = _
"SELECT COUNT(*) FROM AccountTable where Username= '" & _
UserName & "' AND Password='" & _
PassWord & "'"
Dim cmd As New SqlCommand(sCmdString, conn)
If CType(cmd.ExecuteScalar(), Integer) > 0 Then
Return True
Else
Return False
End If
End Using
End Function
End ClassHere the custom attribute SqlFunction makes
the target Static function (i.e. Shared
in VB.Net) as a User-defined SVF, which is found in the
Microsoft.SqlServer.Server namespaces. The custom
attribute indicates whether or not the user-defined function (UDF) uses the
in-process provider to read data in the server. This is indicated by setting DataAccessKind to DataAccessKind.Read
or DataAccessKind.None. This is set in the above code
as:
SqlFunction(DataAccess:=DataAccessKind.Read)
