آموزش سی شارپ-دوره آموزش #C

سلام با آموزش برنامه نویسی سی شارپ قسمت 14 در خدمت شما عزیزان هستیم, در قسمت قبلی با کار با داده به صورت آفلاین در C# آشنا شدیم. با ادامه آموزش سی شارپ همراه ما باشید.

لينك قسمت اول : آموزش #C

افزودن پارامتر به SqlCommand با SqlParameter

بسیاری از دستورات اجرایی روی پایگاه داده نیاز به پارامترهای داده ای دارند که لازم است فراهم شده و هنگام اجرای دستور مقادیرشان به دستور مربوطه ارسال شده باشد. در این بخش از آموزش سی شارپ و مباحث پایگاه داده در سی شارپ (ADO.NET) به این موضوع و کار با کلاس SqlParameter خواهیم داشت.

افزودن پارامتر به فرمان (Command)

این درس توضیح خواهد داد که چگونه از پارامترها در فرمان ها استفاده نمود. اهداف این درس عبارتند از:

• آشنایی با پارامتر (Parameter)
• آگاهی از مزایای استفاده از پارامترها
• چگونگی ایجاد یک پارامتر(SQL Parameter)
• نحوه ی تخصیص پارامتر به فرمان(SQL Command)

مقدمه

دستور SQL که برای شی SqlCommand اختصاص یافته است به صورت یک رشته می باشد. بنابراین اگر در صدد فیلتر کردن و تعیین یک شرط برای Query باشید، می توانید رشته ی مذکور را به صورت پویا (dynamically) بسازید و این در حالی است که تمایل چندانی به انجام این کار ندارید. کد زیر، نمونه ای نامطلوب از فیلترینگِ یک Query را نشان می دهد:

// don't ever do this

SqlCommand cmd = new SqlCommand(&quotselect * from Customers where city = '&quot + inputCity + &quot'&quot

هرگز یک Query را با این روش طراحی نکنید! در کد بالا، متغیر وروردی یا inputCity، معمولا از یک کنترلِ TextBox بر روی Windows form یا Web Page بازیابی می گردد. هر چیزی که درونِ این TextBox قرار گیرد، در متغیرِ inputCity وارد شده و به رشته ی SQL مذکور اضافه می گردد. چنین موقعیتی، این امکان را برای هکرها فراهم می کند که رشته ی مورد نظر را با هرگونه رشته ی مخرب دیگری جایگزین نماید. در بدترین حالت، ممکن است کنترلِ کامل کامپیوتر خود را از دست بدهید.

به جای اینکه یک رشته را به صورت dynamic طراحی کنید، از پارامترها بهره بگیرید. با هر چیزی که درون پارامتر قرار گیرد، به عنوان یک دیتا فیلد (Field data) رفتار خواهد شد، نه به عنوان بخشی از دستورِ SQL. این کار موجب امن تر شدنِ Application می گردد.

استفاده از Query هایی که شامل پارامتر می باشند (Parameterized Queries)، یک پروسه ی سه مرحله ای می باشد:

• ایجاد رشته ی SqlCommand با استفاده از پارامترها
• تعریفِ یک Object SqlParameter با تخصیصِ مقادیرِ مناسب و مطلوب
• تخصیصِ SqlParameter Object به Parameter Property های مربوط به Object SqlCommand
• بخش های بعدی شما را مرحله به مرحله در طول این فرآیند به پیش می برند.

ارسال پارامتر به شی SqlCommand

برای ارسال پارامتر همراه با Query های SQL، اول باید متن دستور اجرایی را به کمک ویژگی CommandText تنظیم نمایید که حاوی پارامترهای موقتی Parameter Placeholder)) باشد. زمانی که SqlCommand اجرا می شود، این پارامترهای موقتی با مقادیرِ واقعی جایگزین می شوند. روش درست نمایشِ (syntax)یک پارامتر به این صورت است که از نمادِ پیشوندیِ "@" قبل از نامِ پارامتر استفاده نماییم. کد زیر این نحوه ی نام گذاری را نشان می دهد:

// 1. declare command object with parameter

SqlCommand cmd = new SqlCommand(&quotselect * from Customers where city = @City&quot, conn);

اولین آرگومان در Constructor (سازنده ی) شی SqlCommand در کد بالا، در بر دارنده ی یک تعریفِ پارامتری به صورتِ @City می باشد. در این مثال تنها از یک پارامتر استفاده شده است، اما زمانی که بخواهیم Query را اختصاصی نماییم، می توان از هر تعداد پارامتر بهره گرفت. هر پارامتر با یک شی SqlParameter که باید برای شی SqlCommand اختصاص داده شود، تطابق می یابد.

تعریفِ شی SqlParameter

آموزش سي شارپ

به عنوانِ هدفِ شی SqlParameter ، هر پارامتر باید در دستورِ SQL تعریف شده باشد. کد شما باید یک نمونه از SqlParameter را برای هر پارامترِ موجود در شی SqlCommand تعریف نماید. کد پایین یک پارامتر را برای پارامترِ @City که در بخش قبل به آن اشاره شد، تعریف می نماید.

// 2. define parameters used in command object

SqlParameter param = new SqlParameter();

param.ParameterName = &quot@City&quot

param.Value = inputCity;

دقت داشته باشید که ویژگی ParameterName از شی SqlParameter، باید دقیقا به صورتِ پارامتری نوشته شود که در CommandText استفاده شده است. همچنین باید مقدار خاصی را برای این پارامتر اختصاص داد. زمانی که شی SqlCommand اجرا می شود، این مقدار جایگزینِ پارامتر خواهد شد.

ایجاد ارتباط بینِ اشیای SqlParameter و SqlCommand

باید برای هر پارامترِ تعریف شده در شی SqlCommand، تعریفی برای شی SqlParameter ارایه داد. همچنین، با تخصیص نمونه ی SqlParameter به شی SqlCommand، این شی قادر به شناسایی این پارامتر خواهد بود. کد زیر چگونگی انجام این کار را نشان می دهد:

// 3. add new parameter to command object

cmd.Parameters.Add(param);

در کدِ بالا، نمونه ی SqlCommand عبارت است از یک آرگومان برای متدِ Add از ویژگی Parameters. همچنین، ضرورت دارد که برای هر پارامترِ تعریف شده در رشته ی فرمانِ SQL (SQL Command String)، یک SqlParameter منحصر به فرد را اضافه کنید.

جمع بندی

قبلا با چگونگیِ استفاده از اشیای SqlCommand و SqlDataReader آشنا شده اید. کدِ پایین یک برنامه ی در حال اجرا (working program) را نشان می دهد که از اشیای SqlParameter استفاده می کنند. بنابراین، از حالا به بعد تمامی مباحث برای شما آشنا خواهند بود مگر قسمت های جدیدی که در این مقاله ارایه شده اند:

using System;

using System.Data;

using System.Data.SqlClient;

class ParamDemo

{

    static void Main()

    {

        // conn and reader declared outside try

        // block for visibility in finally block

        SqlConnection conn = null;

        SqlDataReader reader = null;

        string inputCity = &quotLondon&quot

        try

        {

            // instantiate and open connection

            conn = new SqlConnection(&quotServer=(local);DataBase=Northwind;Integrated Security=SSPI&quot);

            conn.Open();

            // don't ever do this

            // SqlCommand cmd = new SqlCommand(

            // &quotselect * from Customers where city = '&quot + inputCity + &quot'&quot

            // 1. declare command object with parameter

            SqlCommand cmd = new SqlCommand(&quotselect * from Customers where city = @City&quot, conn);

            // 2. define parameters used in command object

            SqlParameter param = new SqlParameter();

            param.ParameterName = &quot@City&quot

            param.Value = inputCity;

            // 3. add new parameter to command object

            cmd.Parameters.Add(param);

            // get data stream

            reader = cmd.ExecuteReader();

            // write each record

            while (reader.Read())

            {

                Console.WriteLine(&quot{0}, {1}&quot, reader[&quotCompanyName&quot], reader[&quotContactName&quot]);

            }

        }

        finally

        {

            // close reader

            if (reader != null)

            {

                reader.Close();

            }

            // close connection

            if (conn != null)

            {

                conn.Close();

            }

        }

    }

}

کد بالا رکوردهای مربوط به هر مشتری را که در لندن زندگی می کند، بازیابی می نماید. با به کار گیریِ پارامترها، این فرآیند ایمن تر خواهد بود. علاوه بر پارامترها، بقیه ی کدها شامل تکنیک هایی هستند که در بخش های قبلی آموخته اید.

خلاصه

به منظورِ فیلتر نمودنِ Query ها با یک روشِ ایمن، باید از پارامترها استفاده نمود. پروسه ی بکارگیریِ پارامتر، شامل سه مرحله می باشد: تعریفِ پارامتر در رشته ی فرمانCommandText) (، تعریف شی SqlParameter با Property های (ویژگی های) اجرایی و کاربردی، و تخصیص شی SqlPararmeter به شی SqlCommand. هنگامی که شی SqlCommand اجرا می شود، مقادیری که به وسیله ی شی SqlParameter مشخص شده اند، جایگزینِ پارامترها می شوند.

استفاده از رویه ذخیره شده (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(&quotTen Most Expensive Products&quot, 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(&quotCustOrderHist&quot, 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(&quot@CustomerID&quot, 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(&quot\nTop 10 Most Expensive Products:\n&quot);

        try

        {

            // create and open a connection object

            conn = new SqlConnection(&quotServer=(local);DataBase=Northwind;Integrated Scurity=SSPI&quot);

            conn.Open();

            // 1. create a command object identifying

            // the stored procedure

            SqlCommand cmd = new SqlCommand(&quotTen Most Expensive Products&quot, 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(&quotProduct: {0,-25} Price: ${1,6:####.00}&quot, rdr[&quotTenMostExpensiveProducts&quot], rdr[&quotUnitPrice&quot]);

            }

        }

        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 = &quotFURIB&quot

        Console.WriteLine(&quot\nCustomer Order History:\n&quot);

        try

        {

            // create and open a connection object

            conn = new SqlConnection(&quotServer=(local);DataBase=Northwind;Integrated Security=SSPI&quot);

            conn.Open();

            // 1. create a command object identifying

            // the stored procedure

            SqlCommand cmd = new SqlCommand(&quotCustOrderHist&quot, 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(&quot@CustomerID&quot, custId));

            // execute the command

            rdr = cmd.ExecuteReader();

            // iterate through results, printing each to console

            while (rdr.Read())

            {

                Console.WriteLine(&quotProduct: {0,-35} Total: {1,2}&quot,

                    rdr[&quotProductName&quot],

                              rdr[&quotTotal&quot]);

            }

        }

        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 دیگری استفاده نمود.

پايان قسمت 14 آموزش سي شارپ