ويژگي‌هاي قابل برنامه‌نويسي

ويژگي‌هاي برنامه‌نويسي جديد در SQL Server 2005 تجمعي از تلاش‌هاي چندين ساله گروه برنامه‌نويسي SQL Server و گروه برنامه‌نويسي .NET Framework است. مهم‌ترين اين ويژگي‌هاي برنامه‌نويسي جديد، يكپارچگي CLR[1] .NET است. يكپارچگي CLR آن را به ميزبان كاملي از قابليت‌هاي جديد تبديل كرده است، از جمله قابليتي براي ايجاد اشياي پايگاه داده با استفاده از هر يك از زبان‌هاي سازگار با .NET، از جمله C#، VB.NET و Managed C++. در اين فصل، مقدمه‌اي بر اين ويژگي‌هاي يكپارچگي .NET CLR جديد خواهيم داشت و مثال‌هايي را خواهيد ديد كه نحوه استفاده آن‌ها را نشان مي‌دهند. سپس، اين فصل به عنواني مي‌پردازد كه براي برنامه‌نويسان DBAهاي SQL Server آشناتر است: ويژگي‌هاي جديد در T-SQL. سپس، اين فصل مروري بر سمت كلاينت خواهد داشت و تعدادي از ويژگي‌هاي جديد برنامه‌نويسي در .NET Framework Data Provider بهنگام شده براي SQL Server را كه همراه با SQL Server 2005 هستند، ارايه مي‌دهد.

يكپارچگي CLR

بدون شك مهم‌ترين ويژگي جديد در SQL Server 2005، يكپارچگي Microsoft .NET Framework است. يكپارچگي CLR با SQL Server، قابليت SQL Server را به روش‌هاي مهم مختلفي توسعه مي‌دهد. در حالي كه T-SQL، زبان دستكاري و دستيابي داده موجود، براي عمليات دستيابي داده مجموعه‌گرا مناسب است، محدوديت‌‌هايي نيز دارد. T-SQL كه بيش از يك دهه از طراحي آن مي‌گذرد، يك زبان رويه‌اي است و نه يك زبان شئ‌گرا. يكپارچگي CLR با SQL Server 2005، توانايي ايجاد اشياي پايگاه داده را با استفاده از زبان‌هاي شئ گرايي نظير VB.NET و C# به آن مي‌دهد. در حالي كه اين زبان‌ها همان قدرت طبيعت مجموعه‌گرا را مثل T-SQL ندارند، از منطق پيچيده‌اي پشتيباني مي‌كنند، قابليت‌هاي محاسبه بهتري دارند، دستيابي آسان‌تري به منابع خارجي فراهم مي‌كنند، استفاده مجدد كد را تسهيل مي‌بخشند و يك محيط برنامه‌نويسي كلاس بالا دارند كه تواني بيش از Query Analyzer قديمي فراهم مي‌كند.

يكپارچگي .NET CLR با SQL Server 2005، برنامه‌نويسي رويه‌هاي ذخيره شده، توابع تعريف شده كاربر، تريگر‌ها، انبوهه‌ها و انواع تعريف شده كاربر را با استفاده از هر يك از زبان‌هاي .NET ممكن مي‌سازد. يكپارچگي .NET CLR با SQL Server 2005 بيش از يك مسأله سطحي است. در واقع، موتور پايگاه داده SQL Server 2005 ميزبان CLR در فرآيند است. با استفاده از مجموعه‌اي از APIها، موتور SQL Server تمام مديريت حافظه را براي برنامه‌هاي CLR ميزباني شده انجام مي‌دهد.

كد مديريت شده با استفاده از ADO.NET در الحاق با SQL Server .NET Data Provider به پايگاه داده دستيابي دارد. يك شئ SQL Server جديد به نام اسمبلي[2] واحد توزيع براي اشياي .NET با پايگاه داده است. براي ايجاد اشياي پايگاه داده CLR، ابتدا بايد يك DLL با استفاده از Visual Studio 2005 ايجاد كنيد. سپس آن DLL را به عنوان يك اسمبلي در SQL Server وارد كنيد. بالاخره، آن اسمبلي را به يك شئ پايگاه داده نظير يك رويه ذخيره شده يا تريگر لينك كنيد. در بخش بعد، نحوه استفاده واقعي از ويژگي‌هاي CLR را در SQL Server 2005 به‌طور مفصل‌تر بررسي مي‌كنيم.

اسمبلي‌ها

براي ايجاد اشياي پايگاه داده .NET، بايد كد مديريت شده‌اي بنويسيد و آن را در يك اسمبلي .NET كامپايل كنيد. متداول‌ترين روش انجام اين كار، استفاده از Visual Studio 2005 و سپس ايجاد يك پروژه SQL Server جديد و كامپايل آن در يك DLL است. جزييات بيشتر درباره نحوه ايجاد يك پروژه كد مديريت شده جديد با Visual Studio 2005، در بخش "رويه‌هاي ذخيره شده .NET" در ادامه فصل ارايه مي‌شود.

بعد از ايجاد اسمبلي، مي‌توانيد آن را با استفاده از فرمان T-SQL CREATE ASSEMBLY در SQL Server بارگذاري كنيد، همان‌گونه كه در اين‌جا مي‌بينيد:

CREATE ASSEMBLY MyCLRDLL

FROM '\\SERVERNAME\CodeLibrary\MyCLRDLL.dll'

فرمان CREATE ASSEMBLY پارامتري مي‌گيرد كه حاوي مسيري براي DLLاي است كه در SQL Server بارگذاري خواهد شد. اين مسير مي‌تواند يك مسير محلي باشد، ولي اغلب مسيري به يك اشتراك فايل شبكه‌اي خواهد بود. هنگامي كه CREATE ASSEMBLY اجرا مي‌شود، DLL در پايگاه داده اصلي كپي مي‌شود.

اگر يك اسمبلي بهنگام شده يا كنار گذاشته شود، آن‌گاه مي‌توانيد با استفاده از فرمان DROP ASSEMBLY به اين صورت آن را حذف كنيد:

DROP ASSEMBLY MyCLRDLL

به دليل اين كه اسمبلي‌ها در پايگاه داده قرار دارند، هنگامي كه كد منبع براي آن اسمبلي اصلاح شده و اسمبلي مجدداً كامپايل مي‌شود، اين اسمبلي ابتدا بايد با استفاده از فرمان DROP ASSEMBLY از اين پايگاه داده حذف شده و سپس با استفاده از فرمان CREATE ASSEMBLY قبل از انعكاس بهنگام‌رساني‌ها در اشياي پايگاه داده SQL Server، مجدداً بارگذاري شود.

مي‌توانيد از ديدگاه sys.assemblies براي مشاهده اسمبلي‌هايي استفاده كنيد كه به SQL Server 2005 اضافه شده‌اند، همان‌گونه كه در اين‌جا نشان داده شده است:

SELECT * FROM sys.assemblies

با توجه به اين كه اسمبلي‌ها با استفاده از فايل‌هاي خارجي ايجاد مي‌شوند، ممكن است بخواهيد فايل‌هاي مورد استفاده براي ايجاد اين اسمبلي‌ها را نيز مشاهده كنيد. مي‌توانيد اين كار را با استفاده از ديدگاه sys.assembly_files انجام دهيد، به اين صورت:

SELECT * FROM sys.assembly_files

SQL Server .NET Data Provider

اگر با ADO.NET آشنا هستيد، شايد متعجب شويد كه چگونه اين رويه‌هاي پايگاه داده CLR جديد به پايگاه داده متصل مي‌شوند. روي هم رفته، ADO.NET اتصال پايگاه داده خود را با استفاده از تأمين كننده‌هاي داده .NET مبتني بر كلاينت نظير .NET Framework Data Provider براي SQL Server كه به پايگاه داده SQL Server متصل مي‌شود يا .NET Framework Data Provider براي Oracle كه برنامه‌هاي ADO.NET را به پايگاه‌هاي داده Oracle متصل مي‌كند. در حالي كه اين امر براي يك برنامه شبكه‌اي عالي است، حركت در بين كتابخانه‌هاي شبكه‌اي، كارآمدترين حالت اتصال براي كدي نيست كه مستقيماً روي سرور اجرا مي‌شود، براي رفع اين مشكل، مايكروسافت SQL Server .NET Data Provider را فراهم كرده است. SQL Server .NET Data Provider، يك اتصال درون حافظه‌اي براي پايگاه داده SQL Server توليد مي‌كند.

مرجعي به SQL Server .NET Data Provider جديد، به‌طور خودكار هنگام ايجاد يك پروژه SQL Server جديد، به برنامه‌هاي Visual Studio 2005 شما اضافه مي‌شود. براي ايجاد يك نوع پروژه SQL Server جديد، ابتدا Visual Studio 2005 را باز كرده و سپس گزينه File | New Project را از منوي انتخاب كنيد. سپس، از كادر محاوره‌اي New Projects، نوع پروژه را انتخاب كرده (مثلاً، Visual Basic Projects، Visual C# Projects) و سپس در ليست قالب‌ها حركت كنيد تا وقتي كه قالب پروژه SQL Server نشان داده شده در شكل 1-4 را ببينيد.

شكل 1-4 ايجاد يك پروژه SQL Server جديد با Visual Studio 2005

توجه: از ايجاد پروژه‌ها SQL Server در Visual Studio 2005 پشتيباني مي‌شود (كدي به نام Whidbey) كه براي عرضه در همان زمان SQL Server 2005 برنامه‌ريزي شده است.

بعد از انتخاب قالب پروژه SQL Server، نامي به پروژه خود داده و OK را براي ايجاد پروژه كليك كنيد. تمام مراجع مورد نياز به‌طور خودكار به پروژه SQL Server شما اضافه مي‌شوند. SQL Server .NET Data Provider به عنوان مرجع SQLaccess اضافه مي‌شود كه مي‌توانيد در شكل 4-2 ببينيد كه متمايز شده است. علاوه بر اين، مي‌توانيد مرجع System.Data را ببينيد كه پشتيباني براي ADO.NET و اشياي داده‌گراي آن از قبيل DataSet و انواع داده SQL Server فراهم مي‌كند.

شكل 2-4 مرجع SQL Server .NET Data Provider

علاوه بر افزودن مراجع مناسب، يكي از مهم‌ترين مواردي كه قالب‌هاي Visual Studio 2005 SQL Server به‌طور خودكار براي شما انجام مي‌دهند، اضافه كردن رهنمودهاي ورود صحيح است. هنگامي كه اشياي پايگاه داده SQL Server CLR را ايجاد مي‌كنيد، بايد مطمئن باشيد كه يك عبارت ورود براي فضاي نام System.Data.SQLServer در پروژه خود داشته باشيد. فضاي نام System.Data.SQLServer حاوي كلاس‌هاي .NET اي است كه SQL Server .NET Data Provider را تشكيل مي‌دهند. رهنمود ورود به شما امكان مي‌دهد تا به كلاس‌هايي در فضاي نام System.Data.SQLServer با استفاده از نام كلاس كوتاه آن‌ها مراجعه كنيد و نه نام‌هاي كاملاً معين طولاني كه هميشه نام فضاي نام قبل از آن‌ها قرار گرفته باشد (مثلاً، System.Data.SQLServer). براي يك پروژه C#، رهنمود ورود چنين است:

using System.Data.SqlServer;

براي يك پروژه VB.NET، فضاي نام System.Data.SQLServer چنين است:

Imports System.Data.SqlServer

برخلاف پروژه‌هاي ADO.NET مناسب كه بايد به‌طور صريح اتصالي به يك نمونه SQL Server معين با استفاده از متد Open شئ اتصال باز كند، SQL Server .NET Data Provider به‌طور ضمني اتصالي به سيستم SQL Server محلي به وجود مي‌آورد و همان‌گونه كه در مثال‌هاي بعد خواهيد ديد، هيچ نيازي به ايجاد يك شئ ADO.NET Connection و احضار متد Open آن نيست.

با صراحت مي‌گوييم كه Visual Studio 2005 براي ايجاد اشياي پايگاه داده .NET براي SQL Server 2005 مورد نياز نيست. مي‌توانيد اشياي پايگاه داده CLR را با استفاده از .NET Framework 2.0 و .NET Framework SDK توسعه دهيد. هرچند، Visual Studio 2005 قالب‌هاي پروژه و گزينه‌هاي توزيع پروژه‌اي را فراهم مي‌كند كه مزاياي مهمي نسبت به ايجاد دستي اين اشيا با استفاده تنها از .NET SDK به آن مي‌دهد.

رويه‌هاي ذخيره شده .NET

رويه‌هاي ذخيره شده[3]، يكي از محتمل‌ترين اشياي پايگاه داده هستند كه بخواهيد با استفاده از يكي از زبان‌هاي .NET مديريت شده ايجاد كنيد، زيرا رويه‌هاي ذخيره شده اغلب حاوي منطقي پيچيده و قوانين تجاري نمايان هستند كه براي بيان در T-SQL مشكل هستند. براي ايجاد يك رويه ذخيره شده CLR در Visual Studio 2005، مي‌توانيد از گزينه Project | Add Stored Procedure براي نمايش كادر محاوره‌اي قالب‌هاي نصب شده Visual Studio استفاده كنيد كه در شكل 3-4 نشان داده شده است.

شكل 3-4 افزودن يك رويه ذخيره شده CLR

از كادر محاوره‌اي Add New Item، گزينه Stored Procedure را از ليست قالب‌هاي نمايش داده شده در ليست Templates انتخاب كرده و سپس نام رويه ذخيره شده را در فيلد Nameاي فراهم كنيد كه مي‌توانيد در پايين صفحه نمايش ببينيد. در اين‌جا، مي‌توانيد ببينيد كه رويه ذخيره شده به نام GetSalesPersonCount است. Visual Studio 2005 كلاس جديدي را براي رويه ذخيره شده به پروژه شما اضافه مي‌كند. فايل كلاس توليد شده بعد از نام رويه ذخيره شده نام‌گذاري شده و شامل تمام رهنمودهاي ورود مورد نياز و كد برجسته‌اي است كه رويه ذخيره شده را نام‌گذاري مي‌كند. پر كردن بقيه كد كه كار رويه ذخيره شده را مي‌سازد، بر عهده شماست. اين مثال كد منبع مورد نياز براي ايجاد يك رويه ذخيره شده CLR را تشريح مي‌كند:

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlServer;

using System.Data.SqlTypes;

public partial class StoredProcedures

    [SqlProcedure]

    public static int GetSalesPersonCount()

        int iRows;

        SqlCommand sqlCmd = SqlContext.GetCommand();

        sqlCmd.CommandText =

        "SELECT COUNT(*) AS 'Sales Person Count' "

              + "FROM Sales.SalesPerson";

        iRows = (int)sqlCmd.ExecuteScalar();

        return iRows;

};

اولين نكته مهم كه در اين كد توجه برانگيز است، رهنمودي است كه فضاي نام System.Data.SQLServer را وارد مي‌كند. اين امر به پروژه MyCLRDLL امكان استفاده از SQL Server .NET Data Provider را بدون نياز هميشگي به مراجعه به نام كاملاً معين مي‌دهد. دومين نكته توجه برانگيز صفت [SQLProcedure] است كه قبل از نام متد قرار گرفته است و به كامپايلر مي‌گويد كه اين متد به عنوان يك رويه ذخيره شده SQL Server ارايه مي‌شود. سپس، مي‌توانيد ببينيد كه نام كلاس پيش‌فرض براي اين رويه ذخيره شده با StoredProcedure تنظيم شده است. اين كلاس حاوي يك متد ايستا به نام GetSalesPersonCount است كه يك نوع داده int را برمي‌گرداند. براي C#، اين متد بايد به صورت ايستا تعريف شود. براي كد VB.NET، اين متد بايد به صورت Shared تعريف شود. كد اين‌جا لزوماً تعداد رديف‌هاي جدول Sales.SalesPerson را در پايگاه داده AdventureWorks نمونه بازيابي مي‌كند. توجه داشته باشيد كه در اين كلاس، متد Open استفاده نمي‌شود. اين يك انحراف آزاد براي كد ADO.NET مبتني بر كلاينت است. در عوض، SQL Server .NET Data Provider به‌طور خودكار اتصالي به سرور محلي باز مي‌كند.

بعد از كامپايل كد منبع رويه ذخيره شده CLR در يك اسمبلي، مي‌توانيد آن اسمبلي را به پايگاه داده اضافه كرده و رويه ذخيره شده CLR را ايجاد كنيد. مي‌توانيد اين كار را به دو روش انجام دهيد: اگر در Visual Studio 2005 برنامه‌نويسي مي‌كنيد، آن‌گاه مي‌توانيد از گزينه Build | Deploy Solution براي نصب رويه ذخيره شده CLR جديد در پايگاه داده SQL Server استفاده كنيد. يا مي‌توانيد مراحل توزيع را به‌طور دستي انجام دهيد. براي كمك به درك نحوه استفاده از اشياي CLR در پايگاه داده، مراحل توزيع دستي را در بخش بعد ببينيد. بعد از توليد DLL، مرحله بعد استفاده از آ‌ن DLL براي ايجاد يك شئ SQL Server جديد به نام اسمبلي است. اين كد ايجاد يك اسمبلي را براي MyCLRDLL.DLL تشريح مي‌كند:

CREATE ASSEMBLY MyCLRDLL

FROM '\\MyFileShare\Code Library\MyCLRDLL.dll'

فرمان CREATE ASSEMBLY از اولين آرگومان براي نام‌گذاري اسمبلي استفاده مي‌كند. در اين‌جا، آن را MyCLRDLL ناميده‌ايم كه شبيه نام .NET DLL واقعي است، ولي استفاده از نام‌هاي مشابه الزامي نيست. آرگومان بعد از بخش FROM به عبارت CREATE ASSEMBLY مي‌گويد كه DLL فيزيكي در ديسك كجا قرار دارد. اين مكان مي‌تواند درايو محلي يا يك مسير UNC باشد.

توجه: در زمان نوشتن اين كتاب، اولين باري كه فرمان CREATE ASSEMBLY را به‌طور دستي اجرا كردم، Microsoft.VisualStudio.DataTools.SQLAttributes.dll نيز بايد در همان دايركتوري حاوي .NET DLL اي باشد كه بايد به عنوان يك اسمبلي اضافه كنيد.

هنگام ايجاد اسمبلي، DLL در پايگاه داده SQL Server مقصد كپي شده و اسمبلي رجيستر مي‌شود. اين كد ايجاد رويه ذخيره شده GetSalesPersonCount را تشريح مي‌كند كه از اسمبلي MyCLRDLL استفاده مي‌كند:

CREATE PROCEDURE GetSalesPersonCount

AS EXTERNAL NAME

MyCLRDLL.StoredProcedures.GetSalesPersonCount

بخش EXTERNAL NAME در SQL Server 2005 جديد است. در اين‌جا، بخش EXTERNAL NAME مشخص مي‌كند كه رويه ذخيره شده GetSalesPersonCount با استفاده از يك اسمبلي .NET ايجاد خواهد شد. يك اسمبلي مي‌تواند حاوي چندين كلاس و متد باشد؛ عبارت EXTERNAL NAME از اين ساختار دستوري براي تعيين متد و كلاس صحيح مورد استفاده اسمبلي استفاده مي‌كند:

Assembly Name.ClassName.MethodName

در مورد مثال قبل، اسمبلي رجيستر شده MyCLRDLL ناميده مي‌شود. كلاس در اسمبلي StoredProcedure است و متد در كلاسي كه اجرا خواهد شد، GetSalesPersonCount است.

بعد از ايجاد رويه ذخيره شده CLR، مي‌تواند به‌طور صريح شبيه هر رويه ذخيره شده T-SQL فراخواني شود، همان‌گونه كه در اين مثال نشان داده شده است:

DECLARE @mycount INT

EXEC @mycount = GetSalesPersonCount

PRINT @mycount

توابع تعريف شده كاربر .NET

ايجاد توابع تعريف شده كاربر[4] (UDFها)، ويژگي جديد ديگري است كه با يكپارچگي .NET CLR امكانپذير شده است. توابع تعريف شده كاربر كه انواع عددي را برمي‌گردانند، بايد يك نوع داده .NET را برگردانند كه مي‌تواند به‌طور ضمني به يك نوع داده SQL Server تبديل شود. توابع عددي نوشته شده با .NET Framework ، مي‌توانند به‌طور مهمي، T-SQL را در روش‌هاي خاصي انجام دهند، زيرا برخلاف توابع T-SQL، توابع .NET با استفاده از كد كامپايل شده ايجاد مي‌شوند. توابع تعريف شده كاربر، هم‌چنين مي‌تواند انواع جدول را برگرداند كه در مورد اين تابع، بايد يك مجموعه نتيجه را برگرداند.

براي افزودن يك UDF با استفاده از Visual Studio 2005، مي‌توانيد از گزينه منوي Project | Add User-Defined Function كه در شكل 4-4 نشان داده شده است، استفاده مي‌كند.

شكل 4-4 افزودن يك تابع تعريف شده كاربر CLR

مي‌توانيد اين را به يك پروژه موجود اضافه كنيد، همان‌گونه كه من انجام داده‌ام (آن را به MyCLRDLL نمونه كه در مثال قبل ايجاد كرديم، اضافه كنيد) يا يك پروژه SQL Server جديد را ايجاد كنيد. مثال بعد، يك UDF ساده به نام GetDateAsString را نشان مي‌دهد كه يك تبديل تاريخ به رشته پايه را انجام مي‌دهد:

using System;

using System.Data.Sql;

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

    [SqlFunction]

    public static SqlString GetDateAsString()

        DateTime CurrentDate = new DateTime();

        return CurrentDate.ToString();

};

در اين‌جا توجه داشته باشيد كه فضاي نام System.Data.SQLServer مورد نياز نيست، همان‌گونه كه اين تابع خاص هيچ دستيابي داده‌اي را انجام نمي‌دهد. سپس، مي‌توانيد ببينيد كه به‌طور پيش‌فرض كلاس UserDefinedFunctions را ايجاد كرده است تا حاوي تمام متدهايي باشد كه اين اسمبلي به عنوان UDF ارايه خواهد كرد. هم‌چنين مي‌توانيد ببينيد كه صفت [SQLFunction] براي تعيين متد GetDateAsString به عنوان يك UDF استفاده مي‌شود. كد اين‌جا تاريخ سيستم را به يك نوع داده رشته‌اي تبديل مي‌كند.

براي ايجاد تابع، اسمبلي ابتدا بايد همانند آن‌چه كه در مثال رويه ذخيره شده بيان شد، ايجاد شود. اگر از Visual Studio 2005 استفاده مي‌كنيد، مي‌توانيد گزينه Build | Deploy Solution را انتخاب كنيد. اگر اين كار را به‌طور دستي انجام مي‌دهيد و اين در يك اسمبلي با اشياي CLR ديگر است، ابتدا بايد اين اشيا را حذف كنيد، سپس اسمبلي را حذف كرده و بالاخره اسمبلي و اشيا را مجدداً ايجاد كنيد. با توجه به اين كه اين متد به MyCLRDLL اضافه شده است كه هم‌اكنون در يك اسمبلي و يك رويه ذخيره شده استفاده مي‌شود، عبارت DROP PROCEDURE و عبارت DROP ASSEMBLY بايد ابتدا اجرا شوند تا اشياي پايگاه داده وابسته قبل از بارگذاري مجدد .NET DLL بهنگام شده در يك اسمبلي حذف شوند. بعد از حذف اشياي موجود، عبارت CREATE ASSEMBLY زير مي‌تواند براي بارگذاري مجدد DLL جديد اجرا شود:

CREATE ASSEMBLY MyCLRDLL

FROM '\\MyFileShare\Code Library\MyCLRDLL.dll'

سپس عبارت CREATE FUNCTION براي ايجاد تابع SQL Server جديدي استفاده مي‌شود كه متد مناسب را در اسمبلي اجرا مي‌كند. ليست زير نحوه ايجاد يك تابع تعريف شده كاربر .NET توسط عبارت CREATE FUNCTION را نشان مي‌دهد:

CREATE FUNCTION GetDateAsString()

RETURNS nvarchar(256)

EXTERNAL NAME

MyCLRDLL.UserDefinedFunctions.GetDateAsString

براي توابع تعريف شده كاربر، عبارت CREATE FUNCTION با بخش EXTERNAL NAME توسعه يافته است كه لزوماً نام تابع تعريف شده كاربر را به متد مناسب در اسمبلي .NET لينك مي‌كند. در اين مثال، تابع GetDateAsString از اسمبلي به نام MyCLRDLL استفاده مي‌كند. در آن اسمبلي، از كلاس UserDefinedFunctions و متد GetDateAsString در آن كلاس استفاده مي‌شود.

بعد از ايجاد تابع، مي‌تواند شبيه يك تابع عادي SQL Server فراخواني شود. مي‌توانيد نحوه اجراي تابع GetDateAsString را در اين مثال ببينيد:

SELECT dbo.GetDateAsString()

تريگرهاي .NET

علاوه بر رويه‌هاي ذخيره شده و توابع تعريف شده كاربر، قابليت‌هاي يكپارچگي .NET در SQL Server 2005، هم‌چنين توانايي ايجاد تريگرهاي تعريف شده كاربر[5] (UDTهاي) .NET را فراهم مي‌كند. براي افزودن يك UDT با استفاده از Visual Studio 2005، مي‌توانيد از گزينه منوي Project | Add Trigger استفاده كنيد، همان‌گونه كه در شكل 5-4 نشان داده شده است.

شكل 5-4 افزودن يك تريگر CLR

همانند اشياي پايگاه داده CLR ديگر، گزينه Trigger را از ليست قالب‌ها انتخاب كرده و سپس نام تريگر را در اعلام نام مهيا كنيد. Visual Studio 2005 يك فايل برجسته را توليد خواهد كرد كه مي‌توانيد به كد خود اضافه كنيد. ليست كد مثال بعد يك تريگر CLR ساده به نام MyTrigger را تشريح مي‌كند:

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlServer;

using System.Data.SqlTypes;

public partial class Triggers

    // Enter existing table or view for the target

    //  and uncomment the attribute line

    [SqlTrigger (Name="MyTrigger",

     Target="Person.ContactType", Event="FOR INSERT")]

    public static void MyTrigger()

        SqlTriggerContext oTriggerContext =

            SqlContext.GetTriggerContext();

        SqlPipe sPipe = SqlContext.GetPipe();

        SqlCommand sqlCmd = SqlContext.GetCommand();

        if (oTriggerContext.TriggerAction == TriggerAction.Insert)

            sqlCmd.CommandText = "SELECT * FROM inserted";

            sPipe.Execute(sqlCmd);

شبيه ساير مثال‌ها، اين فايل برجسته شامل رهنمود ورود مناسب و توليد يك كلاس است كه در اين مورد Triggers ناميده مي‌شود و متدي با صفت متد مناسب است. اين مثال كد از يك جفت شئ ADO.NET جديد استفاده مي‌كند: شئ SQLTriggerContext و شئ .SQLPipe شئ SQLTriggerContext اطلاعاتي درباره عمل تريگري كه فعال شده و ستون‌هايي كه متأثر شده‌اند، فراهم مي‌كند. شئ SQLTriggerContext هميشه توسط شئ SQLContext نمونه‌سازي مي‌شود. معمولاً، شئ SQLContext اطلاعاتي درباره زمينه فراخوان فراهم مي‌كند. بخصوص، در اين مورد، شئ SQLContext به كد امكان دستيابي به جدول مجازي را مي‌دهد كه در طي اجراي تريگر ايجاد شده است. اين جدول مجازي، داده‌اي را كه موجب فعال شدن تريگر شده است، ذخيره مي‌كند.

سپس، شئ SQLPipe ايجاد مي‌شود. شئ SQLPipe معبري را نشان مي‌دهد كه اطلاعات بين CLR و كد فراخوان ارسال مي‌شوند. در اين‌جا، شئ SQLPipe به تريگر توسعه يافته امكان مي‌دهد تا با فراخوان خارجي ارتباط برقرار كند. سپس شئ SQLContext براي تعيين اين مسأله استفاده مي‌شود، آيا عمل تريگر يك عمل درج بوده است. اگر چنين است، آن‌گاه محتويات جدول تريگر مجازي بازيابي شده و با استفاده از متد Execute شئ SQLPipe به فراخوان ارسال كنيد.

هنگامي‌كه كد ايجاد شد، مي‌توانيد آن را با استفاده از گزينه Visual Studio 2005 Build | Deploy Solution براي پايگاه داده توزيع كنيد و اسمبلي و هر شئ مرتبط به آن را حذف كرده و مجدداً ايجاد كنيد. براي ايجاد دستي يك تريگر CLR، مي‌توانيد از حالت CREATE TRIGGER استفاده كنيد كه در مثال بعد آن را مي‌بينيد. اين كد نحوه ايجاد تريگر توسعه يافته را در جدول Person.ContactType در پايگاه داده AdventureWorks را نشان مي‌دهد:

CREATE TRIGGER MyTrigger

ON Person.ContactType

FOR INSERT

AS EXTERNAL NAME

MyCLRDLL.Triggers.MyTrigger

شبيه مثال‌هاي .NET ديگر، تريگر توسعه يافته با استفاده از عبارت CREATE TRIGGER ايجاد مي‌شود. عبارت CREATE TRIGGER با بخش AS EXTERNAL NAME توسعه يافته است كه تريگر را به متدي در يك اسمبلي مرتبط مي‌كند. در اين‌جا بخش EXTERNAL NAME به متدي در اسمبلي مرتبط مي‌شود. در اين‌جا، بخش EXTERNAL NAME به اسمبلي به نام MyCLRDLL اشاره مي‌كند. در كلاس Triggers آن فضاي نام، متد MyTrigger حاوي كدي است كه هنگام فعال شدن تريگر توسعه يافته اجرا خواهد شد.

تريگر .NET براي هر عمل درجي كه در جدول Job انجام مي‌شود، فعال خواهد شد. مثلاً، اين عبارت INSERT رديفي را به جدول Person.ContactType اضافه خواهد كرد كه موجب فعال شدن تريگر .NET خواهد شد:

INSERT INTO Person.ContactType VALUES(102, 'The Big Boss',

  '2004-07-20 00:00:00.000')

تريگر مثال MyTrigger يك عبارت Select را در مقدار رديف درج شده انجام مي‌دهد. سپس از شئ SQLPipe براي ارسال نتايج به فراخوان استفاده مي‌كند. در اين مثال، تريگر محتويات مقادير رديف درج شده را به فراخوان برمي‌گرداند.

انواع داده تعريف شده كاربر CLR

ويژگي مهم ديگر در SQL Server 2005 كه با يكپارچگي .NET CLR فعال مي‌شود، توانايي ايجاد انواع تعريف شده كاربر[6] (UDTهاي) واقعي است. با استفاده از UDTها، مي‌توانيد انواع رديف فراهم شده توسط SQL Server را توسعه داده و انواع داده‌اي را كه خاص برنامه يا محيط شماست، اضافه كنيد.

در مثال بعد، نحوه‌ ايجاد يك UDT را خواهيد ديد كه يك كد جنسيت را نشان مي‌دهد: M براي مرد و F براي زن. در حالي كه مي‌توانيد اين داده را در يك فيلد كاراكتري تك بايتي استاندارد ذخيره كنيد، استفاده از يك UDT اطمينان مي‌دهد كه اين فيلد تنها اين دو مقدار را بدون نياز اضافي به تريگرها، الزامات يا ساير تكنيك‌هاي تأثير اعتبار داده، خواهد پذيرفت.

اگر از Visual Studio 2005 استفاده مي‌كنيد، بهترين روش براي ايجاد UDT، استفاده از قالب‌‌هاي SQL Server است. براي ايجاد يك UDT جديد، روي پروژه خود در Visual Studio 2005 كليك راست كرده و Add | Add Class را از منوي زمينه انتخاب كنيد. اين امر كادر محاوره‌اي Add New Item را نمايش خواهد داد كه در شكل 6-4 نشان داده شده است.

شكل 6-4 ايجاد يك .NET SQL Server UDT

User-Defined Type را از ليست قالب‌هاي SQL Server انتخاب كنيد. نامي را وارد كنيد كه براي انتساب به كلاس در نظر داريد و سپس Open را براي توليد يك فايل برجسته براي UDT توسط Visual Studio كليك كنيد. اين فايل برجسته، چهار متدي را پياده‌سازي مي‌كند كه SQL Server 2005 براي تمام UDTها نياز دارد. اين متدها براي برآورده كردن الزامات قرارداد SQL Server UDT ژنريك هستند (افزودن كد براي ساخت UDT كه اعمال با معني را انجام دهد، به عهده شما گذاشته مي‌شود). چهار متد UDT مورد نياز در جدول 1-4 فهرست شده‌اند.

جدول 1-4 متدهاي UDT مورد نياز

متد	شرح
IsNullable	اين متد مورد نياز براي نشان دادن قابل null بودن شئ استفاده مي‌شود. SQL Server 2005 براي پياده‌سازي null پذيري، نياز به تمام UDTها دارد، بنابراين اين متد هميشه بايد true را برگرداند.
Parse	اين متد مورد نياز، يك پارامتر رشته‌اي را گرفته و آن را به عنوان يك UDT ذخيره مي‌كند.
ToString	اين متد مورد نياز، محتويات UDT را به يك رشته تبديل مي‌كند.
Default constructor	اين متد مورد نياز، نمونه جديدي از UDT را ايجاد مي‌كند.

مي‌توانيد كلاس MFType كامل شده‌اي را ببينيد كه براي پياده‌سازي يك UDT براي كدهاي M (مرد) و F (زن) در اين ليست استفاده مي‌شود:

using System;

using System.Data.Sql;

using System.Data.SqlTypes;

[Serializable]

[SqlUserDefinedType(Format.SerializedDataWithMetadata,

MaxByteSize=512)]

public class MFType: INullable

    string m_value;

    public override string ToString()

        string s = "null";

        if (m_value != null)

            s = m_value.ToString();

            return s;

        else return m_value.ToString();

    public bool IsNull

get

            if (m_value == null)

                return true;

            else return false;

    public static MFType Null

get

            MFType h = new MFType();

            return h;

    public static MFType Parse(SqlString s)

        if (s.IsNull || s.Value.ToLower() == "null")

            return Null;

        MFType u = new MFType();

        u.Value = s.ToString();

        return u;

    // Create a Value Property

    public SqlString Value

get

            return (m_value);

set

            if (value == "M" || value == "F")

                m_value = value.ToString();

            else

                throw new ArgumentException

                    ("MFType data type must be M or F");

اولين بخش اين كد لزوماً قالبي است كه توسط انواع تعريف شده كاربر مورد نياز است. صفت كلاس بايد قابل سريالي شدن باشد، كلاس بايد رابط INullable را پياده‌سازي كند و نام كلاس با نام UDT تنظيم مي‌شود. مي‌توانيد رابط IComparable را به‌طور اختياري اضافه كنيد. در اين مثال، MFType نام كلاس است. يك متغير رشته‌اي به نام m_value براي نگهداري محتويات كد معرفي شده است. سپس، مي‌توانيد متد ToString مورد نياز را ببينيد. متد ToString بررسي مي‌كند تا ببيند آيا محتويات متغير m_value، null است. اگر چنين است، آن‌گاه رشته "null" برگردانده مي‌شود. در غير اين صورت، متد ToString متغير m_value، مقدار رشته‌اي محتويات را برمي‌گرداند.

بخش بعدي كد، خصوصيت IsNull را تعريف مي‌كند. متد get اين خصوصيت، محتويات متغير m_value را بررسي كرده و در صورت null بودن m_value، مقدار true را برمي‌گرداند. در غير اين صورت، متد Get مقدار false را برمي‌گرداند. سپس، مي‌توانيد متد Null را ببينيد كه توسط قالبي براي برآورده كردن شرط UDT براي null پذيري توليد شده است.

متد Parse يك آرگومان رشته‌اي را مي‌گيرد كه در خصوصيت Value شئ ذخيره مي‌شود. مي‌توانيد تعريفي براي خصوصيت value را كمي پايين‌تر در كد ببينيد. متد Parse بايد به صورت ايستا معرفي شود يا اگر از VB.NET استفاده مي‌كنيد، بايد يك خصوصيت Shared باشد. خصوصيت Value خاص اين پياده‌سازي است. در اين مثال، خصوصيت Value براي ذخيره و بازيابي مقدار UDT استفاده مي‌شود و هم‌چنين مسئول ويرايش مقادير مجاز است. در متد Set، مي‌توانيد ببينيد كه تنها مقادير M يا F مجاز هستند. تلاش براي استفاده از هر مقدار ديگري، موجب رخ دادن استثنايي مي‌شود كه به فراخوان هشدار مي‌دهد كه "نوع داده MFType بايد M يا F باشد". بسيار شبيه يك تابع يا رويه ذخيره شده CLR، بعد از تكميل كد، در يك DLL كامپايل مي‌شود. آن‌گاه آن DLL به عنوان يك اسمبلي SQL Server با استفاده از عبارت CREATE ASSEMBLY يا گزينه Visual Studio 2005 Deploy Solution وارد مي‌شود (گزينه Visual Studio 2005 Deploy Solution اسمبلي و UDT را ايجاد مي‌كند).

براي افزودن دستي UDT به پايگاه داده، مي‌توانيد از يك عبارت CREATE TYPE شبيه اين استفاده كنيد:

CREATE TYPE MFType EXTERNAL NAME MyCLRDLL.MFType

همانند هنگام ايجاد ساير اشياي پايگاه داده .NET ، كلمه كليدي EXTERNAL NAME براي مشخص كردن اسمبلي و فضاي نام براي UDT استفاده مي‌شود. در اين مورد، با توجه به اين كه خود UDT به عنوان يك كلاس پياده‌سازي مي‌شود، نياز به نام متد نيست. مقدار MyCLRDLL اسمبلي را مشخص مي‌كند و مقدار MFType كلاس UDT را مشخص مي‌كند. براي مشاهده UDTهايي كه براي يك پايگاه داده ايجاد كرده‌ايد، مي‌توانيد ديدگاه sys.Types را پرس‌وجو كنيد، همان‌گونه كه در اين‌جا نشان داده شده است:

SELECT * FROM sys.Types

هنگامي كه UDT ايجاد مي‌شود، مي‌توانيد از آن در T-SQL شبيه انواع داده طبيعي SQL Server استفاده كنيد. هرچند، با توجه به اين كه UDTها حاوي متدها و خصوصيات هستند، تفاوت‌هايي وجود دارد. اين مثال نشان مي‌دهد كه چگونه MFType UDT مي‌تواند به عنوان يك متغير استفاده شود و چگونه خصوصيت Value آن مي‌تواند مورد دستيابي قرار گيرد:

DECLARE @mf MFType

SET @mf.Value='N'

PRINT @mf.Value

در اين ليست، متغير UDT با استفاده از عبارت T-SQL DECLARE استاندارد معرفي مي‌شود. مي‌توانيد به اعضاي UDT با پيشوند قراردادن آن‌ها با نماد (.) دستيابي داشته باشيد. در اين ليست، عبارت SET براي انتساب مقدار N به خصوصيت value متغير UDT استفاده مي‌شود. به دليل اين كه N يك مقدار معتبر نيست، اين خطا توليد مي‌شود:

.Net SqlClient Data Provider: Msg 6522, Level 16, State 1, Line 2

A CLR error occurred during execution of 'MFType':

System.ArgumentException: MFType data type must be M or F

at MFType.set_Value(SqlString value)

همان‌گونه كه UDTها مي‌توانند به عنوان متغير مورد استفاده قرار گيرند، هم‌چنين مي‌توانند براي ايجاد ستون‌ها استفاده شوند. اين ليست، ايجاد جدولي را نشان مي‌دهد كه از MFType UDT استفاده مي‌كند:

CREATE TABLE MyContacts

(ContactID int,

FirstName varchar(25),

LastName varchar(25),

Gender MFType)

در حالي كه ايجاد ستون‌ها با نوع UDT شبيه هنگام استفاده از يك نوع داده طبيعي است، انتساب مقادير به UDT كمي متفاوت از انتساب ستون استاندارد است. UDTهاي پيچيده مي‌توانند حاوي چندين مقدار باشند. در اين مورد، با توجه به اين كه UDT از يك مقدار ساده استفاده مي‌كند، مي‌توانيد مقاديري را به آن نسبت دهيد، همان‌گونه كه مي‌توانيد در مورد هر نوع داده تعبيه شده‌اي اين كار را انجام دهيد. اين مثال نحوه درج رديفي در جدول نمونه MyContacts را نشان مي‌دهد كه حاوي MFType UDT است:

INSERT INTO MyContacts VALUES(1, 'Michael', 'Otey', 'M')

براي بازيابي محتويات UDT با استفاده از عبارت SELECT، بايد از نمادگذاري UDT.Member هنگام مراجعه به يك ستون UDT استفاده كنيد، همان‌گونه كه در اين‌جا نشان داده شده است:

SELECT ContactID, LastName, Gender.Value FROM MyContacts

انبوهه‌هاي تعريف شده كاربر CLR

يك انبوهه تعريف شده كاربر[7] (UDAGG)، نوع جديد ديگري از شئ پايگاه داده .NET است كه در SQL Server 2005 معرفي شده است. لزوماً، يك انبوهه تعريف شده كاربر، يك تابع قابليت توسعه است كه به شما امكان مي‌دهد تا مقادير را روي گروهي در طي پردازش يك پرس‌وجو انبوهه كنيد. SQL Server هميشه مجموعه پايه‌اي از توابع انبوهه شبيه MIN، MAX و SUM را فراهم كرده است كه مي‌توانيد در يك پرس‌وجو استفاده كنيد. انبوهه‌هاي تعريف شده كاربر به شما امكان توسعه اين گروه از توابع انبوهه را با انبوهه‌هاي اختصاصي مي‌دهد. شبيه توابع انبوهه طبيعي، انبوهه‌هاي تعريف شده كاربر به شما اجازه اجراي محاسبات در مجموعه‌اي از مقادير را مي‌دهند و يك مقدار تكي را برمي‌گردانند. هنگامي كه يك UDAGG را ايجاد مي‌كنيد، منطقي را مهيا كنيد كه انبوهه را انجام خواهد داد. در اين بخش، نحوه ايجاد يك UDAGG ساده را خواهيد ديد كه مقدار ميانه را براي مجموعه‌اي از اعداد محاسبه مي‌كند.

براي ايجاد يك UDAGG جديد با استفاده از Visual Studio 2005، گزينه Project | Add Aggregate را از منويي براي نمايش كادر محاوره‌اي Add New Item انتخاب كنيد كه مي‌توانيد آن را در شكل 7-4 ببينيد.

شكل 7-4 افزودن يك انبوهه تعريف شده كاربر

Aggregate را از ليست قالب‌هاي SQL Server انتخاب كرده و سپس نامي براي كلاس وارد نموده و Open را كليك كنيد. Visual Studio يك فايل برجسته براي كلاس Aggregate توليد خواهد كرد. بيشتر شبيه يك UDT، فايل برجسته كلاس Aggregate چهار متدي را پياده‌سازي مي‌كند كه SQL Server 2005 براي تمام انبوهه‌هاي تعريف شده كاربر نياز دارد. چهار متد مورد نياز براي تمام UDAGGها در جدول 2-4 ليست شده‌اند.

جدول 2-4 متدهاي انبوهه مورد نياز

متد	شرح
Init	اين متد شئ را مقداردهي مي‌كند. اين متد براي هر انبوهه يك بار احضار مي‌شود.
Accumulate	اين متد مورد نياز براي هر آيتم در مجموعه انبوهه شده، يك بار احضار مي‌شود.
Merge	اين متد مورد نياز هنگامي احضار مي‌شود كه سرور يك پرس‌وجو را با استفاده از توازن اجرا مي‌كند. اين متد براي ادغام داده از نمونه‌هاي موازي متفاوت با يكديگر استفاده مي‌شود.
Terminate	اين متد مورد نياز، نتايج انبوهه را برمي‌گرداند. اين متد بعد از پردازش تمام آيتم‌ها، يك بار احضار مي‌شود.

مي‌توانيد كلاس MaxVariance مثال را ببينيد كه براي پياده‌سازي يك انبوهه MaxVar تعريف شده كاربر در اين ليست استفاده مي‌شود:

[Serializable]

[SqlUserDefinedAggregate(Format.SerializedDataWithMetadata, MaxByteSize = 512)]

public class MaxVariance

    int m_LowValue;

    int m_HighValue;

    public void Init()

        m_LowValue = 999999999;

        m_HighValue = -999999999;

    public void Accumulate(SqlInt32 Value)

        if (Value > m_HighValue) m_HighValue = (int)Value;

        if (Value < m_LowValue) m_LowValue = (int)Value;

    public void Merge (MaxVariance Group)

        if (Group.GetHighValue() > m_HighValue)

            m_HighValue = Group.GetHighValue();

        if (Group.GetLowValue() < m_LowValue)

            m_LowValue = Group.GetLowValue();

    public SqlInt32 Terminate ()

        return m_HighValue - m_LowValue;

    // Helper methods

    public int GetLowValue()

        return m_LowValue;

    public int GetHighValue()

        return m_HighValue;

در بالاي اين كلاس، مي‌توانيد صفت سريالي‌سازي را ببينيد كه توسط كلاس‌هاي UDAGG مورد نياز است. سپس، دو متغير براي نگهداري مقادير حداقل و حداكثري معرفي مي‌شوند كه توسط انبوهه مواجه مي‌شويم. بعد از آن، در متد Init، مقادير بالا و پايين به دو متغير نسبت داده مي‌شوند و اطمينان حاصل مي‌شود كه آن‌ها مقادير منتسب از ليست خواهند بود. در حالي كه متد Init فقط يك بار فراخواني مي‌شود، متد Accumulate براي هر رديف در مجموعه نتيجه، يك بار فراخواني مي‌شود. در اين مثال، متد Accumulate، مقدار ورودي را با مقادير ذخيره شده در متغيرهاي m_HighValue و m_LowValue مقايسه مي‌كند. اگر مقدار ورودي بيشتر از مقدار بالاي جاري باشد، در متغير m_HighValue ذخيره مي‌شود. اگر مقدار كوچك‌تر از مقدار m_LowValue باشد، در m_LowValue ذخيره مي‌شود. در غير اين صورت، هيچ عملي براي اين UDAGG توسط متد Accumulate انجام نمي‌شود.

توجه: به دليل اين كه UDAGGها سريالي مي‌شوند، بايد از الزام حافظه كل UDAGG آگاه باشيد. UDAGG بعد از هر بار احضار متد Accumulate سريالي مي‌شود و نمي‌تواند از اندازه ستون حداكثر 8000 بايت تجاوز كند.

متد Merge هنگامي استفاده مي‌شود كه UDAGG به‌طور موازي پردازش مي‌شود كه معمولاً چنين حالتي در بيشتر پرس‌وجوها نيست. اگر Merge فراخواني شود، كار آن وارد كردن مقادير انبوهه جاري از نمونه موازي است. در اين‌جا مي‌توانيد ببينيد كه اين كار با استفاده از دو متد كمكي انجام مي‌شود كه لزوماً مقادير متغيرهاي m_HighValue و m_LowValue را صادر مي‌كنند. اين مقادير با مقادير موجود مقايسه شده و چنان‌چه بيشتر يا كمتر باشند، جايگزين مقادير جاري در m_HighValue و m_LowValue خواهند شد.

متد Terminate يك بار بعد از پردازش تمام نتايج فراخواني مي‌شود. براي اين مثال، متد Terminate كمترين مقدار موجود را از بيشترين مقدار موجود كم كرده و اختلاف را به فراخوان برمي‌گرداند.

بعد از كامپايل كلاس .NET در يك DLL، مي‌توانيد DLL را به عنوان يك اسمبلي SQL Server با استفاده از گزينه Visual Studio 2005 Deploy Solution يا به‌طور دستي با استفاده از عبارت CREATE ASSEMBLY و عبارات CREATE AGGREGATE وارد كنيد. عبارت CREATE AGGREGATE دستي در اين‌جا نشان داده شده است:

CREATE AGGREGATE MaxVariance(@MyInt int)

RETURNS int

EXTERNAL NAME MyCLRDLL.MaxVariance

اين مثال ايجاد يك انبوهه به نام MaxVariance را نشان مي‌دهد. اين انبوهه مي‌تواند با انواع داده صحيح استفاده شود و يك عدد صحيح را برگرداند. در بخش EXTERNAL NAME، مي‌توانيد كدي را براي اين UDAGG ببينيد كه در كلاس MaxVariance اسمبلي MyCLRDLL قرار دارد.

مي‌توانيد از UDAGG درست شبيه توابع انبوهه تعبيه شده SQL Server استفاده كنيد. يك تفاوت كوچك اين است كه بايد قبل از UDAGG، نام طرح‌واره بيايد تا به سيستم اجازه يافتن آن را بدهد. اين خط استفاده از MaxVariance UDAGG را نشان مي‌دهد:

SELECT dbo.MaxVariance(MinQty) FROM Sales.SpecialOffer

نتيجه اين UDAGG تفاوت بين مقادير بالا و پايين موجود در ستون SalesSpecialOffer است.

امنيت شيئي پايگاه داده .NET

هيچ بحثي در مورد ويژگي‌هاي CLR جديد بدون مباحث امنيتي مربوط به استفاده از اسمبلي‌هاي .NET و SQL Server CLR كامل نمي‌شود. برخلاف T-SQL كه هيچ امكان طبيعي براي مراجعه به منابع خارج از پايگاه داده ندارد، اسمبلي‌هاي .NET كاملاً قادر به دستيابي به منابع سيستم و شبكه هستند. بنابراين، ايمن كردن آن‌ها جنبه مهمي از برنامه‌نويسي آن‌هاست. در SQL Server 2005، مايكروسافت مدل امنيتي SQL Server مبتني بر كاربر را با مدل ايمني CLR مبتني بر مجوزها يكپارچه كرده است و با پيروي از مدل امنيتي SQL Server، كاربران امكان دستيابي به تنها اشياي پايگاه داده را دارند (از جمله آن‌هايي كه از اسمبلي‌هاي .NET ايجاد شده‌اند) تا حقوق كاربري داشته باشند. امنيت CLR اين مسأله را با فراهم كردن كنترل روي نوع منابع سيستمي كه مي‌توانند توسط كد .NET در حال اجرا روي سرور مورد دستيابي قرار گيرند، توسعه مي‌دهد. مجوزهاي امنيتي CLR در زماني مشخص مي‌شوند كه اسمبلي با استفاده از بخش WITH PERMISSION_SET عبارت CREATE ASSEMBLY ايجاد مي‌شود. جدول 3-4 گزينه‌هايي را براي مجوزهاي امنيتي پايگاه داده CLR خلاصه مي‌كند كه مي‌توانند بر اشياي پايگاه داده SQL Server اعمال شوند.

جدول 3-4 گزينه‌هاي امنيتي شيئي پايگاه داده CLR

امنيت CLR	دستيابي خارجي مجاز است	فراخواني براي كد مديريت نشده
SAFE	بدون دستيابي خارجي	بدون فراخواني براي كد مديريت نشده
EXTERNAL_ACCESS	دستيابي خارجي از طريق APIهاي مديريتي مجاز است.	بدون فراخواني براي كد مديريت نشده
UNSAFE	دستيابي خارجي مجاز است.	فراخواني براي كد مديريت نشده مجاز است.

استفاده از مجوز SAFE، تمام دستيابي خارجي را ممنوع مي‌كند. مجوز EXTERNAL_ACCESS مقداري دستيابي خارجي به منابع را با استفاده از APIهاي مديريت شده ممكن مي‌سازد. SQL Server براي دستيابي منابع خارجي نقش فراخوان را بازي مي‌كند. بايد مجوز EXTERNAL_ACCESS جديدي براي ايجاد اشيا با اين مجموعه مجوز داشته باشيد. مجوز UNSAFE اصولاً طوري است كه مي‌توان به تمام منابع سيستم دسترسي داشت و فراخواني كد مديريت شده و نشده ميسر است. تنها راهبران سيستم مي‌توانند اشيايي با مجوزهاي UNSAFE ايجاد كنند.

زمان استفاده از اشياي پايگاه داده CLR

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

بهبودهاي T-SQL

با تمام ويژگي‌هاي جديد مربوط به .NET ، شايد تعجب كنيد اگر بدانيد مايكروسافت براي برداشتن پشتيباني از T-SQL برنامه‌ريزي كرده باشد، ولي قطعاً چنين نيست. T-SQL هنوز بهترين زبان براي استفاده جهت دستيابي داده خام است و همان‌گونه كه ممكن است توجه كرده باشيد، ساختار دستوري T-SQL انجام داده است كه در اين بخش به آن‌ها مي‌پردازيم.

بهبودهاي TOP

در SQL Server 2000، مجبور به استفاده از يك مقدار ثابت در الحاق با بخش TOP بوديد. به عبارت ديگر، مي‌توانستيد رديف‌هاي TOP 5 يا TOP 10 را انتخاب كنيد كه مقدار 5 يا 10 يك ثابت بود. در SQL Server 2005، تابع TOP هم اينك كاربرد يك عبارت را در الحاق با بخش TOP ممكن مي‌سازد. يك عبارت مي‌تواند هر عبارت T-SQL مجازي باشد، از جمله يك متغير يا زير پرس‌وجوي عددي. بخش TOP هم‌چنين در عبارات INSERT، UPDATE و DELETE پشتيباني مي‌شود. اين امر انعطاف‌پذيري بيشتري نسبت به قبل به بخش TOP مي‌دهد. مثالي از كاربرد بخش TOP جديد چنين است:

USE AdventureWorks

DECLARE @MyTop INT

SET @MyTop = 15

SELECT TOP (@MyTop) CustomerID, SalesPerson FROM Sales.Customer

CTE[8]

ويژگي T-SQL جديد ديگر، پشتيباني از CTEهاست. CTEها بسيار شبيه ديدگاه‌ها هستند؛ هرچند، آن‌ها در يك پرس‌وجو تعبيه مي‌شوند. دليل اصلي مايكروسافت براي CTEها براي SQL Server 2005، فراهم كردن مكانيزمي براي مديريت پرس‌وجوهاي بازگشتي است. بازگشتي با اين واقعيت به دست مي‌آيد كه يك CTE براي مراجعه به خود مجاز است. براي جلوگيري از امكان همه‌گير بودن سيستم با يك پرس‌وجوي بازگشتي با ساختار ضعيف، SQL Server يك محدوديت گستره سرور را در مورد حداكثر سطح بازگشتي مجاز با يك حداكثر پيش‌فرض 100 سطح پياده‌سازي كرده است. يك CTE به عنوان بخشي از كلمه كليدي WITH پياده‌سازي مي‌شود و مي‌تواند با عبارات SELECT، INSERT، UPDATE و DELETE استفاده شود. براي پياده‌سازي پرس‌وجوهاي بازگشتي با استفاده از CTE جديد، بايد از يك ساختار خاص استفاده كنيد، همان‌گونه كه در مثال كد ساده‌اي در ادامه نشان داده شده است. اين مثال يك پرس‌وجوي بازگشتي ساده را با استفاده از جدول HumanResources.Employee در پايگاه داده نمونه AdventureWorks انجام مي‌دهد:

USE AdventureWorks

WITH EmployeeChart(EmployeeID, ManagerID, Title)

AS

(SELECT EmployeeID, ManagerID, Title

 FROM HumanResources.Employee

 WHERE EmployeeID = 3

 UNION ALL

SELECT L2.EmployeeID, L2. ManagerID, L2.Title

 FROM HumanResources.Employee AS L2

 JOIN EmployeeChart

  ON L2.ManagerID = EmployeeChart.EmployeeID)

SELECT * from EmployeeChart

براي استفاده از يك CTE، ابتدا يك بخش WITH بنويسيد كه براي نام‌گذاري CTE استفاده مي‌كنيد و ستون‌هايي را براي انقياد به يك عبارت SELECT مشخص كنيد. بايد يك سمي‌كالون در جلوي كلمه كليدي WITH قرار دهيد، چنان‌چه اولين عبارت در يك دسته نباشد. اولين عبارت SELECT عضو لنگر[9] ناميده مي‌شود و نبايد به خودش مراجعه كند. در اين مورد، ستون‌هاي EmployeeID، ManagerID و Title را از جدول AdventureWorks Employee بازيابي مي‌كند. دومين عبارت SELECT به CTE مراجعه كرده و عضو بازگشتي[10] ناميده مي‌شود. در اين مورد، عضو بازگشتي ستون‌هايي يكسان را بازيابي مي‌كند و به عضو لنگر در ستون ManagerID ملحق مي‌شود. مي‌توانيد نتايج اين CTE را در اين ليست ببينيد:

EmployeeID              ManagerID               Title

-------------- -------------                  --------------------------------------------------

3                             12                           Engineering Manager

4                             3                             Senior Tool Designer

9                             3                             Design Engineer

11                           3                             Design Engineer

158                         3                             Research and Development Manager

263                         3                             Senior Tool Designer

267                         3                             Senior Design Engineer

270                         3                             Design Engineer

5                             263                         Tool Designer

265                         263                         Tool Designer

79                           158                         Research and Development Engineer

114                         158                         Research and Development Engineer

217                         158                          Research and Development Manager

(13 row(s) affected)

PIVOT و UNPIVOT

اضافه شدن عملگرهاي رابطه‌اي PIVOT و UNPIVOT ويژگي جديد ديگري در
SQL Server 2005 T-SQL است. عملگرهاي PIVOT و UNPIVOT براي روش‌هاي OLAP كه در آن‌ها با داده جدولي و نه داده رابطه‌اي سروكار داريم، مفيد هستند. عملگر PIVOT مجموعه‌اي از رديف‌ها را به ستون‌ها تبديل مي‌كند. همان‌گونه كه ممكن است انتظار داشته باشيد، عملگر UNPIVOT معكوس عملگر PIVOT است و ستون‌هاي محوري را به رديف‌ها تبديل مي‌كند. هرچند، بسته به موقعيت، عمل UNPIVOT ممكن است دقيقاً معكوس عمل PIVOT نباشد. اين موقعيت به اين دليل رخ مي‌دهد كه عمل PIVOT چنين چيزي را تنظيم مي‌كند و مقادير خاصي را از قلم خواهد انداخت. اگر مقداري در طي عمل PIVOT از قلم افتاده باشد، بديهي است كه نمي‌تواند از حالت محوري خارج شود. بنابراين، عملگر UNPIVOT هميشه موجب يك تصوير قرينه دقيق از شرط محوري اصلي نمي‌شود.

مي‌توانيد در ليست زير ببينيد كه عملگر PIVOT چگونه كار مي‌كند. Select ساده‌اي را در فايلي به نام OrderSum در نظر داشته باشيد، مي‌توانيد مجموعه‌اي از سفارشات را براي يك CustomerID برابر 1 ببينيد كه هر سفارش داراي يك سال مرتبط است.

OrderId                    CustomerID              OrderYear

-----------    --------------                 -----------

100          1                             2000

101          1                             2000

102          1                             2000

103          1                             2001

104          1                             2001

105          1                             2002

106          1                             2003

107          1                             2004

(8 row(s) affected)

با استفاده از عملگر PIVOT جديد SQL Server 2005، مي‌توانيد اين مجموعه نتيجه را تبديل كنيد كه هر سال را به‌طور عمودي در مجموعه نتيجه‌اي كه سال‌ها را به‌طور افقي براي هر مشتري ليست كرده و تعداد سفارشات را براي هر سال جمع مي‌بندد، ليست كند. عمل PIVOT نمونه در اين ليست نشان داده شده است:

SELECT * FROM OrderSum

 PIVOT (COUNT(OrderID)

   FOR OrderYear IN([2000], [2001], [2002], [2003], [2004]))

   AS P

 WHERE CustomerID = 1

در اين‌جا، عمل PIVOT با عبارت SELECT براي ايجاد يك مجموعه نتيجه جديد استفاده مي‌شود. اولين مقدار عملگر PIVOT مقداري را تعيين مي‌كند كه در ستون محور قرار خواهد گرفت. در اين مثال، انبوهه COUNT(OrderID) تعداد سفارشات را براي هر مقدار محور جمع مي‌زند. كلمه كليدي FOR ستون را تعيين مي‌كند كه مقادير آن محوري خواهند شد. اين مثال عمل PIVOTاي را نشان مي‌دهد كه روي ستون OrderYear انجام مي‌شود. مقادير تعيين شده توسط ليست كلمه كليدي IN، مقاديري از ستون محوري شده هستند كه به عنوان سرستون‌ها استفاده خواهند شد. مي‌توانيد مجموعه نتيجه محوري شده را در اين ليست ببينيد:

CustomerID         2000        2001        2002        2003        2004

---------------        ----------- ----------- ----------- ----------- -----------

1                          3               2              1             1              1

Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)

تريگرهاي DDL

نگارش‌هاي قبلي SQL Server تنها به تريگرها امكان مي‌دادند تا با رويدادهاي دستكاري داده نظير درج يا بهنگام‌رساني يك رديف استفاده شوند. SQL Server 2005 اين مسأله را با مجاز ساختن تريگرها براي قرار گرفتن در رويدادهاي DDL از قبيل ايجاد و حذف اشياي پايگاه داده از قبيل جداول، ديدگاه‌ها، رويه‌ها و loginها توسعه داده است. تريگرهاي DDL مي‌توانند با عبارات CREATE، ALTER و DROP مرتبط شوند. اين امر به DBA امكان قرار دادن محدوديت‌هايي روي نوع عملياتي DDLاي را مي‌دهد كه مي‌توانند در يك پايگاه داده معين انجام شوند يا مي‌توانيد از اين تريگرها براي ارسال پيام‌هاي هشدار با توجه به تغييرات مهم طرح‌واره‌اي كه در پايگاه داده انجام شده است، استفاده كنيد. اين مثال نحوه افزودن يك تريگر DDL به نام NoTableUpdate را به عبارات DDL ، DROP Table و ALTER Table نشان مي‌دهد:

CREATE TRIGGER NoTableUpdate

ON DATABASE FOR DROP_TABLE, ALTER_TABLE

AS

PRINT 'DROP TABLE and ALTER TABLE statement are not allowed'

ROLLBACK

در اين‌جا، مي‌توانيد ببينيد كه چگونه تريگر DDL جديد مي‌تواند براي محدود كردن كاربرد عبارات DROP TABLE و ALTER TABLE استفاده مي‌شوند. اگر يك عبارت DROP TABLE و ALTER TABLE صادر شود، تريگر NoTableUpdate يك پيام خطا را چاپ خواهد كرد و عمل DDL مذكور roll back مي‌شود. يك تلاش براي صادر كردن يك عبارت ALTER TABLE در پايگاه داده حاوي تريگر NoTableUpdate در اين‌جا نشان داده شده است:

DROP TABLE and ALTER TABLE statement are not allowed

.Net SqlClient Data Provider: Msg 3609, Level 16, State 2, Line 1

Transaction ended in trigger. Batch has been aborted.

براي اعمال تغييرات جداول در يك پايگاه داده بعد از انجام اين تريگر، ابتدا بايد تريگر DDL را حذف كنيد.

خروجي DML

ويژگي T-SQL جديد ديگر در SQL Server 2005، توانايي توليد خروجي از عبارات T-SQL DML (INSERT، UPDATE و DELETE) است. بخش Output جديد، داده اصلاح شده را برمي‌گرداند. مثلاً، اين عبارت DELETE تمام رديف‌ها را از جدول OrderSum حذف مي‌كند:

DECLARE @MyOrderSumTVar TABLE(

    OrderID int,

    CustomerID int,

    OrderYear int);

DELETE FROM OrderSum

OUTPUT DELETED.* INTO @MyOrderSumTVar

SELECT * FROM @MyOrderSumTVar

توجه: جدول نمونه OrderSum در مثال قبلي اين فصل ايجاد شد.

در اين‌جا بخش OUTPUT DELETE.* مشخص مي‌كند كه تمام رديف‌هاي حذف شده، به خروجي خواهند رفت. در نگارش‌هاي قبلي SQL Server، تنها تعدادي رديف را مي‌ديديد كه توسط اين عبارت تحت تأثير قرار مي‌گرفتند. مي‌توانيد نتايج بخش T-SQL DML Output را در اين‌جا ببينيد:

(8 row(s) affected)

OrderID     CustomerID             OrderYear

-----------    -----------                    -----------

100          1                             2000

101          1                             2000

102          1                             2000

103          1                             2001

104          1                             2001

105          1                             2002

106          1                             2003

107          1                             2004

(8 row(s) affected)

WAITFOR

ويژگي T-SQL جديد ديگر در SQL Server 2005، پشتيباني بهبود يافته براي فرمان WAITFOR است. در نگارش‌هاي قبلي SQL Server، فرمان WAITFOR امكان انتظار براي تنها يك زمان از پيش تعريف شده را دارد. در SQL Server 2005، فرمان WAITFOR امكان انتظار براي نتايج يك عبارت RECEIVE را دارد. دليل اصلي پشت اين ويژگي، تسهيل پشتيباني برنامه‌نويسي T-SQL براي قابليت‌هاي صف‌بندي جديد فراهم شده توسط زيرسيستم SQL Service Broker است (مي‌توانيد مطالب بيشتر درباره اين مسأله را در فصل 6 مطالعه كنيد). ليست بعد نشان مي‌دهد كه فرمان WAITFOR جديد چگونه مي‌تواند در الحاق با يك عبارت RECEIVE استفاده شود:

WAITFOR (RECEIVE TOP (1) * FROM dbo.ServiceBrokerQueue)

نوع varchar (max) جديد

نوع داده varchar (max) جديد روش ديگري را براي نوع داده متني/ تصويري فراهم كرده است. نوع داده varchar (max) جديد، توسعه‌اي براي انواع داده varchar، nvarchar و varbinary است. شبيه انواع داده text، ntext و image، نوع داده varchar (max) تا 2GB داده را پشتيباني مي‌كند. هرچند، برخلاف انواع داده text، ntext، و image موجود، نوع داده varchar (max) مي‌تواند حاوي داده كاراكتري و باينري باشد. ضمناً، هيچ پشتيباني براي اشاره‌گرهاي متني ندارد.

مايكروسافت نوع داده varchar (max) جديد را براي كار كردن با انواع داده بزرگ معرفي كرد كه بيشتر شبيه كار كردن با داده رشته‌اي استاندارد است. تمام توابع رشته‌اي در انواع داده varchar (max) كار مي‌كنند و توابع SUBSTRING مي‌توانند براي خواندن مقادير زيادي داده استفاده شوند. علاوه بر اين، عبارت T-SQL UPDATE براي پشتيباني از بهنگام‌رساني قطعات داده در يك نوع داده varchar (max) بهبود يافته است. مي‌توانيد ستوني را با استفاده از نوع داده varchar (max) جديد ايجاد كنيد، به اين صورت:

CREATE TABLE NewBLOB

    DataID INT IDENTITY NOT NULL,

    BLOBData VARCHAR(MAX) NOT NULL

مديريت صرف‌نظر از تراكنش

پيشرفت مهم ديگر در T-SQL در SQL Server 2005، مديريت صرف‌نظر از تراكنش بهبود يافته است. در SQL Server 2005، يك مدل Try/Catch جديد به تراكنش اضافه شده است. اين ساختار Try/Catch جديد خطاهاي صرف‌نظر از تراكنش گير افتاده را بدون فقدان زمينه تراكنش ممكن مي‌سازد. در SQL Server 2000، هرچند مي‌توانستيد از يك تراكنش صرف‌نظر كنيد، ولي هيچ روشي براي حفظ زمينه تراكنش وجود نداشت، بنابراين مي‌توانيد تراكنش صرف‌نظر شده را به‌طور كامل بازيافت كنيد. مديريت صرف‌نظر تراكنش Try/Catch جديد SQL Server 2005 به شما امكان نگهداري زمينه كامل تراكنش صرف‌نظر شده را مي‌دهد و گزينه ايجاد مجدد تراكنش را به شما مي‌دهد. اين ليست كد ساختار T-SQL Try/Catch پايه را نشان مي‌دهد:

BEGIN TRY

    <SQL Statements>

END TRY

BEGIN CATCH TRAN_ABORT

    <SQL Statements>

END CATCH

تراكنش در بلوك Try قرار گرفته است. اگر RAISERROR در عبارت TRAN_ABORT در بلوك Try صادر شود، كنترل به بلوك Catch منتقل مي‌شود. در بلوك Catch، متغير @@error مي‌تواند براي تعيين شرط خطا ارزيابي شود.

زمان استفاده از اشياي پايگاه داده T-SQL

اشياي پايگاه داده T-SQL در SQL Server به تدريج كامل نشده‌اند. در واقع، T-SQL هنوز بهترين گزينه براي اشيايي است كه بايد عمليات دستيابي داده خام را انجام دهند. براي نمونه، اگر رويه‌هاي ذخيره شده‌اي داشته باشيد كه كار اصلي آن‌ها درج، بهنگام‌رساني يا حذف رديف‌هاي داده باشد، آن‌گاه اين اشيا بايد با استفاده از T-SQL توسعه يابند و نه يكي از زبان‌هاي .NET .

بهبودهاي ADO.NET

علاوه بر ويژگي‌هاي T-SQL و CLR جديد، SQL Server 2005 هم‌چنين تعدادي بهبود جامع در سمت كلاينت با دسته‌بندي كردن يك بهنگام‌رساني براي ADO.NET فراهم كرده است. هنگامي كه ADO.NET به پخته‌تر شدن ادامه مي‌دهد، بالاخره ويژگي‌هايي را از دست مي‌دهد كه در نسخه قبلي آن وجود داشته است، ADO مبتني بر COM، به همراه يك جفت ويژگي جديد. همان‌گونه كه خواهيد ديد، ويژگي‌هاي جديدي را بيان مي‌كنم كه مايكروسافت به ADO.NET اضافه كرده است، بيشتر آن‌ها برخي از قابليت‌هاي جديد را ارايه مي‌دهند كه به موتور پايگاه داده SQL Server اضافه شده‌اند. علاوه بر ويژگي‌هاي عمده جديد كه در بخش‌هاي بعد ليست شده‌اند، همان‌گونه كه انتظار مي‌رود، ADO.NET جديد هم‌چنين از انواع داده XML و T-SQL varchar (max) جديد پشتيباني مي‌كند.

پشتيباني از مكان‌نماي سرور با استفاده از SQLResultSet

يكي از مهم‌ترين ويژگي‌هاي جديد فراهم شده در ADO.NET جديد، پشتيباني از مكان‌نماهاي سمت سرور[11] است. اين يكي از نواحيي است كه ADO.NET، ويژگي‌هايي را در ADO مبتني بر COM نداشت. نگارش‌هاي قبلي ADO.NET تنها از مكان‌نماهاي سمت كلاينت پشتيباني مي‌كردند، از قبيل آني كه محيط كلاينت بايد در مورد كار نگهداري مجموعه نتيجه انجام دهد. در مكان‌نماهاي سمت سرور، مي‌توانيد آن‌ كار را به سرور منتقل كنيد. مايكروسافت اين ويژگي جديد را براي پشتيباني از SQL Server .NET Data Provider درون فرآيندي اضافه كرده است كه روي سرور اجرا مي‌شود. مايكروسافت اين ويژگي را براي الزامات سمت سرور درون فرآيندي اضافه كرده است، زيرا در سرور، نياز به اسكرول پويا از طريق نتايج مجموعه نتايج با عمر كوتاه است و كمي همي ارتباط متقابل كاربر مورد نياز است. مكان‌نماهاي سمت سرور حالت نگهداري را انجام مي‌دهند كه مقياس‌پذيري را كاهش داده و نياز به رفت و برگشت‌ها به سرور را افزايش مي‌دهند. هرچند، به دليل مسائل مقياس‌پذيري، مايكروسافت اين را تنها به فضاي نام System.Data.SQLServer سمت سرور اضافه كرده است. پشتيباني مكان‌نماي سمت سرور، بخشي از فضاي نام System.DataSQLClient سمت كلاينت نيست.

در ADO.NET 2.0، شئ SQLResultSet مكان‌نماهاي سمت سرور را براي برنامه شما ارايه مي‌دهد. اين مكان‌نماها هم قابل بهنگام شدن و هم قابل اسكرول به صورت پويا هستند. مكان‌نماهاي سمت سرور جديد توسط متد ExecuteResultSet جديد در شئ System.Data.SQLServer SQLCommand نمونه‌سازي مي‌شوند. اين مثال كاربرد شئ ADO.NET SQLResultSet را تشريح مي‌كند:

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlServer;

using System.Data.SqlTypes;

public partial class StoredProcedures

    [SqlProcedure]

    public static void GetProductName()

        SqlPipe myPipe = SqlContext.GetPipe();

        myPipe.Send("GetProductName: Opening server cursor");

        SqlCommand cmd = SqlContext.GetCommand();

        cmd.CommandType = CommandType.Text;

        cmd.CommandText = "SELECT Name FROM Production.Product WHERE MakeFlag = 1";

        SqlResultSet resultset = cmd.ExecuteResultSet

            (ResultSetOptions.Scrollable |

            ResultSetOptions.Updatable);

        if (resultset.HasRows == true)

            while (resultset.Read())

                myPipe.Send(resultset.GetString(0));

                // You could optionally update with

                //resultset.Update();

                // or scroll back using

                //resultset.ReadLast();

            resultset.Close();

        myPipe.Send("GetProductName: Server cursor closed");

};

در اين‌جا، مي‌توانيد رويه ذخيره شده CLR جديد به نام GetProductName را ببينيد. ابتدا، شئ SQLPipe براي ارسال يك پيام پيشرفت به كلاينت استفاده مي‌شود. سپس، شئ SQLCommand نمونه‌سازي مي‌شود و محتويات ستون Name را در جدول Production.Product پايگاه داده AdventureWorks نمونه بازيابي خواهد كرد. سپس، نمونه‌اي از شئ SQLResultSet ايجاد مي‌شود. بيشتر شبيه SQLDataReader، شئ SQLResultSet با استفاده از شئ SQLCommand نمونه‌سازي مي‌شود. در اين مورد، يك مكان‌نماي قابل بهنگام شدن و قابل اسكرول با استفاده از متد ExecuteResultSet باز مي‌شود. بعد از باز شدن مكان‌نما، برنامه شما مي‌تواند به سمت جلو و به سمت عقب اسكرول كند و بهنگام‌رساني‌ها را انجام دهد. با توجه به اين كه مكان‌نماهاي سمت سرور حالت را نگهداري مي‌كنند و منابع سرور را براي مادامي كه باز هستند، مصرف مي‌كنند، اطمينان از بسته شدن آن‌ها در صورت عدم نياز به آن‌ها، بسيار مهم است.

پشتيباني غيرهم‌زمان

ويژگي ديگري كه در ADO وجود داشت كه در نسخه‌هاي قبلي ADO.NET نبود، پشتيباني براي پرس‌وجوهاي غيرهم‌زمان[12] است. پرس‌وجوهاي غيرهم‌زمان برنامه‌هاي كلاينتي را فراهم مي‌كنند كه توانايي واگذاري پرس‌وجوها را بدون بلوكه كردن مداخله كاربر دارند. در رديف مياني برنامه‌ها، پشتيباني غيرهم‌زمان ADO.NET جديد، توانايي صدور چندين درخواست پايگاه داده را در رشته‌هاي مختلف بدون بلوكه كردن رشته‌ها براي برنامه‌هاي سرور فراهم مي‌كند. اين پشتيباني غيرهم‌زمان جديد هم‌چنين با نگارش‌هاي قبلي SQL Server كار مي‌كند. در SQL Server 2005، ADO.NET پشتيباني غيرهم‌زمان در .NET Framework است. عمليات غيرهم‌زمان با استفاده از متد BEGINxxx شئ شروع شده و با استفاده از متد Endxxx خاتمه مي‌يابد. شئ IAsyncResult براي بررسي وضعيت تكميل فرمان استفاده مي‌شود.

SqlConnection cn = new SqlConnection

 ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"

   + "DATABASE=AdventureWorks;async=True");

SqlCommand cmd = new SqlCommand("SELECT * FROM Production.Product", cn);

cmd.CommandType = CommandType.Text;

try

    cn.Open();

    IAsyncResult myResult = cmd.BeginExecuteReader();

    while (!myResult.IsCompleted)

        // Perform other code actions

    // Process the contents of the reader

    SqlDataReader rdr = cmd.EndExecuteReader(myResult);

    // Close the reader

    rdr.Close();

catch (Exception ex)

    MessageBox.Show(ex.Message);

finally

    cn.Close();

پشتيباني غيرهم‌زمان ADO.NET 2.0 در فضاهاي نام كلاينت نظير فضاي نام System.DataSQLClient پياده‌سازي مي‌شود. اولين نكته مهم در اين مثال رشته اتصال است. براي پياده‌سازي پشتيباني غيرهم‌زمان، رشته اتصال بايد حاوي كلمه كليدي ASYNC=true باشد. سپس، توجه داشته باشيد كه شئ IAsyncResult در بلوك Try است. متد BeginExecuteReader شئ SQLCommand براي شروع يك پرس‌وجوي غيرهم‌زمان استفاده مي‌شود كه تمام رديف‌ها را در جدول Production.Product برمي‌گرداند. كنترل بلافاصله بعد از اجراي عبارت به برنامه برگردانده مي‌شود. برنامه نيازي به انتظار براي اتمام پرس‌وجو ندارد. سپس، يك حلقه while براي بررسي وضعيت شئ IAsyncResult استفاده مي‌شود. هنگامي كه فرمان غيرهم‌زمان كامل مي‌شود، خصوصيت IsCompleted با true تنظيم مي‌شود. در اين لحظه، حلقه while تكميل شده و فرمان EndExecuteReader براي انتساب پرس‌وجوي غيرهم‌زمان به يك SQLDataReader براي پردازش استفاده مي‌شود.

MARS[13]

توانايي بهره بردن از ويژگي MARS جديد SQL Server 2005، بهبود ديگري در نگارش جديد ADO.NET است. در نگارش‌هاي قبلي ADO.NET و SQL Server، محدود به يك مجموعه نتيجه فعال در هر اتصال بوديد. و در حالي كه ADO مبتني بر COM و OLE DB داراي يك ويژگي بودند كه پردازش چند مجموعه نتيجه را به برنامه اجازه مي‌داد، اين ويژگي براي پردازش فرامين اضافي، نياز به اتصالات جديدي داشت. ويژگي MARS جديد در ADO.NET از قابليت SQL Server 2005 براي داشتن چندين فرمان فعال در يك اتصال بهره مي‌برد. در اين مدل، مي‌توانيد يك اتصال به پايگاه داده باز كنيد، سپس به اولين فرمان برگشته و نتايج ديگري را پردازش كنيد. مي‌توانيد با خيالي آسوده بين فرامين مختلف سوييچ كنيد. هيچ بلوكه كردني بين فرامين وجود ندارد و هر دو فرمان از يك اتصال به پايگاه داده استفاده مي‌كنند. اين ويژگي، كارآيي و مقياس‌پذيري بالايي را براي برنامه‌هاي ADO.NET 2.0 به همراه دارد. با توجه به اين كه اين ويژگي وابسته به پايگاه داده SQL Server 2005 است، تنها مي‌تواند با پايگاه‌هاي داده SQL Server 2005 به كار رود و با نگارش‌هاي قبلي SQL Server كار نمي‌كند. اين مثال كاربرد MARS را تشريح مي‌كند:

SqlConnection cn = new SqlConnection

  ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"

    + "DATABASE=AdventureWorks");

SqlCommand cmd1 =

  new SqlCommand("SELECT * FROM HumanResources.Department", cn);

cmd1.CommandType = CommandType.Text;

try

    cn.Open();

    SqlDataReader rdr = cmd1.ExecuteReader();

    while (rdr.Read())

        if (rdr["Name"].ToString() == "Production")

            SqlCommand cmd2 = new SqlCommand

              ("SELECT * FROM HumanResources.Employee "

                + "WHERE DepartmentID = 7", cn);

            cmd2.CommandType = CommandType.Text;

            SqlDataReader rdr2 = cmd2.ExecuteReader();

            while (rdr2.Read())

                // Process results

            rdr2.Close();

    rdr.Close();

catch (Exception ex)

    MessageBox.Show(ex.Message);

finally

    cn.Close();

در اين مثال، مي‌توانيد ببينيد كه cmd1 و cmd2 شئ SQLConnection يكساني به نام cn را به اشتراك گذاشته‌اند. شئ cmd1 براي باز كردن يك SQLDataReader استفاده مي‌شود كه تمام رديف‌هاي جدول HumanResources.Department را مي‌خواند. هنگامي كه Department اي به نام Production پيدا مي‌شود، شئ SQLCommand دوم به نام cmd2، براي خواندن محتويات جدول HumanResources.Employee استفاده مي‌شود. نكته مهم اين است كه SQLCommand به نام cmd2 قادر به اجرا با استفاده از شئ SQLConnection فعال است كه هم‌چنين به شئ cmd1 سرويس مي‌دهد.

صفحه‌بندي

پشتيباني يكپارچه براي صفحه‌بندي، ويژگي جديد جالب ديگري در SQL Server 2005 در مورد ADO.NET است. صفحه‌بندي هميشه مسأله مشكلي براي كار كردن در برنامه‌هاي كلاينت بوده است و ADO.NET جديد، پشتيباني بنيادي براي صفحه‌بندي را با دادن امكان انتخاب و انقياد به محدوده‌اي از رديف‌هاي يك مجموعه نتيجه به برنامه فراهم كرده است. براي مقياس‌پذيري، پياده‌سازي صفحه‌بندي جديد هيچ حالتي را در سرور نگهداري نمي‌كند. هرچند، اين نيز بدان معني است كه عضويت در مجموعه صفحه‌بندي براي تغيير بين اجراها امكان‌پذير است. اين بدان معني است كه ويژگي صفحه‌بندي جديد براي داده‌اي مناسب است كه نسبتاً ثابت باشد و اغلب تغيير نكند. پشتيباني صفحه‌بندي در ADO.NET جديد مبتني بر ترتيب است و براي استفاده از آن، بايد رديف شروعي را در مجموعه نتيجه و تعداد رديف‌ها را براي قرار گرفتن در صفحه مشخص كنيد. رديف‌هاي مجموعه صفحه با استفاده از DataReader استاندارد خوانده مي‌شوند. اين مثال از زيرروال PageProductsTable، استفاده از عملكرد صفحه‌بندي ADO.NET جديد را تشريح مي‌كند:

private SqlDataReader PageProductsTable(int nStartRow, int nPageSize)

     SqlConnection cn = new SqlConnection

       ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"

         + "DATABASE=AdventureWorks");

     SqlCommand cmd =

       new SqlCommand("SELECT * FROM Production.Product", cn);

     cmd.CommandType = CommandType.Text;

     cn.Open();

     return cmd.ExecutePageReader

       (CommandBehavior.Default, nStartRow, nPageSize);

در اين مثال، متد PageProductsTable دو عدد صحيح را كه مكان شروع و تعداد رديف‌هاي خوانده شونده صفحه‌بندي را تعريف مي‌كنند، به عنوان آرگومان مي‌گيرد. اين متد يك شئ ADO.NET SQLDataReader را برمي‌گرداند. در داخل اين روال، اشياي SQLConnection و SQLCommand به صورت طبيعي ايجاد مي‌شوند. بعد از باز شدن شئ SQLConnection، متد ExcutePageReader شئ SQLCommand براي بازيابي صفحه‌اي از نتايج فراخواني مي‌شود. اولين آرگومان متد ExcutePageReader، شمارشگر CommandBehaviorDefault است كه به شئ SQLCommand نحوه مديريت اتصال را هنگام خاتمه عمل مي‌گويد. دومين آرگومان ترتيبي است كه رديف شروع را مشخص مي‌كند. سومين آرگومان تعداد رديف‌هاي برگردانده شونده را مشخص مي‌كند. مي‌توانيد از قابليت‌هاي صفحه‌بندي ADO.NET 2.0 استفاده كنيد، همان‌گونه كه در اين مثال مي‌بينيد:

DataTable dt = new DataTable("Products");

dt.Load(PageProductsTable(10, 10));

dataGridView1.DataSource = dt;

در اين‌جا، يك شئ DataTable جديد ايجاد شده و سپس متد Load براي ارسال نتايج SQLDataReader به DataTable استفاده مي‌شود. سپس DataTable به يك شئ dataGridView مقيد مي‌شود.

درج يكجا

بهبود مهم ديگر در ADO.NET 2.0، شئ SQLBulkCopy جديد است. شئ SQLBulkCopy يك متد با كارآيي بالا براي انتقال اشيا بين پايگاه‌هاي داده مختلف با سيستم‌هاي SQL Server متفاوت فراهم مي‌كند. اين مثال نحوه استفاده از شئ SQLBulkCopy را تشريح مي‌كند:

// Create source & destination connections

SqlConnection cnSource = new SqlConnection

  ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"

    + "DATABASE=AdventureWorks");

SqlConnection cnDest = new SqlConnection

  ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"

    + "DATABASE=AdventureWorks2");

cnSource.Open();

cnDest.Open();

// Read the source data

SqlCommand cmd = new SqlCommand

  ("SELECT * FROM Sales.SpecialOffer", cnSource);

SqlDataReader rdr = cmd.ExecuteReader();

// Create SqlBulkCopy object and write the destination data

SqlBulkCopy bulkData = new SqlBulkCopy(cnDest);

bulkData.DestinationTableName = "SpecialOffers";

bulkData.WriteToServer(rdr);

bulkData.Close();

cnSource.Close();

cnDest.Close();

در اين مثال، دو شئ اتصال ايجاد مي‌شود كه به پايگاه‌هاي داده مختلفي در يك سيستم اشاره مي‌كنند. اولين شئ اتصال از پايگاه داده AdventureWorks استفاده مي‌كند و دومين شئ از يك كپي به نام AdventureWorks2 استفاده مي‌كند. هر دو شئ اتصال باز شده و سپس يك SQLDataReader براي خواندن داده از اتصال منبع استفاده مي‌شود. سپس يك شئ SQLBulkCopy ايجاد شده و به شئ اتصال مقصد متصل مي‌شود. سپس، متد WriteToServer شئ SQLBulkCopy با استفاده از SQLDataReader اي كه به اتصال منبع ضميمه شده است، فراخواني مي‌شود. متد WriteToServer داده را از منبع به مقصد كپي مي‌كند. توجه به اين نكته مهم است كه شئ مقصد بايد در اتصال مقصد وجود داشته باشد. متدهايي اضافي در شئ SQLBulkCopy وجود دارند كه مي‌توانيد از آن‌ها براي انجام نگاشت طرح‌واره اختصاصي بين جداول منبع و مقصد استفاده كنيد.

مدل اتصال مشترك

يكي از مشكلات ADO.NET 1.0 اين واقعيت بود كه از تأمين كننده آگاه نبود. به عبارت ديگر، بايد از تأمين كننده خاصي براي اتصال به يك محيط پايگاه داده مقصد خاص استفاده مي‌كرديد. مثلاً، SQLClient تنها مي‌توانست به سيستم‌هاي SQL Server متصل شود و نه به سيستم‌هاي Oracle. ضمناً، OracleClient تنها مي‌توانست به سيستم‌هاي Oracle متصل شود و نه به سيستم‌هاي SQL Server. در حالي كه مي‌توانستيد كد خود را براي بارگذاري تأمين كننده صحيحي بارگذاري كنيد، نتيجه عالي نبود و راحت نيز نبود. ADO.NET 2.0 اين مشكل را با افزودن يك قابليت Provider Factory جديد كه قادر به نمونه‌سازي تأمين كننده مناسب در زمان اجراست، حل كرده است. اين مثال نحوه استفاده از Provider Factory جديد را تشريح مي‌كند:

DbDataReader rdr;

DbProviderFactory provider =

    DbProviderFactories.GetFactory("System.Data.SqlClient");

using (DbConnection cn = provider.CreateConnection())

    using (DbCommand cmd = provider.CreateCommand())

        cmd.CommandText = "SELECT * FROM Production.Location";

        cmd.Connection = cn;

        cn.ConnectionString =

           ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"

              + "DATABASE=AdventureWorks");

        cn.Open();

        rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        DataTable dt = new DataTable("Product Locations");

        dt.Load(rdr);

        dataGridView1.DataSource = dt;

در اين‌جا، مي‌توانيد ببينيد كه متد GetFactory براي ايجاد نمونه‌اي از داده System.Data.SQLClient فراهم شده در زمان اجرا استفاده مي‌شود. سپس، يك شئ DbCommand براي اجراي فرماني براي بازيابي محتويات جدول Production.Location استفاده مي‌شود كه به DbDataReader ارسال مي‌شود. بالاخره، نتايج DbDataReader براي يك DataTable بارگذاري مي‌شود كه به شئ dataGridView مقيد شده است.

توجه: بايد فضاي نام System.Data.Common را براي استفاده از اشياي DbProvider داشته باشيد.