روش استفاده از SQL Command

سلام بر همه.
مطالب این پست حتما به صورت پراکنده در جایجای این انجمن مطرح شده است. اما به هر حال به نظرم رسید شاید اگر این مطالب به صورت یکجا و کنار هم باشه به درد بخوره. ضمنا این مطالب توسط خودم قبلا در developercenter.ir نیز در اختیار عموم قرار گرفته بودند.

همانطور که می دونین کلاس SqlCommand برای اجرای دستورات روی بانک اطلاعاتی شما ایجاد شده. شما می توانید از این کلاس object ساخته و دستورات خود رو روی بانک اجرا و نتیجه مورد نظر رو داشته باشین.

برای اجرای هر دستور شما باید یک Connection باز داشته باشین. یعنی هر وقت که میخواهین از این کلاس و متد های Execute ی که داری استفاده کنین مطمئن باشین که Connection ی که دارین باز است.

همچنین این کلاس برای اجرای دستورات شما به چهار متد مجهز می باشد.

ExecuteNonQuery()

ExecuteReader()

ExecuteScaler()

ExecuteXmlReader()

خوب بسته به نوع دستوراتی که دارین می تونین از یکی از این دستورات برای اجرای دستور مورد نظر استفاده کنین.

ExecuteNonQuery
با استفاده از این دستور شما می توانید بعد از اجرای دستور خود به تعداد رکورد هایی که در اثر اجرای دستور شما دچار تغییر شده اند یا اصطلاحا (RowAffected) پی ببرین.
دستورات Insert, Update, Delete نمونه های استفاده از این دستور هستند
مثال:

SqlConnection cnn = new SqlConnection(ConnectionString);

SqlCommandcnn = new SqlCommand("Delete from tblPerson where id=1",cnn);

cnn.Open();

int rowAffected = cmd.ExecuteNonQuery();

حالا شما می توانید مطمئن بشین که چند رکورد بانک شما حذف شده است.


ExecuteReader
همون طور که از اسمش برمیاد این دستور برای خواندن اطلاعات به صورت ReadOnly و ForwardOnly مورد استفاده قرار میگیره. خروجی این دستور یک object از جنس کلاس SqlDataReader هست که اصولا برای این منظور ساخته شده. بسیار سریع و در مواقع خواندن کاملا پر کاربرد می باشد.

SqlConnection cmd = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand("Select * from tblPerson",cnn);

cnn.Open();

SqlDataReader reader = cmd.ExecuteReader();

List<Person> list = new List<Person>();

while(reader.Read())

{

Person p = new Person();

if(reader[0] != DbNull.Value)

    p.Name = reader[0].ToString();

if(reader[1] != DbNull.Value)

    p.Family = reader[1].ToString();

if(reader[2] != DbNull.Value)

    p.Age = int.Parse(reader[2].ToString());

list.Add(p);

}

cnn.Close();

نکته ای که باید بهش توجه داشته باشین اینه که وقتی دارین اطلاعات رو می خونین نمی تونین کانکشنتون رو ببندید و در حالت پیشفرض این کاننکش تا تمام شدن خواندن اطلاعات برای دیگران قابل استفاده نیست.

ExecuteScaler
این دستور در مواقعی استفاده می شود که خروجی مورد نظر شما از دستورات یک مقدار واحد باشه. مثلا موقعی که می خواهیم تعداد رکورد ها رو بگیریم. البته در صورتیکه دستور اجرای شمابیشتر از یک مقدار بر گردونه فقط یک موردش در اختیار شما خواهد بود. اولین ستون از اولین سطر.

SqlConnection cnn = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand("Select count(*) from tblPerson",cnn);

cnn.Open();

int Count = (int)cmd.ExecuteScaler();

// دلیل استفاده از Casting به این جهت است که خروجی متد شما object بر میگرداند.

cnn.Close();

ExecuteXmlReader
در مواردی استفاده می شود که شما می خواهین اطللاتتون از بانک به صورت Xml ایجاد شود. مثلا
‍

SqlCommand command = new SqlCommand("SELECT * FROM dbo.Customers FOR XML AUTO, XMLDATA", SqlConn);

این متد یک object از جنس XmlReader در اختیار شما قرار می دهد که می توانید ساختار Xml خروجی را بررسی و نمایش دهید.

در پست بعدی بیشتر در مورد این کلاس صحبت خواهم کرد.

اما بریم سراغ اینکه حالا چطور می شه با استفاده از SqlCommand به جای استفاده مستقیم از دستورات Sqlی از StoredProcedure ها استفاده کرد. اما قبلش بذارین کمی توضیح بدم که اصلا چرا؟

1. اینکه دستورات StoredProcedure در بانک اطلاعاتی SqlServer هستند و موقع ایجادشون Parse می شوند به این معنا که شما خیالتون از بابت Syntax راحته.
2. اینکه به جهت اینکه Stored Procedure ها در بانک اطلاعاتی Compile می شوند سرعت اجراشون بیشتره.
3. اینکه احتمال سوء استفاده از دستورات SqlServer یا اصطلاحا Sql Injection رو به حداقل می رسونن.

در مورد سومی بعدا بیشتر توضیح می دم.
فرض کنید که یک جدول داریم به نام tblPerson و داخلش 4 تا فیلد داریم. فیلد اول یا همون Id به صورت اتوماتیک (Identity)خواهد بود. برای اجرای Stored Procedure ها شما یه Procedure شبیه به این رو در نظر بگیرین.

Create procedure spInserPerson

(

@Id [bigint] =null output ,

@FirstName     [nvarchar](100),

@LastName     [nvarchar](50),

@Age [int]

)

as

Insert into tblPerson (FirstName,LastName,Age) Values (@FirstName,@LastName,@Age)

Set @Id = Scope_Identity()

در مورد خود procedurی نوشتم این توضیح رو بدم که چهار تا Parameter داره که اولیش رو ما مقدار دهی نمی کنیم بلکه مقدار ازش می خوانیم. مقدارش توسط functionی به نام Scope_Identity() پر خواهد شد. این function آْخرین کد Identity تولید شده رو برای ما بر می گرداند. یعنی همونی که الان Insert شده.

اما بریم سراغ کد نویسی C#‎
خوب ما باید اول مشخص کنیم که می خواهیم StoreProcedure استفاده کنیم که این کار رو با استفاده از CommandType انجام می دیم.
دوم باید Parameter ها رو محیا و بعد از از اولیش مقدار بگیریم.

SqlConnection cnn = new SqlConnection(ConnectionString);

SqlCommand cmd = new SqlCommand("spInsertPerson",cnn);

cmd.CommandType = CommandType.StoredProcedure;



cmd.Parameters.Add("@Id",SqlDbTypes.Int).Direction = ParameterDirection.Output;

cmd.Parameters.Add("FirstName",SqlDbType.NVarchar)  .Value = "Ali";

cmd.Parameters.Add("LastName",SqlDbType.NVarchar).Value = "Rezaei";

cmd.Parameters.Add("Age",SqlDbType.Int).Value = 10;

تا اینجا رو توضیح بدم که شما در Constructor نام StoredProcedure مورد نظرتون رو مشخص کردین. البته می تونستین این کار رو از طریق Property که به نام CommandText وجود داره هم انجام بدین.

بعد اومیدیم چهار تا Parameter بهش اضافه کردیم که اولیش با بقیه یه مقداری فرق داشت اونم به این ترتیب که اولیش خروجی داره نه ورودی. این رو با استفاده از Direction مشخص کردیم. برای مقدار دهی ما بقی Parameter ها هم از Value استفاده کردیم.

cmd.ExecuteNonQuery();

int Id = (int)cmd.Parameters["Id"].Value;

حالا دستور رو اجرا کردیم. نکته ای که وجود داره اینه که هیچ تفاوتی بین اجرای دستورات از طریق کد مستقیم SQL و یا StoredProcedure نداره. پس تمامی مسائلی که توی پست قبلی گفتم صدق می کنه و من از ExecuteNonQuery استفاده کردم.
بعد هم برای گرفتن مقدار پارامتر از Value استفاده کردم. دقت کنین که این رو بعد از اجرا نوشتم. و هنوز Connection من بسته نشده است.
و در نهایت:

cnn.Close();

MessageBox.Show(string.Format("New Person Inserted with ID : {0}",Id));

این داستان همچنان ادامه دارد ....

فرض کنیم که یک StoreProcedure شبیه این داریم.
کد:
Create Procedure spSelectTables
as
Select * from tblPerson
Select * from tblEmp
حالا من می خواهم که با استفاده از یک SqlDataReader‌ تمامی رکورد های هر دو جدول رو بخوانم.

کد:

     SqlConnection cnn = new SqlConnection(connectionString);

SqlCommand  cmd = new SqlCommand("spSelectTables",cnn);

cmd.CommandType = CommandType.StoredProcedure;

cnn.Open();

SqlDataReader reader = cmd.ExecuteReader();

do

{

while(reader.Read())

{

lstList.Items.Add(reader.GetString(0) + "\t" + reader.GetString(1));

}

}while(reader.NextResult());

cnn.Close(); 

این سوال MCTS Windows بود. خوبه که کمی به کد توجه کنیم.

خوب حالا یکم حرفه ای ترش کنیم.
شاید موقعی که دارین نرم افزار هاتون رو تست می کنین دیده باشین که موقعی که داره از Sql Server‌ اطلاعات میاره اگه یکم تعداد رکورد ها زیاد باشه نرم افزارتون Freeze میشه و به نظر میرسه که قفل شده. البته دلیلش کاملا واضحه. مثلا موقعی که می خواهید یه Grid رو بوسیله یک DataSet که با SqlDataAdpater پر میشه نشون بدین این اتفاق رو باید دیده باشین. (تصویر 1)

برای اینکه این اتفاق نیافته و توی اون مدت زمانی که طول میکشه تا نرم افزارتون اطلاعات رو از بانک بیاره کار معمولش رو هم ادامه بده باید از روش asynchronous programming استفاده کنین. به این معنی که درخواست آوردن رکورد ها رو توی یک Thread دیگه انجام بدیم. و به محض اینکه جواب اومد اونوقت نمایش بدیم.

برای این موضوع در کلاس SqlCommnad به ازاء سه تا از متد های که در ابتدای این نوشتار توضیح دادیم یک متد متناظر با پیشوند Begin.... وجود داره و همین طور یک متد متناظر با پسوند End.... یعنی:
کد:

     BeginExecuteNonQuery() 

BeginExecuteReader()

BeginExecuteXmlReader()

EndExecuteNonQuery()

EndExecuteReader()

EndExecuteXmlReader() 

اما این متد ها چیست و به چه درد می خوره؟

خوب فرض کنین که شما یک فرم داریم که داخلش یک Grid‌ داره . حالا من می خوام موقعی که Form_Load اتفاق میافته گرید من هم پر بشه و نمایش داده بشه. حالا اگه اینکار برای تعداد رکورد های زیادی انجام بشه اونوقت یکم زمان می بره. کاری که می خوام انجام بدم اینکه فرم من بالا بیاد و بعد داخل یک Thread دیگه پر کردن اطلاعات برای نمایش انجام بشه. هر وقت که کار پر کردن تمام شد اونوقت نشونشون بدم.

نمونه کد رو به پیوست براتون میذارم. فقط چند تا نکته هست که باید توضیح بدم.
وقتی شما دارین از BeginExecute استفاده می کنین باید دقت کنین که نتیجه رو در همان لحظه به شما نمی دهد. در عوض از طریق یک Delegate که یک متد بدون خروجی و با یک Parameter از نوع IAsyncResult شما باید EndExecute رو فراخوانی کنین و نتیجه رو از طریق EndExecute... بگیرین. (تصویر 2)

نکته آخر داشت یادم می رفت اینکه حتما باید در ConnectionString تون امکان استفاده از Asyncronize method ها رو با اضافه کردن Asynchronous Processing=true فعال کنین.

توی این مثال برای اینکه نشون بدم Async چیست یک کلید Add داریم که همزمان با اجرای SqlCommand مون میتونه به لیست اضافه کنه. و یک Label هم وضعیت Command رو نشون میده. و تا موقعی که دوباره Ready نشه امکان بستن فرم رو نداریم.

بحث بعدی در مورد SqlConnection مربوط به Transaction هاست. به این معنی که گاهی مواقع لازم است که یک سری کار ها رو پشت سره هم اجرا بشن و در صورتیکه یکی از کار ها درست انجام نشد مابقی هم به حالت اولیه برگردد. به عنوان مثال وقتی شما می خواهین یک فاکتور رو ثبت کنین باید تمامی قلم های فاکتور نیز ثبت شوند و در صورتیکه حتی یکی از آنها انجام نشد باید ما بقی نیز RollBack شوند.

در دات نت ما یک کلاس داریم به نام SqlTransaction. برای ایجاد یک SqlTransaction شما باید وقتی Connection خود را باز کردید. با استفاده از دستور BeginTransaction روی object connection خود یک SqlTransaction ایجاد کنین:
کد:

     SqlConnection cnn = new SqlConnection(ConnectionString);

SqlTransaction trans = null;

try

{

cnn.Open();

trans = cnn.BeginTransaction();

SqlCommand cmd = new SqlCommand("spInsertFactor",cnn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Transaction = trans;

/// rest of command configuration such as parameters and ....

cmd.ExecuteNonQuery();

trans.Commit();

‌}

catch(Exception ex)

{

if(trans != null)

trans.RollBack();

} 

شما می توانید با استفاده از دستور Commit تمامی دستوراتی که از لحظه BeginTransaction تا فراخوانی Commit اجرا شده اند رو اعمال و یا با استفاده از RollBack تمامی تغییرات رو کنسل نمائید.

نقل قول:

---

3. اینکه احتمال سوء استفاده از دستورات SqlServer یا اصطلاحا Sql Injection رو به حداقل می رسونن.

در مورد سومی بعدا بیشتر توضیح می دم

---

لطفا در مورد آن توضیح دهید