استفاده از رویه ذخیره شده (Stored Procedure in ADO.NET)

یکی از مهم ترین روش های اجرای یک دستور در پایگاه های داده رایج در جهان، استفاده از Stored Procedure است. مزایای زیادی در این روش وجود دارد که شاید مهم ترین آن ها مدیریت و دسته بندی بهتر کدهای پایگاه داده در مقایسه با روش قبلی و همچنین سرعت اجرا و کارایی بالاتر است. در این بخش از آموزش کار با پایگاه داده در سی شارپ به این موضوع خواهیم پرداخت.

استفاده از Stored Procedure

این درس چگونگی استفاده از Stored Procedure در کار با پایگاه داده نشان می دهد. اهداف این درس به شرح زیر می باشند:

• چگونگی تغییر SqlCommand Object به منظور استفاده از Stored Procedure
• چگونگیِ به کار گیریِ پارامترها توسط Stored Procedure

مقدمه

Stored Procedure عبارت است از یک روتینِ از قبل تعریف شده و قابل استفاده ی مجدد(Reusable) که در یک پایگاه داده ذخیره شده است. Stored Procedure ها در SQL Server کامپایل شده و یک برنامه اجرایی (Execution Plan) بهینه برای آن ها ساخته می شود. این امر موجب می شود که استفاده از آنها کارآمدتر شود. بنابراین، به جای طراحیِ داینامیک Queryها، می توان از مزیت های اجرایی و قابلیتِ استفاده ی مجددِ Stored Procedure بهره مند شد. بخش های بعدی نشان خواهند داد که چگونه می توان اشیای SqlCommand را به منظورِ استفاده از Stored Procedure، اصلاح نمود.

اجرای Stored Procedure

علاوه بر Command Textها (متن دستور)، SqlCommand برای اجرای Stored Procedure نیز مورد استفاده قرار می گیرد. برای این منظور، انجام دو کار ضرورت دارد: اول اینکه برای SqlCommand تعریف شود که کدام Stored Procedure را اجرا کند، و در گام بعدی به این شی اعلان شود که در حال اجرای Stored Procedure می باشد. این دو مرحله در کد زیر نشان داده شده اند:

                
// 1. create a command object identifying
// the stored procedure
SqlCommand cmd  = new SqlCommand("Ten Most Expensive Products", conn);

// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;

زمانی که SqlCommand Object در کد بالا در حال تعریف شدن است، اولین پارامتر برای “Ten Most Expensive Products” مقداردهی می گردد. این عبارت، نام Stored Procedure در پایگاه داده Northwind می باشد. دومین پارامتر، شی اتصال (Connection Object) است که مشابه SqlCommand Constructor بوده و برای اجرایِ رشته های Query مورد استفاده قرار می گیرد.

پس از تنظیمِ CommandType Property برای مقادیرِ Stored Procedure، دومین فرمان (Command) به SqlCommand Object اعلان می کند که چه دستوری اجرا خواهد شد. اولینِ پارامتر به طور پیش فرض می فهمد که با SqlCommand Constructor به عنوان یک Query String رفتار کند. با مقداردهیِ CommandType برای Stored Procedure، SqlCommand Constructor اولین پارامتر را به عنوانِ یک Stored Procedure تفسیر و ترجمه خواهد کرد، نه به عنوان یک Command String.

ارسال پارامتر به Stored Procedure

استفاده از پارامترها برای Stored Procedure مشابه استفاده از آنها برای Query String Command ها می باشد. به کد پایین دقت کنید:

                
// 1. create a command object identifying
// the stored procedure
SqlCommand cmd = new SqlCommand("CustOrderHist", conn);

// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;

// 3. add parameter to command, which
// will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

در کدِ بالا، Constructor یک Stored Procedure (که به صورت CustOrderHist نام گذاری شده است) را، به عنوان اولین پارامتر تعریف می کند. این Stored Procedure شامل یک پارامترِ تکی و مجزا به نامِ @CustomerID می باشد. بنابراین، باید با استفاده از یک شیSqlParameter ، این پارامتر را پر نمود. نام پارامتری که به عنوان اولین پارامتر به SqlParameter Constructor منتقل شده است، باید دقیقا مشابه پارامترِ Stored Procedure نوشته شود. سپس، همانند دیگر اشیای SqlCommand، این فرمان (Command) نیز اجرا می شود.

مثال:

کد موجود در لیست 1 دربردارنده ی مثالی است که به صورت جامع، چگونگی کار با Stored Procedure را نشان می دهد. متدهای جداگانه ای برای Stored Procedure هایی که دارای پارامتر هستند، و نیز Stored Procedure های فاقد پارامتر، ارایه شده است:

                
using System;
using System.Data;
using System.Data.SqlClient;

class StoredProcDemo
{
    static void Main()
    {
        StoredProcDemo spd = new StoredProcDemo();

        // run a simple stored procedure
        spd.RunStoredProc();

        // run a stored procedure that takes a parameter
        spd.RunStoredProcParams();
    }

    // run a simple stored procedure
    public void RunStoredProc()
    {
        SqlConnection conn = null;
        SqlDataReader rdr = null;
        Console.WriteLine("\nTop 10 Most Expensive Products:\n");

        try
        {
            // create and open a connection object
            conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Scurity=SSPI");
            conn.Open();

            // 1. create a command object identifying
            // the stored procedure
            SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn);

            // 2. set the command object so it knows
            // to execute a stored procedure
            cmd.CommandType = CommandType.StoredProcedure;

            // execute the command
            rdr = cmd.ExecuteReader();

            // iterate through results, printing each to console
            while (rdr.Read())
            {
                Console.WriteLine("Product: {0,-25} Price: ${1,6:####.00}", rdr["TenMostExpensiveProducts"], rdr["UnitPrice"]);
            }
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
            if (rdr != null)
            {
                rdr.Close();
            }
        }
    }

    // run a stored procedure that takes a parameter
    public void RunStoredProcParams()
    {
        SqlConnection conn = null;
        SqlDataReader rdr = null;

        // typically obtained from user
        // input, but we take a short cut
        string custId = "FURIB";
        Console.WriteLine("\nCustomer Order History:\n");

        try
        {
            // create and open a connection object
            conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
            conn.Open();

            // 1. create a command object identifying
            // the stored procedure
            SqlCommand cmd = new SqlCommand("CustOrderHist", conn);

            // 2. set the command object so it knows
            // to execute a stored procedure
            cmd.CommandType = CommandType.StoredProcedure;

            // 3. add parameter to command, which
            // will be passed to the stored procedure
            cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));

            // execute the command
            rdr = cmd.ExecuteReader();

            // iterate through results, printing each to console
            while (rdr.Read())
            {
                Console.WriteLine("Product: {0,-35} Total: {1,2}",
                    rdr["ProductName"],
                  			  rdr["Total"]);
            }
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
            if (rdr != null)
            {
                rdr.Close();
            }
        }
    }
}

متدِ RunStoredPro در لیست دستور 1، یک Stored Procedure را به سادگی اجرا کرده و نتایج را برای کنسول (Console) چاپ می کند. در متدِ RunStoredProcParams در کد بالا، Stored Procedure مورد استفاده تنها دارای یک پارامتر می باشد. این امر بیانگر این است که در استفاده از پارامترها، بین Query String و یا Stored Procedure هیچ تفاوتی وجود ندارد.

خلاصه

برای اجرای Stored Procedure، نامِ Stored Procedure در اولین پارامترِ SqlCommand Constructor مشخص می شود؛ سپس، CommandType برابر StoredProcedure مقدار دهی می گردد. همچنین می توان با استفاده از SqlParameter Object، پارامترها را به یک Stored Procedure ارسال نمود. همین کار را می توان برای اشیای SqlCommand که متن دستور SQL را اجرا می کنند، اعمال نمود. هر بار که شی SqlCommand ساخته می شود، از آن می توان مانند هر شی SqlCommand دیگری استفاده نمود.