Drilldown Datagrid Searching with ASP.NET

Our Session Begins

The BindGrid() subroutine listed below is responsible for the magic of making our application scalable. We'll begin by setting up our Datagrid's Datasource value -- Source -- as a Dataview variable, as this is our Datagrid's data result set.

Next, we do two things. First, we check whether a user submitted a new search, and if so, we assign the variable Source to our Session ("CachedGrid") key. Next, we determine whether we have an existing session in place -- if we do, we'll use this to bind our Datagrid. If not, we proceed to access our database, then insert our data results into a new Session key, and bind our Datagrid with that.

From this point on, any filtering that we'll perform will solely be from our existing result set -- Session("CachedGrid"), avoiding any unnecessary database access, as seen below:

Sub BindGrid()  
Dim Source As DataView  
 
If Len(Request("search")) = 0 Then Source =  
CType(Session("CachedGrid"), DataView)  
If IsNothing(Source) Then  
'... Data access here  
Source = DS.Tables(0).DefaultView  
' Insert Dataset into Session  
Session ("CachedGrid") = Source  
End If  
'... other page code  
MyDataGrid.DataSource = Source 'Either from a database  
hit or unique cached Dataview  
MyDataGrid.DataBind()  
'... more page code  
End Sub

Sessions and Alternative Techniques

Using sessions to store our data results works very well, and is secure. Our data is stored in server memory, is never redundant (since we clear any values existing session key at the outset), and it won't collide with anyone else's data, because each new session that's created is unique. Once the browser is shut down or the session times out, so will any information that was stored from the search.

Even though this solution appears to be OK, there is one caveat. Sessions may not be the best method to store large blocks of information, as this procedure can diminish Web server performance and resources. Therefore, if you feel that this doesn't suit your particular needs, you may want to try your hand at our second method, .NET Data Caching.

Implementing .NET's Cache object in our example works very well, and provides a noticeable boosts in performance. Nevertheless, keep in mind that NET's cache object is application-based, meaning that the data is cached at application-level, not user-level. So, what's the problem? Well, in using the Cache Object this way will affect all users -- and I mean all!

Consider what would happen if you were to run the application and carry out your work, while another person uses the same application a few seconds later. The second person would end up working with your results, or vice-versa, because you've already created a cache that everyone will end up sing! You can imagine the problems that would arise from such a scenario, and this discloses a number of issues regarding this solution's security and functionality. Sessions, as mentioned before, do not suffer from this drawback.

This is why we've used Session values here -- every user gets their own unique Session ID that expires automatically whenever they close their browser, or it times out. On the other hand, .NET's Caching Object's apparent disadvantage could be easily resolved by creating a unique cache object for each user, similar to a unique Session ID. How is this accomplished? The article Precise .NET Server Content Caching illustrates a clever way of creating unique distinctions with objects, avoiding any problems that may present themselves concerning exclusivity of data.

With this information, you could do the very same thing with caching that we've accomplish here with Sessions. Moreover, you could emulate the Session time out by creating a time-dependent cache. Now, let's move on to our subsearch.

Let's Drill Down

Now that we have our main Datagrid results all ready, and we've created our Dataview Session ("CachedGrid") key value, we're ready to set in motion our drilldown searching. In this instance we'll begin by entering "an" into our subsearch textbox as our criteria. As we submit this and it gets posted back, our Page_Load subroutine, aware, now, that our Session key exists, calls our BindGrid() sub only. The instant this happens, our Datagrid will bind itself from our stored session key, as demonstrated in the code snippet above.

This is where the drilldown magic happens!

Below, the "If Len(Request.Form("sub")) > 0 Then" conditional is responsible for two things. Firstly, it assigns and sets up our subsearch filter command in a second Session key ("Sub"). It will also filter our cached Datagrid with this string held in Session memory. Therefore, with each postback that takes place, we'll be progressively creating a new rowfilter string by concatenating (via the &= operator) the new filter string to the previous one.

If Len(Request.Form("sub")) > 0 Then  
Session ("sub") &= " and (fname LIKE '%" & Request.Form("sub") &  
"%' or lname LIKE '%" & Request.Form("sub") & "%') "  
Source.RowFilter = Session ("sub").SubString(5)  
End If

Thus, from our first subsearch for "an", 11 results (shown below) are returned from our "cached" session-held Datagrid. Our first rowfilter command will look like this: "and (fname LIKE '%an%' or lname LIKE '%an%')."

Upon further subsearching, for the term "man", for instance, we end up with 1 result, as shown below. This time our Dataview's rowfilter is assigned this "and (fname LIKE '%an%' or lname LIKE '%an%') and (fname LIKE '%man%' or lname LIKE '%man%')," again all taken from our Session ("sub") key, created from our repeated postback concatenations. Response.Write the Session ("sub") key to actually see the concatenation taking place as you submit your subsearching, and you'll see what I mean.

This is cool! But wait, not so fast... The filter command listed above doesn't quite contain the string syntax required for this to work, and if you ran it as shown, it would produce an error. Why? Because an SQL query from an initial Select clause will never execute if it's written as: "Select * from employee where and (fname LIKE '%an%' or lname LIKE '%an%')," right? Right. Here's why I use the Substring function to work around this.

For the Rowfilter to properly take effect, we obviously need to "and" all the filter commands, so they append together as one long SQL string, but without the beginning "and." To accomplish this, we need to read everything after those first 5 characters, which is why the Session ("sub").SubString(5) is used. It gives us our rowfilter command "(fname LIKE '%an%' or lname LIKE '%an%') and (fname LIKE '%man%' or lname LIKE '%man%')" which maintains our drilldown Datagrid searching without error.

The rest of our code is pretty general and straightforward, covering recordcounts, and hiding or displaying page elements.