filtering on gridview control VS-2008

Question

This is what I am working with to get back to the web dev world

ASP.Net with VS2008

Subsonic as Data Access Layer

SqlServer DB

Home Project description: I have a student registration system. I have a web page which should display the student records.

At present I have a gridview control which shows the records

The user logs on and gets to the view students page. The gridview shows the students in the system, where one of the columns is the registration status of open, pending, complete.

I want the user to be able to apply dynamic sorting or filters on the result returned so as to obtain a more refined result they wish to see. I envisioned allowing user to filter the results by applying where clause or like clause on the result returned, via a dataset interface by a subsonic method. I do not want to query the databse again to apply the filter

example: initial query

Select * from studentrecords where date = convert(varchar(32,getdate(),101)

The user then should be able to applly filter on the resultset returned so that they can do a last name like '%Souza%'

Is this even possible and is binding a datasource to a gridview control the best approach or should i create a custom collection inheriting from collectionbase and then binding that to the gridview control?

PS: Sorry about the typo's. My machine is under the influence of tea spill on my laptop

Answer 1

I use LINQ-to-SQL, not Subsonic, so YMMV, but my approach to filtering has been to supply an OnSelecting handler to the data source. In LINQ-to-SQL, I'm able to replace the result with a reference to a DataContext method that returns a the result of applying a table-valued function. You might want to investigate something similar with Subsonic.

Answer 2

As tvanfosson said, LINQ is very well suited to making composable queries; you can do this either with fully dunamic TSQL that the base library generates, or via a UDF that you mark with [FunctionAttribute(..., IsComposable=true)] in the data-context.

I'm not familiar with Subsonic, so I can't advise there; but one other thought: in your "date = " code, you might consider declaring a datetime variable and assigning it first... that way the optimiser can usually do a better job of optimising it (the query is simpler, and there is no question whether it is converting the datetime (per row) to a varchar, or the varchar to a datetime). The most efficient way of getting just the date portion of something is to cast/floor/cast:

SET @today = GETDATE()
SET @today = CAST(FLOOR(CAST(@today as float)) as datetime)

[update re comment]

Re composable - I mean that this allows you to build up a query such that only the final query is executed at the database. For example:

var query = from row in ctx.SomeComplexUdf(someArg)
            where row.IsOpen && row.Value > 0
            select row;

might go down the the server via the TSQL:

SELECT u1.*
FROM dbo.SomeComplexUdf(@p1) u1
WHERE u1.IsOpen = 1 -- might end up parameterized
AND u1.Value > 0 -- might end up parameterized

The point here being that only the suitable data is returned from the server, rather than lots of data being returned, then thrown away. LINQ-to-SQL can do all sorts of things via compsable queries, including paging, sorting etc. By minimising the amount of data you load from the database you can make significant improvements in performance.

The alternative without composability is that it simply does:

SELECT u1.*
FROM dbo.SomeComplexUdf(@p1) u1

And then throws away the other rows in your web app... obviously, if you are expecting 20 open records and 10000 closed records, this is a huge difference.

Answer 3

How about something like this?

Rather than assigning a data source / table to your grid control, instead attach a 'DataView' to it.

Here's sort of a pseudocode example:

DataTable myDataTable = GetDataTableFromSomewhere();    
DataGridView dgv = new DataGridView();
DataView dv = new DataView(myDataTable);

//Then you can specify things like:
dv.Sort = "StudentStatus DESC";
dv.Filter = "StudentName LIKE '" + searchName + '";
dgv.DataSource = dv;

...and so on.