SQL Server - How to Email the Database Using CLR (C #)

Views: 463
Reading Time: 5 minutes

Hello people,
Good afternoon!
All right with you ?

In this post I will demonstrate how to send emails through the database using CLR (C #). In the post SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail) I had already demonstrated how to send emails through SQL Server Database Mail, and this time I will show you how to do this directly from C #, without even having to configure anything in SQL Server to send the email.

Simple Email Sending

In this first example, I will demonstrate the simplest way to send an email in HTML format, with attachments, through the CLR using the C # programming language. See how easy it is:

More complete email sending

Already in this second source code, I will demonstrate a more complete and organized way to create an Email Stored Procedure in CLR. To do this, I will separate the authentication information into separate classes and add the following additional features:

  • Input Parameter Validation
  • E-mail audit log recording
  • RETRY routine to try to send email to 10x in case of failure
  • Try..Catch for Error and Exception Handling

Prerequisites

Before demonstrating the source code for the email sending Stored Procedure, you will need to create the dependent classes below, in addition to the Return class, which I use to send error messages to the database. The code of this class you enter the post SQL Server - How to send warnings and error messages to the bank through CLR (C #).

Server.cs

Utils.cs

Stored Procedure source code stpEnvia_Email:

Examples of use

See below some examples of how to use this Stored Procedure in your daily life.

Simple Email Submission:

Sending email to multiple recipients:

Sending email with attachment:

Sending email with multiple attachments:

I hope you enjoyed the post and see you next time.
Hug!