روش استفاده از 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 , SqlDataReader
فرض کنیم که یک 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 بود. خوبه که کمی به کد توجه کنیم.
3 ضمیمه
فراخوانی اطلاعات از Sql به صورت Asyncronize
خوب حالا یکم حرفه ای ترش کنیم.
شاید موقعی که دارین نرم افزار هاتون رو تست می کنین دیده باشین که موقعی که داره از 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 نشه امکان بستن فرم رو نداریم.
استفاده از Transaction در بانک های اطلاعاتی
بحث بعدی در مورد 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 تمامی تغییرات رو کنسل نمائید.
نقل قول: روش استفاده از SQL Command
نقل قول:
3. اینکه احتمال سوء استفاده از دستورات SqlServer یا اصطلاحا Sql Injection رو به حداقل می رسونن.
در مورد سومی بعدا بیشتر توضیح می دم
لطفا در مورد آن توضیح دهید