Search Records in ASP.Net and display in Datagrid

[Rattlesnake]

Hi,

I have a ASP.Net 1.1 application with a Sql Server 2000 Database.

 

I have an orders table that contains the details of an order. There are orders from 2005 to till date.

I am displaying these orders in a Datagrid.

The user has the option to search the Orders table using many criteria (around 8 to 10) criterias.

 

I am uisng a DataTable as the DataSource for the Datagrid.

Below is the Code for the datasource

 

Private Sub BindData()

Dim dv As DataView

sqldataadpater1.Fill(DsOrdersList1)

dv = DsOrdersList1.Tables(0).DefaultView

If Not viewstate("SearchString") = "" Then

dv.RowFilter = viewstate("SearchString")

Else

dv.RowFilter = Nothing

End If

Datagrid1.DataSource = dv

Datagrid1.DataBind()

End Sub

I am using a SQL Server Stored Procedure as Command for the data adapter

 

Offcourse with the above technique , at the server end ALL the Orders table records are put into the Datable and then filtered based on the ROWFILTER.

 

With the increase in the Order Records , the search and display is getting SLOWER.

 

What other technique can I use to retrieve the records from the database , keeping in mind that I want to keep the option of filtering the records by the 8 to 10 criterias that I have.

 

The only way I can think of is using DYNAMIC SQL in the stored Procedure and send the Filter criteria to the stored Procedure. Offcourse this will result in a performance hit.

 

Are there any other ways ?

Edited September 10, 2007 by Rattlesnake