CLR Integration in SQL Server 2005 Using Visual Studio 2005 – Part 2
page 3 of 8
by Soyuj Kumar Sahoo
Feedback
Average Rating: 
Views (Total / Last 10 Days): 42931/ 154

Creating CLR functions

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 Class

Here 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)


View Entire Article

User Comments

Title: Update?   
Name: Apuma
Date: 2010-02-03 7:17:37 PM
Comment:
Can we see an example using VS 2008?
Title: Bout the article..   
Name: ainOmar
Date: 2007-05-13 10:57:53 PM
Comment:
can u simply attach more samples using .net clr
Title: Nice Article   
Name: Abhishek
Date: 2007-02-21 4:50:26 AM
Comment:
Nice article to work with .net CLR.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-01-18 2:27:46 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search