SQL CLRSQL 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ří:
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ázkaNastavení 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. -- Enable CLR support
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Vytvoření CLR kóduUká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. Publikování CLR kóduSamotné 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 -- 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óduNejprve 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. 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 CLRNejč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.
typů. Dále mohou být uživatelsky definované typy použity pro jednoduché šifrování a dešifrování dat. |
Portal di Ensiklopedia Dunia
