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

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

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

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

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

مقدمه

به هنگام کار با دیتا، ممکن است به دلایل مختلفی و بر طبق برخی معیارهای خاص، خواستار فیلتر (filter) کردن نتایج و اعمال شرطِ خاصی باشید. این کار معمولا به صورت دریافت ورودی (input) از یک Userو استفاده از آن ورودی برای طراحی و ایجاد یک Query، انجام می گیرد. برای مثال، ممکن است یک فروشنده بخواهد که تمامی سفارشات بین دو تاریخ خاص را مشاهده نماید. Query بعدی ممکن است به صورت فیلتر کردن افراد بر اساس شهر محل سکونت باشد.

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

                
// don't ever do this
SqlCommand cmd = new SqlCommand("select * from Customers where city = '" + inputCity + "'";

هرگز یک 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("select * from Customers where city = @City", 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 = "@City";
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 = "London";

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

            // don't ever do this
            // SqlCommand cmd = new SqlCommand(
            // "select * from Customers where city = '" + inputCity + "'";
            // 1. declare command object with parameter
            SqlCommand cmd = new SqlCommand("select * from Customers where city = @City", conn);

            // 2. define parameters used in command object
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@City";
            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("{0}, {1}", reader["CompanyName"], reader["ContactName"]);
            }
        }
        finally
        {
            // close reader
            if (reader != null)
            {
                reader.Close();
            }

            // close connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}

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

خلاصه

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