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říkladjazyk Java poskytuje běh aplikací zpracováváním tzv. bytecode. Hostování virtuálního stroje.NET v databázových serverechMicrosoft SQL Server nabízí psaní vlastníchSQL objektů v jazycích jako napříkladC#,VB.NET a dalších z rodiny.NET jazyků. MeziSQL objekty, které je možné vytvořit patří:
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ámoMicrosoft 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žbaSQL Server Agent. Nicméně podporaCLR 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, jakMicrosoft SQL Server Express doplnit o funkcionalitu odesílání e-mailů.
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 supportsp_configure'show advanced options',1;GORECONFIGURE;GOsp_configure'clr enabled',1;GORECONFIGURE;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.
usingSystem;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Data.SqlTypes;usingSystem.Net.Mail;usingSystem.Net;publicclassSendMail{/// <summary>/// Sends e-mail using configuration from db extended properties/// </summary>publicstaticvoidSend(stringfrom,stringrecipients,stringsubject,stringbody){// Create email messageusing(MailMessagemessage=newMailMessage(from,recipients)){message.BodyEncoding=System.Text.Encoding.UTF8;message.Subject=subject;message.Body=body;// Send e-mailGetSmtpClient().Send(message);}}/// <summary>/// Gets instance of SmtpClient class/// </summary>/// <returns></returns>privatestaticSmtpClientGetSmtpClient(){// Initialize SMTP client propertiesstringsmtpServer=GetExtendedProperty("SmtpServer");stringsmtpUser=GetExtendedProperty("SmtpUser");stringsmtpPassword=GetExtendedProperty("SmtpPassword");intport=int.Parse(GetExtendedProperty("SmtpPort")??"25");boolenableSsl=int.Parse(GetExtendedProperty("SmtpEnableSsl")??"0")==1;SmtpClientsmtp=newSmtpClient(smtpServer);if(!string.IsNullOrEmpty(smtpUser))// Use authentication{smtp.UseDefaultCredentials=false;smtp.Credentials=newNetworkCredential(smtpUser,smtpPassword);smtp.EnableSsl=enableSsl;// Force to use SSLsmtp.Port=port;}else{smtp.UseDefaultCredentials=true;}returnsmtp;}/// <summary>/// Gets extended property from current context/// </summary>/// <param name="name">Name of the extended property</param>/// <returns></returns>privatestaticstringGetExtendedProperty(stringname){using(SqlConnectionconnection=newSqlConnection("context connection=true")){connection.Open();stringquery=string.Format(@"SELECT value FROM sys.extended_properties WHERE [Name] = '{0}'",name);SqlCommandcommand=newSqlCommand(query,connection);using(SqlDataReaderreader=command.ExecuteReader()){reader.Read();returnreader.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 typuClass Library.
Kód obsahuje jednu veřejnou metodu, která se jmenujeSend. Právě tato metoda bude volána zT-SQL skriptů. MetodaSend 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 sekciExtended 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říznakemPERMISSION_SET = UNSAFE.
Nakonec je vytvořena T-SQL procedura, která pouze volá metoduSend z předchozí publikované knihovny. T-SQL procedura musí mít stejný počet parametrů jako má metodaSend.
ProměnnáAssemblyFolderPath obsahuje cestu ke složce, kde se nachází zkompilovaná knihovna s kódem pro odesílání e-mailů.
-- VariablesDECLARE@AssemblyFolderPathNVARCHAR(1000)SET@AssemblyFolderPath=N'ASSEMBLY_FOLDER'-- Create SqlSendMail assemblyCREATEASSEMBLY[SqlSendMail]FROM@AssemblyFolderPath+'SqlSendMail.dll'WITHPERMISSION_SET=UNSAFEGO-- Create sp_SendMail procedureCREATEPROCEDURE[sp_SendMail]@from[nvarchar](MAX),@to[nvarchar](MAX),@subject[nvarchar](MAX),@body[nvarchar](MAX)WITHEXECUTEASCALLERASEXTERNALNAME[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.
Nyní už jen stačí zavolat nově vytvořenou T-SQL proceduru.
EXECsp_SendMail@to='receiver@domain',@subject='SQL CRL test e-mail',@from='sender@domain',@body='This is a test email from SQL Server'
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
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.