Drilldown Datagrid Searching with ASP.NET

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.