Question about finding records using Stored Procedures

  • Thread starter Thread starter news.microsoft.com
  • Start date Start date
N

news.microsoft.com

I have a table called Customer. I have a locator screen which is composed
of a grid and a couple of search boxes. The user is going to choose which
customer records to display in the grid. The choices are LastName or
firstName or any part of them, Social Security Number, Status
(Active,Inactive, etc.) and office.
Now the user can choose to find all customers who are active and from a
certain office or they could find all the customers whose last name starts
with S and is active and from a particular office.

In the past, I looked at each search field and built a where clause which
was then passed to a select statement to retrieve the correct records. I am
not happy doing this and want to switch to stored procedures.

When the user clicks the 'Find' button after filling in some search fields,
I will create an empty customer object and fill in the fields that are in
the search screen and this past this object to the server. On the server, I
will parse the object and figure out which fields have values in them
meaning that they are to be part of the search critieria. Once I have done
that, then I will call the appropriate Find method for the cusotmer.

That is my dilemma. Do I have the followng in my code?

FindCustomerBySocSecNo(string socsecno)
FindCustomerByLastName(string lastname)
FindCustomerByOffice(string office)
FindCustomerByStatus(string status)
FindCustomerByStatusAndOffice(string office,string status)
FindCustomerByLastNameStatusAndOffice()
etc.
etc.
etc.

This seems like a lot of work to do.

Bill
 
I have a table called Customer. I have a locator screen which is composed
of a grid and a couple of search boxes. The user is going to choose which
customer records to display in the grid. The choices are LastName or
firstName or any part of them, Social Security Number, Status
(Active,Inactive, etc.) and office.
Now the user can choose to find all customers who are active and from a
certain office or they could find all the customers whose last name starts
with S and is active and from a particular office.

In the past, I looked at each search field and built a where clause which
was then passed to a select statement to retrieve the correct records. I am
not happy doing this and want to switch to stored procedures.

When the user clicks the 'Find' button after filling in some search fields,
I will create an empty customer object and fill in the fields that are in
the search screen and this past this object to the server. On the server, I
will parse the object and figure out which fields have values in them
meaning that they are to be part of the search critieria. Once I have done
that, then I will call the appropriate Find method for the cusotmer.

That is my dilemma. Do I have the followng in my code?

FindCustomerBySocSecNo(string socsecno)
FindCustomerByLastName(string lastname)
FindCustomerByOffice(string office)
FindCustomerByStatus(string status)
FindCustomerByStatusAndOffice(string office,string status)
FindCustomerByLastNameStatusAndOffice()
etc.
etc.
etc.

This seems like a lot of work to do.

Bill
Click to expand...

Hello mate,

Here are a few thoughts:

1.Have 1 method Called FindCustomer() and overload it for every type.
2.You could pass the WHERE clause to a Stored Proc, we have done this
and it works fine.
3.Have 1 object per search type and a base Search type, this protects
the other objects should you wish to add another type
(FindCustomerByAge(int age) for example).

Hopw that helps,

Jon - NantwichOnline
www.nantwichonline.com/placestovisit.aspx
 
Back
Top