Another way of coding search functionality in ASP.net

I need to code a page to search the database for user's data. For example, the fields are
first name, last name, status, phone, DOB, type, etc.

This is how I always do a search page:
1. Create a dynamic stored proc and pass it in the "where" clause that I build in code
2. In the code, check for user's input and build the where clause. For example, I check which fields are populated and that's how I build the where clause. A lot of If statements.

That method works but I don't think it's a good way of coding it. I know SQL Server has some kind of a search (I think indexed search, not sure)

At the bottom of this site, there's an example. It loops thru the keywords. Not sure if I can use this method, somehow;
http://code.msdn.microsoft.com/CSASPNETSearchEngine-3eb92010


This time around, I have too many fields to use if-else statement. Is there a better way of doing this?
LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
SQL server does have full-text indexing which can speed up the search by indexing the content of your data, but for the types of field you are talking about you won't see much benefit.

The sample you have posted would be the way to go if you want to dynamically build your WHERE clause. I'd personally pass the criteria to your stored proc, and build the WHERE clause there rather than building it in code as passing it across.
0
CamilliaAuthor Commented:
The sample you have posted would be the way to go if you want to dynamically build your WHERE clause. I'd personally pass the criteria to your stored proc, and build the WHERE clause there rather than building it in code as passing it across.

So, pass all the parameters to the stored proc and how do I build the where clause there?I saw one example of using CASE but not sure.
0
Carl TawnSystems and Integration DeveloperCommented:
Similar to how you would in code - it's a bit laborious i'm afraid, but that's the nature of dynamic code in T-SQL:
DECLARE @Where  VARCHAR(500);

IF LEN(@FirstName) > 0 
BEGIN
     IF LEN(@WHERE) > 0 @WHERE = @WHERE + ' AND '
     @WHERE = @WHERE + ' [FirstName] LIKE ''' + @FirstName + '%'''
END

// repeat for each search column

Open in new window


You do it more safely with a parameterised query, but i'd get it working first, and then polish it later.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
ah, so pass each parameter , for example, @firstname, to the query and build it like that.

Yeah, i have 6 or 7 parameters to pass. It needs a little bit of work. Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.