سلام با آموزش برنامه نویسی سی شارپ قسمت یازدهم در خدمت شما عزیزان هستیم, در قسمت قبلی با اتصال به SQL Server در C# آشنا شدیم. با ادامه آموزش سی شارپ همراه ما باشید.
لينك قسمت اول : آموزش #C
اجرای دستورات پایگاه داده در سی شارپ (SqlCommand)
همانطور که پیش از این در آموزش پایگاه داده در سی شارپ گفتیم برای اجرای دستورات پایگاه داده بایستی پس از اتصال به پایگاه داده دستور مربوطه را در قالب یک شی SqlCommand از طریق اتصال ایجاد شده توسط SqlConnection اجرا کنیم. در این بخش از آموزش ADO.NET در سی شارپ به بررسی روش انجام این کار خواهیم پرداخت.
این درس درباره ی SqlCommand object بوده و نشان می دهد که برای تبادل داده و تعامل با یک پایگاه داده چگونه از این شی استفاده می شود. اهداف این درس به شرح زیر می باشند
شی SqlCommand به شما این امکان را می دهد تا شکل رفتار خود با پایگاه داده را مشخص نمایید. به عبارت دیگر مشخص کنید که چه نوع عملیاتی را می خواهید بر روی پایگاه داده انجام دهید. برای مثال، می توان عملیات delete، select، modify، و insert را بر روی رکوردهای داده در یک جدول پایگاه داده انجام داد. شی SqlCommand می تواند برای پشتیبانی از سناریوهای مدیریتِ داده هایی که در دسترس نیستند(disconnected data) مورد استفاده قرار گیرد، اما در این درس قصد بر این است که شی SqlCommand را به تنهایی مورد استفاده قرار دهیم. درس بعدی که در رابطه با SqlDataAdapter است، توضیح خواهد داد که چگونه یک Application را که از داده های Disconnected استفاده می کند، پیاده سازی نماییم. این درس همچنین چگونگی بازیابی یک مقدار واحد، مثل تعداد رکوردها در جدول را نشان می دهد.
مشابه دیگر شی های سی شارپ، یک شی SqlCommand به صورت زیر، ساخته می شود:
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
کد بالا برای شی های SqlCommandرایج می باشد. این کد یک آرگومان رشته ای (String Parameter ) را دریافت کرده که در بردارنده ی فرمانی است که در صدد اجرای آن بوده و نیز حاویِ اشاره گر (reference) به SqlConnection می باشد. SqlCommand دارای تعدادی نسخه ی Overload شده است که در طول این دوره آموزشی، عنوان خواهد شد.
به هنگام استفاده از SQL، با دستور select دسته ای از داده ها برای مشاهده بازیابی می گردند. برای تکمیل این کار با یک شی SqlCommand، از متد ExcecuteReader که شی 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 است که در ادامه ی این مبحث و در بخش جمع بندی به آن خواهیم پرداخت.
به منظور درج داده ها در پایگاه داده، از متدِ 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)) به عنوان اولین آرگومان سازنده ی SqlCommand، از یک متغیر به نام 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 است که در بخش جمع بندی بررسی خواهیم نمود.
متد 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) قرار می دهیم، اما این بار از یک سازنده ی متفاوتِ SqlCommand استفاده می کنیم که تنها دستور را دریافت می کند. در گام دوم، شی ِconn را به ویژگی (property) مربوط به شی cmd تخصیص می دهیم. این کار را می توانستیم به وسیله ی تابع سازنده ای (Constructor) که برای دستور Insert استفاده شد به همراه دو پارامتر ورودی نیز انجام دهیم. این روش در واقع به این امر اشاره دارد که می توان یک شی Connection (اتصال به پایگاه داده) را که برای یک فرمان خاص تخصیص یافته، در هر زمانی تغییر داد.
متد ExecuteNonQuery دستور یا عملیات Update را اجرا می کند. این کد بخشی از Update Data مربوط به لیست دستور 1 است که در بخش بعدی در مورد آن صحبت خواهیم کرد.
با استفاده از متد 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 بدون ارسال پارامتر ورودی به آن، استفاده شده است. در عوض، Property یا خصوصیت های CommandText و Connection از شی SqlCommand را به صورت صریح مقداردهی (set) کرده است. همچنین می توان از هر کدام از دو حالت overload شده سازنده SqlCommand قبلی که برای فرمان Insert و یا به روز رسانی استفاده شده اند، با نتایج یکسانی استفاده نمود. این مساله نشان می دهد که می توان در هر زمانی هم متن فرمان(Command Text) و هم شی اتصال (Connection Object) را تغییر داد. متد Execute Non Query، فرمان را به پایگاه داده انتقال می دهد. این کد بخشی از متد DeleteData است که در بخش بعدی درباره ی آن صحبت خواهیم کرد.
گاهی اوقات تمام آنچه که از پایگاه داده نیاز دارید یک مقدار واحد است که می تواند عبارت باشد از محاسبه (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 گرفتن از داده ها در سازنده ی SqlCommand، شمارشِ تمامی رکوردها از جدول دسته بندی (Category Table) را به دست می آورد. این Query تنها یک مقدار واحد را برمی گرداند. متد ExecuteScalar در گام دوم، این مقدار را باز می گرداند. از آنجایی که نوع خروجی ExecuteScalar گونه داده ای Object می باشد، از عبارت (int) پیش از آن برای تبدیل مقدار به مقدار عددی صحیح استفاده می گردد.
در بخش های قبلی، برای راحتی در نشان دادنِ تکنیک های کاربردی، بخش های کوچکی از کدها ارایه شد. برای مشاهده ی اینکه چگونه این کدها در یک برنامه ی کاربردی مورد استفاده قرار می گیرند، فراهم نمودن لیست کدهای کامل نیز مفید خواهد بود. در اینجا تمامی کدهای مورد استفاده در این مثال را به همراه درایوی در متد اصلی (Main method) برای تولید خروجی های شکل گرفته (formatted output)، نشان می دهد.
using System;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Demonstrates how to work with SqlCommand objects
/// </summary>
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);
}
/// <summary>
/// use ExecuteReader method
/// </summary>
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();
}
}
}
/// <summary>
/// use ExecuteNonQuery method for Insert
/// </summary>
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();
}
}
}
/// <summary>
/// use ExecuteNonQuery method for Update
/// </summary>
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();
}
}
}
/// <summary>
/// use ExecuteNonQuery method for Delete
/// </summary>
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();
}
}
}
/// <summary>
/// use ExecuteScalar method
/// </summary>
/// <returns>number of records</returns>
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 نمونه سازی شده است. این کار مشکلی ندارد زیرا زمانی که زباله جمع کنِ CLR اجرا می شود، خودِ شی پاکسازی خواهد شد. مهمترین مساله این است که پس از اتمام کار، حتما اتصال را قطع نماییم. در همه ی متدها، این برنامه اتصال را در یک قطعه کد (try block) try برقرار نموده و در یک final block قطع می نماید. متد ReadData، محتوای ستونِ CategoryName را در جدول دسته بندی (Category Table) نشان می دهد. برای نشان دادن وضعیت فعلی جدول دسته بندی (Category Table)، که پس از هر یک از فرمان های delete، insert، و update عوض می شود، بارها از این متد استفاده می نماییم. به همین دلیل، استفاده ی مجدد به منظور نشان دادنِ تاثیراتِ هر متد، کاری راحت خواهد بود.
شی SqlCommand به شما این اجازه را می دهد که از داده ها Query گرفته یا فرمانی را به دیتابس ارسال نمایید. این شی متدهایی دارد که برای فرمان های مختلفی تخصیص داده شده اند. متد ExecuteReader یک شی SqlDataReader را برای مشاهده ی نتایجِ یک کوئریِ (select query)، بر می گرداند. برای فرمان های Sql مربوط به insert، update، و delete، از متد ExecuteNonQuery استفاده می شود. اگر تنها به یک رقم مجموع و واحد از یک Query نیاز داشته باشیم، متد ExecuteScalar بهترین گزینه خواهد بود.
پايان قسمت یازدهمآموزش سي شارپ
