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?

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

A user-defined scalar valued function that returns a single value.
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#.

7 Comments

Join the conversation! Your thoughts help the community grow.

Viknaraj Manogararajah8y ago
Nice Article, Thank you for sharing
ameer adel8y ago
Wonderful explanation, thanks for the effort. will this run successfully with obfuscated dll files? lets say with using the ConfusEx, and does this solution add a layer of security, if without obfuscation? what type of added security does it offer? if the dll file was edited by someone else, wont that be bad? whats the added execution time?
- Dinesh Kushwaha8y ago
  .NET DLL files are Assemblies - and that means they aren't "editable". They are readable (and always will be, even if you obfuscate the content to make it harder) because the system needs to be able to read them in order to execute the code they contain. But they aren't "editable" in any useful sence- there is no "Assembly editor" which allows me to make changes directly to your DLL. That doesn't mean I can't edit them using a hex editor and utilities (https://github.com/0xd4d/dnSpy) if I know exactly what I'm doing - but they can't be changed in tech sense of altering your code as C# and the DLL "magically" reflects that. In order for tech DLL to change, it would need to be recompiled and after compilation, you have to install this assembly in the database again in order to that you would require SQL sysadmin permission since you are a sysadmin SQL user you can do many more thing that is not desirable. So if you are "changing lines in DLL files" then that implies that you have added the DLL Project to your solution - which is fine, and allowed because you often want to develop the EXE and one or more DLL Assemblies at the same time. But it's not the only way. Backup first, then remove the Project from the solution and the references from the other projects. Then create a new Solution, and include the DLL Assembly as its Project. Build that for Release. Go back to your original Solution, and add a Reference to the newly created DLL in its Release folder using the Browse button. Now your app will build and will be able to use the DLL content, but it won't be able to change it. Do let me know if it makes sense to you?
- Dinesh Kushwaha8y ago
  For code access security you can refer this link : https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/security/clr-integration-code-access-security?view=sql-server-2017
Tridip Bhattacharjee8y ago
In what kind of scenario people go for clr store procedure. thanks
- Dinesh Kushwaha8y ago
  T-SQL is more powerful and efficient in data manipulation scenarios, whereas the CLR is richer and more efficient in scenarios that are computation intensive or involve procedural or iterative logic or string manipulation.
- Dinesh Kushwaha8y ago
  You can refer this link for more information: http://www.itprotoday.com/software-development/clr-or-not-clr-question