Mahmoud.Afrad
دوشنبه 18 اردیبهشت 1391, 00: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);
}
}
علامت % به ابتدا و انتهای مقادیر افزوده شده اند تا کلمات شامل متن ورودی را نیز نمایش دهند.
در انتها سورس همین توضیحات رو قرار میدم.
فقط در نظر داشته باشید در صورت عدم اجرای پروژه میتونید دیتابیسی به پروژه اضافه و اسکریپت جدول را در آن اجرا و در نهایت کانکشن را اصلاح کنید.
در این تاپیک قصد دارم انجام جستجوی پیشرفته را با پیاده سازی توسط 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);
}
}
علامت % به ابتدا و انتهای مقادیر افزوده شده اند تا کلمات شامل متن ورودی را نیز نمایش دهند.
در انتها سورس همین توضیحات رو قرار میدم.
فقط در نظر داشته باشید در صورت عدم اجرای پروژه میتونید دیتابیسی به پروژه اضافه و اسکریپت جدول را در آن اجرا و در نهایت کانکشن را اصلاح کنید.