یکپارچهسازی آسان SQL CLR: توابع سفارشی در SQL Server با C#
یکپارچهسازی زمان اجرای زبان مشترک (CLR) با SQL Server به شما امکان میدهد تا رویههای ذخیرهشده (Stored Procedures)، توابع (Functions)، تریگرها (Triggers)، انواع داده (Data Types) و تجمیعات (Aggregates) را با استفاده از زبانهای .NET مانند C# یا VB.NET توسعه دهید. این قابلیت امکان گسترش قدرتمند SQL Server را فراتر از T-SQL فراهم میکند و به توسعهدهندگان اجازه میدهد تا منطق پیچیده کسبوکار، محاسبات سنگین و تعامل با منابع خارجی را مستقیماً در محیط پایگاه داده پیادهسازی کنند.
یکی از مزایای اصلی استفاده از CLR، توانایی بهرهبرداری از کتابخانههای گسترده .NET Framework است. این شامل قابلیتهایی مانند عبارات منظم (Regular Expressions)، محاسبات ریاضی پیچیده، دستکاری رشتهها و فایلها، رمزنگاری و بسیاری موارد دیگر میشود که در T-SQL به راحتی قابل انجام نیستند یا بسیار دشوار خواهند بود. علاوه بر این، برای توسعهدهندگان آشنا به C# یا VB.NET، استفاده از CLR میتواند فرآیند توسعه را سریعتر و کارآمدتر کند.
چرا از CLR استفاده کنیم؟
استفاده از CLR در SQL Server در سناریوهای خاصی بسیار مفید است:
قابلیتهای پیشرفته: T-SQL برای کارهای مربوط به دادهها بهینهسازی شده است، اما برای عملیاتهای پیچیدهتر مانند پردازش رشتهای سنگین، عبارات منظم، یا عملیاتهای I/O سیستمی، CLR عملکرد بهتری ارائه میدهد.
عملکرد: برای برخی عملیاتها، کد CLR کامپایلشده میتواند سریعتر از معادل T-SQL آن اجرا شود، به ویژه در مورد الگوریتمهای پیچیده.
یکپارچهسازی با .NET: اگر منطق کسبوکار شما قبلاً در .NET پیادهسازی شده است، میتوانید به راحتی آن را به SQL Server بیاورید و از تکرار کد جلوگیری کنید.
کتابخانههای غنی: دسترسی به تمام کتابخانههای .NET Framework، که مجموعهای وسیع از ابزارها و توابع آماده را فراهم میکند.
پیشنیازها
برای دنبال کردن این راهنما، به موارد زیر نیاز دارید:
1- SQL Server (نسخههای 2005 و بالاتر).
2- Visual Studio (هر نسخهای که از .NET Framework پشتیبانی کند).
3- .NET Framework نصب شده روی سیستم توسعه و SQL Server.
ایجاد یک پروژه
اولین گام ایجاد یک پروژه “Class Library” در Visual Studio است. این پروژه حاوی کد C# خواهد بود که در SQL Server اجرا میشود.
1. Visual Studio را باز کنید.
2. یک پروژه جدید ایجاد کنید.
3. قالب “Class Library” (کتابخانه کلاس) را انتخاب کنید.
4. نامی برای پروژه خود انتخاب کنید، مثلاً `MySqlClrFunctions`.
5. مطمئن شوید که پروژه شما .NET Framework را هدف قرار داده است (نه .NET Core یا .NET 5+، مگر اینکه از SQL Server 2019+ و ویژگیهای خاص آن استفاده کنید). برای سازگاری گستردهتر با SQL Server، معمولاً .NET Framework 4.x توصیه میشود.
نوشتن تابع CLR
اکنون، تابع CLR خود را در فایل `Class1.cs` (یا هر نام فایل دیگری که انتخاب کردهاید) مینویسیم. برای شروع، یک تابع ساده خواهیم ساخت که یک رشته “Hello, [نام]!” را برمیگرداند.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlString Hello(SqlString name)
{
if (name.IsNull)
{
return new SqlString(“Hello, World!”);
}
return new SqlString(“Hello, ” + name.Value + “!”);
}
}
توضیح کد:
`[SqlFunction(…)]`: این ویژگی (Attribute) متد را به عنوان یک تابع SQL CLR علامتگذاری میکند.
`DataAccess = DataAccessKind.None`: نشان میدهد که تابع به دادههای SQL Server دسترسی ندارد.
`IsDeterministic = true`: بیان میکند که تابع همیشه برای ورودیهای یکسان، خروجی یکسانی تولید میکند. این برای بهینهسازی SQL Server مهم است.
`public static SqlString Hello(SqlString name)`: این تابع عمومی و ایستا (static) است که یک `SqlString` (نوع داده .NET که با `NVARCHAR` در SQL Server نگاشت میشود) را به عنوان ورودی گرفته و یک `SqlString` را برمیگرداند.
`if (name.IsNull)`: چک میکند که آیا ورودی `name` تهی (NULL) است یا خیر.
`return new SqlString(…)`: نتیجه را به صورت `SqlString` برمیگرداند.
ساخت پروژه
پروژه را با رفتن به منوی “Build” و انتخاب “Build Solution” بسازید. این کار یک فایل DLL در پوشه `bin\Debug` (یا `bin\Release`) پروژهتان ایجاد میکند. به مسیر این فایل DLL نیاز خواهید داشت.
استقرار در SQL Server
حالا باید DLL کامپایل شده را به SQL Server استقرار دهید. این کار شامل دو مرحله اصلی است: ایجاد Assembly و سپس ایجاد تابع SQL که به متد CLR اشاره دارد.
مرحله 1: ایجاد Assembly
Assembly کدی است که شما در SQL Server قرار میدهید. برای این کار از دستور `CREATE ASSEMBLY` استفاده میکنیم.
CREATE ASSEMBLY MySqlClrFunctionsAssembly
AUTHORIZATION dbo
FROM ‘C:\Path\To\Your\MySqlClrFunctions.dll’
WITH PERMISSION_SET = SAFE;
توضیح دستور:
`CREATE ASSEMBLY MySqlClrFunctionsAssembly`: یک Assembly جدید با نام `MySqlClrFunctionsAssembly` ایجاد میکند.
`AUTHORIZATION dbo`: صاحب Assembly را `dbo` تعیین میکند.
`FROM ‘C:\Path\To\Your\MySqlClrFunctions.dll’`: مسیر کامل فایل DLL کامپایل شده شما را مشخص میکند. این مسیر را با مسیر واقعی فایل DLL خود جایگزین کنید.
`WITH PERMISSION_SET = SAFE`: به Assembly کمترین سطح دسترسی را میدهد. این حالت امنترین گزینه است و برای توابعی که به منابع خارجی دسترسی ندارند، مناسب است. سطوح دیگر شامل `EXTERNAL_ACCESS` (برای دسترسی به فایلها، شبکه) و `UNSAFE` (برای دسترسی نامحدود) هستند که نیاز به تنظیمات امنیتی بیشتری دارند.
مرحله 2: ایجاد تابع SQL
پس از ایجاد Assembly، باید یک تابع SQL ایجاد کنید که به متد `Hello` در Assembly CLR شما اشاره کند.
CREATE FUNCTION HelloClr(@name NVARCHAR(MAX))
RETURNS NVARCHAR(MAX) AS
EXTERNAL NAME MySqlClrFunctionsAssembly.[UserDefinedFunctions].Hello;
توضیح دستور:
`CREATE FUNCTION HelloClr(@name NVARCHAR(MAX))`: یک تابع SQL با نام `HelloClr` تعریف میکند که یک پارامتر از نوع `NVARCHAR(MAX)` میگیرد.
`RETURNS NVARCHAR(MAX)`: نوع بازگشتی تابع را `NVARCHAR(MAX)` مشخص میکند.
`EXTERNAL NAME MySqlClrFunctionsAssembly.[UserDefinedFunctions].Hello`: مشخص میکند که این تابع SQL به متد `Hello` در کلاس `UserDefinedFunctions` از Assembly با نام `MySqlClrFunctionsAssembly` اشاره دارد.
تست تابع
حالا میتوانید تابع `HelloClr` را درست مانند هر تابع T-SQL دیگری فراخوانی کنید.
SELECT dbo.HelloClr(‘SQL Server Central’) AS Greeting;
SELECT dbo.HelloClr(NULL) AS NullGreeting;
خروجی باید به ترتیب:
`Greeting`
`Hello, SQL Server Central!`
و
`NullGreeting`
`Hello, World!`
باشد.
ملاحظات مربوط به مجوزها (Permissions considerations)
SAFE: امنترین سطح مجوز. Assembly نمیتواند به منابع خارجی مانند فایل سیستم، شبکه یا رجیستری دسترسی پیدا کند. این برای بیشتر توابع محاسباتی خالص مناسب است.
EXTERNAL_ACCESS: این سطح به Assembly اجازه میدهد به منابع خارجی خارج از SQL Server دسترسی پیدا کند (مانند خواندن/نوشتن فایلها، دسترسی به شبکه). برای استفاده از این سطح، Assembly باید توسط گواهینامه (Certificate) یا کلید نام قوی (Strong Name Key) امضا شود و یک Login متناظر در SQL Server داشته باشید که دارای مجوز `EXTERNAL ACCESS ASSEMBLY` باشد.
UNSAFE: خطرناکترین سطح مجوز. Assembly میتواند به هر منبعی دسترسی داشته باشد و میتواند کد غیرمدیریتشده (unmanaged code) را فراخوانی کند. استفاده از این گزینه نیاز به دقت بسیار زیادی دارد و باید فقط در صورت لزوم و با کنترلهای امنیتی شدید استفاده شود. برای استفاده از `UNSAFE`، Login متناظر باید مجوز `UNSAFE ASSEMBLY` را داشته باشد.
برای فعال کردن CLR در SQL Server، ممکن است لازم باشد گزینه `clr enabled` را در تنظیمات پیشرفته سرور فعال کنید:
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
EXEC sp_configure ‘clr enabled’, 1;
RECONFIGURE;
بهروزرسانی Assembly
اگر کد C# خود را تغییر دادید، باید پروژه را دوباره بسازید و سپس Assembly را در SQL Server بهروزرسانی کنید.
ALTER ASSEMBLY MySqlClrFunctionsAssembly
FROM ‘C:\Path\To\Your\MySqlClrFunctions.dll’
WITH PERMISSION_SET = SAFE;
نکته: اگر تابع در حال استفاده باشد، ممکن است نتوانید Assembly را بهروزرسانی کنید. باید مطمئن شوید که هیچ فرآیندی از تابع استفاده نمیکند.
حذف Assembly و تابع
برای حذف کامل تابع و Assembly از SQL Server، ابتدا باید تابع را حذف کرده و سپس Assembly را حذف کنید.
DROP FUNCTION HelloClr;
DROP ASSEMBLY MySqlClrFunctionsAssembly;
نکته: نمیتوانید یک Assembly را حذف کنید در حالی که توابع، رویهها یا سایر اشیاء به آن وابستهاند. ابتدا باید تمام اشیاء وابسته را حذف کنید.
نتیجهگیری
یکپارچهسازی SQL CLR یک ابزار قدرتمند برای گسترش قابلیتهای SQL Server با استفاده از زبانهای .NET است. با پیروی از این مراحل ساده، میتوانید توابع سفارشی خود را ایجاد، استقرار و مدیریت کنید و از قدرت کامل .NET Framework در محیط پایگاه داده خود بهرهمند شوید. همیشه ملاحظات امنیتی و عملکردی را در نظر بگیرید، به ویژه هنگام انتخاب سطح مجوز برای Assemblyهای خود.