نگاهی به SqlReader

در اغلب مواردی که به فکر نوشتن یک برنامه برای اجرای یک کوئری بر روی پایگاه داده ها بودم می‌دیدم که معمولا تعداد خطوط این برنامه کمتر از 8 خط نمیشوند. این کار شامل یک روند تکراری به این شرح است ابتدا باید ارتباط را بازکنیم، سپس دستور Sql مورد نظر را ایجاد کرده و پس از آن از یک data reader استفاده کرده و آن را پیمایش میکنیم و در نهایت آن را بهمراه ارتباط مورد استفاده میبیندیم.
معمولا کدی که من نوشته ام چیزی شبیه به این بوده است:

List<string> result = new List<string>();
using (SqlConnection cn = new SqlConnection("Data Source=DatabaseServer;
Initial Catalog=Database;
User ID=UserID;Password=Password")) {
cn.Open();
using (SqlCommand cm = cn.CreateCommand()) {
cm.CommandText = "mystoredproc";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@param1", "1");
cm.Parameters.AddWithValue("@param2", "2");
using (SqlDataReader dr = cm.ExecuteReader()) {
if (dr.HasRows) {
while (dr.Read()) {
result.Add(dr.GetString(0));
}
}
}
}
}
return result;

نوشتن کدی اینچنین نه تنها خسته کننده و کسل کننده است بلکه زمان بر نیز هست. برای حل این مشكل من دو کلاس به نامهای SqlReader و SqlData نوشته ام.

کلاس SqlReader

این کلاس میتواند کوئری ها را با استفاده از دستورات متنی یا رویه ای اجرا کند. تفاوت این که نوع دستور، متنی است یا از نوع رویه ذخیره شده است به سادگی و با رعایت این موضوع مشخص میشود که اگر در رشته‌ای که بعنوان کوئری استفاده میشود فاصله وجود داشته باشد، دستور از نوع متنی است. در غیر این صورت دستور از نوع رویه ای خواهد بود.

اشیاء این کلاس توسط یک متد سازنده ایستا شبیه آنچه میبنید ایجاد میشوند:

SqlReader sr = SqlReader.Create("Data Source=DatabaseServer;Initial Catalog=Database;
User ID=UserID;Password=Password",
"select * from myTable");

این متد دو آرگومان میپذیرد. اولین آرگومان رشته ارتباطی یا همان connection string است. دومین آرگومان کوئری مورد نظر ماست.این کوئری میتواند یک دستور SQL را نام یک رویه ذخیره شده باشد:

SqlReader sr = SqlReader.Create("Data Source=DatabaseServer;Initial Catalog=Database;
User ID=UserID;Password=Password", "mystoredproc");

بازهم تاکید میکنم که کلاس SqlReader خودش تشخیص میدهد که آنچه شما خواسته اید یک رویه ذخیره شده است یا یک دستور SQL . کد این متد سازنده چنین خواهد بود:

/// <summary>
/// Creates a SqlReader object with CommandType of Text
/// </summary>
/// <param name="connectionString">The connection string to use to connect to
/// the server.&gt/param>
/// <param name="commandText">The text to execute on the SQL server.&gt/param>
public static SqlReader Create(string connectionString, string commandText) {
SqlReader sr = new SqlReader();
sr._connectionString = connectionString;
sr._commandText = commandText;
if (commandText.Trim().Contains(" "))
sr._commandType = CommandType.Text;
else
sr._commandType = CommandType.StoredProcedure;
return sr;
}

دقت کنید که commandtype با توجه به متن کوئری تعیین شده است. چنانچه در این متن حداقل یک فاصله وجود داشته باشد نوع دستور، متنی خواهد بود.

برای افزدون پارامترها نیز از ترفند زیر استفاده میکنیم:

SqlReader sr = SqlReader.Create("Data Source=DatabaseServer;Initial Catalog=Database;
User ID=UserID;Password=Password", "mystoredproc")
.AddParam("@param1", "1").AddParam("@param2", "2");

اکنون ما 2 پارامتر به کوئری مورد نظرمان اضافه کرده ایم. هر متد AddParam یک شیء جدید SqlReader برمیگرداند. بنابراین با یک سیکل بازگشتی، هر تعداد پارامتری که بخواهیم میتوانیم داشته باشیم.

حال برای اجرای این دستور دو را پیش رو داریم. ExecuteQuery() و ExecuteNonQuery(). این دو که همنام متدهایی در کلاس SqlCommand هستند در نوع مقدار بازگشتی شان متفاوت هستند. ExecuteQuery() یک شی از کلاس SqlData برمیگرداند در حالی که ExecuteNonQuery() تنها یک void برمیگرداند.

SqlData sd = SqlReader.Create("Data Source=DatabaseServer;Initial Catalog=Database;
User ID=UserID;Password=Password", "mystoredproc")
.AddParam("@param1", "1").AddParam("@param2", "2")
.Execute();

دقت کنید که من نوع متغیر جدیدمان به نام sd را از نوع SqlData تعیین کرده ام. این به این خاطر است که متد Execute() یک شیء SqlData برمیگرداند، نه یک شیء SqlReader. دلیل این کار را نیز بعدا خواهم گفت.

کلاس SqlData

هر سطر از یک کوئری SQL در یک کلاس SqlRow ذخیره میشود که مقادیر و ستونهای هر سطر را نگهداری میکند. یک مجموعه از این سطرها کلاس SqlData را تشکیل میدهند. این کلاس متدهای متعددی برای دستیابی به داده ها دارد که بازنویسی شده اند نظیر : GetValueList, GetStringList, GetInt32List, GetInt64List, GetValue, IsNull, GetString, GetInt32 و GetInt64.

دستورات لیست نام یک ستون یا شماره آن را گرفته و لیستی از تمام مقادیر درون یک ستون بخصوص را برمیگردانند. این مورد برای پردازش حلقه ها بسیار مفید است. مثلا به کد زیر دقت کنید:

foreach (string s in SqlReader.Create("Data Source=DatabaseServer;
Initial Catalog=Database;User ID=UserID;Password=Password",
"mystoredproc").AddParam("@param1", "1").AddParam("@param2", "2")
.Execute().GetStringList(0))
Console.WriteLine(s);

این کد باعث میشود زمانی که mystoredproc را با پارامترهای @param1 با مقدار 1 و @param2 با مقدار 2 اجرا شود، در خروجی، هر رشته از سمت چپ ترین ستون بازگشت داده شده نوشته شود.

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

static void Main(string[] args) {
SqlData sd =SqlReader.Create("Data Source=DatabaseServer;Initial Catalog=Database;
User ID=UserID;Password=Password", "mystoredproc")
.AddParam("@param1", "1").AddParam("@param2", "2")
.Execute();
for (int i = 1; i < sd.RowCount; i++) {
Console.WriteLine(string.Format("Data from column 1: {0},
Data from column 2: {1}", sd.GetString(i, 0),
sd.GetString(i, 1)));
}
}

کوئری - بخش اصلی کلاس SqlReader

اگر آنچه که گفتیم را تاکنون متوجه شده باشید، الان به بخش جالب ماجرا نزدیک هستید! بخش اصلی کلاس SqlReader متد execute است.اینجاست که همه کارهای جالب ماجرا انجام میگیرد!

/// <summary>
/// Executes a query.
/// </summary>
public SqlData Execute() {
if (_hasRun)
_sqlData = new SqlData();
_hasRun = true;
if (string.IsNullOrEmpty(_connectionString))
throw new Exception("Morton's Common Tasks - SqlReader -
No Connection String Entered");
if (string.IsNullOrEmpty(_commandText))
throw new Exception("Morton's Common Tasks - SqlReader -
No Command to Perform");
using (SqlConnection cn = new SqlConnection(_connectionString)) {
cn.Open();
using (SqlCommand cm = cn.CreateCommand()) {
cm.CommandType = _commandType;
cm.CommandText = _commandText;
foreach (KeyValuePair<string, object> kvp in _parameters)
cm.Parameters.AddWithValue(kvp.Key, kvp.Value);
if (_returnData) {
using (SqlDataReader dr = cm.ExecuteReader()) {
if (dr.HasRows) {
while (dr.Read()) {
SqlRow sqlRow = new SqlRow();
for (int i = 0; i < dr.FieldCount; i++) {
if (string.IsNullOrEmpty(dr.GetName(i)))
sqlRow.AddSqlData("column_" + i.ToString(),
dr.GetValue(i));
else
sqlRow.AddSqlData(dr.GetName(i).ToLower(),
dr.GetValue(i));
}
_sqlData.Add(sqlRow);
}
}
}
} else {
cm.ExecuteNonQuery();
}
}

}
return this.Result;
}

اولین دو خط کد بررسی میکنند که ببینند آیا کوئری مورد نظر اجرا شده است یا خیر. اگر شده است، سپس ما نیاز داریم تا مقادیر بازگشی را مجددا تنظیم کنیم. در صورتی که کوئری یا رشته ارتباط ما null باشند، ما exceotion هایی ایجاد خواهیم نمود. در غیر این صورت، نسبت به اجرای موئری اقدام خواهیم کرد.

متغیر _returnData به طور پیش فرض true است اما ممکن است توسط متد ExecuteNonQuery() تبدیل به false شود. چنانچه _returnData برابر true باشد، آنگاه ما بدنبال نتایج خواهیم گشت.در این صورت، یک شیء SqlDataReader ایجاد خواهیم کرد و همه داده هایمان را به SqlRow اضافه خواهیم کرد و پس از آن همه سطرهایمان را به شیء SqlData اضافه میکنیم.

چنانچه ستونی بدون نام باشد، نام ستون به صورت پویا با یک نام قراردادی پر میشود. مثلا column_1, column_2 و ... . در نهایت نتایج بازگشت داده خواهند شد. خط return this.Result; از ویژگی زیر برای بازگرداندن شیء SqlData استفاده میکند:

/// <summary>
/// An object returning the result of a Sql Query.
/// </summary>
public SqlData Result {
get {
return _sqlData;
}
}