Implementing Efficient Searches (with Paging & Sorting)
in C#

Hello,

I'm wondering if there is a set of 'best practices' regarding the implementation
of searches in web applications.  Is the best thing to simply hard-code SQL into
the code-behind of a search page (to control paging, sorting & possibly most
importantly, the WHERE clause, through SQL, as opposed to a BLL.cs &
DAL.xsd)?

Here is my current setup:


I have a "BLL" called Search.cs that calls on a DataTable definition
in my DAL (data.xsd).
The DataTable simply returns all rows 12 columns (right now about 1500 records)
When the data called through the .aspx interface, I cache the complete resultant
DataTable (this takes about 0.225 seconds, including rendering of controls and
HTML in the browser)
Any search conditions selected in the .aspx interface are then applied to the
cached data through a DataView.RowFilter (I load the cached data into a DataView
first) - using the cached data, the results will load in about 0.035 seconds. 
Paging & Sorting are also handled exclusively with the cached data.

This seems pretty fast to me, and it inhibits return trips to the database
(though not the server), but I'm figuring that it is only because I'm dealing
with 1500 records, and not 150,000 records - is that correct/incorrect?  Will
the above setup scale, in your opinion, to 150,000 records?  If not, can you
recommend/link-to a better way to handle this scenario (needing to provide the
end-user with the ability to control the WHERE clause of a full-search)?

Thanks in advance for any thoughts!

Darin

NEVER write hard-code SQL in your code behind (or anywhere) in your code. At
least, use parameters when you do do it. 

I guess, the best solution would be customized paging in the stored procedure.
Have a look at this article explaining custom paging with sql 2005 and asp.net
2.0. 
http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

You can also do it in SQL 2000, but that requires much complexer procedures.
Just do a google search on "sql custom paging" and you get a lot of
results back ;)