اجرای دستورات پایگاه داده در سی شارپ (SqlCommand)

وقتی توی برنامه‌نویسی با پایگاه داده کار می‌کنی، فقط وصل شدن به دیتابیس کافی نیست؛ باید بتونی دستورهای مختلف SQL رو هم به شکل درست اجرا کنی. اینجاست که SqlCommand وارد عمل می‌شه و به برنامه کمک می‌کنه دستورهای SQL رو به پایگاه داده ارسال کنه.

توی این آموزش از ADO.NET در سی شارپ، قدم‌به‌قدم با شی SqlCommand و روش استفاده از اون آشنا می‌شیم. اگه توی مسیر یادگیری C# هستی، آشنایی با این شی و متدهای مختلفش می‌تونه دید بهتری از نحوه تعامل برنامه با پایگاه داده بهت بده.

همون‌طور که پیش از این در آموزش پایگاه داده در سی شارپ گفتیم، برای اجرای دستورات پایگاه داده باید بعد از اتصال به دیتابیس، دستور موردنظر رو در قالب یک شی SqlCommand و از طریق اتصالی که با SqlConnection ایجاد شده، اجرا کنیم. توی این بخش از آموزش ADO.NET در سی شارپ می‌خوایم ببینیم این کار دقیقاً چطور انجام می‌شه.

شی SqlCommand

توی این درس با SqlCommand object آشنا می‌شیم و می‌بینیم چطور می‌تونیم از این شی برای تبادل داده و تعامل با یک پایگاه داده استفاده کنیم. در پایان این درس با این موارد آشنا می‌شیم:

• شی Command چیست؟
• چطور از متد ExecuteReader برای Query گرفتن از داده‌ها استفاده کنیم؟
• چطور با استفاده از متد ExecuteNonQuery داده‌ها رو درج یا حذف کنیم؟
• چطور از متد ExecuteScalar برای برگردوندن یک مقدار واحد استفاده کنیم؟

مقدمه

شی SqlCommand این امکان رو بهت می‌ده که مشخص کنی برنامه چه کاری باید با پایگاه داده انجام بده. به عبارت ساده‌تر، با استفاده از این شی می‌تونی نوع عملیاتی که قراره روی دیتابیس انجام بشه رو مشخص کنی. برای مثال، می‌تونی عملیات delete، select، modify و insert رو روی رکوردهای یک جدول انجام بدی. SqlCommand می‌تونه برای کار با سناریوهای مدیریت داده‌های در دسترس‌نبوده (disconnected data) هم استفاده بشه، اما توی این درس قراره SqlCommand رو به‌تنهایی بررسی کنیم. در درس بعدی که مربوط به SqlDataAdapter هست، می‌بینیم چطور می‌شه یک Application رو که با داده‌های Disconnected کار می‌کنه، پیاده‌سازی کرد. توی این درس همچنین یاد می‌گیریم چطور یک مقدار واحد، مثل تعداد رکوردهای یک جدول، رو از پایگاه داده دریافت کنیم.

ایجاد یک شی SqlCommand

مثل خیلی از شی‌های دیگه در سی‌شارپ، می‌تونیم یک شی SqlCommand رو به شکل زیر بسازیم:

    SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

کدی که بالا می‌بینی، یکی از روش‌های رایج برای ساخت شی SqlCommand هست. این کد یک آرگومان رشته‌ای (String Parameter) دریافت می‌کنه که شامل دستوریه که قراره اجرا بشه و در کنار اون، یک reference به SqlConnection هم داره. SqlCommand چند نسخه Overload شده داره که در طول این دوره آموزشی با اون‌ها آشنا می‌شیم.

Query گرفتن از داده‌ها (Querying Data)

وقتی با SQL کار می‌کنیم، معمولاً برای بازیابی داده‌ها از دستور select استفاده می‌کنیم. برای انجام این کار با یک شی SqlCommand، از متد ExecuteReader استفاده می‌کنیم که یک شی SqlDataReader برمی‌گردونه. در درس بعدی درباره SqlDataReader بیشتر صحبت می‌کنیم. فعلاً مثال زیر رو ببینیم تا نحوه استفاده از SqlCommand برای دسترسی به SqlDataReader رو بهتر متوجه بشیم.

    // 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand ("select CategoryName from Categories", conn);

// 2. Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();

توی مثال بالا اول یک نمونه از شی SqlCommand می‌سازیم و بعد «متن دستور SQL» و شی SqlConnection رو به عنوان آرگومان به Constructor اون می‌دیم. در مرحله بعد، با صدا زدن متد ExecuteReader از شی SqlCommand که در اینجا اسمش رو cmd گذاشتیم، یک شی SqlDataReader دریافت می‌کنیم.

این کد بخشی از متد ReadData مربوط به لیست 1 هست که در ادامه این مبحث و در بخش جمع‌بندی بهش برمی‌گردیم.

عملیات درج داده‌ها (Inserting Data)

برای درج داده‌ها در پایگاه داده، از متد ExecuteNonQuery مربوط به شی SqlCommand استفاده می‌کنیم. کد زیر نشون می‌ده چطور می‌تونیم یک داده جدید رو داخل پایگاه داده درج کنیم.

    // prepare command string
    string insertString = @"insert into Categories
                            (CategoryName, Description)
                            Values
                            ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
    
    // 1. Instantiate a new command with a query and connection
    SqlCommand cmd = new SqlCommand(insertString, conn);
    
    // 2. Call ExecuteNonQuery to send command
    cmd.ExecuteNonQuery();

روش ساخت SqlCommand اینجا کمی با چیزی که قبلاً دیدیم فرق داره، ولی در اصل همون کار رو انجام می‌ده. این بار به جای اینکه مقدار ثابت رشته‌ای (Literal String) رو به عنوان اولین آرگومان Constructor وارد کنیم، از یک متغیر به اسم InsertString استفاده کردیم. همون‌طور که توی کد می‌بینی، متغیر InsertString درست قبل از تعریف SqlCommand ساخته شده.

حالا به دو تا تک‌کوتیشن (") در متن Insert String و بالای کلمه “doesn”t” دقت کن. با استفاده از این کاراکترها مطمئن می‌شیم ستون‌های جدول پایگاه داده به شکل درست با مقدار رشته‌ای موردنظر پر می‌شن و ابهامی که ممکنه به خاطر استفاده از کوتیشن ایجاد بشه، برطرف می‌شه.

یک نکته دیگه هم اینجا وجود داره که باید بهش توجه کنیم. توی کد بالا، اسم ستون‌های Category و Description رو به‌صورت مشخص از جدول Category وارد کردیم. جدول Category یک فیلد Primary Key به نام CategoryID داره. چون خود SQL Server این فیلد رو اضافه می‌کنه، اون رو در لیست ستون‌ها قرار ندادیم. اگه بخوایم یک مقدار برای فیلد Primary Key، مثل CategoryID، وارد کنیم، با خطا (Exception) مواجه می‌شیم.

برای اجرای این دستور هم کافیه متد ExecuteNonQuery رو روی نمونه SqlCommand که اسمش cmd هست اجرا کنیم. این کد بخشی از متد Insert Data هست که در بخش جمع‌بندی بهش برمی‌گردیم.

به روز رسانی داده‌ها (Updating Data)

از متد ExecuteNonQuery برای به‌روزرسانی داده‌ها هم استفاده می‌کنیم. کد زیر نشون می‌ده داده‌ها چطور به‌روزرسانی می‌شن:

    // prepare command string
    string updateString = @"update Categories set
                      CategoryName = 'Other'
                      where CategoryName = 'Miscellaneous'";
    
    // 1. Instantiate a new command with command text only
    SqlCommand cmd = new SqlCommand(updateString);
    
    // 2. Set the Connection property
    cmd.Connection = conn;
    
    // 3. Call ExecuteNonQuery to send command
    cmd.ExecuteNonQuery();

اینجا هم دوباره دستور SQL رو داخل یک متغیر رشته‌ای (String Variable) قرار می‌دیم، اما این بار از یک Constructor متفاوت برای SqlCommand استفاده می‌کنیم که فقط دستور رو دریافت می‌کنه. در مرحله دوم، شی conn رو به Property مربوط به شی cmd اختصاص می‌دیم. این کار رو می‌تونستیم با همون Constructorی که برای دستور Insert استفاده کردیم و با دو پارامتر ورودی هم انجام بدیم. این روش در واقع نشون می‌ده که می‌تونیم شی Connection (اتصال به پایگاه داده) مربوط به یک دستور خاص رو در هر زمانی تغییر بدیم.

متد ExecuteNonQuery دستور یا عملیات Update رو اجرا می‌کنه. این کد بخشی از Update Data مربوط به لیست دستور 1 هست که در بخش بعدی درباره‌ش صحبت می‌کنیم.

حذف داده‌ها (Deleting Data)

با استفاده از متد ExecuteNonQuery می‌تونیم داده‌ها رو حذف هم بکنیم. مثال زیر نشون می‌ده چطور می‌شه با استفاده از این متد، یک سطر (Record) رو از پایگاه داده حذف کرد.

    // prepare command string
    string deleteString = @"delete from Categories
                      where CategoryName = 'Other'";
    
    // 1. Instantiate a new command
    SqlCommand cmd = new SqlCommand();
    
    // 2. Set the CommandText property
    cmd.CommandText = deleteString;
    
    // 3. Set the Connection property
    cmd.Connection = conn;
    
    // 4. Call ExecuteNonQuery to send command
    cmd.ExecuteNonQuery();

توی مثال بالا، SqlCommand رو بدون ارسال پارامتر ورودی به Constructor ساختیم. در عوض، Propertyهای CommandText و Connection مربوط به شی SqlCommand رو به‌صورت مستقیم مقداردهی کردیم. می‌تونیم از هر کدوم از دو حالت Overload شده Constructor قبلی که برای دستور Insert یا به‌روزرسانی استفاده کردیم هم استفاده کنیم و به نتیجه یکسانی برسیم. این موضوع نشون می‌ده که می‌تونیم هم متن دستور (Command Text) و هم شی اتصال (Connection Object) رو در هر زمانی تغییر بدیم. متد ExecuteNonQuery هم دستور رو به پایگاه داده ارسال می‌کنه. این کد بخشی از متد DeleteData هست که در بخش بعدی درباره‌ش صحبت می‌کنیم.

به دست آوردن مقادیر مجزا (Single Values)

گاهی اوقات چیزی که از پایگاه داده نیاز داریم فقط یک مقدار واحده؛ مثلاً یک مقدار count، جمع (sum)، میانگین (average) یا هر مقدار مشخص دیگه‌ای از مجموعه داده‌ها (Data Set). برای این کار، استفاده از ExecuteReader و محاسبه نتیجه داخل کد، روش چندان کارآمدی نیست. بهتره این محاسبه رو به خود پایگاه داده بسپاریم تا همون مقدار موردنیاز رو برگردونه. مثال زیر نشون می‌ده چطور می‌تونیم این کار رو با متد ExecuteScalar انجام بدیم:

    // 1. Instantiate a new command
    SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);

    // 2. Call ExecuteNonQuery to send command
    int count = (int)cmd.ExecuteScalar();

Query که داخل Constructor مربوط به SqlCommand قرار دادیم، تعداد تمام رکوردهای جدول دسته‌بندی (Category Table) رو به دست میاره. این Query فقط یک مقدار واحد برمی‌گردونه. در مرحله دوم، متد ExecuteScalar همین مقدار رو برمی‌گردونه. از اونجایی که نوع خروجی ExecuteScalar از نوع Object هست، قبل از اون از عبارت (int) استفاده کردیم تا مقدار به یک عدد صحیح تبدیل بشه.

جمع بندی

تا اینجا برای اینکه تکنیک‌های مختلف رو راحت‌تر بررسی کنیم، هر بار فقط بخش کوچکی از کد رو دیدیم. اما برای اینکه ببینیم این کدها در یک برنامه کاربردی چطور کنار هم قرار می‌گیرن، بهتره یک نمونه کامل رو هم ببینیم. در ادامه، تمام کدهایی که در این مثال استفاده کردیم رو به همراه درایوی در متد اصلی (Main method) برای تولید خروجی‌های شکل‌گرفته (formatted output) می‌بینیم.

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

    /// 

    /// Demonstrates how to work with SqlCommand objects
    /// 
    class SqlCommandDemo
    {
        SqlConnection conn;

        public SqlCommandDemo()
        {
            // Instantiate the connection
            conn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
        }

        // call methods that demo SqlCommand capabilities
        static void Main()
        {
            SqlCommandDemo scd = new SqlCommandDemo();
            Console.WriteLine();
            Console.WriteLine("Categories Before Insert");
            Console.WriteLine("------------------------");

            // use ExecuteReader method
            scd.ReadData();

            // use ExecuteNonQuery method for Insert
            scd.Insertdata();

            Console.WriteLine();
            Console.WriteLine("Categories After Insert");

            Console.WriteLine("------------------------------");
            scd.ReadData();

            // use ExecuteNonQuery method for Update
            scd.UpdateData();
            Console.WriteLine();
            Console.WriteLine("Categories After Update");
            Console.WriteLine("------------------------------");
            scd.ReadData();

            // use ExecuteNonQuery method for Delete
            scd.DeleteData();
            Console.WriteLine();
            Console.WriteLine("Categories After Delete");
            Console.WriteLine("------------------------------");
            scd.ReadData();

            // use ExecuteScalar method
            int numberOfRecords = scd.GetNumberOfRecords();

            Console.WriteLine();
            Console.WriteLine("Number of Records: {0}", numberOfRecords);
        }

        /// 

        /// use ExecuteReader method
        /// 
        public void ReadData()
        {
            SqlDataReader rdr = null;

            try
            {
                // Open the connection
                conn.Open();

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

                // 2. Call Execute reader to get query results
                rdr = cmd.ExecuteReader();

                // print the CategoryName of each record
                while (rdr.Read())
                {
                    Console.WriteLine(rdr[0]);
                }
            }
            finally
            {
                // close the reader
                if (rdr != null)
                {
                    rdr.Close();
                }

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

        /// 

        /// use ExecuteNonQuery method for Insert
        /// 
        public void Insertdata()
        {
            try
            {
                // Open the connection
                conn.Open();

                // prepare command string
                string insertString = @"insert into Categories
                                        (CategoryName, Description)
                                        values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";

                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(insertString, conn);

                // 2. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
        /// 

        /// use ExecuteNonQuery method for Update
        /// 
        public void UpdateData()
        {
            try
            {
                // Open the connection
                conn.Open();

                // prepare command string
                string updateString = @"update Categories
                                        set CategoryName = 'Other'
                                        where CategoryName = 'Miscellaneous'";

                // 1. Instantiate a new command with command text only
                SqlCommand cmd = new SqlCommand(updateString);

                // 2. Set the Connection property
                cmd.Connection = conn;

                // 3. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        /// 

        /// use ExecuteNonQuery method for Delete
        /// 
        public void DeleteData()
        {
            try
            {
                // Open the connection
                conn.Open();

                // prepare command string
                string deleteString = @"delete from Categories
                                        where CategoryName = 'Other'";

                // 1. Instantiate a new command
                SqlCommand cmd = new SqlCommand();

                // 2. Set the CommandText property
                cmd.CommandText = deleteString;

                // 3. Set the Connection property
                cmd.Connection = conn;

                // 4. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        /// 

        /// use ExecuteScalar method
        /// 
        /// number of records
        public int GetNumberOfRecords()
        {
            int count = -1;
            try
            {
                // Open the connection
                conn.Open();

                // 1. Instantiate a new command
                SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);

                // 2. Call ExecuteScalar to send command
                count = (int)cmd.ExecuteScalar();
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return count;
        }
    }

توی برنامه بالا، شی SqlConnection داخل SqlCommandDemo نمونه‌سازی شده. این کار مشکلی ایجاد نمی‌کنه، چون وقتی Garbage Collector مربوط به CLR اجرا بشه، خود شی پاکسازی می‌شه. با این حال، نکته مهم اینه که بعد از تموم شدن کار، حتماً اتصال به پایگاه داده رو ببندیم. توی تمام متدهای این برنامه، اتصال داخل یک قطعه کد try برقرار شده و در بخش finally بسته می‌شه. متد ReadData محتوای ستون CategoryName رو در جدول دسته‌بندی (Category Table) نمایش می‌ده. از این متد چند بار استفاده می‌کنیم تا وضعیت فعلی جدول Category رو بعد از هر کدوم از فرمان‌های delete، insert و update ببینیم. به همین دلیل، استفاده دوباره از این متد کمک می‌کنه نتیجه و تأثیر هر عملیات رو راحت‌تر مشاهده کنیم.

خلاصه

شی SqlCommand بهت اجازه می‌ده از داده‌ها Query بگیری یا یک دستور رو به دیتابیس ارسال کنی. این شی متدهای مختلفی داره که هر کدوم برای نوع خاصی از دستورها استفاده می‌شن. متد ExecuteReader یک شی SqlDataReader برمی‌گردونه تا نتایج یک کوئری (select query) رو مشاهده کنیم. برای دستورهای Sql مربوط به insert، update و delete از متد ExecuteNonQuery استفاده می‌کنیم. اگه فقط به یک مقدار مجموع و واحد از یک Query نیاز داشته باشیم، ExecuteScalar گزینه مناسبیه.