SQL CLR

Z Wikipedie, otevřené encyklopedie
ikona
Tento článek potřebuje úpravy.
Můžete Wikipedii pomoci tím, že ho vylepšíte. Jak by měly články vypadat, popisují stránky Vzhled a styl, Encyklopedický styl a Odkazy.

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 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#, 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 Microsoft SQL Server Express doplnit o funkcionalitu odesílání e-mailů.

Ukázka[editovat | editovat zdroj]

Nastavení prostředí[editovat | editovat zdroj]

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 nejprve 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

Vytvoření CLR kódu[editovat | editovat zdroj]

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.

Publikování CLR kódu[editovat | editovat zdroj]

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.

Testování CLR kódu[editovat | editovat zdroj]

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'

Využití SQL CLR[editovat | editovat zdroj]

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.
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.
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.

Citováno z „https://cs.wikipedia.org/w/index.php?title=SQL_CLR&oldid=23008863“