Databases Reference
In-Depth Information
If the user enters a search string for
Title
, the second
Where
method is called, and the
SQL query looks like the following:
select * from Customers where ContactTitle = @Title
Only if the user entered both
City
and
Title
search strings, both
Where
methods are
called, and the SQL query includes conditions for both columns:
select * from Customers where City = @City and ContactTitle = @Title
In other words, with the help of LINQ, you can build focused queries that include only
those conditions required for the search values supplied by the user. This helps the data-
base server choose a more efficient execution plan than a typical hand-coded SQL (shown
next) that tries to handle both search conditions and usually requires the database server
to perform a full table scan:
select * from Customers
where City = ISNULL(@City, City)
and ContactTitle = ISNULL(@Title, ContactTitle)
A filter template is a user control that encapsulates both presentation and data access logic
necessary to implement query filtering for a single column. In traditional search pages,
this logic is usually spread over the markup and code-behind files. Consider the filtering
logic required for the City column in our Customer search page (see the markup in Listing
5.1).
LISTING 5.1
Simple Search Page (Markup)
<%@ Page Title=”” Language=”C#”
MasterPageFile=”~/Site.master” CodeBehind=”SamplePage.aspx.cs”
Inherits=”WebApplication.Samples.Ch05.LinqFilter.SamplePage” %>
<asp:Content ContentPlaceHolderID=”main” runat=”server”>
Customer City:
<asp:TextBox runat=”server” ID=”cityFilter” />
Title: <asp:TextBox runat=”server” ID=”titleFilter” />
<asp:Button runat=”server” Text=”Find” OnClick=”Find_Click” />
<asp:GridView ID=”gridView” runat=”server” />
</asp:Content>
The traditional page includes a
TextBox
control where the user can enter a value to search
for in the City column. The data access logic for filtering rows based on this value is a part
of the
Find_Click
event handler shown in Listing 5.2.
