یکی از دغدغه های یک برنامه نویس صرفه جویی در کد نویسی میباشد که یکی از مصادیق این صرفه جویی نوشتن کدهای SQL جامع میباشد. دو تکنیک در StoredProcedur نویسی را در زیر مشاهده میکنیم. به نظرم آمد اگر مستقیماً از هر تکنیک مثالی آورده شود بیان مطلب برای نویسنده و درک مطلب برای خواننده راحت تر میباشد:

 

1-     StoredProcedure با پارامترهای پویا

صورت مسئله: درپایگاه داده ای با نام university دریک جدول به نامTfardi   1000رکورد داریم و هر رکورد شامل "name"، "family"، "city"، "gender"، "CodeMelli"،"active" می باشد. در "صفحه"ی UserIntarface هم 5فیلد متناظر و همنام با 5فیلد اول جدول داریم، فیلد "active" نیز یک یا صفر بودنش نشانگر اعتبار یا عدم اعتبار آن رکورد است. میخواهیم عمل جستجو بین این رکوردها را با پارامترهای کاملاً دلخواه که در داخل فیلدهای UserInterFace مینویسیم انجام دهیم. مثلاً:

(افرادی که شهرشان "همدان" و جنسیتشان "مذکر" میباشد.) یا (افرادی که نامشان "علی" و جنسیت "مونث دارند!!!") یا (همه افراد) یا (آنها که در کد ملی شان 387 دارند) یا (پیدا کردن شخصی با نام "امید" و فامیلی"وثاقی") یا ...

راه حل اول: ترکیبهای ممکن از پارامترها را در نظر میگیریم(در این برنامه 2⁵=32حالت داریم) و از 32 عدد عبارت شرطی IF استفاده میکنیم.(عذاب آور بودن این راه حل را با 5پارامتر تجربه کنید)

راه حل دوم: ساخت یک StoredProcedure پویاست که به تناسب پارامترهای داده شده gnerate میشود. روش این کار را در زیر میبینیم.( کدها در visualStodio2005-C# میباشند)

 

الف) از منوی Standard مسیر زیر را دنبال میکنیم:

Website\ Add Reference \Browse

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

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder\Microsoft.SqlServer.ConnectionInfo.dll

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder\Microsoft.SqlServer.Smo.dll

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder\ Microsoft.SqlServer.SqlEnum.dll

دو خط زیر را در قسمت کد، بالای صفحه ی مورد نظر مینویسیم.

Using Microsoft.SqlServer.Management.SMO

Using Microsoft.SqlServer.Management.Common

با فشردن دگمه ی"ُsearch" تابع زیر اجرا میشود¹:

protected void Button1_Click( sender, EventArgs e)

    {

        string mySelectCommand;

        Server srv = new Server(".\\SQLEXPRESS");

        Database myDB = srv.Databases["university.mdf"];

        StoredProcedure sp_deleted = myDB.StoredProcedures["SPSearchFardi", "dbo"];

        sp_deleted.Drop();

        StoredProcedure sp = new StoredProcedure(myDB, "SPSearchFardi");

        sp.TextMode = false;

        sp.AnsiNullsStatus = false;

        sp.QuotedIdentifierStatus = false;

        mySelectCommand = "select * from Tfardi where acive=1";

        if (name.Text != "")

        {

            StoredProcedureParameter name = new StoredProcedureParameter(sp, "@name", DataType.NVarChar(50));

            sp.Parameters.Add(name);

            mySelectCommand = mySelectCommand + " and name=@name";

        }

        if (family.Text != "")

        {

            StoredProcedureParameter family = new StoredProcedureParameter(sp, "@family", DataType.NVarChar(50));

            sp.Parameters.Add(family);

            mySelectCommand = mySelectCommand + " and family=@family";

        }

       

        if (CodeMelli.Text != "")

        {

            StoredProcedureParameter CodeMelli = new StoredProcedureParameter(sp, "@CodeMelli", DataType.NVarChar(10));

            sp.Parameters.Add(CodeMelli);

            mySelectCommand = mySelectCommand + " and CodeMelli like "%"+" + "@CodeMelli" + "+"%"";

        }

        if (city.Text != "")

        {

            StoredProcedureParameter city = new StoredProcedureParameter(sp, "@city", DataType.NVarChar(50));

            sp.Parameters.Add(city);

            mySelectCommand = mySelectCommand + " and city=@city";

        }

        if (gender.SelectedValue == "f") mySelectCommand = mySelectCommand + " and gender="f"";

        else if (gender.SelectedValue == "m") mySelectCommand = mySelectCommand + " and gender="m"";

   

    sp.TextBody = mySelectCommand;

    sp.Create();

    myDB.Refresh();

}

  

   

   

 

2-Select همه کاره

برای نوشتن Select از Table های مختلف نیازی به نوشتن چندین select نداریم. بلکه میتوان با قطعه کد زیر که در StoredProcedure آن را مینویسیم یک Select را برای چندین Table استفاده کرد. در کد زیر نام جدول را به عنوان پارامتر به StoredProcedure میدهیم:

create PROCEDURE dbo.SelectPrQu (TableName NVARCHAR(50))

AS

 

DECLARE @SelectString NVARCHAR(200)

-- ساخت یک دستور به صورت پویا

SET @SelectString = "SELECT * FROM " +@TableName

EXEC sp_executesql @SelectString

 

واضح است که میتوانیم در کنار پارامتر "TableName" پارامترهای دیگری را نیز بکار برد.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

پانویس1- هنگام استفاده از این تکنیک همه ی Connection ها باید به صورت سنتی باشند یعنی فرمتشان مثل Connection زیر باشد:

server=.\SQLEXPRESS;uid=sa;pwd=;database=UNIVERSITY.MDF;Integrated Security=True

و از Connection های به فرمت زیر استفاده نشود:

Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\university.mdf; Integrated Security=True; User Instance=True

همچنین "SQL Server Management Studio Express" یا همان "SSMSEE" را نیز نصب کنید.

محسن حمدیه ::: یکشنبه 87/1/4::: ساعت 9:47 صبح

محسن حمدیه
پی‌نوشت، پانوشت، یا پانویس شرح یا ارجاعی است که در حاشیه متن جای می‌گیرد و ممکن است با عدد یا نشانه‌هایی نظیر ستاره مشخص گردد و در متن نیز به آن رجوع داده شود. پانویس، در واقع، بخشی از نوشته است که برای دادن اطلاع بیشتر یا اعتبار بخشیدن به نوشته فراهم می‌شود؛ اگرچه جزء ضروری نوشته تحقیقی است ولی ماهیتآ به‌گونه‌ای است که نمی‌توان آن را در متن نوشته جای داد.... (محسن حمدیه)