دسترسی به منابع خارجی در SQL Server با استفاده از مفهوم CLR

در دنیای برنامه‌نویسی و کوئری‌نویسی در پایگاه داده، مواقعی پیش می‌آید که نیاز به نوشتن توابعی با کارکرد پیچیده داریم. نوشتن این توابع در SQL Server ممکن است دشوار باشد، اما می‌توان با قابلیت CLR و استفاده از زبانی مثل #C توابع دلخواه را ایجاد کرد. در این مقاله، نحوه طراحی توابع با استفاده از CLR توضیح داده خواهد شد.

سناریو

فرض کنید ما یک نرم‌افزار و به تبع آن یک پایگاه داده جهت مدیریت پردازش‌های آن نرم‌افزار داریم. در این پایگاه داده، جدولی وجود دارد که فایل‌های مربوط به این پردازش‌ها را نگهداری می‌کند و در نهایت، پس از اتمام پردازش، فایل‌ها از محل حافظه حذف می‌شوند. ما از جزئیات کسب‌وکار و پردازش این نرم‌افزار اطلاعی نداریم، اما می‌دانیم این پردازش‌ها (مستقیم یا غیرمستقیم) با اندازه فایل در ارتباط هستند. نیاز داریم علاوه بر مسیر کامل فایل، اندازه آن را نیز به دست آورده و ذخیره کنیم؛ این یک سناریوی کاربردی برای نوشتن توابع CLR است.

مفهوم CLR

عبارت CLR سرواژه Common Language Runtime است و به بیان ساده، به معنای استفاده از قابلیت NET. در SQL Server می‌باشد. با استفاده از قابلیت‌های NET. و یکی از زبان‌های آن مانند #C یا VB می‌توان کدنویسی کرده و از اشیاء SQL Server استفاده کرد. یکی از موارد استفاده، نوشتن توابع برای SQL Server است. در این روش، توابع به زبان #C نوشته شده و سپس در SQL Server استفاده می‌شوند. با CLR می‌توان توابع، Stored Procedure و … ایجاد کرد؛ در ادامه، به نوشتن یک تابع CLR پرداخته خواهد شد.

دو بخش کلی نوشتن یک تابع CLR 

1.  کارهایی که باید در NET. (به زبان #C) انجام شود.
2.  کارهایی که باید در SQL Server انجام شود.

کارهایی که باید در NET. (به زبان #C) انجام شود

الف) تعریف پروژه در Visual Studio: پروژه باید از نوع Class Library (.NET Framework) باشد، زیرا این نوع پروژه پس از کامپایل، توابع و کلاس‌ها را در قالب فایل dll. در اختیار ما قرار می‌دهد. نام این پروژه یا Solution، SQL_CLR خواهد بود.

ب) ایجاد یک کلاس و نوشتن کد زیر در آن: در این مثال قصد داریم تابعی ایجاد کنیم که مسیر یک فایل را به عنوان ورودی بگیرد و حجم آن را به بایت بازگرداند.

using System.IO;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
namespace SQL_CLR
{
  public class File
  {
    		[SqlFunction]
    	public static SqlInt64 GetSize(string filename)
    		{
if (System.IO.File.Exists(filename))
        	{
      	FileInfo fileInfo = new FileInfo(filename);
      	return (new SqlInt64(fileInfo.Length));
      }
        		return SqlInt64.Null;
    	}
  }
}

ج) کامپایل پروژه: پس از کامپایل پروژه، فایل dll. آن در مسیر پروژه مطابق ساختار زیر قابل دسترسی است:

-- STEP 1
/* انتخاب دیتابیس موردنظر. از دیتابیس مستر استفاده نکنید. */
USE MyDB;
GO


-- STEP 2
/* در برخی از تنظیمات، این ویژگی باید فعال باشد. */
ALTER DATABASE MyDB SET TRUSTWORTHY ON;
GO

-- STEP 3
/* فعال‌سازی قابلیت تغییر  */
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

/* غیرفعال کردن strict security */
EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;
GO

/* فعال کردن CLR */
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

-- STEP 4
/* تعریف اسمبلی (فایل دی ال ال) در SQL Server */
CREATE ASSEMBLY SQL_CLR
FROM 'D:\Projects\SQL_CLR\bin\Debug\SQL_CLR.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

-- STEP 5
/* تعریف تابع در sql server */
CREATE FUNCTION dbo.GetSize(@filename NVARCHAR(MAX))
RETURNS BIGINT
AS EXTERNAL NAME [SQL_CLR].[SQL_CLR.File].[GetSize];
GO

توضیح مراحل (STEPs)

• دیتابیس master را انتخاب نکنید. بهتر است این توابع فقط در دیتابیس مربوطه تعریف شوند.

• ویژگی TRUSTWORTHY یکی از تنظیمات امنیتی پایگاه داده است که تعیین می‌کند آیا یک دیتابیس اجازه دارد به منابعی فراتر از خودش (مانند CLR با دسترسی بالا یا اجرای کدهایی که نیاز به سطح دسترسی بالاتری دارند) دسترسی داشته باشد یا خیر. این قابلیت به صورت پیش‌فرض خاموش است و باید با احتیاط استفاده شود.

• برای تعریف SQL CLR نیاز به فعال‌سازی آن داریم. مرحله ۳ این کار را انجام می‌دهد. حتماً توجه داشته باشید این کد برای محیط تست است. در محیط اجرایی باید تمهیدات امنیتی لازم در نظر گرفته شود.

• تعریف فایل dll. (همان اسمبلی) در SQL Server باعث می‌شود SQL Server به توابع موجود در کلاس دسترسی داشته باشد. بخش PERMISSION_SET میزان دسترسی به منابع خارجی را تعیین می‌کند و می‌تواند مقادیر زیر را داشته باشد:

  • SAFE: امن‌ترین حالت، بدون دسترسی به منابع خارجی.

  • EXTERNAL_ACCESS: دسترسی به منابع خارجی مانند شبکه یا فایل‌ها.

  • UNSAFE: دسترسی کامل، خطرناک.

• تعریف تابع GetSize در SQL Server باعث می‌شود تابع با همان ورودی و خروجی متد موجود در اسمبلی استفاده شود.

پس از انجام تمام مراحل فوق، تابع GetSize قابل استفاده خواهد بود؛ برای مثال:

SELECT [MyDB].[dbo].GetSize('D:\Music\Saw 2 Hello Zepp.mp3')
GO

به ورودی تابع، مسیر یک فایل داده شده است؛ این مسیر باید کامل باشد. در صورت وجود فایل، سایز آن فایل برحسب بایت نمایش داده می‌شود. در شکل زیر خروجی کوئری و مشخصات فایل نظیر آدرس محل ذخیره سازی، حجم فایل و … را مشاهده می‌کنید. به سایز فایل در هر دو شکل دقت کنید که دقیقاً عین هم هست. 

سخن پایانی

قابلیت CLR در SQL Server به توسعه‌دهندگان کمک می‌‌کند تا از قدرت و انعطاف‌پذیری زبان‌های NET. برای ایجاد توابع و رویه‌های ذخیره‌شده در دیتابیس SQL Server استفاده کنند. این قابلیت‌های قدرتمند، امکان انجام وظایف پیچیده را ساده کرده و به شما کمک می‌کند برنامه‌های یکپارچه با T-SQL و NET. ایجاد ‌کنید. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله در بخش نظرات هستیم.