I'm currently upgrading a website and the previous developer had implemented a real time filtering concept on a large table of employee names, emails, and phone numbers. It was a really neat idea, but used an external JavaScript file and inline looping code to create the HTML table with the rows.
So I created the same functionality using a repeater control and greatly cut down the lines of code originally used - not to mention that I finally found a non-annoying and non-invasive use for the text box on text changed event!
Okay, first thing is first. The ASPX markup. It utilizes a repeater, as referenced above, as well as a text box for each table column in your return. The ultimate UI should look something like this:
<asp:UpdatePanel runat="server" ID="UpdatePanel">
<ContentTemplate>
<table cellpadding="1" cellspacing="4" width="700px" style="border: solid 1px silver;">
<tr>
<td align="center" colspan="5" m style="font-weight: bold; font-size: 10px;">
You filter by full or partial words by entering the filter and pressing the Enter
key. Filters are cumulative.
</td>
</tr>
<tr>
<td>
<asp:TextBox runat="server" ID="txtName" CssClass="textBox" Font-Size="10px" Width="97%"
OnTextChanged="Filter" ValidationGroup="Products" AutoPostBack="true" />
<cc1:TextBoxWatermarkExtender ID="wmProductName" runat="server" TargetControlID="txtName"
WatermarkText="Filter Product Name..." />
<td>
<asp:TextBox runat="server" ID="txtUnitPrice" CssClass="textBox" Font-Size="10px" Width="97%"
OnTextChanged="Filter" ValidationGroup="Products" AutoPostBack="true" />
<cc1:TextBoxWatermarkExtender ID="wmUnitPrice" runat="server" TargetControlID="txtUnitPrice"
WatermarkText="Filter Unit Price..." />
</td>
<td>
<asp:TextBox runat="server" ID="txtCatName" CssClass="textBox" Font-Size="10px" Width="97%"
OnTextChanged="Filter" ValidationGroup="Products" AutoPostBack="true" />
<cc1:TextBoxWatermarkExtender ID="wmCatName" runat="server" TargetControlID="txtCatName"
WatermarkText="Filter Category Name..." />
</td>
</tr>
<tr style="background-color: #eeeeee; font-weight: bold;">
<td>
Product Name
</td>
<td>
Unit Price
</td>
<td>
Category Name
</td>
</tr>
<asp:Repeater runat="server" ID="rptProducts">
<ItemTemplate>
<tr>
<td style="padding-left: 8px;">
<asp:Label runat="server" ID="lblEmployeeName" Text='<%#Bind("ProductName") %>' />
</td>
<td>
<asp:Label runat="server" ID="lblArea" Text='<%#Bind("UnitPrice","{0:c}") %>' />
</td>
<td>
<asp:Label runat="server" ID="lblTitle" Text='<%#Bind("CategoryName") %>' />
</td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
</ContentTemplate>
</asp:UpdatePanel>
Basically, it's just a HTML table with a few rows - The first row containing the text box controls, a second row containing the column headings, and then the third row containing the repeater called rptProducts. I'm making use of the AJAX toolkit's Watermark extender control to place the "Filter Product Name..." watermark in the textboxes on page load. Lastly, I'm wrapping it all in an update panel so the post back on applying a filter will be barely noticeable, if noticeable at all.
Note the ontextchanged events for the text box controls call Filter. Also, note that the autopostback property for the text boxes is set to true to fire the Filter event.
On page load, we call a function called GetProducts and data bind the repeater. GetProducts queries the Northwind database and returns a data table.
C#:
protected void Page_Load(object sender, System.EventArgs e)
{
if (Page.IsPostBack == false) {
rptProducts.DataSource = getProducts();
rptProducts.DataBind();
}
}
protected datatable getProducts()
{
DataTable dt = new DataTable();
try {
SqlConnection conn = new SqlConnection("YourConnectionString");
SqlCommand comm = new SqlCommand("SELECT TOP 10 ProductName, " + "UnitPrice, " + "cat.CategoryName " + "FROM [Northwind].[dbo].[Products] prod " + "INNER JOIN categories cat ON cat.categoryID = prod.CategoryID " + "ORDER BY ProductName", conn);
conn.Open();
SqlDataAdapter adapter = newSqlDataAdapter(comm);
adapter.Fill(dt);
adapter.Dispose();
comm.Dispose();
conn.Close();
conn.Dispose();
}
catch (Exception ex) {
EmailError.emailHandledError(ex);
Response.Write(ex.Message);
}
return dt;
}
VB.NET:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Page.IsPostBack = False Then
rptProducts.DataSource = getProducts()
rptProducts.DataBind()
End If
End Sub
Protected Function getProducts() As datatable
Dim dt As New DataTable
Try
Dim conn As New SqlConnection("YourConnectionString")
Dim comm As New SqlCommand("SELECT TOP 10 ProductName, " _
& "UnitPrice, " _
& "cat.CategoryName " _
& "FROM [Northwind].[dbo].[Products] prod " _
& "INNER JOIN categories cat ON cat.categoryID = prod.CategoryID " _
& "ORDER BY ProductName", conn)
conn.Open()
Dim adapter As New SqlDataAdapter(comm)
adapter.Fill(dt)
adapter.Dispose()
comm.Dispose()
conn.Close()
conn.Dispose()
Catch ex As Exception
EmailError.emailHandledError(ex)
Response.Write(ex.Message)
End Try
Return dt
End Function
Then, to wire up the Filter event that will fire whenever the text is changed in one of the filter textboxes:
C#:
protected void Filter(object sender, System.EventArgs e)
{
string filter = string.Empty;
// For each filter text box that is not empty, create a
// filter using LIKE and the value input into the text box
if (!string.IsNullOrEmpty(txtName.Text)) {
filter = "ProductName LIKE '%" + txtName.Text + "%' AND ";
// Disable the watermark extender to keep the filter input in place on postback
wmProductName.Enabled = false;
}
else {
// Reenable the extender if the filter text box is empty
wmProductName.Enabled = true;
}
if (!string.IsNullOrEmpty(txtUnitPrice.Text)) {
filter += "UnitPrice LIKE '%" + txtUnitPrice.Text + "%' AND ";
wmUnitPrice.Enabled = false;
}
else {
wmUnitPrice.Enabled = true;
}
if (!string.IsNullOrEmpty(txtCatName.Text)) {
filter += "CategoryName LIKE '%" + txtCatName.Text + "%' AND ";
wmCatName.Enabled = false;
}
else {
wmCatName.Enabled = true;
}
// Create data table from function
DataTable dt = getProducts();
try {
if (filter.Length > 0) {
// Create a data view from data table to apply filter
DataView dv = new DataView(dt);
// Remove final AND off of filter string
dv.RowFilter = filter.Remove(filter.Length - 5);
// Databind repeater to data view
rptProducts.DataSource = dv;
rptProducts.DataBind();
dv.Dispose();
}
else {
// No filters are specified, bring back all rows
rptProducts.DataSource = dt;
rptProducts.DataBind();
}
}
catch (Exception ex) {
EmailError.emailHandledError(ex);
}
finally {
// Clean up
dt.Dispose();
}
}
VB.NET:
Protected Sub Filter(ByVal sender As Object, ByVal e As System.EventArgs)
Dim filter As String = String.Empty
' For each filter text box that is not empty, create a
' filter using LIKE and the value input into the text box
If txtName.Text <> "" Then
filter = "ProductName LIKE '%" & txtName.Text & "%' AND "
' Disable the watermark extender to keep the filter input in place on postback
wmProductName.Enabled = False
Else
' Reenable the extender if the filter text box is empty
wmProductName.Enabled = True
End If
If txtUnitPrice.Text <> "" Then
filter += "UnitPrice LIKE '%" & txtUnitPrice.Text & "%' AND "
wmUnitPrice.Enabled = False
Else
wmUnitPrice.Enabled = True
End If
If txtCatName.Text <> "" Then
filter += "CategoryName LIKE '%" & txtCatName.Text & "%' AND "
wmCatName.Enabled = False
Else
wmCatName.Enabled = True
End If
' Create data table from function
Dim dt As DataTable = getProducts()
Try
If filter.Length > 0 Then
' Create a data view from data table to apply filter
Dim dv As DataView = New DataView(dt)
' Remove final AND off of filter string
dv.RowFilter = filter.Remove(filter.Length - 5)
' Databind repeater to data view
rptProducts.DataSource = dv
rptProducts.DataBind()
dv.Dispose()
Else
' No filters are specified, bring back all rows
rptProducts.DataSource = dt
rptProducts.DataBind()
End If
Catch ex As Exception
EmailError.emailHandledError(ex)
Finally
' Clean up
dt.Dispose()
End Try
End Sub
The Filter event gets the value of the filter text box that fired the event and creates a filter out of it. Using the data view class, you can apply the filter much like you apply the WHERE clause in a SQL statement. The += for each filter in the conditional statements checking if the text box values are not empty concatenates the filters together, separated by AND, and the final dv.RowFilter = filter.Remove(filter.Length - 5); removes the last AND in the filter(s).
SInce we're filtering the actual data source, the repeater is filtered since we bind it to the data source. And you get real time filtering with just pressing the enter key!
As an alternative to running the query again, you can place the data table generated from the GetProducts function in a session variable or something similar, and then create the data view from it in the Filter event.
I wil try to get a working example up of this soon. I need to install the Northwind database on my production site first. Have fun!