Why CLR Database User Defined Scalar and Table Value Function

Introduction

In this article, we will learn about CLR database user-defined scalar and table-valued function in C#.

Description

Why use CLR user-defined scalar and table-valued function instead of T-SQL user-defined scalar and table value function?

1. If you'd prefer to write the code in (VB.Net, C#, etc.) than SQL.
2. If you want to do complex operations on data in CLR.
3. If you want to be close to the data (i.e. not in ASP.NET or a WinForms app etc.)
4. The CLR has RegEx but SQL has not.
5. You can call web services in CLR, but T-SQL does not.
6. The CLR has better performance (if you had to do a lot of math on every row).
7. You can re-use code.

CLR database user-defined scalar and table-valued function

This article will show how to create two types of CLR database functions,

1. A user-defined scalar valued function that returns a single value.
2. A user-defined table-valued function that returns a table.

Let’s see CLR database function practically:

Step 1

Create a library project (CLRProcedures) in visual studio as below.

 

Create one class files “CLRScalarAndTableValuedFunctions.cs”.

In file “CLRScalarAndTableValuedFunctions.cs” add the below lines of code.

using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace CLRProcedures
{
    public class CLRScalarAndTableValuedFunctions
    {
        private class User
        {
            public int UserID { get; set; }
            public string EmailAddress { get; set; }
            public User(int userId, string emailAddress)
            {
                this.UserID = userId;
                this.EmailAddress = emailAddress;
            }
        }

        [SqlFunction(DataAccess = DataAccessKind.Read)]
        public static int GetUserCountByEmail(string emailAddress)
        {
            using (SqlConnection connection = new SqlConnection("context connection=true"))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand(@"SELECT COUNT(*) FROM [dbo].[Users] WHERE EmailAddress=@EmailAddress", connection);
                SqlParameter modifiedSinceParam = cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar);
                modifiedSinceParam.Value = emailAddress;
                return (int)cmd.ExecuteScalar();
            }
        }

        [SqlFunction(DataAccess = DataAccessKind.Read,
            FillRowMethodName = "FillRow")]
        public static IEnumerable GetUsersByEmail(string emailAddress)
        {
            ArrayList users = new ArrayList();
            using (SqlConnection connection = new SqlConnection("context connection=true"))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand(@"SELECT UserID,EmailAddress FROM [dbo].[Users] WHERE EmailAddress=@EmailAddress", connection);
                SqlParameter modifiedSinceParam = cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar);
                modifiedSinceParam.Value = emailAddress;
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        users.Add(new User((int)reader.GetSqlInt32(0),(string)reader.GetSqlString(1)));
                    }
                }
            }
            return users;
        }
        [SqlFunction(DataAccess = DataAccessKind.Read,
            FillRowMethodName = "FillRow",
            TableDefinition = "UserID int, EmailAddress nvarchar(150)")]
        public static IEnumerable GetUsersByEmailWithTableDefinition(string emailAddress)
        {
            ArrayList users = new ArrayList();
            using (SqlConnection connection = new SqlConnection("context connection=true"))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand(@"SELECT UserID,EmailAddress FROM [dbo].[Users] WHERE EmailAddress=@EmailAddress", connection);
                SqlParameter modifiedSinceParam = cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar);
                modifiedSinceParam.Value = emailAddress;
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        users.Add(new User((int)reader.GetSqlInt32(0), (string)reader.GetSqlString(1)));
                    }
                }
            }
            return users;
        }
        public static void FillRow(object user, out SqlInt32 UserID, out string EmailAddress)
        {
            User _user = (User)user;
            UserID = _user.UserID;
            EmailAddress = _user.EmailAddress;

        }
    }
}

Step 2

Open project property and set output path=C:\dlls as below,

 

Step 3

Build or compile library project and verify dll copied in output folder (C:\dlls).

Step 4

Open new query window for the targeted database in MSSQL Server Management Studio and add the below SQL statement in the query window to create a “Users” table with some data.

CREATE TABLE [dbo].[Users](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](200) NOT NULL,
    [RealName] [nvarchar](200) NOT NULL,
    [EmailAddress] [varchar](150) NULL,
    [ModifiedDate] [date] NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
    [UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Users] ON

GO
INSERT [dbo].[Users] ([UserID], [UserName], [RealName], [EmailAddress], [ModifiedDate]) VALUES (44, N'dinesh.singh', N'Dinesh', N'[email protected]', CAST(N'2018-05-15' AS Date))
GO
INSERT [dbo].[Users] ([UserID], [UserName], [RealName], [EmailAddress], [ModifiedDate]) VALUES (45, N'ramesh.singh', N'Ramesh', N'[email protected]', CAST(N'2018-05-15' AS Date))
GO
SET IDENTITY_INSERT [dbo].[Users] OFF
GO

Step 5

Let’s create a CLR database scalar valued function that returns a value.

Open SQL management studio query window and write & execute below SQL statement to create scalar value function,

CREATE ASSEMBLY StoredProcedures from 'c:\dlls\CLRProcedures.dll' WITH PERMISSION_SET = SAFE

CREATE FUNCTION CLRGetUserCountByEmail(@emailAddress nvarchar(150))
RETURNS INT
AS EXTERNAL NAME StoredProcedures.[CLRProcedures.CLRScalarAndTableValuedFunctions].GetUserCountByEmail

Note

First create assembly statement, after that run create function statement. Don’t run both at the same time otherwise you will get an error as below.

 

Let’s see whether CLR database assembly and function are created in SQL database object explorer or not.

 

From the above screen, CLR assembly and scalar valued function have been created in SQL database. Now let’s run the created CLR scalar valued function “CLRGetUserCountByEmail” and see the output.

 

Now you can see we are getting results from SQL database through CLR scalar value function “CLRGetUserCountByEmail”.

Step 6

Let’s create a CLR database table-valued function that returns a table.

Open SQL management studio query window and write & execute the below SQL statement to create the table-valued function,

CREATE FUNCTION CLRGetUsersByEmail(@emailAddress nvarchar(150))
RETURNS TABLE (
   UserID int,
   EmailAddress nvarchar(150)
)
AS EXTERNAL NAME StoredProcedures.[CLRProcedures.CLRScalarAndTableValuedFunctions].GetUsersByEmail

Let’s see whether CLR database table-valued function is created in SQL database object explorer or not.

 

Now it is clear that table-valued function has been created. Now let’s execute this table-valued function and see the result.

 

Now you can see we are getting results from SQL database through CLR database table valued function “CLRGetUsersByEmail”.

Step 7

Let’s create a CLR database table-valued function that returns a table with custom table definition.

Open SQL management studio query window and write and execute the below SQL statement to create the table-valued function with custom table definition:

CREATE FUNCTION CLRGetUsersByEmailWithTableDefinition(@emailAddress nvarchar(150))
RETURNS TABLE (
   UserID int,
   EmailAddress nvarchar(150)
)
AS EXTERNAL NAME StoredProcedures.[CLRProcedures.CLRScalarAndTableValuedFunctions].GetUsersByEmailWithTableDefinition

Let’s see whether CLR database table-valued function is created in SQL database object explorer or not.

 

Now it is clear that table-valued function has been created. Now let’s execute this table-valued function and see the result.

 

Now you can see we are getting results from SQL database through CLR database table valued function “CLRGetUsersByEmailWithTableDefinition” with custom table definition. The result is the same as we were getting without table definition.

Points of interest

If you are getting an error as below while executing CLR database user-defined scalar and table-valued function,

 

 

Then you must run the below SQL statement to enable CLR.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Now you can run CLR procedure successfully.

To disable CLR integration, use the CLR enabled option of the sp_configure stored procedure.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO

To un-install CLR database scalar and table-valued function, you must write & execute below SQL statement in the MSSQL server management studio query window.

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CLRGetUserCountByEmail')
DROP FUNCTION CLRGetUserCountByEmail

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CLRGetUsersByEmail')
DROP FUNCTION CLRGetUsersByEmail

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CLRGetUsersByEmailWithTableDefinition')
DROP FUNCTION CLRGetUsersByEmailWithTableDefinition

To un-install CLR database assembly you must write & execute the below SQL statement in the MSSQL server management studio query window.

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'StoredProcedures')
DROP ASSEMBLY StoredProcedures

Note
So far, I have not able to understand the uses of CLR database function attribute “TableDefinition = "UserID int, EmailAddress nvarchar(150)" but I promise as soon as I get a reasonable answer I will post an article on it.

Congratulation you have successfully created, installed and uninstalled CLR database user-defined scalar and table-valued function. If you have any query or concern just do let me know or put it in the comment box and I will respond as soon as possible. I am open to discussing anything, even silly questions as well. If you have any suggestion related to this article, please let me know and I promise I will improve this article to a maximum level.

Summary

In this article, we have learned how to create, install and uninstall CLR database user-defined scalar and table-valued function and assembly in C#.