wait لطفا صبر کنید
02 بهمن 1395
صفحه اصلی  » مقالات
1394/01/15
7644
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
علاوه بر ايجاد استورد پروسيجر، مي توان شي هاي از نوع تابع، نوع داده و تريگر نيز ايجاد كرد.