Building an Efficient Search Page Using QueryExtender control in ASP.Net 4.0

Building a search page is one of the very common and repeated tasks we do in any data driven websites. To do this, we will build a select query with where clause based on the search parameter supplied through the input controls on the page. With the introduction of .Netframework 2.0, the data access is made easier with the help of new set of data access controls called DataSource controls. But, building complex filtering conditions that is required to build a search page is still complex with the help of existing datasource controls. Understanding this need, Microsoft has introduced a new extender control called QueryExtender control with ASP.Net 4.0(Currently in beta 1) release that works with LINQ.

What is QueryExtender control? A QueryExtender control can be used to do efficient filtering on data at the database end with the help of LINQ technology. This means this control works adjacent with the LINQDataSource and EntityDataSource control to do filteration at the back end of your application. Pre-Requisites Visual Studio 2010 and ASP.Net 4.0 The current version of visual studio 2010 and .Netframework 4.0 is beta 1. You can download the Visual Studio 2010 beta 1 and .net FX 4 from here.  To know more about downloading and installing newer version of Visual Studio, please refer here.  Download the products and install it. Note Since the products are still in beta, the functionality discussed here are subject to change. If you have feedback or bug, please submit at the visual studio support page. To have a better understanding, we will build a sample application that searches employee information from the table Employees. Steps 1.      Open Visual Studio 2010. 2.      Click New> Website and Select “ASP.Net Web Site”. You can select the language of your choice. I have selected C#. Rename the website name as per your need. 3.      Include a new SQL express database inside App_Data folder and create a table called Employees. Note You can add a database by right clicking App_Data folder in the solution and clicking Add New Item. This will bring a dialog box where you need to select “Sql Server Database” and click Add. Then, create a table called Employees with the necessary columns using the “Server Explorer”. Just right click the added database and click “Open” to open your database using Server Explorer on the left pane of your Visual Studio 2010. Refer my Server explorer, As i said earlier, the new QueryExtender control will work along with LINQDataSource control. Hence, to proceed with we need to first design the LINQ to SQL classes. To do this, right click your project in solution explorer and click “Add New Item..”. Select “Linq to SQL Classes” and click OK. You can rename the class if you require. Designing the LINQ to SQL classes I assume you have already created a SqlExpress database in APP_Data folder with Employees table. Open Server Explorer, Expand the database tables. Drag Employee table into LINQ to SQL designer. The LINQ to SQL Objects will be created automatically. Click Save.  Refer the below figure. Next, we will design our search page with the help of LINQDataSource control and QueryExtender control. Creating Search Page Drag a GridView control and LINQDataSource control from the data tab of visual studio 2010. Configure the LINQDataSource control to use our DataContext class. Next, configure the GridView control’s DataSourceID property to the ID of LINQDataSource control. Refer the code below, <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"         DataKeyNames="EmpNo" DataSourceID="LinqDataSource1">         <Columns>             <asp:BoundField DataField="EmpNo" HeaderText="EmpNo" InsertVisible="False"                 ReadOnly="True" SortExpression="EmpNo" />             <asp:BoundField DataField="EmpName" HeaderText="EmpName"                 SortExpression="EmpName" />                  <asp:BoundField DataField="Age" HeaderText="Age"                 SortExpression="Age" />             <asp:BoundField DataField="Address" HeaderText="Address"                 SortExpression="Address" />             <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />             <asp:BoundField DataField="Country" HeaderText="Country"                 SortExpression="Country" />             <asp:CheckBoxField DataField="IsTemporaryEmployee"                 HeaderText="IsTemporaryEmployee" SortExpression="IsTemporaryEmployee" />         </Columns>     </asp:GridView>     <asp:LinqDataSource ID="LinqDataSource1" runat="server"         ContextTypeName="DataClassesDataContext" EntityTypeName=""         TableName="Employees">     </asp:LinqDataSource> Execute the page. You can see the employee information populated on the screen. Next, we will add some search functionality in the page and use QueryExtender control to configure search. To have a simple understanding, we build our search page that searches based on the employee name typed in a textbox control. To do this, we will drag a TextBox control and Button control to the page.  Now, we will add a QueryExtender control that uses our LINQDataSource control to provide search based on employee name. The QueryExtender control will perform the filtration based on the filtration parameter configured with the QueryExtender control. In our case, it is SearchExpression that needs to be configured to get the parameter value from textbox control for searching. Refer the code below, Name: <asp:TextBox ID="txtName" runat="server"></asp:TextBox><br /> <asp:Button ID="Button1" runat="server" Text="Search" /> //GridView code, Refer the code in previous section  <asp:LinqDataSource ID="LinqDataSource1" runat="server"         ContextTypeName="DataClassesDataContext" EntityTypeName=""         TableName="Employees">     </asp:LinqDataSource>      <asp:QueryExtender ID="QueryExtender1"  TargetControlID="LinqDataSource1" runat="server">                    <asp:SearchExpression DataFields="EmpName"         SearchType="StartsWith">         <asp:ControlParameter ControlID="txtName" />          </asp:SearchExpression>        </asp:QueryExtender> When you execute the page, you may get the following error “Unknown server tag 'asp:SearchExpression'” with visual Studio 2010 beta 1. To fix this error, add the following tag prefix and namespace to the controls section of Pages tag in web.config file, <pages> <controls>         <add tagPrefix="asp" namespace="System.Web.UI.WebControls.Expressions" assembly="System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />        .... </controls> </pages> Now, execute the page and you can see the search in section. Refer the below figure, In coming sections, we will try to implement more searching options the QueryExtender offers us.
Search based on Range of Values Sometimes, it is required to search based on range of values. For example, to search employees based on the age that falls in a range (From till To). To do this, we can use RangeExpression packed with QueryExtender control. Name: <asp:TextBox ID="txtName" runat="server"></asp:TextBox><br />         Age From: <asp:TextBox ID="txtFrom" runat="server"></asp:TextBox><br />         Age To: <asp:TextBox ID="txtTo" runat="server"></asp:TextBox><br />         <asp:Button ID="Button1" runat="server" Text="Search" /> //GridView code, Refer the code in previous section     <asp:LinqDataSource ID="LinqDataSource1" runat="server"         ContextTypeName="DataClassesDataContext" EntityTypeName=""         TableName="Employees">     </asp:LinqDataSource>      <asp:QueryExtender ID="QueryExtender1"  TargetControlID="LinqDataSource1" runat="server">                    <asp:SearchExpression DataFields="EmpName"         SearchType="StartsWith">                <asp:ControlParameter ControlID="txtName" />           </asp:SearchExpression>       <asp:RangeExpression DataField="Age" MinType="Inclusive"      MaxType="Inclusive">         <asp:ControlParameter ControlID="txtFrom" />         <asp:ControlParameter ControlID="txtTo" />     </asp:RangeExpression>       </asp:QueryExtender> Execute the page and see it in action. Refer the below figure, Search based on a Value We can use PropertyExpression if we want to search based on a value. For example, if we want to search employees based on value on a column like age, name etc. To list employee who are permanent and age equal to a value, Name: <asp:TextBox ID="txtName" runat="server"></asp:TextBox><br />         Age From: <asp:TextBox ID="txtFrom" runat="server"></asp:TextBox><br />         Age To: <asp:TextBox ID="txtTo" runat="server"></asp:TextBox><br />         Temporary Employee: <asp:CheckBox ID="chkIsTempEmployee" runat="server" /><br />          Age <asp:TextBox ID="txtAge" runat="server"></asp:TextBox><br />         <asp:Button ID="Button1" runat="server" Text="Search" />         <asp:LinqDataSource ID="LinqDataSource1" runat="server"         ContextTypeName="DataClassesDataContext" EntityTypeName=""         TableName="Employees">     </asp:LinqDataSource>      <asp:QueryExtender ID="QueryExtender1"  TargetControlID="LinqDataSource1" runat="server">                    <asp:SearchExpression DataFields="EmpName"         SearchType="StartsWith">                <asp:ControlParameter ControlID="txtName" />          </asp:SearchExpression>       <asp:RangeExpression DataField="Age" MinType="Inclusive"      MaxType="Inclusive">         <asp:ControlParameter ControlID="txtFrom" />         <asp:ControlParameter ControlID="txtTo" />     </asp:RangeExpression>    <asp:PropertyExpression>     <asp:ControlParameter ControlID="chkIsTempEmployee" Name="IsTemporaryEmployee" />   </asp:PropertyExpression>    <asp:PropertyExpression>     <asp:ControlParameter ControlID="txtAge" Name="Age" />   </asp:PropertyExpression>     </asp:QueryExtender> Execute the page and see it in action. Refer the below figure, Clearly, the new QueryExtender control has made some of the advance search implementation very easier.