Zobrazení zdroje stránky SQL CLR

{{upravit}}
'''SQL CLR''' je technologie od Microsoftu, která hostuje virtuální stroj [[.NET]] frameworku. Virtuální stroj poskytuje běh veškerého [[.NET]] framework kódu stejně jako například [[Java (programovací jazyk)|jazyk Java]] poskytuje běh aplikací zpracováváním tzv. bytecode. Hostování virtuálního stroje [[.NET]] v databázových serverech [[Microsoft SQL Server]] nabízí psaní vlastních [[SQL]] objektů v jazycích jako například [[C Sharp|C#]], [[VB.NET]] a dalších z rodiny [[.NET]] jazyků. Mezi [[SQL]] objekty, které je možné vytvořit patří:
* Procedury (SP)
* Triggery
* Uživatelsky definované funkce
* Uživatelsky definované typy
* Uživatelsky definované agregační funkce

Využití technologie SQL CLR je prezentováno v následující ukázce. Ukázkový kód představuje jednoduchou třídu, která slouží k odesílání e-mailů. Jak je známo [[Microsoft SQL Server]] ve verzi Express neobsahuje interní podporu pro odesílání e-mailů pomocí [[T-SQL]], to je možné pouze u verzí, jejichž součástí je služba [[SQL Server Agent]]. Nicméně podpora [[CLR]] virtuálního stroje je obsažena ve všech verzích, takže tato ukázka může sloužit i jako návod na to, jak [[SQL Server Express|Microsoft SQL Server Express]] doplnit o funkcionalitu odesílání e-mailů.

== Ukázka ==
=== Nastavení prostředí ===
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.<br />
Ve výchozím nastavení Microsoft SQL Server je podpora SQL CLR zakázána. Je tedy potřeba ji nejprve povolit, k tomu slouží následující skript:<br />
<syntaxhighlight lang="sql">
-- Enable CLR support
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
</syntaxhighlight>
=== Vytvoření CLR kódu ===
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.

<syntaxhighlight lang="csharp">
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);
            }
        }
    }
};
</syntaxhighlight>
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''.<br />
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.<br />
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''.

=== Publikování CLR kódu ===
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 <code>PERMISSION_SET = UNSAFE.</code><br />
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''.<br />
Proměnná ''AssemblyFolderPath'' obsahuje cestu ke složce, kde se nachází zkompilovaná knihovna s kódem pro odesílání e-mailů.

<syntaxhighlight lang="sql">
-- 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
</syntaxhighlight>
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''.

=== Testování CLR kódu ===
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.

<syntaxhighlight lang="sql">
EXEC sp_SendMail
	@to = 'receiver@domain', 
	@subject = 'SQL CRL test e-mail', 
	@from  = 'sender@domain',
	@body = 'This is a test email from SQL Server'
</syntaxhighlight>
== Využití SQL CLR ==
Nejčastějším využitím SQL CLR jsou algoritmy pro práci s řetězci, pro tuto oblast v T-SQL neexistuje dostatečné množství zabudovaných funkcí. Dalším častým využitím jsou procedury a funkce, které obsahují pokročilé matematické operace.<br />
Použití uživatelsky definovaných typů se v praxi moc neuchytilo, protože složité datové typy jsou velmi náročné na zpracování. Používají se spíše jednoduché úpavy stávajících
* číselných,
* časových a datumových
* a měnových
typů. Dále mohou být uživatelsky definované typy použity pro jednoduché šifrování a dešifrování dat.<br />
Obecná rada při zvažování, zda využít klasické funkcionality T-SQL nebo sáhnout po SQL CLR je použít T-SQL, pokud je to možné. Špatné napsání SQL CLR kódů může vést k velmi rychle klesajícímu výkonu databází. Dalším problémem se může stát implementace programů na straně klienta, kde podpora CLR může být z bezpečnostních důvodů zakázána.

[[Kategorie:Microsoft]]