If you’ve used the AjaxControlToolkit’s AutoCompleteExtender, you’re no doubt pleased by the fact you can now replace cumbersome dropdowns with slick, user-friendly auto-complete textboxes.  To take it one step further, you can actually replace multiple search fields with a single auto-complete textbox, with only a little extra code.

In this example, we’re going to be searching our Bus Line database for executive travel.  Our imaginary sub-system tracks business trips made by the CEO, CIO, CTO, and CFO.

If we have a gridview with all the business trips for a given date range, there could be many pages to look through to find the trip you’re looking for.  It’s only nice to allow our users to search the gridview based on any number of criteria.

For example, with a single textbox, we can allow the user to search by trip name, the person’s name, the person’s title, trip start date, trip destination, airline, etc.

Let’s look first at our sample executive travel entity and a gridview to display our trips (obviously this is not normalized, etc…):

An example of our Trip entity

Our trips span 3 pages

So you know what the code is doing when you get to that point, I’ll go ahead and post all of the AutoCompleteExtender dropdown screenshots now.  Keep in mind this is one textbox wired to one AutoCompleteExtender calling one method of one webservice.  There are no tricky code-arounds or messy overloads.

The idea is that the user can type in anything they know about a trip…who’s on it, when it is, where it’s going, etc, and then choose the desired trip from the dropdown.

Here we're searching on trip number

Searching by person title (returning all where title == CEO)

Searching where Airline like("Airtran Airways")

Where State like("Nevad%")

In this one, our CEO is named Steve Jobs, and our CIO is named Steve Wozniak

Looking for all trips in October (remember the date thing, we'll come back to that later)

…and so on.  I took more screenshots, but by now it should be obvious that we can search an entitySet on any number of fields (essentially a deep-text search), and return the corresponding Id (PK) of the entity.  If you recall my previous post on returning key/value pairs from an AutoCompleteItem, we can gloss over that part of it and just strip the web service down to the functionality this article addresses:

[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public string[] GetTripList(string prefixText, int count)
{
/**********************************************************
* Takes a prefix string from a textbox and retrieves
* trips from the entitySet. Used by the AJAX Toolkit
* AutoCompleteExtender.
***********************************************************/

    List<string> TripList = new List<string>();

    using (BusLineEntities myEnts = new BusLineEntities())
    {
      IQueryable<ExecutiveTrip> tripsQuery =
              from t in myEnts.ExecutiveTrips
                    where t.TripName.Contains(prefixText)
                              || t.PersonName.Contains(prefixText)
                              || t.PersonTitle.Contains(prefixText)
                              || t.DestinationCity.Contains(prefixText)
                              || t.DestinationState.Contains(prefixText)
                              || t.Airline.Contains(prefixText)
                    orderby t.TripName, t.StartDate
                    select t;

      //add the values to the list and include id
      foreach (ExecutiveTrip t in tripsQuery) {
              TripList.Add(AjaxControlToolkit.AutoCompleteExtender. _
                CreateAutoCompleteItem(t.TripName, t.Id.ToString()));
         }
   }
return TripList.ToArray();
}//end GetTripList

As you can see above, all we really need to do is chain together as many logical OR || operators and statements. That should be pretty plain, so I’ll skip ahead to a situation that can be slightly confusing…

Recall above we wanted to search by date in our web service.  In this particular case, the date is in our trip name, but you might not always be so lucky.  Assuming your date field is a System.DateTime datatype, it seems logical to try something along the lines of:

where Convert.ToDateTime(t.StartDate).ToLongDateString().Contains(prefixText)

Unfortunately, that won’t work.  You’re not able to do the conversion inside of your query.  While there are probably better ways to do this, my approach was fairly simple and can be used on any datatype.  Where required, I make a SQL view that has all of the fields I’m going to want to search on, and I make all but the primary key varchar fields (either by cast or convert).  This allows us to do a string search on DateTimes, Int32s, etc.  In case you need the conversion, a SQL DateTime or SmallDateTime value can be converted to a string in your view’s SELECT statement like so:

convert(varchar, getdate(), 100)
       output: Sep 11 2009 9:56PM

convert(varchar, getdate(), 101)
       output: 9/11/2009

convert(varchar, getdate(), 102)
       output: 2009.09.11

convert(varchar, getdate(), 106)
       output: 11 Sep 2009

convert(varchar, getdate(), 107)
       output: Sep 11, 2009

Which one you choose really just depends on how you want it to be searched (I’d go with 107, personally).

That’s all for now, my next post will explain the two different ways you can use the return value to databind your EntityDataSource on the trip Id.