اگر فرض کنیم که متد GetStrings() تحت عنوان یک TVF با همان نام ثبت شده است، آنگاه مثلا زیر، قطعه ای از T-SQL از یک stored procedure است که از این TVF برای بیرون کشیدن (extract) line items ، در شکل جدولی استفاده می کند.

CREATE PROCEDURE Insert_Order @cust_id int, @lineitems nvarchar(8000)
AS
BEGIN
    ...
    INSERT LineItems
    SELECT * FROM dbo.GetStrings(@lineitems)
    ...
END

کدام را استفاده کنیم؟

تصمیم در مورد اینکه یک stored procedure را در ترکیب با SqlPipe- چه غیر مستقیم در T-SQL و چه مستقیم د ر یک CLR routine- یا یک table-valued function استفاده کنیم، بستگی به فاکتورهای زیادی دارد که باید مد نظر قرار گیرند، که شامل composability requirements، منبع داده ها، نیاز به اثرات جانبی، و typing requirements برای خروجی است. به ترتیب هر کدام از آنها را مورد بحث قرار می دهیم.

Composability Requirements

شاید استفاده دوباره یا تغییر (manipulation) خروجی بدست آمده درون یک TVF یا یک stored procedure، مطلوب باشد. Table-valued functions از نقطه نظر composability، پرکاربردتر هستند، مثلاً نوع بازگشت (return type) یک TVF، یک rowset نسبی است که در هر جاییکه چنین ساختاری مجاز است، قابل استفاده است. مخصوصاً، دربخش FROM در عبارت SELECT، قابل استفاده است، و چنین خروجیی می توانند از composability of SELECT در sub-queryها، عبارات INSERT...SELECT، جدولهای مشتق شده، و جدول عبارات استفاده کنند.

از سوی دیگر، ، می توان stored procedureها را فقط به عنوان بخشی از ترکیب INSERT...EXEC، از درون T-SQL language، compose کرد، که به خروجی بدست آمده اجازه ذخیره شدن در یک جدول دایم یا موقت را می دهد. عملیات INSERT، یک کپی واقعی از داده ها را ارائه می کند که احتمالاً روی performance تاثیر گذار خواهد بود.

اگر composability و استفاده مجدد از خروجی درون سرور یک نیاز باشند، TVFها جایگزینهای بهتری هستند. اگر خروجی بدست آمده فقط نیاز به stream شدن به client یا middle-tier داشته باشند، هر دو روش منطقی است.

منبع داده ها (Source of the Data)

منبع داده هایی که بازگردانده می شود، فاکتور مهم دیگری در انتخاب بین پیاده سازی بر اساس T-SQL یا CLR است. خروجی را میتوان هم از طریق خواندن source در local instance با استفاده از ADO.NET provider یا از طریق یک source خارج از SQL Server، بدست آورد. در منابع خارجی، پیاده سازی بر پایه CLR، انتخاب بهتری از T-SQL است، به علت داشتن سهولتی که logic accessing the external data، توسط آن انجام می شود.

در صورت بدست آوردن خروجی بر اساس یک query که با استفاده از ADO.NET provider روی local instance اجرا شده، یک stored procedure، معمولاً یک query را اجرا می کند، از طریق خروجی تکرار می شود، و بعضی از عملیات ها را روی rowها، قبل از بازگرداندن از طریق یک SqlPipe، انجام می دهد.

با یک TVF ، برنامه نویسان می توانند یک شی data reader را خوانده و در یک کلکسیون در حافظه بارگذاری کنند. اما، SQL Server 2005، به requestها اجازه نمی دهد تا زمانی که یک table-valued function باز می گردد، pending بمانند؛ هر query که از طریق ADO.NET provider اجرا شده است، باید کاملاً اجرا شوند و خروجی باید به طور کامل قبل از اینکه function body بتواند بازگردد، consume شوند. اگر عبارت بازگشت (return statement)، هنگامی که SqlDataReader operationها در ADO.NET provider، معلق (pending) است، اجرا شود، یک error ممکن است روی دهد. این بدین معناست که در اکثر مواردی که داده ها از local database instance بازگردانده می شوند، نمی توان داده ها را از طریق یک CLR TVF، stream کرد. اگر فاکتورهای دیگر از قبیل composability، مستلزم نوشته شدن به عنوان یک TVF باشد، شاید نوشتن آن در T-SQL تنها آپشن باشد. در غیر این صورت، استفاده از یک stored procedure مدیریت شده که از SqlPipe استفاده می کند، ممکن است.

در مواردی که در آنها خروجی قرار است از درون یک stored procedure بر اساس داده ها از local instance، بدست آید، استفاده از APIهای SendResults فقط در مواردی معنا دارد که خروجی نیاز به مقداری اصلاح یا تغییر رویه ای (procedural modification or manipulation) دارد.

عملیاتهایی (operation) با اثرات جانبی (Side-Effect)

بطور کلی، operationهایی که دارای side-effect هستند- operationهایی که حالت database، از قبیل عبارات DML، یا transaction operationها، را تغییر می دهند- از functionهایی (مثلاً functionهای table-valued) که تعریف کاربر است، منع می شوند؛ اما شاید این operationها مطلوب باشند. مثلاً ممکن است کسی بخواهد یک SAVEPOINT transaction را set کند، یا یک operation اجرا کند، و در صورت روی دادن error، آن را به SAVEPOINT، rollback کند.

با فرض اینکه functionهایی که کاربر تعریف می کند، منع می شوند، چنین سناریویی فقط از طریق یک stored procedure قابل پیاده سازی است، و خروجی باید از طریق SqlPipe بازگردانده شوند. اما به یاد داشته باشید، به operationهایی با side-effect، اجازه اجرا شدن از طریق ADO.NET provider، زمانیکه SqlPipe مشغول فرستادن خروجی است، داده نمی شود. این operationها فقط قبل از اینکه خروجی شروع یا بعد از اینکه تکمیل شوند، مجاز هستند.

Typing و تعداد خروجی

توصیف خروجی بدست آمده از طریق یک CLR stored procedure توسط SqlPipe، با خروجی بدست آمده از یک CLR TVF متفاوت، و با همتایانشان در T-SQL یکسان هستند.

از سوی دیگر، یک stored procedure declaration، هیچ عبارتی در مورد خروجی بدست آمده – یا حتی آیا آنها را بدست می آورد- نمی سازد. ممکن است آسان بنظر آید، و گرچه مسلماً انعطاف (flexibility) بیشتری ایجاد می کند، ولی باید در نوشتن applicationهایی که stored procedureها را اجرا می کنند دقت بیشتری کرد، زیرا ممکن است است به طور دینامیکی شکل خروجی را دوباره تعریف کند. اما، اگر لازم باشد که schema برای خروجی در میان فراخوانی ها ()invocation متغیر باشند، باید از یک stored procedure استفاده کرد، زیرا فقط SqlPipe این انعطاف را در اختیار می گذارد.

در حقیقت، نوع داده های ضعیف خروجی ها تولید شده از طریق sqlpipe درون Stored Procedureها، فراتر از Schemaی یک خروجی واحد است که احتمال بازگشت چندین خروجی متغیر را دربر می گیرد. هم typeها و هم تعداد خروجی را می توان توسط stored procedure به طور دینامیکی تعیین کرد.

خلاصه

جدول زیر خلاصه ای از راهنماییهایی در مورد چگونگی تعیین اینکه آیا یک application ویژه باید در T-SQL یا CLR نوشته شود، و یا اینکه آیا یک stored procedure یا یک table-valued function باید استفاده شود، است.

در اکثرجاهای این بخش، ارسال خروجی از طریق SqlPipe، به شدت با procedureها پیوند خورده است. گرچه SqlPipe و امکان بازگرداندن خروجی در بدنه CLR triggerها در دسترس هستند، اما این عمل به شدت نهی می شود، زیرا ممکن است به خروجی غیر منتظره ای برای issuing Data Manipulation Language یا عبارات Data Definition Language statements با triggerهایی که روی اشیاء هدف تعریف شده، منجر شود.

اجرای Aggregationهای سفارشی روی داده ها

سناریوهایی وجود دارند که ممکن است aggregation نیاز به اجرا شدن روی data داشته باشد، که شامل اجرای محاسبات آماری از قبیل یافتن میانگین ها، انحراف از معیار (standard deviation) و غیره است. اگر aggregation function مورد نظر، در داخل ساخته نشود، راههای زیادی برای اضافه کردن (add) functionality در SQL Server 2005 وجود دارد:

· نوشتن aggregation به عنوان یک aggregation تعریف شده توسط کاربر (UDA).

· نوشتن aggregation با استفاده از یک CLR stored procedure.

· استفاده از یک cursor جانبی سرور (server-side) در T-SQL.

بیایید این سه راه را در بافت (context) یک aggregation function ساده که محصول یک دسته از valueهای معین را محاسبه می کند، امتحان کنیم.

مثال: یک Product که به عنوان یک aggregate function تعریف شده توسط کاربر، پیاده سازی شده

در اینجا کدی برای این task آورده شده که به عنوان یک aggregate تعریف شده توسط کاربر نوشته شده. Logic محاسبه این محصول، وقتی که وارد می شود، در متد Accumulate() وجود دارد. متد Merge()، تعریف می کند اگر این دو aggregate ادغام شوند، چه اتفاقی می افتد.

در Visual Basic .NET:

Imports System
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

<Serializable()> _
<SqlUserDefinedAggregate(Format.Native)> _
Public Structure ProductAgg

    Private product As SqlInt32

    Public Sub Init()
        product = 1
    End Sub

    Public Sub Accumulate(ByVal value As SqlInt32)
        product = product * value
    End Sub

    Public Sub Merge(ByVal group As ProductAgg)
        product = product * group.product
    End Sub

    Public Function Terminate() As SqlInt32
        Return product
    End Function

End Structure

در C#:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct ProductAgg
{
    private SqlInt32 product;

    public void Init()
    {
        product = 1;
    }

    public void Accumulate(SqlInt32 value)
    {
        product = product * value;
    }

    public void Merge(ProductAgg group)
    {
        product = product * group.product;
    }

    public SqlInt32 Terminate()
    {
        return product;
    }
}

بعد از اینکه این type، با SQL Server ساخته و ثبت شد، می توان آن را فقط به عنوان یک built-in aggregate از T-SQL استفاده کرد:

SELECT dbo.ProductAgg(intcol) 
FROM tbl
GROUP BY col

مثال: Product، به عنوان یک Stored Procedure مدیریت شده

یک stored procedure را می توان ایجاد کرد که روی data تکرار می شود تا محاسبه (computation) را اجرا کند. این تکرار با استفاده از کلاس SqlDataReader بدست می آید، همانطور که در پایین نشان داده شده.

در Visual Basic .NET:

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Data.SqlClient

Partial Public Class StoredProcedures

    <SqlProcedure()> _
    Public Shared Sub VBProductProc(ByRef value As SqlInt32)

        ' The empty product is 1
        value = 1

        Using conn As New SqlConnection("context connection = true")
            conn.Open()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = conn
            cmd.CommandText = "SELECT intcolumn FROM tbl"
            Dim r As SqlDataReader = cmd.ExecuteReader()
            Using r
                Do While r.Read()
                    value = value * r.GetSqlInt32(0)
                Loop
            End Using
            conn.Close()
        End Using
    End Sub

End Class

در C#:

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Data.SqlClient;
public partial class StoredProcedures
{
    [SqlProcedure]
    public static void ProductProc(out SqlInt32 value)
    {
        // Ensure that we write to value.
        // Empty product is 1.
        value = 1;
        
        using (SqlConnection conn = 
            new SqlConnection("context connection = true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT intcolumn FROM tbl";
            SqlDataReader r = cmd.ExecuteReader();
            using (r)
            {
                while (r.Read()) //skip to the next row
                {
                    value *= r.GetSqlInt32(0);
                }
            }
        }
    }
}

می توان این را با استفاده از عبارت T-SQL EXEC فرا خواند:

EXEC Product @p OUTPU

مثال: Product به عنوان یک T-SQL Stored Procedure که از یک Cursor استفاده می کند

نهایتاً، می توان یک T-SQL stored procedure ایجاد کرد که یک query را اجرا و محاسبه را با استفاده از یک T-SQL cursor انجام می دهد تا data را تکرار کند.

T-SQL:

create procedure TSQL_ProductProc (@product int output)
as
begin
    declare @sales int
    declare c insensitive cursor for select intcolumn from tbl

    set @product = 1

    open c    
    fetch next from c into @sales
   
    while @@FETCH_STATUS = 0
    begin
        set @product = @product * @sales
        fetch next from c into @sales
    end
   
   close c
   deallocate c
end
go

خلاصه

تصمیم گیری در مورد استفاده از یک UDA یا یکی از راه حلهای دیگر برای بدست آوردن خروجی به چند عامل بستگی دارد، که شامل composability requirements، مشخصه های aggregation algorithm، و نیاز به side-effect می شود.

در واقع، یک UDA، یک شی مستقل است که از هر T-SQL query، قابل استفاده است، عموماً در همان مکانی که یک system aggregate استفاده می شود. هیچ تصوری در مورد یک query که روی آن عمل می کند وجود ندارد. مثلاً، می توان آن را در view definitionها (اما نه در indexed viewها) و در sub-queryهای ترازویی (scalar) به شمار آورد.

می توان UDAها را قبل از کلمه ORDER BY در یک query ارزیابی کرد، پس هیچ تضمینی برای ترتیبی که valueها به aggregation function ارائه می شوند وجود ندارد. بنابراین، اگر aggregation algorithm باید valueها را به ترتیب خاصی consume کند، نمی توان از یک UDA استفاده کرد. یک UDA نیز دقیقاً valueهای کل یک گروه را consume می کند و یک value واحد باز می گرداند. اگر مشکل حل نشد، آنگاه باید از یک تکنیک دیگر استفاده کرد.

همچنین یک UDA، هیچ data access را اجرا نمی کند و هیچ side-effect ندارد؛ اگر هر یک از اینها لازم باشند، آنگاه باید از یک stored procedure استفاده کرد.

گرچه UDAها دارای محدودیتهایی هستند، اما احتمال دارد بهترین performance را از آپشنهای ارائه شده انجام دهند، پس aggregation عموماً باید از طریق یک UDA اجرا شود، مگر اینکه الزامات (requirement) دیگر مانع آن شوند.

Typeهای تعریف شده توسط کاربر

حالا به یکی از قدرتمندترین ویژگیهای SQL Server 2005 می رسیم که اغلب مورد سوء تفاهم قرار می گیرد. با Typeهای تعریف شده توسط کاربر (UDTها)، می توان scalar type system را در database، گسترش داد. و این، فراتر از تنها تعریف کردن alias برای یک system type است که در نسخه قبلی SQL Server دردسترس بود. تعریف یک UDT، به سادگی نوشتن یک کلاس در کد مدیریت شده، ایجاد یک اسمبلی، و سپس (register) ثبت کردن type در SQL Server با استفاده از عبارت CREATE TYPE است. مثال زیر، اسکلت یک کد است که شکل ساختاری یک UDT را نشان می دهد.

در Visual Basic .NET:

<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> _
Public Structure SimpleType
    Implements INullable

    Public Overrides Function ToString() As String
        ...
    End Function

    Public ReadOnly Property IsNull() As Boolean Implements _
        INullable.IsNull
        ...
    End Property

    Public Shared ReadOnly Property Null() As SimpleType
        ...
    End Property

    Public Shared Function Parse(ByVal s As SqlString) As SimpleType
        ...
    End Function

End Structure

در C#:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct SimpleUdt : INullable
{
    public override string ToString() { ...  }
    public bool IsNull { ...  }
    public static SimpleUdt Null
    {
        get { ...  }
    }
    public static SimpleUdt Parse(SqlString s) { ...  }
}

می توان این را در T-SQL بارگذاری (load) و استفاده کرد:

CREATE TYPE simpleudt FROM [myassembly].[SimpleUdt]
CREATE TABLE t (mycolumn simpleudt)

کی یک UDT ایجاد کنیم؟

UDTها در SQL Server 2005، یک مکانیزم توسعه پذیر object-relational نیستند؛ بلکه راهی برای گسترش scalar type system در database هستند. scalar type system شامل typeهای ستونی (columnar) است که با SQL Server (int، nvarchar، uniqueidentifier، و غیره) منتقل و استفاده می شوند. با UDTها، می توان یک typeجدید تعریف کرد که میتوان به جای یک built-in scalar type استفاده کرد. اگر type، یک value اتمی است که برای مدل شدن به عنوان یک ستون مناسب است، یک UDT ایجاد کنید.

کاندیدهای خوب جهت پیاده سازی یک UDT، شامل تاریخ custom و typeهای time data در تقویم های مختلف و typeهای currency data است. یک UDT، یک کلاس واحد است که در معرض همه رفتارهای (behavior) دردسترس روی type است و داده های بنیادی ذخیره شده توسط type را خلاصه می کند: کل data access از programmatic interface در UDT استفاده می کنند. اغلب می توان از functionality موجود در .NET framework — از قبیل بین المللی کردن (internationalization) یا calendar functionality — استفاده کرد تا functionality را که به سختی فراهم می شود، فراهم کرد.

کی یک UDT ایجاد نکنیم؟

از یک UDT نباید برای مدل سازی اشیای business از قبیل کارمندان، قراردادها، یا مشتری ها استفاده کرد. SQL Server با یک UDT به عنوان یک واحد (unit) رفتار می کند که به آن opaque است. بعضی از مسائل با UDTهای پیچیده، شامل محدودیت 8 کیلو بایتی برای typeها، محدودیتهای indexing، و این واقعیت است که کل value باید هنگامی که هر value در UDT، update می شود، update شود.

فاکتورهایی که هنگام طراحی یک UDT باید در نظر گرفت

از آنجاییکه UDTها ستونی هستند، می توان index ها را، روی کل valueهای UDT تعریف کرد؛ همانطور که referential integrity constraint، از قبیل unique بودن، را می توان. همچنین می توان از UDTها در مقایسه و مرتب کردن سناریوها استفاده کرد.

مقایسه Valueهای UDT، بوسیله مقایسه binary representation of the type انجام می شود. اگر از Format.Native به عنوان مکانیزم پایداری (persistence mechanism) استفاده شود، آنگاه یک persisted form با استفاده از همان ترتیب field، به عنوان آنچه که در type تعریف می شود، ایجاد می شود؛ پس باید دقت کرد که آنها در جای صحیح قرار می گیرند.

غیر از مقایسه، هر عملیاتی روی یک UDT، نیازمند این است که UDT value، de-serialize شود و یک متد، فراخوانده شود. این الگو (pattern) موجب تحمیل بار اضافی روی server می شود، که باید هنگام تصمیم گیری در مورد اینکه آیا آن را به عنوان یک UDT، مدلسازی کنیم یا نکنیم، مد نظر قرار بگیرد. UDTها زمانی که یک type ،که نیاز به model شدن دارد، رفتارهای پیچیده ای دارد، مفیدتر هستند. اگرtype نسبتاً ساده باشد، آنگاه شاید بهتر باشد از ساختار UDT پرهیز کنیم.

نهایتاً، می توان از متدهای static یک UDT به عنوان یک مکانیزم بسته بندی (packaging) راحت جهت ذخیره سازی library تابعهای مربوط استفاده کرد. متدهای static را می توان از T-SQL با استفاده از syntaz زیر فراخوانی کرد:

select Type::Function(@arg1)

مثال: تاریخ های غیر غربی

می خواهیم valueهای زمان و تاریخ را با استفاده از تقویم "ام القرا" که با تقویم گریگوری که SQL Server datetime data type از آن استفاده می کند، ذخیره کنیم. می خواهیم این data type همان رفتارهای پایه ای را داشته باشد، بویژه string conversion، قابلیت بازیابی اجزای data، و محاسبات تاریخ و غیره.

مثال زیر از یک type تعریف شده توسط کاربر، یک پیاده سازی ساده از این data type است، و از UmAlQuraCalendar type استفاده می کند، که در نسخه 2.0 .NET Framework جدید است. متوجه شدن این مثال کمک زیادی به فراهم کردن متذهای لازم می کند.

UDT ام القرا در C#

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;

[Serializable]
[SqlUserDefinedType(Format.Native, IsByteOrdered = true)]
public struct UmAlQuraDateTime : INullable
{

    /*
     * Private state.
     */ 
    
    private long dtTicks;
    private bool isNull;

    // Calendar object used for all calendar-specific operations
    private static readonly UmAlQuraCalendar s_calendar = 
        new UmAlQuraCalendar();

    // For correct formatting we need to provie a culture code for 
    // a country that uses the Um Al Qura calendar: Saudi Arabia.
    private static readonly CultureInfo ci = 
        new CultureInfo("ar-SA", false);
    

    /*
     * Null-Handling
     */

    // get a null instance
    public static UmAlQuraDateTime Null
    {
        get
        {
            UmAlQuraDateTime dt = new UmAlQuraDateTime();
            dt.isNull = true;
            return dt;
        }
    }

    public bool IsNull
    {
        get
        {
            return this.isNull;
        }
    }

    /*
     * Constructors
     */

    public UmAlQuraDateTime(long ticks)
    {
        isNull = false;
        dtTicks = ticks;
    }

    public UmAlQuraDateTime(DateTime time) : this(time.Ticks)
    {
    }

    /*
     * Factory routines.
     */

    public static UmAlQuraDateTime Parse(SqlString s)
    {
        if (s.IsNull) return Null;
        DateTime t = DateTime.Parse(s.Value);
        return new UmAlQuraDateTime(t);
    }

    public static UmAlQuraDateTime ParseArabic(SqlString s)
    {
        if (s.IsNull) return Null;
        DateTime t = DateTime.Parse(s.Value, ci);
        return new UmAlQuraDateTime(t);
    }

    public static UmAlQuraDateTime FromSqlDateTime(SqlDateTime d)
    {
        if (d.IsNull) return Null;
        return new UmAlQuraDateTime(d.Value);
    }

    public static UmAlQuraDateTime Now
    {
        get
        {
            return new UmAlQuraDateTime(DateTime.Now);
        }
    }

    /*
     * Conversion Routines
     */

    public DateTime DateTime
    {
        get { return new DateTime(this.dtTicks); }
    }

    public SqlDateTime ToSqlDateTime()
    {
        return new SqlDateTime(this.DateTime);
    }

    public override String ToString()
    {
        return this.DateTime.ToString(ci);
    }

    public String ToStringUsingFormat(String format)
    {
        return this.DateTime.ToString(format, ci);
    }

    /*
     * Methods for getting date parts.
     */

    public int Year
    {
        get
        {
            return s_calendar.GetYear(this.DateTime);
        }
    }

    public int Month
    {
        get
        {
            return s_calendar.GetMonth(this.DateTime);
        }
    }

    public int Day
    {
        get
        {
            return s_calendar.GetDayOfMonth(this.DateTime);
        }
    }

    /*
     * Date arithmetic methods.
     */

    public UmAlQuraDateTime AddYears(int years)
    {
        return new 
            UmAlQuraDateTime(s_calendar.AddYears(this.DateTime, years));
    }

    public UmAlQuraDateTime AddDays(int days)
    { 
        return new 
            UmAlQuraDateTime(s_calendar.AddDays(this.DateTime, days));
    }   

    public double DiffDays(UmAlQuraDateTime other)
    {
        TimeSpan diff = DateTime.Subtract(other.DateTime);
        return diff.Days;
    }
}

وقتی این type در SQL Server بارگذاری می شود، می توان این type از طریق T-SQL استفاده کرد. در زیر چند مثال T-SQL آورده شده اند که از این UDT و خروجیی که بدست می آورند، استفاده می کنند.

ابتدا یک تاریخ ام القرا را parse و سپس آن را در دو فرمت با معادلهای غربی چاپ می کنیم:

DECLARE @d UmAlQuraDateTime
SET @d = UmAlQuraDateTime::ParseArabic('01/02/1400')
PRINT @d.ToString()
PRINT @d.ToStringUsingFormat('F')
PRINT @d.ToSqlDateTime()

نتیجه زیر حاصل می آید:

می توانیم تاریخ های غربی را نیز به ام القرا تبدیل کنیم:

DECLARE @n DateTime
SET @n = 'March 20, 2005'
DECLARE @d UmAlQuraDateTime
SET @d = UmAlQuraDateTime::FromSqlDateTime(@n)
PRINT @n
PRINT @d.ToString()

نتیجه:

نهایتاً، بااین type می توانیم جداولی را با ستون، ایجاد و اصلاح کنیم:

CREATE TABLE dates (
   western DateTime,
   umalqura UmAlQuraDateTime
)

INSERT INTO dates(western) VALUES ('June 1, 2005')
INSERT INTO dates(western) VALUES ('July 1, 2005')

UPDATE dates 
SET umalqura = UmAlQuraDateTime::FromSqlDateTime(dates.western)

SELECT western, umalqura.ToString() as umalqura FROM dates

این جدول، نتیجه نهایی است:

نتیجه گیری

این مقاله، راهنماییها، سناریوهای استفاده واقعی، و نمونه هایی را با استفاده از ویژگیهای CLR integration در SQL Server 2005، ارائه کرده است. برنامه نویسان و معماران (Database application developers and architects) باید از این مقاله جهت ترکیب با documentation در ویژگیهای دیگر SQL Server 2005، از قبیل Transact-SQL، XML، و Service Broker استفاده کنند.

حل کردن مشکلات رایج در Database Programming

بخش قبلی، برنامه نویسی بر پایه CLR را در سطحی بالاتر که راجع به T-SQL، extended stored procedures XPها(، و کدی در middle-tier است، قرار داد. در این بخش، ما نگاهی به دسته ای از taskها و الگوهایی (pattern) که یک database application developer ممکن است با آن مواجه شود، خواهم انداخت، و چگونگی استفاده (یا عدم استفاده) از CLR integration جهت حل کردنشان را مورد بحث قرار خواهیم داد. ما چندین code example در اختیار قرار می دهیم، هم در C# و هم در Visual Basic .NET.

اعتبار دهی داده ها با استفاده از NET Framework Library.

CLR integration در SQL Server 2005 به کاربران اجازه می دهد از عملکرد غنی (rich functionality) که توسط class libraryهای .NET Framework فراهم شده، برای حل مشکلات database programmingشان استفاده کنند.

یک مثال، استفاده از expressionهای مرتب است، جهت فراهم کردن الگوهای پیچیده تر نسبت به آنچه که با استفاده از اپراتور LIKE در T-SQL دردسترس است. کد زیر را که چیزی جز یک wrapper ساده اطراف کلاس RegEx در System.Text.RegularExpressions namespace نیست، ملاحظه فرمایید.

در Visual Basic .NET:

Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text.RegularExpressions

Partial Public Class Validation

    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
    Public Shared Function RegExMatch(ByVal pattern As String, _
        ByVal matchString As String) As Boolean

        Dim r1 As Regex = New Regex(pattern.TrimEnd(Nothing))
        Return r1.Match(matchString.TrimEnd(Nothing)).Success
    End Function

    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
    Public Shared Function ExtractAreaCode(ByVal matchString As String)_
        As SqlString

        Dim r1 As Regex = New Regex("\((?<ac>[1-9][0-9][0-9])\)")
        Dim m As Match = r1.Match(matchString)

        If m.Success Then
            Return m.Value.Substring(1, 3)
        Else
            Return SqlString.Null
        End If

    End Function
End Class

در C#:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
    
public partial class Validation
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static bool RegExMatch(string pattern, string matchString)
    {
        Regex r1 = new Regex(pattern.TrimEnd(null));
        return r1.Match(matchString.TrimEnd(null)).Success;
    }

    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString ExtractAreaCode(string matchString)
    {

        Regex r1 = new Regex("\\((?<ac>[1-9][0-9][0-9])\\)");
        Match m = r1.Match(matchString);
        if (m.Success)
            return m.Value.Substring(1, 3);
        else return SqlString.Null;
    }
}

بیایید فرض کنیم متدهای RegExMatch() و ExtractAreaCode()، به عنوان تابع های (function) تعریف شده توسط کاربر، در database با آپشن RETURNS NULL ON NULL INPUT ثبت شده اند، به طوری که function، هنگامی که هر یک از ورودی هایش (input) بی اثر هستند، بی اثر باز می گردد. این، به ما اجازه می دهد از نوشتن هر special null handling code درون function بپرهیزیم.

حالا می توانیم constraintهایی روی ستونهای جدولی که از کد بالا برای اعتبار دهی آدرسهایی ایمیل و شماره تلفنها استفاده می کند، به ترتیب زیر تعریف کنیم:

CREATE TABLE contacts 
(
    firstName nvarchar(30),
    lastName  nvarchar(30),
    emailAddress nvarchar(30) CHECK 
    (dbo.RegExMatch('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu)', 
        emailAddress) = 1),
    usPhoneNo nvarchar(30) CHECK 
    (dbo.RegExMatch(
        '\([1-9][0-9][0-9]\) [0-9][0-9][0-9]\-[0-9][0-9][0-9][0-9]', 
        usPhoneNo)=1),
    areaCode AS dbo.ExtractAreaCode(UsPhoneNo) PERSISTED 
)

به یاد داشته باشید که ستون areaCode، یک ستون دایم و محاسبه شده است، که area code را از ستون usPhoneNo با استفاده از تابع ExtractAreaCode() بیرون می کشد. ستون areaCode ممکن است index شود، و queryها را در جدولی که توسط area code دنبال مخاطبین می گردد، تسهیل می کند.

بطور کلی، این مثال نشان می دهد چگونه می توان از libraryهای .NET Framework برای افزایش T-SQL built-in function library با تابع های مفید که در T-SQL به سختی بیان می شوند، استفاده کرد.

بدست آوردن خروجی

نیاز به بدست آوردن خروجی از یک database object (از قبیل یک stored procedure یا یک view) که درون سرور اجرا می شود، یکی از رایج ترین taskهای database programming است. اگر بتوان خروجی را با استفاده از یک query واحد ایجاد کرد، آنگاه می توان این کار را تنها با استفاده از یک view یا یک inline table-valued function انجام داد. اما، اگر برای ایجاد خروجی، نیاز به عبارات چند گانه و procedural logic باشد، آنگاه دو آپشن وجود دارد: stored procedureها و table-valued functionها. در حالیکه SQL Server 2000 دارای table-valued functionها است، اما می توان آنها را فقط در T-SQL نوشت. با CLR integration در SQL Server 2005، می توان این functionها را با استفاده از یک زبان مدیریت شده (managed language) نیز نوشت. در این بخش، ما نگاهی به چگونگی نوشتن stored procedureها و table-valued functionها با استفاده از CLR می اندازیم.

برگرداندن خروجی نسبی (relational results) از T-SQL، چه به عنوان return value یک table-valued function، چه از طریق "caller's pipe" مستقیم و همیشه حاضر، درون یک stored procedure ممکن است: از هرجایی در یک stored procedure –بدون در نظر گرفتن nesting در execution level– یک عبارت SELECT اجرا شده، خروجی را به caller باز می گرداند.

اگر دقیق تر بگوییم، این درست است که عبارات SELECT، مقدار دهی به متغیررا اجرا نمی کنند. عبارات FETCH، READTEXT، PRINT، و RAISERROR، نیز خروجی را مستقیماً به caller بازمیگردانند.

به یاد داشته باشید که " caller" بدرستی تعریف نشده است؛ بستگی به فراخوانی بافت (invocation context) در stored procedure دارد.

اگر یک stored procedure از هر client data access API(ا زقبیل ODBC، OLEDB، یا SQLClient) فراخوانده شود، caller، API واقعی و هر abstraction (مثلاً، hstmt، IRowset، یا SqlDataReader) که برای ارائه خروجی مهیا می کند، است. بطور کلی، این بدین معناست که خروجی بدست آمده از درون یک stored procedure، به API فراخواننده بازگردانده خواهد شد، و همه frameهای T-SQL روی stack را کنار می گذارد، مانند مثال زیر:

 class="csharpcode">CREATE PROC proc1 AS
   SELECT col1 FROM dbo.table1;
CREATE PROC proc2 AS 
   EXEC proc1;

هنگام اجرای رویه proc2، خروجی بدست آمده از proc1، به caller of proc2 می روند. فقط یک راه هست که در آن proc2 می تواند خروجی بدست آمده را capture کند: از طریق stream کردن خروجی به disk، با استفاده از INSERT یا EXEC، به یک جدول دایم یا موقت، یا یک جدول متغیر.

CREATE PROC proc2 AS
   DECLARE @t TABLE(col1 INT);
   INSERT @t (col1) EXEC proc1;
   -- do something with results

Stored procedureهای SQL Server 2005 CLR، نوع جدیدی از caller را معرفی می کنند. وقتی یک query با استفاده از ADO.NET provider از درون یک frame مدیریت شده اجرا می شود، خروجی از طریق شی SqlDataReader قابل دسترسی می شوند و می توانند درون stored procedure مصرف شوند.

در Visual Basic .NET:

...
Using conn As New SqlConnection("context connection = true")
    conn.Open()
    Dim cmd As SqlCommand = new SqlCommand( _
        "SELECT col1 FROM dbo.table1", conn)
    Dim reader As SqlDataReader = cmd.ExecuteReader()
           
    Do While reader.Read()
        ' Do something with current row
    Loop       
End Using
...

در C#:

...
using (SqlConnection conn= new SqlConnection("contect connection = true"))
{
    ...
    SqlCommand cmd = new SqlCommand(
        "SELECT col1 FROM dbo.table1", conn);
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        // do something with current row
    }
    ...
}
...

مسئله ای که باقی می ماند، این است که یک managed routine چگونه خروجیش را به caller باز می گرداند. این کار در stored procedureها و table-valued functionها به طور متفاوتی انجام می شود. یک stored procedure از یک مثال static که از کلاس SqlContext قابل دسترسی است، جهت بازگرداندن data استفاده می کند، در حالیکه یک table-valued function، یک interface را پیاده سازی می کند که به SQL Server اجازه بازیابی خروجی را می دهد. هر دوی اینها در قسمت بعدی مورد بحث قرار می گیرند.

Stored Procedureهای CLR و SqlPipe

در میان متدهای دردسترس در کلاس SqlPipe، ساده ترینشان، ExecuteAndSend() است که یک command object را یک پارامترورودی در نظر می گیرد. این متد، command را اجرا می کند، اما به جای اینکه خروجی برای managed frame قابل دسترس کند، خروجی را به فراخواننده stored procedure ارسال می کند. این، معادل قرار دادن یک عبارت درون یک T-SQL stored procedure است، و در حالیکه ناخوشایندتر است، همراه با معادل T-SQL، روی par است.

یک stored procedure ساده برای اجرای یک SELECT در T-SQL:

CREATE PROC proc1 AS
   SELECT col1 FROM dbo.table1;

معادلش در C#:

...
[Microsoft.SqlServer.Server.SqlProcedure]
public static void proc1()
{
    using (SqlConnection conn =
        new SqlConnection("context connection = true"))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(
            "SELECT col1 FROM dbo.table1", conn);
        SqlContext.Pipe.ExecuteAndSend(cmd);
        conn.Close();
    }
}
...

و در Visual Basic .NET:

...
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub VBproc1()
    Using conn As New SqlConnection("context connection=true")
        conn.Open()
        Dim cmd As SqlCommand = new SqlCommand( _
            "SELECT col1 FROM dbo.table1", conn)
        SqlContext.Pipe.ExecuteAndSend(cmd)
        conn.Close()
    End Using
End Sub
...

SqlPipe.ExecuteAndSend()، در سناریوهایی که داده های در شرف بازگشت، مستقیماً توسط یک query اجرا شده بدست می آیند، خوب جواب می دهد. اما، ممکن است مواردی وجود داشته باشد که در آنها، تغییر (manipulation) داده ها قبل از ارسال آنها، یا ارسال داده هایی که از sourceهای خارج از local SQL Server instance بدست آمده، مطلوب باشد.

SqlPipe، گروهی از متدهایی را که با هم کار می کنند تا applicationها را قادر به باز گرداندن خروجی اختیاری به caller، (SendResultsStart()،SendResultsRow()، و SendResultsEnd()) کنند، در اختیار می گذارد. در اکثر موارد، این APIها، شبیه APIهای srv_describe و srv_sendrow هستند که برای extended stored procedureها دردسترس هستند.

SendResultsStart()، یک SqlDataRecord را یک پارامتر ارسالی (argument) در نظر می گیرد و شروع یک نتیجه جدید را نشان می دهد. این API، اطلاعات metadata را از record object می خواند و آن را به caller ارسال می کند. متعاقباً، می توان Rowها را با فراخواندن SendResultsRow()، یکبار برای هر row که در شرف ارسال است، باز گرداند. بعد از اینکه همه rowهای دلخواه ارسال شدند، فراخوانی SendResultsEnd() جهت نشان دادن پایان خروجی، لازم است.

مثال: بازگرداندن یک RSS Feed در یک CLR Stored Procedure

C# code fragment زیر، بخشی از یک stored procedure تشان می دهد که یک XML document را از Web می خواند- یک Really Simple Syndication (RSS) feed از MSDN- و کلاسهای System.Xml برای parse کردن آن استفاده می کند، و اطلاعات را به شکل نسبی باز می گرداند. به یاد داشته باشید که کد باید در یک EXTERNAL_ACCESS یا UNSAFE assembly نمایش داده شود، زیرا permissionهای Code Access Security (CAS) که برای دسترسی به اینترنت ضروری هستند، فقط در این دسته ازpermission ها قابل دسترس هستند.

...
using (SqlConnection conn = 
    new SqlConnection("context connection = true"))
{
    // Retrieve the RSS feed
    XPathDocument doc = new 
        PathDocument("http://msdn.microsoft.com/sql/rss.xml");
    XPathNavigator nav = doc.CreateNavigator();
    XPathNodeIterator i = nav.Select("//item");

    // create metadata for four columns
    // three of them are string types and one of the is a datetime
    SqlMetaData[] rss_results = new SqlMetaData[4];
    rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
    rss_results[1] = new SqlMetaData("Publication Date", 
        SqlDbType.DateTime);
    rss_results[2] = new SqlMetaData("Description", 
        SqlDbType.NVarChar, 2000);
    rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);

    // construct the record which holds metadata and data buffers
    SqlDataRecord record = new SqlDataRecord(rss_results);

    // cache a SqlPipe instance to avoid repeated calls to  
    // SqlContext.GetPipe()
    SqlPipe sqlpipe = SqlContext.Pipe;
 
    // send the metadata, do not send the values in the data record
    sqlpipe.SendResultsStart(record);

    // for each xml node returned, extract four pieces 
    // of information and send back each item as a row
    while (i.MoveNext())
    {

        record.SetString(0, (string)
                            
        i.Current.Evaluate("string(title[1]/text())"));
            record.SetDateTime(1, DateTime.Parse((string)
                                             
        i.Current.Evaluate("string(pubDate[1]/text())")));
            record.SetString(2, (string)
                                    
        i.Current.Evaluate("string(description[1]/text())"));
            record.SetString(3, (string)
                                     
        i.Current.Evaluate("string(link[1]/text())"));
        sqlpipe.SendResultsRow(record);
    }

    // signal end of results
    sqlpipe.SendResultsEnd();
}
...

به خاطر داشته باشید که بین callها به SendResultsStart() و SendResultsEnd()، عبارت SqlPipe در یک حالت busy تنظیم می شود، وفراخوانی هر متد Send، غیر از SendResultsRow()، منجر به یک error خواهد شد. SendingResults property، در حالیکه SqlPipe در حالت busy است، true تنظیم می شود.

Functionsهای Table-Valued

CLR integration، از functionهای table-valued (TVF) که در managed languageها نوشته شده اند، نیز ساپورت می کند. مانند معادل T-SQL، CLR TVFها نیز ابتداً برای بازگرداندن خروجی جدولی استفاده می شدند. واضح ترین تفاوت این است که T-SQL table-valued function، خروجی را موقتاً در یک جدول ذخیره می کنند، در حالیکه CLR TVF، قابلیت stream کردن خروجی بدست آمده را دارد، بدین معنی که این خروجی نباید قبل از بازگشت از function، materialize شوند.

مثال: یک Table-Valued Function برای بازیابی یک RSS Feed

در اینجا، ما RSS retrieval recast را به عنوان یک table-valued function در C# نشان می دهیم. به تطابق بین SqlFunction annotation در متد RSS_TVF() و امضاء (signature) متد FillTVFRow()، دقت کنید.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml.XPath;
using System.Collections;

public partial class UserDefinedFunctions
{
    [SqlFunction(FillRowMethodName = "FillTVFRow", 
        TableDefinition = "Title nvarchar(250), " +
            "PublicationDate datetime, " +
            "Description nvarchar(2000), " +
            "Link nvarchar(1000)")
    ]
    public static IEnumerable RSS_TVF()
    {
        return new RssReader();
    }

    public static void FillTVFRow(object row, out SqlString str, 
        out SqlDateTime date, out SqlString desc, out SqlString link)
    {
        // split each object array
        object[] rowarr = (object[])row;
        str = (SqlString)(rowarr[0]);
        date = (SqlDateTime)(rowarr[1]);
        desc = (SqlString)(rowarr[2]);
        link = (SqlString)(rowarr[3]);
    }
}

public class RssReader : IEnumerable
{
    XPathDocument doc;
    XPathNavigator nav;

    // Construct helper class, initializing metadata for the results
    // reading from the RSS feed, creating the iterator
    public RssReader(string site)
    {
        // Retrieve the RSS feed
        //doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml");
        doc = new XPathDocument(site);
        nav = doc.CreateNavigator();
    }

    public IEnumerator GetEnumerator()
    {
        return new RSSEnumerator(this);
    }

    private class RSSEnumerator : IEnumerator
    {
        XPathNodeIterator i;
        Object[] current;
        RssReader reader;

        public RSSEnumerator(RssReader reader)
        {
            this.reader = reader;
            Reset();
        }

        public void Reset()
        {
            i = reader.nav.Select("//item");
        }

        public bool MoveNext()
        {
            if (i.MoveNext())
            {
                current = new Object[4];
                current[0] = new SqlString((string)
                    i.Current.Evaluate("string(title[1]/text())"));
                current[1] = new SqlDateTime(DateTime.Parse((string)
                    i.Current.Evaluate("string(pubDate[1]/text())")));
                current[2] = new SqlString((string)
                    i.Current.Evaluate("string(description[1]/text())"));
                current[3] = new SqlString((string)
                i.Current.Evaluate("string(link[1]/text())"));
                return true;
            }
            else return false;

        }

        public Object Current
        {
            get
            {
                return current;
            }
        }
    }
}

یک query ساده برای consume کردن خروجی از این جدول، اینگونه خواهد بود:

SELECT *
FROM RSS_TVF() 

طبیعتاً، queryهای قویتر را می توان روی شکل TVF این داده، بیان کرد. با فرض اینکه ما یک تابع CanonicalURL() داریم که نسخه كانونيك یک URL را باز می گرداند، داده هایی از یک RSS feed را می توان با استفاده از URLهای کانونیک به آسانی بازگرداند:

select title, publicationDate, description, dbo.CanonicalURL(link) 
from dbo.RSS_TVF()
order by publicationDate

توجه داشته باشید که در مثال بالا، ما از قابلیتهای stream کردن TVF استفاده نمی کنیم، زیرا ما کل RSS feed را consume می کنیم، یک navigator در بالا درست می کنیم، و سپس آیتمهای مجزا را هنگامی که MoveNext() فراخوانده می شود، تکرار می کند. اما، consume کردن خروجی از یک Web source و تکرارXML تولید شده با یک XmlReader، با استفاده از streaming API ممکن است. قابل ذکر است که با فرض وجود تفاوت مدل اجرا بین table-valued functions در CLR و T-SQ، تفاوتی بزرگ ممکن است مشاهده شود، مخصوصاً در سناریوهایی که در آنها stream کردن خروجی ممکن است.

مثال: crack کردن Scalarها در Rowها

معمولاً در برنامه نیاز داریم که چندین پارامتر ورودی را به صورت همزمان ارسال کنیم. مثلاً، امکان دارد یک order processing system نیاز به یک stored procedure داشته باشد که یک order را در جدول orderها قرار می دهد. امکان دارد یک پارامتر ورودی مطلوب یک stored procedure، در order، line-items باشد؛ اما، این موضوع با این محدودیت روبرو می شود که T-SQL از پارامترهای چندگانه ساپورت نمی کند، و فاقد collectionها و arrayها است.

یک راه حل،encode کردن collection به عنوان یک scalar value است – مثلاً به عنوان یک nvarchar یا xml- و ارسال آن به عنوان یک پارامتر ورودی به stored procedure. Stored procedure می تواند از table-valued function که scalar input را می گیرد و آن را تبدیل به دسته ای از rowها می کند، استفاده می کند، و سپس می تواند در یک جدول line-items یا تغییر یافته (manipulated) قرار گیرد.

در حالیکه table-valued function را می توان در T-SQL نوشت، اما اگر در CLR نوشته شود، performance بهتری خواهد داشت. زیرا می تواند از string manipulation functionها در System.Text namespace استفاده بهینه ببرد، همچنین انجامش هم بسیار ساده است.

مثال زیر، پیاده سازی یک table-valued function را نشان می دهد که یک input string را که توسط یک نقطه ویرگول جدا شده را می گیرد و قطعه ها را به شکل دسته ای از rowها باز می گرداند.

در Visual Basic .NET:

Imports System.Collections
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

    ' This needs to return an IEnumerable, but since an array
    ' does, in this case we do not need to define a new class
    ' that implements it: we can simply return the array.
    <SqlFunction(FillRowMethodName:="FillRow", _
        TableDefinition:="value nvarchar(60)")> _
    Public Shared Function GetStrings(ByVal str As SqlString) _
        As IEnumerable

        Return str.Value.Split(";"c)
    End Function

    ' This method does the decoding of the row object. Since the
    ' row is already a string, this method is trivial. Note that
    ' this method is pointed to by the annotation on the
    ' GetString method.
    Public Shared Sub FillRow(ByVal row As Object, _
        ByRef str As String)

        str = CType(row, String)
    End Sub

End Class

در C#:

using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    /*
     * This needs to return an IEnumerable, but since an array
     * does, in this case we do not need to define a new class
     * that implements it: we can simply return the array.
     */
    [SqlFunction(FillRowMethodName="FillRow",
        TableDefinition="value nvarchar(60)")]
    public static IEnumerable GetStrings(SqlString str)
    {
        return str.Value.Split(';');
    }

    /*
     * This method does the decoding of the row object. Since the
     * row is already a string, this method is trivial. Note that
     * this method is pointed to by the annotation on the
     * GetString method.
     */
    public static void FillRow(object row, out string str)
    {
        str = (string)row;
    }
}

خلاصه: این مقاله ویژگیهای جدید CLR integration در SQL Server 2005، را توصیف می کند و توضیح می دهد که چگونه یک database application developer و architectها می توانند از آنها جهت نوشتن رویه ها(procedure)، functionها، و triggerهایی که توسط کاربر تعریف شده اند، استفاده کنند. و همچنین typeها و aggregateهای جدیدی را تعریف می کنند.

ما، برنامه نویسی بر پایه CLR را با مدلهای برنامه نویسی از قبیل Transact-SQL و extended stored procedures، را که در SQL Server ساپورت می شوند، مقایسه ، و نقاط قوت و ضعف هر تکنیک را برجسته می کنیم، و لیستی از رهنمودهای سطح بالا در مورد چگونگی انتخاب alternativeهای در دسترس برای برنامه نویسی را در اختیار می گذاریم. همچنین، گروهی از نمونه کدهایی را که خصوصیات CLR integration را نشان میدهند، نیز در اختیار می گذاریم.

مقدمه

Microsoft SQL Server 2005 به طور چشمگیری مدل database programming را با میزبانی از Microsoft .NET Framework 2.0 Common Language Runtime (CLR) بهبود می بخشد. این موضوع، برنامه نویسان را قادر به نوشتن رویه ها (procedure)، functionها، و triggerها در هر زبان CLR، مخصوصاً Microsoft Visual C# .NET, Microsoft Visual Basic و Microsoft Visual C++ می کند. همچنین به برنامه نویسان اجازه می دهد database را با typeها وaggregateهای جدید، گسترش دهند.

این مقاله چگونگی استفاده بهینه از این تکنولوژی را از نگاه یک database application developer توضیح می دهد، و تکنیکهای CLR integration را با programming language support موجود در SQL Server، Transact-SQL (T-SQL) و stored procedure های گسترش یافته، مقایسه می کند. این مقاله، مرجعی برای propertyها نیست، اطلاعات مربوط به مرجع را می توان در SQL Server 2005 Books Online پیدا کرد. مثالهای این مقاله، CTP آوریل سال 2005 از SQL Server 2005 را در نظر می گیرد.

مخاطبین این مقاله شامل database application developerها، architectها و administratorها می شود. این مقاله، شامل آشنایی موثر با .NET Framework-based programming و database programming می شود.

مرور CLR Integration

آنچه در ادامه می آید، مروریست مختصر بر عملکرد SQL Server که توسط CLR integration فعال شده، و اینکه Visual Studio 2005، چگونه این خصوصیات را ساپورت می کند.

نصب (deployment) دستی

ثبت و اجرای کدهای مدیریت شده در database شامل مراحل زیر می شود:

1. برنامه نویس، یک برنامه مدیریت شده را به عنوان مجموعه ای از تعاریف class می نویسد. Routinesهای SQL Server، stored procedureها، functionها، به عنوان متدهای static (یا shared در Microsoft Visual Basic .NET) یک کلاس نوشته می شوند. Type و aggregateهایی که کاربر تعریف کرده به عنوان کلاسهای کلی نوشته می شوند. برنامه نویس کد را compile و یک assembly ایجاد می کند.

2. Assembly به یک SQL Server database، یعنی جاییکه در کاتالوگهای سیستم، با استفاده از عبارات DDL، ذخیره می شود، آپلود می شود.

3. سپس Objectهای Transact-SQL (T-SQL) از قبیل routineها، typeها و aggregateها ایجاد می شوند و به entry pointهایی که قبلاً آپلود شده اند، محدود می شوند. این روند، با استفاده از عبارات CREATE PROCEDURE/FUNCTION/TRIGGER/TYPE/AGGREGATE به دست می آید.

4. بعد از اینکه routineها ایجاد می شوند، applicationها می توانند آنها را مثل routineهای T-SQL مورد استفاده قرار دهند. مثلاً، می توان CLR functionها را از queryهای T-SQL، و CLR procedureها را از یک client application یا از یک T-SQL batch فرا خواند، گویی procedureهای T-SQL هستند.

ساختن، نصب (deployment)، و debug کردن با استفاده از Visual Studio

Visual Studio 2005 از development، deployment و debug کردن کدهای مدیریت شده در SQL Server 2005 ساپورت می کند. یک SQL Server Project جدید، code templateهایی را که شروع نوشتن کدها برای routineها، typeها و aggregateهای CLR-based database را برای برنامه نویسان آسان می کند، در اختیار می گذارد. همچنین این پروژه به برنامه نویسان اجازه می دهد مرجعهایی (reference) را به assemblyهای دیگری در database اضافه کنند.

زمانیکه یک SQL Server Project ساخته می شود، به یک assembly، compile می شود. Deploy کردن پروژه، assembly binary را در SQL Server database که مربوط به پروژه است، آپلود میکند. همچنین عملیات deploy،routine ها، typeها و aggregateهایی را ایجاد می کند که در database assembly که برپایه attributeهای دلخواه (SqlProcedure, SqlFunction, SqlTrigger) موجود در کد است، تعریف می شوند. همچنین deploy، کدهای منبع و سمبولهای debugging (فایلهای .pdb) را که مربوط به اسمبلی هستد، آپلود می کند.

از آنجاییکه debugging، بخش مهمی از تجربه یک برنامه نویس برای هر platform است، SQL Server 2005 و Visual Studio 2005، database programmerهایی را با چنین قابلیتهایی در اختیار می گذارند. یک ویژگی کلیدی در debug کردن اشیاء در SQL Server 2005، سهولت نصب و استفاده است. اتصال به کامپیوترهایی که SQL Server را run می کنند، ممکن است مانند فرایندهایی که تحت یک سیستم عامل قدیمی اجرا می شوند، debug شوند. نوع اتصال به سروری که client، دارد، عملکرد debugger را تحت تاثیر قرار نمی دهد. هم Tabular Data Stream (TDS) و هم HTTP connectionها ممکن است debug شوند. به علاوه، debug کردن، در همه زبانها، کاملاً یکپارچه عمل می کند و به کاربر می دهد از T-SQL به متدهای CLR یا بالعکس، دست یابد.

CLR و Alternativeهایش

در ارزبابی استفاده از CLR integration، یک برنامه نویس باید آنرا با optionهای دردسترس دیگر مقایسه کند. هدف ما در اینجا، فراهم کردن پایه ای برای این مقایسه است، طوری که بتوان آنرا مقابل تکنیکهای برنامه نویسی کنونی از قبیل: Transact-SQL, extended stored procedures و code in the middle-tier، قرارداد. در این قسمت، ما روی routineهایی که کابر تعریف می کند، تمرکز خواهیم کرد .

CLR در برابر Transact-SQL

Transact-SQL (T-SQL) یک زبان برنامه نویسی ذاتی (native) است که توسط SQL Server ساپورت می شود. مثل اکثر نسخه های SQL، شامل ویژگیهای تغییر داده ها (data manipulation features) و ویژگیهای تعریف داده ها (data definition features) می شود. data manipulation features را می توان به دو گروه کلی تقسیم بندی کرد:

یک declarative query language (تشکیل شده از عبارات SELECT/INSERT/UPDATE/DELETE) و یک procedural language (WHILE, assignment, triggers, cursors و غیره). اگر بخواهیم کلی بگوییم، CLR support در SQL Server، جایزگزینی را برای بخش روندی T-SQL (procedural portion) در اختیار می گذارد.

حتی بدون CLR support نیز، مهم است تشخیص دهیم که database applicationها باید تا آنجا که ممکن است از declarative query language استفاده کنند. این قسمت از زبان، قادر به استفاده از قدرت query processor است که می تواند به نحو احسن، operation bulkها را اجرا و بهینه کند. Database applicationها باید فقط از procedural programming برای بیان منطق (logic) که در query language قابل بیان (express) نیست، استفاده کند.

تمامی اینها، با CLR support در SQL Server، نیز صدق می کند: از CLR نباید برای نوشتن procedural code که با استفاده از ویژگیهای زبان T-SQL، قابل نوشتن هستند، استفاده کرد. برنامه نویسان باید آگاه باشند که تعدادی enhancementهای مهم T-SQL query language در SQL Server 2005 وجود دارند که قدرت T-SQL query language را افزایش می دهند، و باید مطمئن شوند که از همه مزایای آنها قبل از نوشتن procedural code بهره می برند، چه در CLR و چه در جای دیگر. بعضی از این ویژگیهای اضافه شده عبارتند از:

· قابلیت نوشتن Queryهای بازگشتی جهت traverse کردن سلسله مراتب (hierarchy) بازگشتی در یک جدول.

· Functionهای تحلیلی (analytical) جدید از قبیل RANK و ROW_NUMBER که اجازه درجه بندی ردیفها (RANKING ROW) را در لیست خروجی می دهد.

· اپراتورهای نسبی (relational) جدید از قبیل EXCEPT, INTERSECT, APPLY, PIVOT و UNPIVOT.

برنامه نویسان باید به CLR به عنوان جایگزینی کارآمد برای منطق (logic) که در query language قابل بیان نیست، نگاه کنند.

بیایید نگاهی به به برخی از سناریوهایی که در آنها برنامه نویسی بر پایه CLR میتواند قدرت T-SQL query language را تکمیل کند بیاندازیم. اغلب، ما نیاز به قراردادن procedural logic درون یک Query که میتوان function نامید، داریم. این، شامل موقعیتهای زیر می شود:

· اجرای محاسبات پیچیده (که باید با استفاده از procedural logic بیان شوند) روی valueهای ذخیره شده در جداول database. این کار مستلزم ارسال خروجی این محاسبات به client، یا استفاده از آنها برای فیلتر کردن دسته ای از ردیفها (row) که به client ارسال می شوند، است؛ مانند مثال زیر:

•    SELECT <complex-calculation>(<column-name>,...)
•    FROM <table>
•    WHERE <complex-calculation>(<column-name>,...) = ... 

· استفاده از procedural logic جهت ارزیابی خروجیی که در قسمت FROM در یک عبارت SELECT یا DML، query می شوند. (DML مخفف Data Manipulation Languaga، به معنای زبان تغییر داده ها به دستورات INSERT، UPDATE، و DELETE است)

SQL Server 2000، (Function) تابع های T-SQL را معرفی کرد که این سناریو هارا فعال می کند. با SQL Server 2005، این تابع ها، با استفاده از زبانهای CLR، راحتتر نوشته می شوند. زیرا برنامه نویسان می توانند از libraryهای بسیار گسترده در .NET Framework API، بهترین استفاده را ببرند. به علاوه، زبانهای برنامه نویسی CLR، ساختارهای قوی data از قبیل (arrayها، listها، و غیره) رادر اختیار می گذارد که در T-SQL وجود ندارد، و می توانند به علت داشتن مدلهای متفاوت execution در CLR و T-SQL، بهتر اجرا شوند.

بطور کلی، تابعها کاندیدهای خوبی برای نوشته شدن، با استفاده از CLR هستند؛ زیرا نیاز به دسترسی به database از درون یک تابع به ندرت پیش می آید؛ valueهای database، معمولاً تحت عنوان پارامتر ارسالی (argument) ارسال می شوند که در taskهای محاسبه ای، بهتر از T-SQL هستند.

Data Access از CLR

حالا نگاهی به CLR به عنوان یک option برای نوشتن routineهایی که data access اجرا میکند، می اندازیم، هم از منظر مدل برنامه نویسی (programming model) وهم ازمنظر اجرا (performance).

در T-SQL، عبارات query language از قبیل SELECT، INSERT، UPDATE و DELETE، فقط در procedural code قرا می گیرند. ار سوی دیگر، کد مدیریت شده، ازADO.NET data access provider برای SQL Server (SqlClient) استفاده می کنند. در این روش، همه عبارات query language توسط stringهای دینامیک که به متدها و خصوصیات (propert) در ADO.NET API تحت عنوان پارامتر ارسالی (argument) ارسال می شوند.

به خاطر این تفاوت، data access code نوشته شده که از CLR استقاده می کند، می تواند از T-SQL طولانی تر باشد. مهمتر اینکه، از آنجاییکه عبارات SQL در stringهای دینامیک رمزگذاری (encode) می شوند، تا وقتی که اجرا شوند، compile و اعتباردهی (validate) نمی شوند ؛ که هم روی debug کردن کد و هم روی اجرایش (performance) تاثیر می گذارد. اما، مدل برنامه نویسیdatabase با ADO.NET بسیار شبیه مدلی است که در client یا middle-tierها استفاده شده، که هم move کردن کد بین tierها و هم استفاده از مهارتهای موجود را آسانتر می کند.

یه یاد داشته باشید که مدلهای برنامه نویسی بر پایه T-SQL- و هم بر پایه CLR، از یک SQL query language استفاده می کنند؛ تنها procedural portionها فرق دارند.

همانطور که قبلاً هم ذکر شد، کد مدیریت شده، با در نظر گرفتن اکثر محاسباث رویه ای (procedural)، از مزیت "اجرا شدن حتمی" در مقایسه با T-SQL بهره می برد؛ اما برای data-access،T-SQL معمولاً بهتر عمل می کند.

بنابراین، یک قانون رایج خوب این است که "کد حساس به محاسبه و منطق" (computation- and logic-intensive)، انتخاب بهتری برای پیاده سازی در CLR نسبت به کد حساس به data-access است.

بیایید نگاهی به بعضی از الگوهای معمولی در data-access programming بیاندازیم، که توضیح می دهد T-SQL و برنامه نویسی مدیریت شده (managed programming)، که از integrated CLR و ADO.NET استفاده می کند، چگونه در این سناریوها اجرا می شوند.

ارسال خروجی به Client

سناریوی اول ما، مستلزم ارسال دسته ای از ردیفها (row) به Client بدون consume کردن آنها در سرور است. مثلاً، هیچ ردیفی خارج از routine، navigate نمی شود. با T-SQL، وارد کردن یک عبارت SELECT در T-SQL procedure، به اندازه ارسال ردیفهای ایجاد شده توسط SELECT به cleint، موثر است. با کدهای مدیریت شده، شی SqlPipe، برای ارسال خروجی به client استفاده می شود. T-SQL و ADO.NET این سناریو را تقریباً به یک صورت اجرا می کنند.

Submit کردن عبارات SQL

Submit کردن عبارات SQLاز CLR مستلزم traverse کردن لایه های اضافی کد، به منظور switch بین کد مدیریت شده و کد SQL، است. به همین دلیل، T-SQL دارای مزیت performance، هنگام صدور (issue) یک SQL query است. توجه داشته باشید که بعد از submit شدن این عبارت در query processor، فرقی در performance که بر پایه منبع عبارت است، وجود ندارد؛ (چه در T-SQL، چه در کد مدیریت شده). اگر query پیچیده باشد و ارزیابی اش (evaluate) طول بکشد، آنگاه تفاوتها در performance بین T-SQL و کد مدیریت شده، کم اهمیت خواهد بود. به طور خلاصه، queryهای ساده، overhead لایه های اضافی کد می تواند performance یک procedure مدیریت شده را تحت تاثیر قرار دهد.

احتمال دارد stored procedureهای معمولی و حساس به data-access، درگیر submit کردن یک سری (sequence) از عبارات SQL شود. اگر عبارات SQL ساده باشند و زیاد طول نکشند تا اجرا شوند، آنگاه فراخواندن overhead از کد مدیریت شده می تواند بر زمان اجرا تسلط پیدا کند؛ چنین procedureهایی نوشتن در T-SQL را بهتر اجرا می کنند.

Forward-Only, Read-Only Row Navigation (خواندن رکوردها، فقط رو به جلو و فقط خواندنی)

در T-SQL، forward-only, read-only navigation با استفاده از یک cursor پیاده می شود. در کد CLR، این کار با یک SqlDataReader پیاده می شود. معمولاً، مقداری پردازش برای هر قطعه از DATA انجام می شود. اگر این موضوع را نادیده بگیریم، T-SQL یک مزیت دارد، زیرا Row Navigation که از CLR استفاده می کند، کمی آهسته تر از T-SQL است. اما، از آنجاییکه CLR در هر پردازشی که روی data انجام می شود، به طور برجسته ای اجرای (performance) بهتری نسبت به T-SQL دارد، CLR performance، هنگامی که مقدار پردازش افزایش می یابد، جای اجرای T-SQL را می گیرد.

به علاوه، شخص هنگام استفاده از cursorهای T-SQL، باید از پتانسیل امکانات اضافی (latency) آگاه باشد. گرچه بعضی از queryها، الزاماً مقداری latency نشان می دهند، زیرا باید خروجی میانی (intermediate) را ظاهر کنند، cursorهای STATIC و KEYSET همیشه خروجی نهایی را در یک جدول موقت قبل از بدست آوردن هر نتیجه دیگری، ظاهر می کنند. یک cursor می تواند یا مستقیماً با STATIC با KEYSET، تعریف (declare) شود و یا غیر مستقیماً ، به علت برخی ویژگیهای query و data، به یک تبدیل شود. CLR SqlDataReader همیشه خروجی را هنگامی که در دسترس هستند و با پرهیز از این latency تولید می کند.

Row-Navigation با updateها

اگر این مشکل مستلزم update کردن ردیفها (row) برپایه موقعیت کنونی cursor باشد، آنگاه هیچ مقایسه performance مرتبطی وجود نخواهد داشت. این عملکرد در ADO.NET ساپورت نمی شود و باید از طریق cursorهای قابل update در T-SQL انجام شود. اما به خاطر داشته باشید که معمولاً استفاده از عبارات UPDATE برای update کردن ردیفهای خالی بهتر است، و modificationهایی را که بر پایه cursor هستند، برای زمانیکه نمی توان با declarative SQL بیان کرد، ذخیره می کند.

خلاصه

در زیر، خلاصه ای از راهنماییهایی که دیده ایم می توانند در انتخاب CLR یا T-SQL استفاده شوند، آورده شده است:

· هر گاه ممکن است، از عبارات SELECT، INSERT، UPDATE، و DELETE استفاده کنید. پردازش رویه ای (procedural) و پردازش بر پایه row باید فقط هنگامی که logic با استفاده از declarative language قابل بیان نیست، استفاده شود.

· اگر رویه (procedure) ،فقط یک wrapper برای فرمانهای declarative T-SQL است، باید در T-SQL نوشته شود.

· اگر رویه (procedure)، ابتداً مستلزم forward-only, read-only row navigation از طریق یک دسته از خروجی با همان پردازش هر row باشد، احتمالاً استفاده از CLR، بازده بیشتری دارد.

· اگر رویه (procedure)، هم مستلزم data access و هم محاسبه باشد، تقسیم کردن کد رویه ای (procedural code) را در CLR portion که یک T-SQL procedure را جهت اجرای data access یا یک T-SQL procedure را که CLR را جهت اجرای محاسبه فرا می خواند، در نظر بگیرید. راه حل دیگر، استفاده از یک T-SQL batch واحد است که شامل یک سری query است که یک بار از managed code جهت کاهش تعداد round tripهای submit کردن عبارات T-SQL اجرا شده است.

قسمت های بعدی بیشتر به بحث درباره زمان و چگونگی استفاده صحیح از T-SQL و CLR هنگام کار با خروجی می پردازد.

CLR در مقابل XPها

در نسخه های قبلی SQL Server، extended stored procedureها (XPها) تنها جایگزین T-SQL بودند که نوشتن کد سمت سرور (server-side code) به آن سبک بسیار سخت بود. CLR integration، یک جایگزین قویتر برای XPها در اختیار می گذارد. به علاوه، با CLR integration، خیلی از stored procedureها بهتر بیان می شوند و به آنها، با استفاده از query language، اجازه فراخوانده شدن و تغییر (manipulation) می دهد.

بعضی از مزیتهای استفاده از CLR procedureها نسبت به XPها عبارتند از:

· کنترل دانه ای (Granular control): administratorهای SQL Server کنترل کمی روی کارهایی که XPها می تواند یا نمی تواند انجام دهند، دارند. با استفاده از مدل Code Access Security، یک SQL Server administrator می تواند یکی از سه permission bucketهای SAFE، EXTERNAL_ACCESS، یا UNSAFE، را جهت بکار گیری درجات مختلف کنترل عملیاتهایی که کد مدیریت شده اجازه دارد اجرا کند، تعیین کند.

· قابلیت اعتماد (Reliability): کد مدیریت شده، مخصوصاً در permissionهای SAFE و EXTERNAL_ACCESS، مدل برنامه نویسی قابل اعتماد تری را نسبت به XPها در اختیار می گذارد. کدهای مدیریت شدهِ قابل تایید، تضمین می کنند که دسترسی به اشیا از طریق interfaceهایی که احتمال اینکه برنامه به memory bufferهایی که متعلق به SQL Server است، دسترسی پیدا کند یا آنها را مختل کند، کاهش می دهد.

· Data access: با XPها، باید اتصالی مستقیم (loop-back connection) به database ، جهت دسترسی به SQL Server database محلی (local) ایجاد شود. به علاوه، این loop-back connection باید مستقیماً به transaction context های جلسه اصلی (original session) محدود شود تا تضمین کند XP در transaction که در آن فراخوانده می شود، شرکت دارد. کد مدیریت شده CLR می تواند با استفاده از یک مدل برنامه نویسی طبیعی و کارآمدارتر که از connection و transaction context حاضر بیشترین بهره را ببرد، به داده های محلی (local data) دسترسی پیدا کند.

· Data Typeهای اضافی: APIهای مدیریت شده از data typeهای جدید (از قبیل XML، (n)varchar(max)، و varbinary(max)) که در SQL Server 2005 معرفی شدند، ساپورت می کند؛ در حالیکه ODS APIها جهت ساپورت از این typeهای جدید، گسترش نیافته اند.

· قابلیت صعود پذیری (Scalability): APIهایی که در معرض منابعی (resource) از قبیل حافظه، threadها، و همسان سازی (synchronization) هستند، در بالای SQL Server resource manager پیاده می شوند، و به SQL Server اجازه مدیریت این منابع برای کد CLR را می دهد. در مقابل، SQL Server هیچ دید یا کنترلی روی منبع استفاده از یک XP ندارد. اگر یک XP، زمان و حافظه زیادی از CPU را بگیرد، راهی برای شناسایی یا کنترل آن از درون SQL Server وجود ندارد. با کد CLR، SQL Server می تواند پی ببرد که یک thread تعیین شده، به مدت طولانی بازگشتی نداشته و task را مجبور به بازگشت می کند، طوریکه بتوان کار دیگری را برنامه ریزی کرد. در نتیجه، استفاده از کد مدیریت شده، قابلیت صعود (scalability) و قوت (robustness) بهتری را در اختیار می گذارد.

همانطور که در بالا اشاره شد، برای data access و ارسال خروجی به client CLR، routineها بهتر از XPها عمل می کنند. برای کدی که مستلزم data access یا ارسال خروجی نیست، مقایسه اجرای XPها (performance) و کد مدیریت شده، مانند مقایسه کد مدیریت شده با native code است. معمولاً، کد کنترل شده نمی تواند بر اجرای native code در این سناریوها فایق آید. علاوه بر این، یک cost اضافی نیز در زمان انتقال از کد مدیریت شده به کد native، هنگام run شدن درون SQL Server وجود دارد، زیرا SQL Server نیاز به انجام book-keeping روی تنظیمات مخصوص thread هنگام انتقال به native code و بالعکس دارد. در نتیجه، XPها می توانند اجرای (running) کد مدیریت شده را درون SQL Server برای مواردی که انتقالهای (transition) مکرر بین کد مدیریت شده و کد native وجود دارد، بهتر انجام دهد.

در اکثر procedureها، مزیتهای کد مدیریت شده می تواند procedureهای CLR را جایگزین جذابتری نسبت به XPها کند. در مواردی که performance، ابتداً توسط پردازش حساس به محاسبه (computationally-intensive processing) و انتقالات مکرر بین کد مدیریت شده و کد native تعیین شده، مزایای CLR باید در مقابل مزایای اجرای خام XPها (raw performance) سنجیده شود.

کد در Middle Tier

گزینه دیگر برنامه نویسان، قرار دادن logicشان بیرون از database است. این کار به آنها اجازه نوشتن کدشان را در زبانهای انتخابیشان می دهد. با در اختیار گذاشتن یک مدل برنامه نویسی غنی در database، CLR integration به برنامه نویسان امکان منتقل کردن چنین logic را به database می دهد. البته، این بدین معنا نیست که همه یا اکثر کدها باید به database منتقل شوند.

انتقال logic به database tier می تواند مقدار داده ای را که روی network جاری است کاهش دهد، اما باری اضافی روی resourceهای باارزش سی پی یوی سرور می گذارد. این موضوع باید قبل از تصمیم گیری درباره قراردادن کد برای یک application، با دقت مد نظر قرار گیرد. ملاحظات زیر می تواند database را تبدیل به code location ارجح کند:

· اعتبار دهی داده ها (Data validation): نگه داشتن data validation logic در database، امکان بهتر خلاصه کردن این logic را با داده ها می دهد، و از تکثیر شدن validation logic در data touch pointها، از قبیل back-end processing، bulk upload و update کردن داده ها از middle tier و غیره جلوگیری می کند.

· کاهش ترافیک شبکه (Network traffic reduction): ممکن است قراردادن logic در database برای taskهای پردازش داده ها که مستلزم پردازش مقدار زیادی داده است، هنگام تولید درصد کمی از آن داده ها، مناسب باشد. نمونه های معمولی شامل applicationهای تحلیل داده ها از قبیل demand forecasting (پیش بینی تقاضا) و scheduling production (زمانبندی تولید) بر اساس forecast demandها و غیره باشد.

البته این ملاحظات بدون CLR integration هم مهم هستند؛ CLR integration فقط به تضمین اینکه انتخاب زبان برنامه نویسی در تعیین موقعیت مناسب کد دخالت نمی کند، کمک می کند.

مثال: زمانبندی تولید (Production Scheduling)

Production scheduling یک کار عادی در کارخانجات صنعتی است. در سطحی بالا، این کار مستلزم ارائه طرحی برای زمان تولید محصولات به منظور برآورده کردن تقاضا و در عین حال، به حداقل رساندن هزینه کل تولید و انبار کردن محصولات است. الگوریتمهای متعددی وجود دارند که پیش بینی تقاضا، هزینه های نگهداری انبار کالا، و هزینه های راه اندازی خط تولید را به عنوان ورودی، و استراتژی تولید را خروجی در نظر می گیرند.

با فرض اینکه پیش بینی تقاضاهای آینده در جدولی در SQL Server ذخیره می شوند، پیاده سازی چنین الگوریتمی دارای مشخصه های زیر است:

1. مقدار زیادی داده (پیش بینی تقاضا) را ورودی در نظر می گیرد.

2. نتیجه کمی را تولید می کند، مثلاً تعداد واحدهایی که باید در یک زمان یا زمانهای مشخص تولید شوند.

3. مستلزم محاسبات زیاد جهت مشتق کردن خروجی از ورودی است.

پیاده سازی چنین الگوریتمی در middle tier اختیاری است، اما انتقال داده های تقاضا از database، هزینه زیادی دربر دارد. انجام آن در T-SQL به عنوان یک stored procedure نیز شدنی است، اما عدم وجود data typeهای پیچیده پیاده سازی را مشکل می کند، و performance در T-SQL، به علت کمیت و پیچیدگی محاسبات لازم، مسئله ساز خواهد بود. البته، مشخصه های اجرا (performance characteristics) بسته به مقدار واقعی داده ها و پیچیدگی الگوریتم، متنوع خواهد بود.

جهت تایید تطابق CLR integration با چنین سناریویی، ما یک الگوریتم برنامه ریزی تولید—الگوریتم Wagner-Whitin — را در نظر گرفتیم و آنرا با استفاده از هم CLR و هم T-SQL پیاده کنیم. همانطور که انتظار می رفت، CLR integration پیاده سازی خیلی بهتری نسبت به T-SQL داشت. پیاده سازی در C# راحتتر بود، زیرا این الگوریتم از arrayهای تک و چند بعدی که در T-SQL وجود ندارند استفاده می کند. روی هم رفته، نسخه CLR چندین orders of magnitude را بهتر از T-SQL پیاده می کند.

بیایید شکل database ساده زیر را که لیست محصولاتی را که تولید می شوند را نگه می دارد، در نظر بگیریم.

جدول t_products:

به علاوه، جدول زیر پیش بینی تقاضا برای هر محصول در هر روز را ذخیره می کند. ما فرض می کنیم که ستون pid یک کلید خارجی در جدول t_products است.

جدول t_salesForecast:

ما یک stored procedure ایجاد کردیم که علاوه بر داده های محصول، پارامترهایی را در نظر می گیرد که دامنه تاریخ هایی را که باید برنامه ریزی تولید ارائه کرد، بیان می کند.

این stored procedure، یک rowset را با schema در جدول زیر باز می گرداند.

نسخه C# این کد، در زیر آورده شده تا نوع ستاریویی را که می تواند از CLR integration سود ببرد نشان دهد:

using System; 
using System.Data;
using System.Collections;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class ProductionScheduler
{
    const int MAXNAME = 256;                   // The maximum name size

    [Microsoft.SqlServer.Server.SqlProcedure]  // Flag as a SQL procedure 
    public static void Schedule(SqlDateTime start, SqlDateTime end)
    {
        // Guarantee that we have a valid connection while we run
        using (SqlConnection conn = 
            new SqlConnection("context connection=true"))
        {
            conn.Open();                       // open the connection
            SqlPipe pipe = SqlContext.Pipe;    // get the pipe

            // Find all the products in the database with any demand
            // whatsoever along with data about their production costs.
            // Make sure they are ordered.
            ArrayList items = new ArrayList();
            SqlCommand cmd = new SqlCommand(
                " SELECT DISTINCT tp.pid, pname, startupCost,"
                "     inventoryCost" +
                " FROM t_products tp" +
                " JOIN t_salesForecast ts" +
                " ON tp.pid = ts.pid" + 
                " ORDER BY pid",
                conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                items.Add(new Item(
                    reader.GetInt32(0), reader.GetSqlChars(1),
                    reader.GetInt32(2), reader.GetInt32(3)));
            }
            reader.Close();

            // Now get all the production schedule information, ordered
            // by PID and demand date
                "SELECT pid, demandDate, demandQty" + 
                " FROM t_salesForecast" + 
                "  WHERE demandDate >= @start" +
                "  AND demandDate <= @end" +
                " ORDER BY pid, demandDate",
                conn);
            cmd.Parameters.AddWithValue("@start", start);
            cmd.Parameters.AddWithValue("@end", end);
            reader = cmd.ExecuteReader();

            // Read each section of schedule information into the items.
            reader.Read();
            for (int i = 0; (i < items.Count) && (!reader.IsClosed); i++)
            {
                ((Item)(items[i])).readData(reader);
            }

            // ensure the reader is closed
            if (!reader.IsClosed) reader.Close();


            foreach (Item item in items)
            {
                // Compute the schedule and report it
                item.ComputeSchedule();
                item.OutputSchedule(pipe);
            }
        }
    }

    class Item
    {
        // Information about the product we are scheduling. These will
        // be pulled from the database.
        private int pid;
        private SqlChars name;
        private int startCost;
        private int holdCost;

        // Store how many dates we have.
        private int size = 0;

        // The dates on which we have demand. These are guaranteed to
        // be unique by the database, and we will load them in order.
        private ArrayList dates = new ArrayList();
        // Store what the demand was on each date.
        private ArrayList quantities = new ArrayList();

        // Our schedule, which we have not yet computed.
        int[] schedule = null;

        // Set up the metadata for the return
        SqlMetaData[] metadata = new SqlMetaData[] {
            new SqlMetaData("product", SqlDbType.NVarChar, MAXNAME),
            new SqlMetaData("period", SqlDbType.DateTime),
            new SqlMetaData("quantity", SqlDbType.Int)
        };

        public Item(int pid, SqlChars name, int startCost, int holdCost)
        {
            this.pid = pid;
            this.name = name;
            this.startCost = startCost;
            this.holdCost = holdCost;
        }

        /*
         * Read data from the stream until the PID does not match
         * ours anymore. We assume the reader is cued up to our
         * information and we leave it cued to the next item's
         * information UNLESS there is no more information, in which
         * case we close the reader to indicate as much.
         */
        public void readData(SqlDataReader reader)
        {
            size = 0;
            do
            {
                if (reader.GetInt32(0) == pid)
                {
                    size++;
                    dates.Add(reader.GetDateTime(1));
                    quantities.Add(reader.GetInt32(2));
                }
                else
                {
                    return;
                }
            } 
            while (reader.Read());

            // reader ran out. close it.
            reader.Close();
        }

        /*
         * This method is called to compute the production schedule
         * for the item. It does no I/O, but puts in motion the
         * dynamic programming algorithm which produces the schedule.
         */
        public void ComputeSchedule()
        {
            int[] days = ComputeProductionDays();
            schedule = new int[size];
            for (int i = 0; i < size; i++)
            {
                schedule[days[i]] += (Int32)(quantities[i]);
            }
        }

        /* 
         * Pipe the schedule to the user, computing it if need be.
         */
        public void OutputSchedule(SqlPipe pipe)
        {
            // Ensure that the schedule has been computed.
            if (schedule == null)
            {
                ComputeSchedule();
            }

            // Make a record in which to store the data.
            SqlDataRecord record = new SqlDataRecord(metadata);
            record.SetSqlChars(0, name);

            // Start up the output pipe.
            pipe.SendResultsStart(record);
            for (int i = 0; i < size; i++)
            {
                // Pipe each day of production. Omit zero production
                // days.
                if (schedule[i] != 0)
                {
                    record.SetDateTime(1, (DateTime)(dates[i]));
                    record.SetInt32(2, schedule[i]);
                    pipe.SendResultsRow(record);
                }
            }
            pipe.SendResultsEnd();
        }

        /* 
         * Compute the table and then walk it to find the best
         * days to produce the item.
         */
        private int[] ComputeProductionDays()
        {
            // We fill this in. It says when each day's quota is
            // actually produced.
            int[] productionDays = new int[size];
            
            // First, compute the table.
            int[][] table = ComputeTable();

            // Then walk the table, creating a second table which encodes
            // the best production days.
            int[] optimal = new int[size + 1];
            int[] optimalLoc = new int[size];
            optimal[size] = 0;
            for (int i = size - 1; i >= 0; i--)
            {
                int min = table[i][i] + optimal[i + 1];
                int minloc = i;
                for (int j = i+1; j < size; j++)
                {
                    int temp = table[i][j] + optimal[j + 1];
                    if (temp < min)
                    {
                        min = temp;
                        minloc = j;
                    }
                }
                optimal[i] = min;
                optimalLoc[i] = minloc;
            }

            // Finally, decode the optimal values into production days.
            int pday = 0;
            int until = optimalLoc[0] + 1;
            for (int i = 0; i < size; i++)
            {
                if (until == i)
                {
                    pday = i;
                    until = optimalLoc[i] + 1;
                }
                productionDays[i] = pday;
            }

            // We now have a list of days which we will produce the good.
            return productionDays;
        }

        /* 
         * The main part of the dynamic programming solution. Each entry
         * table[i,j] stores the cost of producing enough of the good on
         * day i to meet needs through day j. This table is only half-
         * filled when complete.
         */
        private int[][] ComputeTable()
        {
            int[][] table = new int[size][];
            for (int i = 0; i < size; i++) table[i] = new int[size];
            for (int i = 0; i < size; i++)
            {

                // If we produce the good on the same day we ship it we
                // incur a startup cost.
                table[i][i] = startCost;
                
                // For other days, we have the cost for the previous
                // cell plus the cost of storing the good for this long.
                for (int j = i + 1; j < size; j++)
                {
                    table[i][j] = table[i][j - 1] + 
                        (((int)quantities[j]) * holdCost *
                        diff((DateTime)(dates[i]), (DateTime)(dates[j])));
                }
            }
            return table;
        }

        /* 
         * A utility to compute the difference between two days. 
         */
        private int diff(DateTime start, DateTime end)
        {
            TimeSpan diff = end.Subtract(start);
            return diff.Days;
        }

    }
};

مقدمه

کد زیر چگونگی بارگذاری Crystal Reports در VB.NET، که همه مسایل logon را حل می کند، را نشان می دهد؛ که شامل زیرگزارشها و ارسال پارامتر می شود. شما می توانید گزارش هایتان را تنها با فراخوانی تابع های لازم با پارامترهایش مشاهده کنید.

استفاده از این کد

استفاده از این کد در برنامه تان بسیار ساده است. در مرحله اول، باید یک فرم اضافه کنید وآن را frmViewReport بنامید، سپس کنترل Crystal Report Viewer را روی فرم قرار دهید و آن را rptViewer بنامید. در این قسمت کد، فقط تابع زیر را paste کنید. شما می توانید این تابع را از هر کجای برنامه تان با رجوع به فرم frmViewerReport فرابخوانید. کد نمونه جهت فراخوانی این تابع در زیر آورده شده است:

Dim objForm As New frmViewReport
objForm.ViewReport("C:\test.rtp", , "@parameter1=test�mter2=10")
objForm.show()

حالا بگذارید مفصلاً به شما توضیح دهم در کد چه اتفاقی می افتد و فرمت رشته پارامتر (parameter string) چیست. اگر پارامترهایی در Crystal Reports باشند، آنگاه باید آنها را با valueشان به param تابع فرستاد. parameter string باید به صورت زیر باشد:

<First Parameter Name>=<First Paramter Value>&
<Second Parameter Name>=<Second Paramter Value>..

به خاطر داشته باشید که نام پارامتر و value pairهایش با یک '&' جدا می شوند. نام گزارش با مسیر کاملش باید به تابع sReportName ارسال شود.

در زیر کد تابع با commentها آورده شده:

Friend Function ViewReport(ByVal sReportName As String, _
    Optional ByVal sSelectionFormula As String = "", _
    Optional ByVal param As String = "") As Boolean

    'Declaring variablesables
    Dim intCounter As Integer
    Dim intCounter1 As Integer

    'Crystal Report's report document object
    Dim objReport As New _
        CrystalDecisions.CrystalReports.Engine.ReportDocument 

    'object of table Log on info of Crystal report
    Dim ConInfo As New CrystalDecisions.Shared.TableLogOnInfo

    'Parameter value object of crystal report 
    ' parameters used for adding the value to parameter.
    Dim paraValue As New CrystalDecisions.Shared.ParameterDiscreteValue

    'Current parameter value object(collection) of crystal report parameters.
    Dim currValue As CrystalDecisions.Shared.ParameterValues

    'Sub report object of crystal report.
    Dim mySubReportObject As _
        CrystalDecisions.CrystalReports.Engine.SubreportObject

    'Sub report document of crystal report.
    Dim mySubRepDoc As New _
        CrystalDecisions.CrystalReports.Engine.ReportDocument

    Dim strParValPair() As String
    Dim strVal() As String
    Dim index As Integer

    Try

        'Load the report
        objReport.Load(sReportName)

        'Check if there are parameters or not in report.
        intCounter = objReport.DataDefinition.ParameterFields.Count

        'As parameter fields collection also picks the selection 
        ' formula which is not the parameter
        ' so if total parameter count is 1 then we check whether 
        ' its a parameter or selection formula.

        If intCounter = 1 Then
            If InStr(objReport.DataDefinition.ParameterFields(_
                0).ParameterFieldName, ".", CompareMethod.Text) > 0 Then
                intCounter = 0
            End If    
        End If

        'If there are parameters in report and 
        'user has passed them then split the 
        'parameter string and Apply the values 
        'to their concurrent parameters.

        If intCounter > 0 And Trim(param) <> "" Then
            strParValPair = param.Split("&")

            For index = 0 To UBound(strParValPair)
            If InStr(strParValPair(index), "=") > 0 Then
                strVal = strParValPair(index).Split("=")
                paraValue.Value = strVal(1)
                currValue = _ 
                    objReport.DataDefinition.ParameterFields(_
                    strVal(0)).CurrentValues
                currValue.Add(paraValue)
                objReport.DataDefinition.ParameterFields(_
                    strVal(0)).ApplyCurrentValues(currValue)
            End If
        Next
    End If

    'Set the connection information to ConInfo 
    'object so that we can apply the 
    'connection information on each table in the report
    ConInfo.ConnectionInfo.UserID = <User Name>
    ConInfo.ConnectionInfo.Password = <Password>
    ConInfo.ConnectionInfo.ServerName = <Server Name>
    ConInfo.ConnectionInfo.DatabaseName = <Database Name>

    For intCounter = 0 To objReport.Database.Tables.Count - 1
        objReport.Database.Tables(intCounter).ApplyLogOnInfo(ConInfo)
    Next

    ' Loop through each section on the report then look 
    ' through each object in the section
    ' if the object is a subreport, then apply logon info 
    ' on each table of that sub report

        For index = 0 To objReport.ReportDefinition.Sections.Count - 1
            For intCounter = 0 To _
                objReport.ReportDefinition.Sections(_
                index).ReportObjects.Count - 1
            With objReport.ReportDefinition.Sections(index)
                If .ReportObjects(intCounter).Kind = _
                CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
                    mySubReportObject = CType(.ReportObjects(intCounter), _
                      CrystalDecisions.CrystalReports.Engine.SubreportObject)
                    mySubRepDoc = _ 
             mySubReportObject.OpenSubreport(mySubReportObject.SubreportName)
                 For intCounter1 = 0 To mySubRepDoc.Database.Tables.Count - 1
                       mySubRepDoc.Database.Tables(_
                           intCounter1).ApplyLogOnInfo(_
                           ConInfo)sp;
                       mySubRepDoc.Database.Tables(_
                           intCounter1).ApplyLogOnInfo(ConInfo)
                 Next
                 End If
             End With
         Next
     Next
    'If there is a selection formula passed to this function then use that
    If sSelectionFormula.Length > 0 Then
        objReport.RecordSelectionFormula = sSelectionFormula
    End If
    'Re setting control 
    rptViewer.ReportSource = Nothing

    'Set the current report object to report.
    rptViewer.ReportSource = objReport

    'Show the report
    rptViewer.Show()
        Return True
    Catch ex As System.Exception
        MsgBox(ex.Message)
    End Try
End Function

نکات جالب

مشکلات اصلی هنگام develop کردن این تابع، زیرگزارشها و selection formula بودند. وقتی هیچ پارامتری وجود نداشت و فقط selection formula وجود داشت، تابع عمل نکرد، زیرا parameters collection، فرمول انتخاب (selection formula) را انتخاب می کرد.