SQL CLR

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ů. +more 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

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

Ukázková aplikace je tvořena pouze jednou třídou. Používá základní knihovny +moreNET'>. 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 { /// /// Sends e-mail using configuration from db extended properties /// 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. +moreText. Encoding. UTF8; message. Subject = subject; message. Body = body;.

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

/// /// Gets instance of SmtpClient class /// /// 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") . +more "25"); bool enableSsl = int. Parse(GetExtendedProperty("SmtpEnableSsl") . "0") == 1;.

SmtpClient smtp = new SmtpClient(smtpServer); if (. string. +moreIsNullOrEmpty(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; }

/// /// Gets extended property from current context /// /// Name of the extended property /// 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ů. +more 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

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ů. +more 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]. +more[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. +moredll.

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. +more * 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

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ů. +more 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í. +more 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