SQL CLR

Pro vývoj SQL CLR objektů je potřeba mít nainstalovaný databázový server Microsoft SQL Server 2005 ve verzi Express a vyšší. Jako vývojové prostředí je nejlepší použít Microsoft Visual C# 2008 Express Edition. Pro správu databáze a testování SQL skriptů je dobré mít také nainstalovaný program Microsoft SQL Server Management Studio ve verzi 2005 a vyšší. Všechny zmíněné programy jsou dostupné ke stažení zdarma.
Ve výchozím nastavení Microsoft SQL Server je podpora SQL CLR zakázána. Je tedy potřeba ji nejprpve povolit, k tomu slouží následující skript:

-- Enable CLR support
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Ukázková aplikace je tvořena pouze jednou třídou. Používá základní knihovny .NET frameworku. SQL CLR integrace zajišťuje dostupnost jen některých knihoven. Jakmile by byla použita například knihovna System.Drawing, musela by se dodatečně nahrát i se všemi referencovanými knihovnami do SQL Serveru.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Net.Mail;
using System.Net;

public class SendMail
{
    /// <summary>
    /// Sends e-mail using configuration from db extended properties
    /// </summary>
    public static void Send(string from, string recipients, string subject, string body)
    {
        // Create email message
        using (MailMessage message = new MailMessage(from, recipients))
        {
            message.BodyEncoding = System.Text.Encoding.UTF8;
            message.Subject = subject;
            message.Body = body;

            // Send e-mail
            GetSmtpClient().Send(message);
        }
    }

    /// <summary>
    /// Gets instance of SmtpClient class
    /// </summary>
    /// <returns></returns>
    private static SmtpClient GetSmtpClient()
    {
        // Initialize SMTP client properties
        string smtpServer = GetExtendedProperty("SmtpServer");
        string smtpUser = GetExtendedProperty("SmtpUser");
        string smtpPassword = GetExtendedProperty("SmtpPassword");
        int port = int.Parse(GetExtendedProperty("SmtpPort") ?? "25");
        bool enableSsl = int.Parse(GetExtendedProperty("SmtpEnableSsl") ?? "0") == 1;

        SmtpClient smtp = new SmtpClient(smtpServer);
        if (!string.IsNullOrEmpty(smtpUser)) // Use authentication
        {
            smtp.UseDefaultCredentials = false;
            smtp.Credentials = new NetworkCredential(smtpUser, smtpPassword);
            smtp.EnableSsl = enableSsl; // Force to use SSL
            smtp.Port = port;
        }
        else
        {
            smtp.UseDefaultCredentials = true;
        }

        return smtp;
    }

    /// <summary>
    /// Gets extended property from current context
    /// </summary>
    /// <param name="name">Name of the extended property</param>
    /// <returns></returns>
    private static string GetExtendedProperty(string name)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();

            string query = string.Format(
                @"SELECT value FROM sys.extended_properties WHERE [Name] = '{0}'",
                name
                );

            SqlCommand command = new SqlCommand(query, connection);
            using (SqlDataReader reader = command.ExecuteReader())
            {
                reader.Read();
                
                return reader.GetString(0);
            }
        }
    }
};

Aby kód mohl být spouštěn z SQL Serveru je potřeba, aby byl nejprve zkompilován. Nejjednodušší způsob je vytvořit v aplikaci Visual C# nový projekt typu Class Library.
Kód obsahuje jednu veřejnou metodu, která se jmenuje Send. Právě tato metoda bude volána z T-SQL skriptů. Metoda Send akceptuje čtyři parametry – adresu odesílatele, adresy příjemců oddělené středníkem, předmět e-mailu a text, který bude obsažený v jeho těle. V ukázce je možné odesílat e-maily pouze v prostém textu.
Nastavení SMTP serveru je uloženo v metadatech databáze, ze které je skript spuštěn. konkrétně se jedná o sekci Extended properties.

Samotné zkompilování knihovny nezpřístupní kód v SQL Serveru. Je potřeba knihovnu do SQL Serveru nahrát pomocí speciálních příkazů. Jelikož kód přistupuje i k prostředkům mimo SQL Server, je potřeba publikovat knihovnu s příznakem PERMISSION_SET = UNSAFE.
Nakonec je vytvořena T-SQL procedura, která pouze volá metodu Send z předchozí publikované knihovny. T-SQL procedura musí mít stejný počet parametrů jako má metoda Send.
Proměnná AssemblyFolderPath obsahuje cestu ke složce, kde se nachází zkompilovaná knihovna s kódem pro odesílání e-mailů.

-- Variables
DECLARE @AssemblyFolderPath NVARCHAR(1000)
SET @AssemblyFolderPath = N'ASSEMBLY_FOLDER'

-- Create SqlSendMail assembly
CREATE ASSEMBLY [SqlSendMail] FROM @AssemblyFolderPath + 'SqlSendMail.dll'
WITH PERMISSION_SET = UNSAFE
GO

-- Create sp_SendMail procedure
CREATE PROCEDURE [sp_SendMail]
   @from [nvarchar](MAX),
   @to [nvarchar](MAX),
   @subject [nvarchar](MAX),
   @body [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlSendMail].[SendMail].[Send]
GO

Jméno knihovny se může lišit v závislosti na nastavení projektu v aplikaci Visual C#. Jméno publikovaného sestavení (assembly) v SQL Serveru nemusí být stejné jako jméno zkompilovaného souboru – v tomto případě SqlSendMail.dll.

Nejprve je potřeba nastavit níže uvedené hodnoty v metadatech databáze. V SQL Server Management Studio se toto nastavení provádí pomocí záložky Extended properties ve vlastnostech databáze.

• SmtpServer – název SMTP serveru
• SmtpUser – Přihlašovací jméno
• SmtpPassword – Přihlašovací heslo
• SmtpPort – Port pro připojení k SMTP serveru
• SmtpEnableSsl – Hodnota 1 pokud má být použit protokol SSL při komunikaci s SMTP serverem

Nyní už jen stačí zavolat nově vytvořenou T-SQL proceduru.

EXEC sp_SendMail
	@to = 'receiver@domain', 
	@subject = 'SQL CRL test e-mail', 
	@from  = 'sender@domain',
	@body = 'This is a test email from SQL Server'