selecting data in database with different queries

[Jeff]

hi

asp.net 2.0
sql server 2005

I have a GridView on my webpage. Sometimes I want to query the bound
datasource in different ways.:
- Get all arroved records
- Get all unapproved records
- Get all records created last week
- Get all records created by current user.
- Get top 100 records (return 100 records)

this is a standard table in a database on sqlserver 2005. primary key is
uniqueidentifier

Hope there are a better way of doing this than creating multiple stored
procedures....

any suggestions?

 

[sloan]

If the where clause is the only thing changing, then look here:
http://www.sqlservercentral.com/articles/Stored+Procedures/thezerotonparameterproblem/2283/

The "TOP 100", I would isolate.

 

[Jeff]

Thanks for that link.

But now I'm wondering if there is another possible approach. The thing is
that my DAL creates a collection of the objects.
for example List<car> cars = siteprovider.getCars();

So maybe a possible workaround to using xml parameter is to in the
application start event put some logic that reads in the entire collection
of cars and then store it the cache. And then use the search mecanism on the
list (List<car>) to get the cars I want. In that approach I don't need to
make another trip to the database.

One issue I see is that perhaps sometime the cache will be lost, but then
maybe make a new trip to the database and return all the records. Then store
it in cache etc...

any suggestons? problem using this approach?

 

[sloan]

Sure.

"workaround to xml parameter"
Why would you fear using xml? Sql Server does a nice job of shredding them.

But you can put the data into cached object.

However, it dependings on how many optional filters you need to code.
If you have alot, it won't be trivial.

I would research the "Filter" using Ludwig's very good article:
http://www.scribd.com/doc/2057557/CTG-Articles-Generics

I foresee a maze of Filter methods......but maybe you can figure out
something.