How to send database mail using a CLR stored procedure

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:

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

The following are the drawbacks of the CLR SPs

It is not useful to execute simple queries. In such cases, the standard stored procedures are a better option
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:

When we want to implement the complex business logic
The application flow is CPU intensive. As I mentioned that the CLR SPs are compiled and managed; hence it gives better performance
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
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:

Create a Microsoft.Net class library. The class library contains the code which sends the email to the specified sender
Build the Microsoft.Net class library to generate the dynamic link library (DLL) file
Create a SQL Server assembly using the DLL file that is generated by the Microsoft.NET class library
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:

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:

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.

1 2	using System.Net; using System.Net.Mail;

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

namespace SendDatabaseMail

{

public class SendMail_ClassLib

{

}

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

public static void SendEmailUsingCLR()

{

string _sender = "nisargupadhyay87@outlook.com";

string _password = "YourPassword";

string _Receiver = "nisargupadhyay87@gmail.com";

SmtpClient client = new SmtpClient("smtp-mail.outlook.com");

client.Port = 587;

client.DeliveryMethod = SmtpDeliveryMethod.Network;

client.UseDefaultCredentials = false;

System.Net.NetworkCredential credentials =

new System.Net.NetworkCredential(_sender, _password);

client.EnableSsl = true;

client.Credentials = credentials;

try

{

var mail = new MailMessage(_sender,_Receiver);

mail.Subject = "Voila..!! This email has been send using CLR Assembly.";

mail.Body = "Voila..!! This email has been send using CLR Assembly.";

client.Send(mail);

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

throw ex;

}

In the image below is the entire code:

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:

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:

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:

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:

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:

1	AssemblyName.[AssemblyName.ClassNameInProgram].MethodNameInProgram

To create a CLR SP, use the code below:

CREATE PROCEDURE spSendEmail

EXTERNAL NAME SendDatabaseMail.[SendDatabaseMail.SendMail_ClassLib].SendEmailUsingCLR

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:

use master

EXEC sp_configure 'clr enabled', 1

RECONFIGURE WITH OVERRIDE

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

use master

alter database [DBA] set trustworthy on

Send Test email

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

use DBA

exec spSendEmail

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

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.

Author
Recent Posts

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

Latest posts by Nisarg Upadhyay (see all)

Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
Changing the location of FILESTREAM data files in SQL Database - October 14, 2022