Best way to handle multi field queries

  • Thread starter Thread starter Bill Gower
  • Start date Start date
B

Bill Gower

I am currently reevaluating how I do look ups and searches. What is the
best method for handling forms where user can search by different criteria.
Example, A member form allows users to look up a member by either SSN,
Lastname + firstname (whole or partial), all active or inactive or by an
office. Should I have multiple Get Methods such as

GetMemberBySSN()
GetMemberByMemberName()
GerMemberByOffice()
GetMemberByStatus()
GetMemberByStatusOffice()

or

GetMemberWithCriteria(string criteria) and then append the criteria string
to a WHERE clause.

Bill
 
Definitely option #1. You don't want people passing in strings
that you are going to append to dynamic sql. Big security risk.

Plus, you want a structured and "obvious" set of methods for
other developers to look at whether you are the only developer
or not. Noone would know how to properly format "criteria"
in option #2.
 
I agree with Robbe. Simpler SPs build better, more efficient (faster
running) query plans.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Robbe Morris - [MVP] C# said:
Definitely option #1. You don't want people passing in strings
that you are going to append to dynamic sql. Big security risk.

Plus, you want a structured and "obvious" set of methods for
other developers to look at whether you are the only developer
or not. Noone would know how to properly format "criteria"
in option #2.

--
Robbe Morris [Microsoft MVP - Visual C#]
.NET PropertyGrid Control - ListBox, ComboBox, and Custom Classes
http://www.eggheadcafe.com/tutorial...af-5cd3abe27a75/net-propertygrid-control.aspx




Bill Gower said:
I am currently reevaluating how I do look ups and searches. What is the
best method for handling forms where user can search by different
criteria. Example, A member form allows users to look up a member by
either SSN, Lastname + firstname (whole or partial), all active or
inactive or by an office. Should I have multiple Get Methods such as

GetMemberBySSN()
GetMemberByMemberName()
GerMemberByOffice()
GetMemberByStatus()
GetMemberByStatusOffice()

or

GetMemberWithCriteria(string criteria) and then append the criteria
string to a WHERE clause.

Bill
Click to expand...
Click to expand...
 
Back
Top