PDA

View Full Version : آموزش: جستجوی پیشرفته توسط استورپروسیجر stored procedure



Mahmoud.Afrad
یک شنبه 17 اردیبهشت 1391, 23:37 عصر
با سلام.


در این تاپیک قصد دارم انجام جستجوی پیشرفته را با پیاده سازی توسط StoredProcedure توسط یک مثال عملی توضیح بدهم. پس تمام روند براساس فرض زیر است.


دیتابیس testDB و با جدولی به نام TBL را در نظر می گیریم.
جدول:
CREATE TABLE [dbo].[Tbl] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[FirstName] NVARCHAR (50) NULL,
[LastName] NVARCHAR (50) NULL,
[Rank] INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
با توجه به اینکه دستور مربوط به جستجوی پیشرفته شکل ثابتی ندارد و در هنگام اجرا تعیین میشود، معمولا برای انجام این کار در C#‎‎‎‎‎‎‎‎‎‎‎‎‎‎ کوئری sql مربوط به جستجوی پیشرفته را برحسب موارد دخیل در جستجو، ساخته و بعد آنرا اجرا میکنند اما با توجه به تمام محاسن StoredProcedure ها ، از انجام آن به خاطر حالات مختلف بوجود آمده برای جستجو و مشکلاتی که برای ساخت کدهای sql بوجود می آید ، از آن صرف نظر میشود.


انجام جستجوی پیشرفته بدون StoredProcedure و ساختن کوئری با سی شارپ و اجرای آن (البته من روش زیر را توصیه میکنم) (http://barnamenevis.org/attachment.php?attachmentid=87599&d=1338229193)





اگر میشد همان کاری را که در C#‎‎‎‎‎‎‎‎‎‎‎‎‎‎ برای تولید کوئری جستجوی پیشرفته انجام میدهیم در استورپروسیجر انجام داد کار تمام است.


ما در این جا میخواهیم همین کار را انجام دهیم.

خوب برای انجام کار ابتدا یک StoredProcedure به صورت زیر ایجاد کنید.
بعد از تعیین نام باید تمام پارامترهایی که در جستجو میتوانند دخیل باشند را تعریف میکنیم. نوع پارامترها همان انواعی هست که در جدول تعریف شده اند.
CREATE PROCEDURE [dbo].[SP_AdvancedSearch]
@FirstName Nvarchar(50) ,
@LastName Nvarchar(50) ,
@Rank Int
AS
SELECT *
FROM Tbl
WHERE
FirstName LIKE ISNULL(@FirstName , FirstName) AND
LastName LIKE ISNULL(@LastName , LastName) AND
[Rank] LIKE ISNULL(@Rank , [Rank] )
RETURN 0
روند سلکت بدین صورت است که در تابع ISNULL در صورتی که مقداری(غیرnull) به پارامترهای پروسیجر ارسال شده باشد، این تابع آن مقدار را برمیگرداند و در صورتی که مقدار null به پارامتر ارسال شده باشد ، مقدار آرگومان دوم را برمیگرداند که ما در اینجا نام همان ستونی را مینویسیم که میخواهیم شرط روی آن ستون بررسی شود.
برای مثال، در مورد شرط
FirstName LIKE ISNULL(@FirstName , FirstName)
اگر مقداری غیرnull به پارامتر @FirstName ارسال شود، شرط بالا مانند زیر خواهد بود:
FirstName LIKE @FirstName
و اگر null را به این پارامتر ارسال کنیم، شرط به صورت زیر در خواهد آمد
FirstName LIKE FirstName
و از آنجایی که این شرط همواره درست است(هر مقداری با خودش برابر و شبیه است) پس در کل بی اثر خواهد بود و این همان چیزی است که زمانی که مقداری توسط کاربر وارد نشود باید رخ دهد.


خب تعریف استورپروسیجر تمام است و آنرا ایجاد کنید. کار در این قسمت تمام است.


در برنامه میخواهیم براساس ترکیبی از سه فیلد نام و نام خانوادگی و رتبه (با توجه به ستونهای جدول) جستجو انجام دهیم.

به ازای هر کدام از پارامترهای ورودی پروسیجر باید یک پارامتر ایجاد کرده در صورتی که مقداری وارد شده بود(توسط کاربر) آنرا به عنوان Value پارامتر در نظر گرفته و در صورتی که مقداری وارد نشده بود DBNull.Value را به پارامتر نسبت میدهیم.

برای دکمه جستجو در فرم این کدها را قرار داده ام:
private void btnSearch_Click(object sender, EventArgs e)
{
string firstName = txtFirstName.Text.Trim();
string lastName = txtLastName.Text.Trim();
string rank = txtRank.Text.Trim();

_cmd.CommandText = "SP_AdvancedSearch";
_cmd.CommandType = CommandType.StoredProcedure;

_cmd.Parameters.Clear();

SqlParameter firstNameParameter = new SqlParameter
{
ParameterName = "@FirstName",
Value = string.IsNullOrEmpty(firstName) ? (object) DBNull.Value : "%" + firstName + "%"
};
_cmd.Parameters.Add(firstNameParameter);

SqlParameter lastNameParameter = new SqlParameter
{
ParameterName = "@LastName",
Value = string.IsNullOrEmpty(lastName) ? (object) DBNull.Value : "%" + lastName + "%"
};
_cmd.Parameters.Add(lastNameParameter);

SqlParameter rankParameter = new SqlParameter
{
ParameterName = "@Rank",
Value = string.IsNullOrEmpty(rank) ? (object) DBNull.Value : rank
};
_cmd.Parameters.Add(rankParameter);

_dtResult.Clear();
try
{
_da.Fill(_dtResult);
dgvResult.DataSource = _dtResult;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

علامت % به ابتدا و انتهای مقادیر افزوده شده اند تا کلمات شامل متن ورودی را نیز نمایش دهند.

در انتها سورس همین توضیحات رو قرار میدم.
فقط در نظر داشته باشید در صورت عدم اجرای پروژه میتونید دیتابیسی به پروژه اضافه و اسکریپت جدول را در آن اجرا و در نهایت کانکشن را اصلاح کنید.

samadblaj
شنبه 28 مرداد 1391, 14:38 عصر
سلام جناب Mahmoud.Afrad منم قبلا برای کار با دیتابیس از روش Stored Procedure استفاده میکردم...در حال حاظر هم دارم یه کتاب Ling entity رو مطالعه میکنم که روش های حرفه ای برای جستجو داره...
بازم ممنونم از زحماتی که برای این آموزش کشیدی ، اینجور آموزش هایی رو خیلی دوست داره به صورت مقاله.
موفق باشید./

orca13
جمعه 18 بهمن 1392, 15:33 عصر
سلام،
من سینتکس تعریف پروسیجر رو دقیق نفهمیدم.
میشه یه توضیح بدید؟

(البته من جواب تاپیک خودم رو ازون روش دیگه، بدون StoreProducer، گرفتم، اما این بهتره، همون طور که خودتون هم این روش رو پیشنهاد کردین)

Mahmoud.Afrad
جمعه 18 بهمن 1392, 16:42 عصر
سلام،
من سینتکس تعریف پروسیجر رو دقیق نفهمیدم.
میشه یه توضیح بدید؟

تابع ISNULL در صورتی که آرگومان اول مقدار داشته باشه خودش و اگر مقدار نداشته باشه مقدار دوم یعنی در اینجا مقدار داخل ستون رو برگشت میده. یعنی در صورتی که مقداری ارسال بشه مورد جستجو قرار میگیره و اگر مقدار null ارسال بشه ، مقدار موجود در فیلد برگشت داده میشه و در این صورت اون ستون در جستجو شرکت داده نخواهد شد.
مثلا where (name like isnull(@name , name + %) در صورتی که مقداری برای نام وارد بشه تابع isnull همون مقدار رو برمیگردونه انگار که کوئری رو به صورت where name like @name + % وارد کرده باشی که خب جستجو انجام خواهد شد و اگر نخواهیم نام در جستجو شرکت داده بشه مقدار null ارسال میکنیم. در اینصورت کوئری شبیه where name like name خواهد شد که عملا همیشه درست هست و در جستجو بی تاثیر خواهد بود چون هر فیلد نامی با خودش مقایسه میشه.

orca13
جمعه 18 بهمن 1392, 18:33 عصر
ممنون، متوجه شدم، فقط اگه از LIKE استفاده نکنیم هم میشه دیگه؟
یعنی:name = ISNULL (@name, name)

Mahmoud.Afrad
جمعه 18 بهمن 1392, 18:36 عصر
بله، چرا نشه.

orca13
جمعه 18 بهمن 1392, 18:56 عصر
اگه نوع داده ما توی جدول char نباشه، (الان مورد سوال من bit هستش)، چجوری باید پارامتر رو بفرستیم؟

Mahmoud.Afrad
جمعه 18 بهمن 1392, 19:12 عصر
مقدار بولین باید بفرستی.(true , false)

orca13
جمعه 18 بهمن 1392, 19:37 عصر
ببخشید من مدام سوال میکنم، اما الان به یه مشکل برخوردم.
تو فیلدهای جدول من یه مقدار INT هم هستش (که آخرین مقداری هست که به پروسیجر میدم)، اما وقتی که دارم پارامتر ها رو اضافه میکنم، وقتی که به مقدار آخر میرسه، جابجا میشه. یعنی:
باید اینجوری باشه،
null, null, null, 'ali', 1
اما اینجوری میشه:
null, null, null, '1, 'ali

البته اون ali که نوشتم، تو بانک به صورت فارسی ذخیره شده. فک کنم هم واسه همینه!
چکار کنم؟

ویرایش:
الان اون INT رو به جابجا کردم، به عنوان اولین پارامتر، درست شد. اما بازم اگه به جز این راه حلی داره، ممنون میشم بگید.

Mahmoud.Afrad
جمعه 18 بهمن 1392, 21:36 عصر
مهم نیست چجوری دیده میشه مهم اینه که به ترتیب اضافه شده باشه.

orca13
شنبه 19 بهمن 1392, 07:40 صبح
خب قبل اینکه جابجا کنم، کار نمیکرد. trace که کردم فهمیدم که اونجوری میشه.

vesall
سه شنبه 27 اسفند 1392, 00:15 صبح
لطفا در مورد مقادیر Date هم مثالی بزنید

SamiraProgrammer
دوشنبه 28 مهر 1393, 11:49 صبح
سلام و با تشکر از مطلب خوب
ی سوال
چطور با این روش از همه جداول دیتا بیس سلکت و جستجو کنیم