Filtering control gridview VS-2008
This is what I work with to get back into the world of web developers
ASP.Net with VS2008
Subsonic as a data access layer
SqlServer DB
Home Project Description: I have a student registration system. I have a web page that should display student posts.
I currently have a gridview control that shows records
The user logs in and goes to the browsing page. The gridview displays the students in the system, where one of the columns is the registration status open pending completion.
I want the user to be able to apply dynamic sorting or filters to the returned result in order to get a more precise result that they want to see. I assumed it allows the user to filter the results by applying a where clause or similar to the returned result via a subsonic dataset interface. I don't want to query the database again to apply the filter
example: initial request
Select * from studentrecords where date = convert(varchar(32,getdate(),101)
The user must then be able to apply filters on the returned result set so they can make a surname like "% Souza%"
Is this possible and is binding the datasource to a gridview control the best approach, or should I create a custom set inheriting from the collection and then bind it to the gridview control?
PS: Sorry for the typo. My car is under the influence of a tea spill on my laptop.
I'm using LINQ-to-SQL, not Subsonic, so YMMV, but my filtering approach was to provide an OnSelecting handler to the data source. In LINQ-to-SQL, I can replace the result with a reference to the DataContext method, which returns the result of applying the table function. You might want to explore something similar with Subsonic.
source to share
As tvanfosson said, LINQ is very well suited for building complex queries; you can do this either with the fully dunamic TSQL that the underlying library generates, or through the UDF, which you mark with [FunctionAttribute (..., IsComposable = true)] in the data context.
I am not familiar with Subsonic so I cannot advise there; but one more thought: in your "date =" code, you might think about declaring a datetime variable and assigning it first ... this way the optimizer can usually do better optimization (the query is simpler, and there is no question of whether it converts datetime (to string) to varchar or varchar to datetime). The most efficient way to get only a part of the date of something is to do cast / floor / cast:
SET @today = GETDATE()
SET @today = CAST(FLOOR(CAST(@today as float)) as datetime)
[update comment]
Re composable - I mean this allows you to create a query like this so that only the final query is executed in the database. For example:
var query = from row in ctx.SomeComplexUdf(someArg)
where row.IsOpen && row.Value > 0
select row;
can go down from the server via 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 is that only suitable data is returned from the server, not a lot of returned data, and then discarded. LINQ-to-SQL can do all sorts of things with compsable queries, including paging, sorting, etc. By minimizing the amount of data that you load from the database, you can significantly improve performance.
The non-linkable alternative is that it just does:
SELECT u1.*
FROM dbo.SomeComplexUdf(@p1) u1
And then throws other lines in your web application ... obviously if you expect 20 open records and 10,000 closed records, that's a huge difference.
source to share
How about something like this?
Instead of assigning a datasource / table to your grid control, attach a "DataView" to it instead.
Here is an example pseudocode:
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;
... etc.
source to share