Question about finding records using Stored Procedures

22 Oct 2007 9:56 PM

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

23 Oct 2007 1:34 PM

Jon

Show quote

On 22 Oct, 22:56, "news.microsoft.com" <billgo***@charter.net> wrote:
> 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

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