wait لطفا صبر کنید
صفحه اصلی  » مقالات
1395/12/08
8200
10

الحاق CLR به SQL SERVER

سرور SQL مایکروسافت نسخه 2005 (MS SQL Server 2005)  بمنظور ارتقاء مدل برنامه نویسی پایگاه داده خود، تکنولوژی SQL CLR خود را معرفی کرد.

سرور SQL مایکروسافت نسخه 2005 (MS SQL Server 2005) بمنظور ارتقاء مدل برنامه نویسی پایگاه داده خود، تکنولوژی SQL CLR را معرفی کرد. این تکنولوژی همچنین با اسامی SQL Server CLR Integrated، CLR Enabled، CLR Embeded نیز شناخته می شود. تکنولوژی SQL CLR با میزبانی کردن از CLR (.Net Common Language Runtime یا همان محیط زمان اجرای دات نت)، به مدیران پایگاه داده (DBA) اجازه می دهد تا از قابلیت‎های موجود در دات نت برای تعریف انواع داده، توابع، استورد پروسیجر و تریگرهای پیشرفته استفاده کنند.

این قابلیت توسط فضاهای نام system.data، system.data.sql و Microsoft.sqlserver.server که در اسمبلی System.data.dll و در دایرکتوری دات نت قرار داده شده اند، پشتیبانی می گردند.

در کل ایجاد شی‎های پایگاه داده (انواع داده، توابع، استورد پروسیجر و تریگرها) با استفاده از امکان SQL CLR، طی مراحل زیر ممکن می گردد:

1.      نوشتن کد .net (C# و VB) و کامپایل آن

2.      بارگذاری و اجرای کد کامپایل شده در مرحله قبل در SQL سرور

a.      فعال سازی امکان SQL CLR در پایگاه داده

b.      ایجاد اسمبلی از کد مزبور در پایگاه داده

c.       ایجاد شی پایگاه داده ای (استورد پروسیجر و یا تابع و یا غیره) از اسمبلی مرحله قبل

نوشتن کد .net و کامپایل آن:

به عنوان مثال کد مورد نظر خود را در کلاسی با نام StoredProcedures و در تابع SP1 نوشته و آنرا کامپایل می کنیم. کد کامپایل شده را (StoredProcedures.dll) باید در مرحله بعد در سرور SQL بارگذاری کنیم (برای سادگی می توان از کامپایلر خط فرمان csc استفاده کرد).

البته کد زیر تنها یک hello world بمنظور آشنایی با نحوه کار است و می توان از تمام قابلیت ها و کلاس های موجود در دات نت در این کلاس استفاده کرد.

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
 
public class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SP1()
    {
        SqlContext.Pipe.Send("Hello world!\n");
    }
}

 

بارگذاری و اجرای کد کامپایل شده در مرحله قبل در SQL سرور

·         فعال سازی امکان CLR  SQLدر پایگاه داده:

امکان CLR SQL در SQL Server به صورت پیش فرض غیرفعال است و برای فعال سازی آن باید کد زیر را اجرا کرد:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

برای غیرفعال سازی همان کد بالا را با مقدار صفر اجرا می کنیم.

·         ایجاد اسمبلی در پایگاه داده:

گام بعدی ایجاد اسمبلی در پایگاه داده از روی کد کامپایل شده است.

CREATE ASSEMBLY HelloWorld – AssemblyName

from 'c:\users\......\StoredProcedures.dll'

WITH PERMISSION_SET = SAFE

 

با توجه به کدی که نوشته ایم و برای کنترل دسترسی کد CLR به دیگر کدها از PERMISSION_SET استفاده می گردد. با مقادیر زیر:

ویژگی SAFE- به اسمبلی ها اجازه می دهد که تنها محاسبات محلی و دسترسی به فایلهای محلی را داشته باشد.

ویژگی EXTERNAL_ACCESS- مشابه ویژگی قبلی با این تفاوت که اسمبلی می تواند به منابع شبکه دسترسی داشته باشد.

ویژگی UNSAFE- اجازه دسترسی نامحدود به منابع و کدهای غیر از دات نت (Unmanaged Code) را هم می دهد.

برای استفاده از ویژگی UNSAFE باید ویژگی اعتماد (trustworthy) را در پایگاه داده فعال کرد. این ویژگی مشخص می کند که پایگاه داده به کدهای بیرونی اعتماد دارد (البته توصیه نمی شود). در غیر این صورت با فعال سازی خصوصت UNSAFE خطا اعلام می شود:

ALTER DATABASE DB_NAME SET trustworthy ON

در صورتیکه در هنگام ایجاد اسمبلی خطای زیر رخ داد، باید نسخه دات نت را از 4 به 3.5 و یا پایین تر تغییر دهیم. زیرا در حال حاضر SQL Server 2008 نسخه 4 دات نت را لود نمی کند (ورژن های بعدی دات نت 4 را پشتیبانی خواهند کرد).

CREATE ASSEMBLY for assembly <Assembly Name> failed because the assembly is built for an unsupported version of the Common Language Runtime

·         ایجاد شی پایگاه داده ای(استورد پروسیجر و یا تابع و یا غیره) از اسمبلی مرحله قبل:

حال می توان از اسمبلی ایجاد شده در مرحله قبل برای ایجاد SP و یا تابع مورد نظر استفاده کرد. چون در مرحله قبل ما کلاسی از نوع استورد پروسیجر ایجاد کردیم، در SQL Server برای استفاده از آن یک SP ایجاد می کنیم. نمونه هایی از تابع و یا نوع داده را نیز در ادامه خواهیم آورد.

create PROCEDURE hello

 

AS

 

-- assemblyname.classname.methodname

 

EXTERNAL NAME helloworld.StoredProcedures.SP1

با اجرای SP، کد نوشته شده در تابع sp1 اجرا می گردد:

exec hello

 

-- output is

 

-- Hello world!

برای حذف ابتدا باید شی هایی که ارجاعی از اسمبلی دارند (در اینجا استورد پروسیجر hello) حذف و سپس خود اسمبلی حذف گردد:

drop procedure hello

 

drop assembly helloworld

علاوه بر ایجاد استورد پروسیجر، می توان شی های از نوع تابع، نوع داده و تریگر نیز ایجاد کرد.

تماس با ما

ایمـیل : I N F O @ D E P N A . C O M
 دفتر تهران : خیابان کارگر شمالی - بعد از مسجد النبی - خیابان ابراهیمی (17) - پلاک 140
تلفن:88019001-021
 دفتر قم : خیابان 45 متری صدوقی - 20 متری فجر - خیابان دانش - کوی 14 - پلاک 10
تلفن:32906868-025