|
|
|
|
|
|
Author Rank:
|
|
Total page views :
14348
|
|
Total downloads :
237
|
|
|
|
|
Download
Files:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Similar ArticlesMost ReadTop RatedLatest
|
|
|
|
|
|
|
|
|
|
Introduction
This article describes the use of filter expressions in junction with an SQL Data Source. Filter expressions may be applied to the data returned from the use of an SQL Data Source to limit what is displayed; the filter expression itself is quite similar in nature to a ‘where' clause but there isn't any need to generate a new query or stored procedure as the filter expression is applied to the data collected using the SQL Data Source. For example, if one had an SQL Data Source configured to work with a stored procedure that pulled back all of the accounts for one zip code, the developer could use filter expressions to limit the account data shown by a range to dates, or a last name. This is not a replacement for parameterized queries, just another tool you can add to your arsenal.
Figure 1: Filtering Data in Grid View Control
Getting Started:
In order to get started, unzip the included project and open the solution in the Visual Studio 2008 IDE. In the solution explorer, you should note these files (Figure 2):
Figure 2: Solution Explorer
The solution contains a single web application project called "FilteredSourceVB"; the site contains a master page and a default web page.
Code: Master Page (FsSite.Master)
The master page contains a single table (2x2) with the top two cells merged to form an area for a banner, and a side bar on the left. The side bar contains a few hyperlink controls used to navigate to other pages. The lower right hand cell contains a single content panel which is used to display the default web page.
The code behind does not manage any data or perform any particular function and so there is no code to speak of; the html defines the layout of the master page and handles a few hyperlinks in the side bar:
<%@ Master Language="VB" AutoEventWireup="false" CodeBehind="FsSite.master.vb" Inherits="FilteredSourceVB.FsSite" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>SQL Data Source Filters</title>
<style type="text/css">
.style1
{
width: 100%;
}
</style>
</head>
<body>
<form id="form2" runat="server"
style="font-family: Arial, Helvetica, sans-serif; font-size: small">
<table class="style1">
<tr>
<td bgcolor="#000066" colspan="2"
height="50px">
<asp:Label ID="Label1" runat="server" Font-Bold="True" Font-
Names="Arial"
ForeColor="White" Text="SQL Data Source
Filters"></asp:Label>
</td>
</tr>
<tr>
<td align="left" bgcolor="#6699FF" height="400px" valign="top"
width="120px">
<br />
<asp:HyperLink ID="HyperLink1" runat="server"
ForeColor="White"
NavigateUrl="http://www.stop1080poison.com/">Stop 1080
Poison</asp:HyperLink>
<br />
<br />
<asp:HyperLink ID="HyperLink2" runat="server"
ForeColor="White"
NavigateUrl="http://www.thegrafboys.org/">A Shadow of
Doubt</asp:HyperLink>
<br />
<br />
<asp:HyperLink ID="HyperLink3" runat="server"
ForeColor="White"
NavigateUrl="http://kaka1080.co.nz/">Kaka
1080</asp:HyperLink>
</td>
<td align="left" valign="top">
<asp:ContentPlaceHolder ID="ContentPlaceHolder1"
runat="server">
</asp:ContentPlaceHolder>
</td>
</tr>
</table>
</form>
</body>
</html>
Code: Default.aspx
This default web page is used to display a GridView control and a few controls used to filter the data displayed in that GridView. The page also contains two SQLDataSource controls; one of which is used to populate the grid and the other to populate a drop down list of cities. Both data source controls bind to the example Northwind database in SQL Server 2005 for this example. One data source control is used to populate a drop down list with a distinct list of cities while the other is used to bind to the grid view which serves as the primary means for displaying the data; it is the second data source that is subject to filtering.
The class begins with the class declaration.
Public Partial Class WebForm1
Inherits System.Web.UI.Page
The next section of code is used to handle the page load event. To retain filtering between post backs, I opted to store the filter expression into the session and to reload to reset the filter expression if one should exist.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
If (Session("FiltExp") <> Nothing) Then
SqlDataSource1.FilterExpression = Session("FiltExp").ToString()
End If
End Sub
The next bit of code is a drop down list selected index changed even handler. This drop down list is populated using a secondary SQLDataSource which has been configured to do a select distinct query against the City field of the employee table of the Northwind example database. When the user selects a city name from the drop down list, the handler sets the SQLDataSource filter property to filter the SqlDataSource1 data source to only show those employees that reside in the selected city. After setting the expression, the filter expression is saved to the session to support reapplication of the filter during post backs.
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As EventArgs) Handles DropDownList1.SelectedIndexChanged
SqlDataSource1.FilterExpression = "city='" & _
DropDownList1.SelectedValue & "'"
Session("FiltExp") = "city='" & DropDownList1.SelectedValue & "'"
End Sub
The next bit of code it used to handle the Show By Date button click event; this method just shows a separate example of applying a filter to the same data source in order to modify the visible results of an identical set of data through the application of a filter expression. In the demonstration web application, the user may type in a date and click this button to filter the results to show only employees born after the user supplied date.
Protected Sub btnShowByDate_Click(ByVal sender As Object, ByVal e As
EventArgs) Handles btnShowByDate.Click
If (Not String.IsNullOrEmpty(txtDay.Text) And _
Not String.IsNullOrEmpty(txtMonth.Text) And _
Not String.IsNullOrEmpty(txtYear.Text)) Then
Dim dt As New DateTime(Convert.ToInt32(txtYear.Text), _
Convert.ToInt32(txtMonth.Text), _
Convert.ToInt32(txtDay.Text))
SqlDataSource1.FilterExpression = "BirthDate > #" + dt + "#"
Session("FiltExp") = "BirthDate > #" + dt + "#"
End If
End Sub
The last example use of filters in this demonstration merely shows that they can be removed entirely; the show all button click event hander merely sets the filter expression to null; doing show will result in the redisplay of entire result set without any filtering.
Protected Sub btnShowAll_Click(ByVal sender As Object, ByVal e As
EventArgs) Handles btnShowAll.Click
SqlDataSource1.FilterExpression = Nothing
Session("FiltExp") = Nothing
End Sub
End Class
Summary
The article is pretty short and simple. The intent was only to show how an SQLDataSource may be filtered on the fly using the Filter Expression property. This is not the only way to do this, one could for example use parameterized queries instead.
|
|
|
Login
to add your contents and source code to this article
|
|
|
|
|
|
|
|
Scott Lysle
Freelance software developer residing in Alabama. Bachelors, Masters Degrees from Wichita State University. I spent the first half of my career working on aircraft controls and displays and in that time I worked on the cockpits for the OH-58 AHIP, the AH-1W, the V-22, the F-22, the C-130J, the C-5 AMP, AWACS, JPATS, and a few others. Since 1997 I have been largely involved with Windows and web development, GIS application development, consumer electronics development (embedded linux/java), but still sometimes work on aircraft and military projects, the most recent of which was the presidential transport helicopter. I tend to work primarily with C/C++, Java, VB, and C#.
|
|
|
|
|
|
|
|
|
C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional
consulting company, our consultants are well-known experts in .NET and many of them
are MVPs, authors, and trainers. We specialize in Microsoft .NET development and
utilize Agile Development and Extreme Programming practices to provide fast pace
quick turnaround results. Our software development model is a mix of Agile Development,
traditional SDLC, and Waterfall models.
|
|
Click here to learn more about C# Consulting. |
|
|
|
|
|
|
|
Introducing MaxV - one click. infinite control. Hyper-V Hosting from MaximumASP.
Finally – a virtual platform that delivers next-generation Windows Server 2008 Hyper-V virtualization technology from a managed hosting partner you can truly depend on. Visit www.maximumasp.com/max for a FREE 30 day trial. Hurry offer ends soon.
Climb aboard the MaxV platform and take advantage of High Availability, Intelligent Monitoring, Recurrent Backups, and Scalability – with no hassle or hidden fees.
As a managed hosting partner focused solely on Microsoft technologies since 2000, MaximumASP is uniquely qualified to provide the superior support that our business is built on. Unparalleled expertise with Microsoft technologies lead to working directly with Microsoft as first to offer IIS 7 and SQL 2008 betas in a hosted environment; partnering in the Go Live Program for Hyper-V; and product co-launches built on WS 2008 with Hyper-V technology.
|
Dynamic PDF
ceTE software specializes in components for dynamic PDF generation and manipulation. The DynamicPDF™ product line allows you to dynamically generate PDF documents, merge PDF documents and new content to existing PDF documents from within your applications.
|
Go.NET
Build custom interactive diagrams, network, workflow editors, flowcharts, or software design tools. Includes many predefined kinds of nodes, links, and basic shapes. Supports layers, scrolling, zooming, selection, drag-and-drop, clipboard, in-place editing, tooltips, grids, printing, overview window, palette. 100% implemented in C# as a managed .NET Control. Document/View/Tool architecture with many properties&events. Optional automatic layout.
|
Dundas Software
Dundas Chart for .NET is the most advanced .NET charting package available today. With an extremely complete feature set, elegant architecture and easy implementation, Dundas Chart can quickly add advanced Charting functionality to enhance and transform ASP.NET and Windows Forms applications. Whether you are implementing charting into internal projects, or building applications for clients, Dundas Chart offers advanced technology and advanced results to get the most out of data.
|
60 FREE UI Controls from DevExpress
Register for your FREE copy on over 60 free presentation controls from
DevExpress - Absolutely Free-of-Charge without any royalties or distribution
costs. Visit Devexpress.com/60 today. Free controls include advanced lists box, dropdown calendar, rich text edit, spin
edit, tab control and so much more!
DevExpress engineers feature rich presentation controls and reporting tools for WinForms, ASP.NET, WPF, and Silverlight. Our technologies help you build your best, see complex software with greater clarity and deliver compelling business solutions for Windows and the web in the shortest possible time.
|
Clickatell's SMS Gateway
Clickatell's Developer Solutions allow you to SMS enable any website or
application via a range of API's. Learn More about our API connections.
|
Microsoft Visual Studio 2010
Visualize your workspace with new multiple monitor support, powerful Web development, new SharePoint support with tons of templates and Web parts, and more accurate targeting of any version of the .NET Framework. Get set to unleash your creativity.
|
Nevron Chart for .NET 2010.1 Now Available
The leading .NET charting control now features PDF, Flash and Silverlight export, visualization of large datasets and more. Deliver true charting functionality to your BI, Scorecard, Presentation or Scientific apps. Download evaluation now.
|
Developer-Ready ASP.NET 2.0 Web Hosting with 3 MONTHS FREE
Now supporting .NET 3.0 Framework with Windows Workflow Foundation, Windows Communication Foundation (WCF), Windows Presentation Foundation (WPF), windows CardSpace (WCS)! Providing more flexibility for Developers with Web Services Support and a User/Permission Manger. Also supporting MS SQL 2005/2000 with Real-Time Backups, FREE Automated Attach .MDF Tool, FREE SQL Restore and Shrink SQL DB Tools, and SQL
|
|
|
|
|
|
|
|
|
|
|
|
|