A recent project required me to build a search function that looked for existing customers by name. Our company has thousands of customers so simply putting the list into a dropdownlist or bulleted list was not feasible. I decided to build a simple textbox input search function which was wired up with the AJAX AutoCompleteExtender. The user would enter a name of a customer and the list would filter down until either a match was made or it wasn't.
Working with the extender is quite simple. You add a textbox control to your form, associate the extender with it and point the extender to a webservice. Each character entered into the textbox triggers a webmethod of the webservice which queries data and returns results as a string array [].
On the backend the webservice would execute a simple query against a table on our SQL Server based on the contents of the text entered in the textbox. As more and more people use this function however, the traffic and performance of all of this activity becomes a concern. Since the data being queried against rarely changed (names of customers), I decided to utilize an application cache which the webmethod would use as the basis of finding matches via the extender.
The original setup of my webmethod was quite simple. It would execute a query against the database that returned X amount of rows (to limit the number shown in the autocomplete results). These rows (containing 1 column) would be inserted into a string array and returned to the extender for display.
Original code:
[WebMethod]
public string[] GetCustomers(string prefixText, int ResultsReturned)
{
string strSQL = "SELECT TOP " + ResultsReturned.ToString() + " CUSTOMER_NAME + ' - ' + CUSTOMER_STATUS FROM tblCUSTOMERS ";
strSQL += "WHERE REPLACE(REPLACE(UPPER(CUSTOMER_NAME),'.',''),',','') LIKE '%" + prefixText.ToUpper().Replace(".", "").Replace(",", "") + "%' ";
strSQL += "ORDER BY CUSTOMER_NAME";
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConn"].ToString());
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(strSQL, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
conn.Close();
string[] items = new string[ds.Tables[0].Rows.Count];
int i = 0;
foreach (DataRow dr in ds.Tables[0].Rows)
{
items.SetValue(dr[0].ToString(), i);
i++;
}
ds.Dispose();
sda.Dispose();
conn.Dispose();
return items;
}
To utilize caching I had to determine the best scope to use. If the data being cached was different or would change depending on the user accessing that cache, I would use a Session-level cache. If the data was the same across the board, regardless of the user, an Application cache is the best bet. Since this was the case, I went the application route.
Implementing a cached data source takes a little extra coding since I now have to filter my data from a persistant (sortof) dataset as opposed to requerying the database and building a new dataset each time. The first step is to add a constructor that can build a basic, unfiltered dataset (using a DataTable) and add it into the Application.Cache space as such:
private DataTable GetCustomersDT()
{
DataTable dt = (DataTable)HttpContext.Current.Cache["CustomersDT"];
if (dt == null)
{
string strSQL = "SELECT CUSTOMER_NAME, CUSTOMER_STATUS FROM tblCUSTOMERS";
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConn"].ToString());
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(strSQL, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
dt = ds.Tables[0];
HttpContext.Current.Cache.Insert("CustomersDT", dt, null, Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(5));
sda.Dispose();
ds.Dispose();
conn.Close();
}
else
{
dt = (DataTable)HttpContext.Current.Cache["CustomersDT"];
}
return dt;
}
Now that we have a Cached DataTable containing the data we'll use to filter for our extender, lets look at the WebMethod that utilizes it:
[WebMethod]
public string[] GetCustomersFromCache(string prefixText, int ResultsReturned)
{
DataTable dt = GetCustomersDT();
DataView dv = new DataView(dt);
dv.RowFilter = "CUSTOMER_NAME LIKE '%" + prefixText.ToUpper().Replace(".", "").Replace(",", "") + "%'";
dv.Sort = "CUSTOMER_NAME";
int rowcount = dv.Count;
int counter = 5;
if (rowcount < 5)
{
counter = rowcount;
}
string[] items = new string[counter];
int x = 0;
foreach (DataRowView drv in dv)
{
if (x < counter)
{
items.SetValue(drv[0].ToString(), x);
x++;
}
else
{
break;
}
}
return items;
}
And there you have it. You'll see that the first line of the WebMethod calls GetCustomersDT() which returns the DataTable used for filtering our results. This DataTable will either exist in the Application's Cache or be created and then added to the Cache. This should save many, many round-trips to the database.
