Saturday, July 28, 2007

Filtering data with the ASP.NET 2.0 GridView

Finally, something else to blog about...  Are you happy, Brad?

In implementing a full Identity Management solution, there are times when you need to supply the customer with some external utilities.  In this case, we needed a simple interface to allow the customer to manage a list of location codes, mapping them to Active Directory OUs and login script paths.  We decided that, in this case, the best solution was a simple database table and a web interface to update the information.

ASP.NET, especially with it's more recent incarnations, provides some mechanisms for building simple sites with little more than drag and drop in Visual Studio.  But as you try to build in a bit more functionality, you find that you sometimes have to tweak the built-in stuff.  (Our tweaking, here, is very simple - it's just a matter of adding the proper code to the proper events.)

The LocationCodes mini-project found me banging my head against the wall (drywall repairs: $73.26) trying to implement something as simple as a filter for narrowing down the list of items on the page.  Here's what I discovered through a combination of posting, searching and just plain messing around with it.  It's actually pretty simple...

First thing to note is that since a GridView merely displays the record set that it's given, filtering actually takes place in the DataSource control it's bound to.  Cool - the Datasource control has a "Filtering" event!  How much simpler could it be...?  Well, just to mess with you, the "Filtering" event of the DataSource is exactly where you don't place the code.  The filtering event only triggers if the DataSource's filter property already has a value.

Here's our scenario: We have a web page with a DataSource, a GridView and a couple of Buttons and a TextBox for dealing with the filter.

Here's the event firing order for the first time the page loads (We have no filter set yet.):

Object Event
Page PreInit
DataSource Init
GridView Init
Page Init
Page InitComplete
Page PreLoad
Page Load
DataSource Load
GridView Load
Page LoadComplete
Page PreRender
GridView DataBinding
GridView RowDataBound (Once for each row.)
GridView DataBound
GridView PreRender
Page PreRenderComplete
DataSource Unload
GridView Unload
Page Unload

When a filter is set, or cleared, or when an item is edited, the order of events is slightly different because we have to react to what the users intent is, meaning that we'll trigger some events programmatically.

Eliminating most of the events that don't really effect us, here are the events that occur when a user interacts with the page:

When a filter is entered in the TextBox and the user clicks the Filter button:

Object Event
Page Load
DataSource Load
GridView Load
Filter Button Click (Set Filter, Call DataSource.DataBind)
DataSource DataBinding
Page LoadComplete
DataSource Filtering
GridView DataBinding
GridView RowDataBound (Once for each row.)
GridView DataBound
DataSource Unload
GridView Unload
Page Unload

When the button is clicked, we set a Session variable to hold the filter text and force the DataSource to bind.  (For readability, the code snips presented her don't include the full method signatures.  But Visual Studio creates those for you anyway...)

Protected Sub btnFilter_Click(...) 
Session("FilterExp") = txtFilter.Text
LocationCodesDS.DataBind()
End Sub


In the DataBinding event, we check to see if there is any filter text.  If so, then set the FilterExpression property of the DataSource.  If there is no filter (the Session variable is not set) then clear the FilterExpression property:



Protected Sub LocationCodesDS_DataBinding(...)
Dim f As String = Session("FilterExp")
 
If (f Is Nothing) Then
LocationCodesDS.FilterExpression = ""
Else
LocationCodesDS.FilterExpression = _
"LocationCode Like '" & f & "%'"
End If
End Sub


When the FilterProperty of the DataSource is set, the text in the FilterExpression is basically added to the DataSource's SQL statement as a WHERE clause.  If the FilterExpression of the DataSource is set, then the Filtering event of the DataSource will be triggered.  This is where you can validate the filter expression and intercept the filtering process (e.Cancel = True).  The way I implemented this, I would check the filter expression in the Filter Button's click event.  But if you bound the DataSource's FilterExpression property to the TextBox directly, you'd need a way to stop the filtering process if, by chance, a user entered something that might be considered invalid or even malicious when attached to the underlying SQL.



Since the click event of the Filter button doesn't run every time the page loads (there are other ways to interact with page besides that one button) we need to tell the DataSource about any potential filters every time we have to rebuild the page.  The DataSource's Init event is fine place to do this.  Without this, if a user, say, clicks to edit a particular row, the DataSource will load the full record set on the postback and the GridView's row pointer will very likely end up pointing to a different row, causing the user to edit the wring data.  The code is simple:



Protected Sub LocationCodesDS_Init(...)
If Session("FilterExp") <> String.Empty Then
LocationCodesDS.DataBind()
End If
End Sub


VB .NET is kind enough to allow String.Empty to be compared to an actual empty string or to VB's null equivalent of 'Nothing'.  Thus allowing us this comparison even if the Session variable doesn't exist.  Conversion of this bit of code to C# needs to take this into account.



The last bit of functionality required for our basic filter implementation is the ability to clear the filter.  We do this in the Click event of the ClearFilter Button:



Protected Sub btnClearFilter_Click(...)
Session.Remove("FilterExpression")
LocationCodesDS.DataBind()
txtFilter.Text = ""
End Sub


That's it for basic filtering.  Next time, we'll talk a bit more about the GridView and how to work with it in regards to maintaining a proper user interface...

4 comments:

Steve S. said...

Hi,

I used this post as advice to implement some smarter-than-basic filtering, and it worked great!

I have two questions/comments:
1) It seemed to me that I needed to call DataBind on both my data source and the grid view that I was trying to filter. Your example only uses filtering on the DataSource.

2) Can you give an example of malicious text...

Thanks much!

Steve S.
Sacramento CA

Digital Camel said...

Thanks, Steve, glad it helped.

1) It may depend on the type of data source you're using. I'd have to see what you're doing to see why it behaves differently.

2) Malicious text could be embedded SQL. If a user enters, say, a SQL DELETE statement in a text box and you just paste it into your query as a parameter, the server may actually execute it, as embedded statements are perfectly legal syntax.

Valencia said...

People should read this.

Do Ran said...

Very Very Very good article. Can you please put all of this together and zip it. I need to actual source code and work in order me get a good grasp on this. I would truly appreciate it.