Drilldown Datagrid Searching with ASP.NET

By Dimitrios Markatos   |   Published: 08 July 2005   |   Reader Level:  Intermediate

Introduction

Using .NET's Datagrid to retrieve and display data is a quick and easy process, and .NET offers many features to customize its look and performance. The one thing that's not commonly known is that .NET gives developers the ability to search within Datagrid results, or drilldown and filter within the data until they find what they're looking for.

With all the search engines available, more often than not, a search will return more results than necessary. This is why many leading Websites, such as Microsoft, offer users the ability to search within results. This saves users from repeating their search with altogether different criteria, in order to find something specific within a broad category of results.

In this article I'll show you how to perform this kind of drilldown data searching using the Datagrid. .NET offers two methods to filter data. One is the Dataset's Dataview Rowfilter property that we'll be using here. The is the Datatable's Select command, but, as this doesn't support features like the paging, etc. of a Datagrid, we'll won't focus on this solution here.

No matter how effective these two methods are in filtering data, you'll soon realize that you won't be able to drilldown search in the way we'd like, because .NET filters only once! In light of this, I'll take .NET's standard filtering one step further, and demonstrate how to drilldown and filter data more than once. Using some simple logic and string concatenation, we'll be able to continuously drilldown and build a new filter string each time within your given result set, until you find the exact thing you're searching for.

The Basics

In our example I'll use the Pubs database that comes with SQL server, and an html page with two forms in it: one for the initial query, and one for our drilldown searching. Our standard .aspx .NET page always includes the necessary namespace imports that'll allow us to connect with our data. Also, as we'll be working with the Datagrid, if you need more information, try the article titled Custom ASP.NET Datagrid Paging With Exact Count at 4guysfromrolla.com.

The rest of our code uses standard .NET Web form controls to display our data result statistics and the like. Other than the common elements to be found on any page, the real bulk of our task depends on two key concepts: sessions and string concatenation. We'll be taking full advantage of ASP.NET's Session State to store all our data results and queries, as opposed to storing anything client-side through such methods as hidden fields, querystrings, cookies, or even .NET's Viewstate, which could pose security problems.

So, with all this in mind, let's get to it.

The Initial Search

Our .NET page display, as seen below, has two search fields: the main search box, <input type="text" name="search">, which initializes our main search, and our drilldown search box, <input type="text" name="sub">, for all our subsearches.

As soon as a user enters a search term into our main search field and submits it, our Page_Load subroutine will be prompted to confirm the submission. Next. It will determine whether there are any existing sessions that we'll need to clear before we work with a new result set. After this, it makes sure our Datagrid is reset to page 1, before it finally calls our data access subroutine -- BindGrid() -- to retrieve and display our data, and bring our subsearch textbox into view:

Sub Page_Load(Source As Object, E As EventArgs)
If Len(Request ("search")) > 0 Or Len(Request ("sub")) > 0 Then
   If Len(Request ("search")) > 0 Then Session.Clear()
     MyDataGrid.CurrentPageIndex = 0 'resets the Datagrid to page 1
     BindGrid()
     subsrch.visible = "true"
   Else
     subsrch.visible = "false"
   End if
End Sub

Now that we've discussed the logic behind this code, we'll fire up our Datagrid drilldown searching via a simple, all-inclusive "select * from all" query that will return to us all available records -- 43 results to be exact -- and enable our subsearch text field, as seen below.

The Perfect Datagrid

The Datagrid results shown above were achieved by accessing the database. However, if you take a look at the code, you'll observe that this will only occur once. Whenever you begin writing any application that deals with large amounts of data, it's always good practice to minimize prolonged database access.

.NET's straight out-of-the-box, unenhanced Datagrid suffers this type of limitation, whether you retrieve data or a page, simply because it requires continuous database work each time. As you can imagine, these never-ending database hits are not the way to go. To dramatically improve our application, and ensure that it can scale well, we'll explore ways to specifically remedy this in our example, and we'll discuss another alternative as well.

I already mentioned that we'll take full advantage of .NET's Session State to store any and all of our data and string information, which will all be used for drilldown searches..NET offers two solid options to circumvent repetitive database access: Sessions, as mentioned, and .NET Data Caching, which we'll discuss in just a moment.

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
    'Either from a database hit or unique cached Dataview
    MyDataGrid.DataSource = Source
    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.

The Code

Here is the complete ASP.NET page code.

Cut and paste this into your favorite editor and let it rip!

<%@ Page Language="VB" Debug="False" Explicit="True" Buffer="True"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html><head><title>Drilldown Datagrid Searching Within  
Results</title></head>
<script runat="server" language="VB">
Sub Page_Load(Source As Object, E As EventArgs)
    Response.BufferOutput = "True"
    If Len(Request("search")) > 0 Or Len(Request("sub")) > 0 Then
        If Len(Request("search")) > 0 Then Session.Clear()
        MyDataGrid.CurrentPageIndex = 0 ' resets the Datagrid to page 1 
        BindGrid()
        subsrch.visible = "true"
    Else
        subsrch.visible = "false"
    End If
End Sub

Sub BindGrid()
    Dim Source As DataView

    If Len(Request("search")) = 0 Then 
        Source = CType(Session("CachedGrid"), DataView)
    End If

    If IsNothing(Source) Then
        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlDataAdapter
        Dim RcdCount As integer
        Dim DS As DataSet
        Dim sqlQuery As String = 
          "SELECT emp_id, fname, lname FROM employee where (fname like '%" &
          Request("search") & "%' or lname like '%" & Request("search") &
          "%') Order by emp_id asc"

        Dim strConn As String = "server=(local);uid=sa;pwd=;database=pubs;"
        MyConnection = New SqlConnection(strConn)
        MyCommand = New SqlDataAdapter(sqlQuery, MyConnection)
        DS = new DataSet()
        MyCommand.Fill(DS, "MyDataGrid")
        MyDataGrid.CurrentPageIndex = 0
        Source = DS.Tables(0).DefaultView
        ' Insert Dataset into Session
        Session("CachedGrid") = Source
    End If

    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

    If (Source.Count.ToString) >= 1 Then
        MyDataGrid.visible = "true"
    Else
        msg.text = "<br><b>No records found</b>"
        subsrch.visible = "false"
        MyDataGrid.visible = "false"
    End If

    Try
       'Get count from Dataview row count, same as Datagrid row count
        count.text = "<b>" & Source.Count.ToString & "  
        </b>results found<br>"
        MyDataGrid.DataSource = Source
        MyDataGrid.DataBind()
    Catch e As Exception
        'reset the Datagrid back to page 1 on any errors
        MyDataGrid.CurrentPageIndex = 0 
    End Try
End Sub
 
Sub MyDataGrid_Page(sender As Object, e As DataGridPageChangedEventArgs)
    MyDataGrid.CurrentPageIndex = e.NewPageIndex
    BindGrid()
End Sub
</script>
<body bgcolor="#FFFFFF" topmargin="0" marginheight="0"  
onLoad="document.forms[0].search.focus();">
<center>
<h3>Datagrid Drilldown Search Example</h3>
<form method="post">
<input type="text" name="search">
<input type="submit" Value="Go">
</form>
<form runat="server">
<span id="subsrch" runat="server">
Search Within results
<input type="text" name="sub">
<input type="submit" Value="Go">
</span>
<br><asp:label id="msg" runat="server" />
<br><asp:label id="count" runat="server" />
<ASP:DataGrid id="MyDataGrid" runat="server"
AllowPaging="True" PageSize="10" PagerStyle-Mode="NumericPages"  
PagerStyle-HorizontalAlign="Right"
PagerStyle-NextPageText="Next" PagerStyle-PrevPageText="Prev"  
OnPageIndexChanged="MyDataGrid_Page"
BorderColor="black" BorderWidth="1" GridLines="Both" CellPadding="3"  
CellSpacing="0" Font-Name="Verdana"
Font-Size="8pt" HeaderStyle-BackColor="#FF8040"  
AlternatingItemStyle-BackColor="#eeeeee" />
</form></center></body></html>

Conclusion

Well there you have it, a cool way to filter data instead of running a query over again with new criteria, especially when the actual query might not bring back the desired results. It's an easy way to allow your applications more functionality, and enhance your user's end results.

Author: Dimitrios Markatos