Nisarg Upadhyay
C# Code to create .Net class library

How to send database mail using a CLR stored procedure

February 3, 2020 by

In this article, I am going to explain how we can send an email from SQL Server express edition using a CLR stored procedure. First, let me explain about the CLR stored procedures.

What are the CLR Stored procedures

The CLR is a common language runtime, and the SQL Server stored procedures are a collection of the SQL Queries and the command logic. The stored procedures are compiled and stored in the database. The CLR stored procedures are the combination of the CLR and stored procedure. CLR SPs are the .Net objects which run in the SQL Server memory of the SQL Server database.

Both CLR SPs and extended stored procedures (Stored procedures, functions, and triggers) accesses the system resources and both runs under the database memory. CLR SPs can perform the same tasks that can be performed by the extended stored procedures, but the only difference is that the extended stored procedures are the unmanaged codes and CLR SPs contains the managed code.

The extended stored procedures use the same process space as a database engine that can affect the performance of the database, but standard stored procedures and functions are considered the best for the data-oriented tasks. CLR SPs are managed objects; hence it runs as per the specification of the common language runtime (CLR). It uses the .NET classes, which makes the implementation of the complex logic, complex string operations iterations and data encryption much easier. It can also replace the complex business logic which is difficult to implement using standard stored procedures or functions. Moreover, CLR SPs are compiled and managed code; hence it gives better performance.

Following are the benefits of the CLR SPs:

  1. It’s a managed code; it ensures memory management and type safety
  2. It uses the .Net classes; it is easy to implement the complex logic
  3. It provides the object-oriented programming capabilities enables polymorphism, encapsulations, and inheritance
  4. It can be written in any language, supported by the Microsoft.NET framework, thus easy for the programmer to develop it
  5. It can be used with the Oracle 10g R2 and later database version

The following are the drawbacks of the CLR SPs

  1. It is not useful to execute simple queries. In such cases, the standard stored procedures are a better option
  2. The deployment is difficult when you are trying to deploy the CLR assemblies as a part of continuous deployment. There are few problems:
    • We cannot drop and create the schema bounded assemblies
    • Difficulties in changing or alter the file path due to permission issues

CLR SPs can be used under the following scenarios:

  1. When we want to implement the complex business logic
  2. The application flow is CPU intensive. As I mentioned that the CLR SPs are compiled and managed; hence it gives better performance
  3. If you want to perform the tasks that are not possible by using T-SQL. For example, accessing the system resources, implement the cryptography and access web services
  4. Any operation or task which requires higher safety

To create the CLR Store procedures, we must create a Microsoft.Net class library and SQL Server assembly. Considering the topic of the article, I am not going to include detailed information on the .Net class library and SQL Server assemblies. Following gives a basic idea of Microsoft.Net class library and SQL Server assemblies.

What is Microsoft.Net class library?

Microsoft.Net framework class library is the collection of the various classes, interfaces, namespaces, and values type which can be used in .Net application or program. You can read more about the Microsoft.Net class library here.

What are SQL Server assemblies?

The SQL Server assemblies are the DLL files that are imported in the SQL Server database. Once we import the DLL and register it within the SQL Server stored procedures. The assemblies are a great way to expand the functionality of the native functionalities of the SQL Server. You can read more about the SQL Server assemblies here.

Demonstration

Now, as I mentioned that to explain the usage of the CLR SPs, we are going to use it to send the email from the SQL Server.

As we know, the SQL Server express edition does not provide the database mail feature; we can use the CLR Store procedure to send the email.

In the demonstration, I am going to create a CLR SP which is going to use to perform the following steps:

  1. Create a Microsoft.Net class library. The class library contains the code which sends the email to the specified sender
  2. Build the Microsoft.Net class library to generate the dynamic link library (DLL) file
  3. Create a SQL Server assembly using the DLL file that is generated by the Microsoft.NET class library
  4. Create a CLR SP using the SQL Server assembly

Create Microsoft.Net class library

To create a .Net class library, open Visual studio 2017 Click on files Hover on New select “New Project.” See the following image:

Create new class library project for CLR Stored procedure

In the New Project dialog box, select the Class Library (.NET Framework). In the Name box provide the desired name and in the Location box enter the location of the visual studio project and click OK:

Create new project

The System.Net and System.Net namespaces contain the classes which can be used to send the email. To send the mail using the class library, we must import the System.Net and System.Net.Mail namespaces in the cs file. To import the namespace, we must use the following code.

In the SendDatabaseMail namespace, rename the class from Class1 to Sendmail_ClassLib. See the following code:

Now, lets create the method named SendEmailUsingCLR() in Sendmail_ClassLib. This method will be used to send the email. Following is the code:

In the image below is the entire code:

C# Code to create .Net class library

Build the class library

Now to build the class library, in the Solution Explorer, right-click on the SendDatabaseMail project and from the context menu, choose the Build command:

Build the class library

Alternatively, you can build the solution by pressing the Ctrl+B shortcut.

Once the project is built successfully, the DLL file is generated on the C:\Visual Studio Projects\SendDatabaseMail\SendDatabaseMail\bin\Debug location. See the image:

Dll file for CLR Stored Procedure

Create Assembly in SQL Server database

Once the class library has been built successfully, and the DLL file is generated, we will use it to create a SQL Server assembly. To do that, open SQL Server Management Studio, connect to the database engine. In Object Explorer, expand the DBA database, under the Programmability folder, right click on the Assemblies folder and from the context menu, choose the New Assembly command:

New Assembly for CLR Stored procedure

The New Assembly window opens. In the Path to the assembly text box, provide the location of the DLL file, which is generated by building the class library. We want to keep this assembly as unsafe hence, choose Unrestricted from the Permission set drop-down box:

New Assembly

Click OK to close the dialog box.

Create CLR Stored procedure

Once the assembly has been created, we will create a CLR SP. Please note that the EXTERNAL NAME must be set in the following way:

To create a CLR SP, use the code below:

Once the CLR SP is created, we must deploy it to use it. For deployment, you must enable the CLR integration on the database to do that, execute the following code:

Once CLR integration is enabled, we must set the database TRUSTWORTHY. To do that, execute the following code:

Send Test email

Once CLR SP has been created, execute the following code to test it.

The procedure has been executed successfully. The following is the screenshot of the email.

Email sent by CLR Stored procedure

Summary

In this article, I have explained about the SQL Server CLR Stored procedure and how we can use it to send the database email.

Nisarg Upadhyay
Database mail, Stored procedures, T-SQL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

4,222 Views