asp:feature
LANGUAGES: SQL
ASP.NET VERSIONS: 2.0
Bind Your Data in ASP.NET 2.0
Get to know the new Data Source Controls, code-free data binding, and the new GridView and DetailsView Web controls.
By Steven A. Smith
One of the most powerful features of ASP.NET 1.x was its introduction of rich data-bound controls, such as the DataGrid. Although the DataGrid provides a great deal of functionality, it invariably requires you to write run-time code to enable some of its features. At a minimum you must call its DataBind method and, for more complex functionality such as sorting, paging, or editing, you must add a great deal of code to make everything work correctly. One goal of ASP.NET 2.0 is to reduce this data-binding code overhead and encapsulate more of this work within the controls themselves. In this article, I will demonstrate how ASP.NET's new Data Source Controls allow declarative "code-free" data binding, and I'll show off some of the features of the new GridView and DetailsView Web controls.
Rather than rehashing the documentation of these new features, I'll demonstrate their usage via a scenario. The scenario involves the standard pubs sample database included with Microsoft SQL Server. The scenario requires you to display the contents of the Authors table and show that this display provides paging, bidirectional sorting, and filtering capabilities. In addition, you also should display the selected author's titles on the page (below the Authors table). Finally, these titles should be editable, including the ability to add new rows to the table.
Explore the Data Source Controls
To begin, you need a way to talk to the pubs database. In ASP.NET 1.0, you had to write code to create a database connection and pull out a DataSet or DataReader with the author's data in it, then write more code to bind this to your DataGrid. With ASP.NET 2.0, this no longer is necessary. Instead, you can place a Data Source Control, such as the SqlDataSource for relational databases, on the Web form like any other control. You set a few properties, either within the control's tags in the HTML or using the Visual Studio designer's property grid, and you're done. In this case, a simple SqlDataSource for the Authors table of the pubs database might look like this:
connectionstring="server=aspsmith;database=pubs; Integrated Security=true " SelectCommand="SELECT * FROM Authors" /> You can use Data Source Controls for more than reading
data - they can also perform updates, deletes, and inserts. You can configure
them to use dynamic parameters, and they can call stored procedures as easily
as custom SQL statements. In addition to the SqlDataSource, other Data Source
Controls include the XmlDataSource, DataSetDataSource, SiteMapDataSource,
AccessDataSource, and ObjectDataSource. The Data Source Controls specified
declaratively on the Web form allow for code-free data binding by other Web
controls on the form. In ASP.NET 1.x, you achieve data binding by setting the
DataSource property of an appropriate control, then calling its DataBind
method. Although you could set the property declaratively, more typically both
tasks were performed at run time, resulting in two lines of code necessary for
every data-bound control on every page in an application. One key motivation
behind Data Source Controls is to eliminate this common code and let you
specify the data-binding behavior of controls declaratively. One of the new controls available with ASP.NET 2.0 is the
GridView control, which is a more powerful version of the DataGrid control
(which, don't worry, is still available). To wire up a GridView to a
SqlDataSource, all you need to do is set the GridView's DataSourceID property
to the id of the SqlDataSource. So in this case, your simple GridView
declaration looks like this: DataSourceID="pubs" /> At run time, with only these two Web controls on the page,
an HTML table listing all the columns and rows in the Authors table is
rendered. Note that I haven't written a single line of code to accomplish this. Use the GridView Control Getting back to the scenario requirements, you need to
display the authors in a table with paging and sorting enabled. You also want
to show only the columns in which you're interested, not all the columns in the
table, so you need to specify the columns explicitly rather than having the
GridView generate them automatically. To do this (and to enable sorting and
paging), set the AutoGenerateColumns property to false, the EnableSorting
property to true, and the EnablePaging property to true. Then, add a few
columns (named xxxField controls rather than xxxColumn controls, to distinguish
them from DataGrid columns), and this source code creates the output shown in
Figure 1: DataSourceID="PubsDataSource" AllowSorting="true" AllowPaging="true"
AutoGenerateColumns="false"> headertext="Last Name" sortexpression="au_lname"
/> headertext="First Name"
sortexpression="au_fname" /> headertext="City" sortexpression="city" /> headertext="State" sortexpression="state"
/> You can sort column-specific results by clicking on any of
the column headers that have a defined sortexpression; clicking on the same
header successively reverses the sort direction. The default pager style lists
the current page and links to other pages (in Figure 1, pages 2 and 3 are
links). You can customize this to use Next and Previous links and First and
Last links, and you can specify images for these links instead of using text
links. Before you add filtering to the results, you want to
optimize them a bit. You see, by default the Data Source object loads the
results on every page load, which results in database hits every time the user
sorts by a different column or selects a new page. Because this data is not
terribly volatile, it's safe to cache it for a few minutes at a time. You can
do this without relying on page caching by using the Data Source object's
EnableCaching and CacheDuration parameters. By setting these to true and 300,
respectively, you can cache your pubs result set for 300 seconds (five
minutes). Here is the final version of the GridView's Data Source object, with
caching enabled: connectionstring="server=aspsmith;database=pubs; _ Integrated Security=true " SelectCommand="SELECT * FROM Authors" EnableCaching="true" CacheDuration="300" /> With caching enabled, you're ready to add some filtering;
I've allowed filtering by last name or by state. To do this, add two more Web
controls: a TextBox for the last name and a DropDownList for the state. The
DropDownList will be populated with all the states that exist in the Authors
table, letting the user select any one of these states to see only authors from
that state. The TextBox will let the user enter a string and view only authors
whose last name matches that string. The TextBox is simple: Next, bind the DropDownList to another Data Source object
to fill it with the states that exist in the Authors table. The Data Source
object for the DropDownList looks like this (with caching enabled): runat="server" connectionstring= "server=aspsmith;database=pubs;uid=sa;pwd=" selectcommand="SELECT DISTINCT state _ FROM Authors ORDER BY state" enablecaching="true" cacheduration="300"
/> The DropDownList, which is populated with the values from
this data source, looks like this: datatextfield="state"
datasourceid="PubsStatesDataSource" autopostback="true" /> Now at run time, the DropDownList databinds to the PubsStatesDataSource
and produces a listing of unique states from the Authors table. The next step
is to filter the AuthorsGrid display based on this selection, which in ASP.NET
1.x would have required writing some code in the DropDownList's SelectedIndexChanged
event handler. In this case, the last three properties (DataTextField,
DataSourceId, and AutoPostBack) provide the control with enough information to
let it perform the filtering without writing a single line of code. To add filtering, modify the PubsDataSource to include
parameters. Data Source Controls support several sets of parameters to coincide
with the different kinds of data access they can perform. In this case, you
want to use the SelectParameters collection because the parameters will be applied
to your Select command and will populate it with parameters for last name and
state. You also need to modify my query so it makes use of these parameters.
The new PubsDataSource declaration is shown here: connectionstring="server=aspsmith;database=pubs; _ Integrated Security=true" selectcommand="SELECT * FROM Authors WHERE au_lname = _ ISNULL(NULLIF(@au_lname,''),au_lname)" FilterExpression="state='@state'" enablecaching="true" cacheduration="300" > propertyname="Text" name="au_lname" treatemptystringasnull="false" /> propertyname="SelectedValue" name="state"
/> A bit of trickery is involved in the SQL for this code to
work properly because there is no way in the current 2.0 build to send a DBNull
as the value of a ControlParameter without writing some code; I'm told this
should be corrected by the time 2.0 ships. Because I'm trying to avoid writing
code for this article, I've forced the ControlParameter to send empty strings
as the parameter values, then I'm converting the empty strings to nulls in the
query and ignoring them using a SQL ISNULL statement. The end result is if the
Last Name TextBox has no value set, all rows matching the rest of the query
will be returned rather than only those rows with an empty string for the
au_lname column. Note that there are two ways to filter the results of a
select query: SelectParameters and FilterParameters. The SelectParameters data
is sent to the database and results in a separate resultset being returned for
each variation in parameters. The FilterParameters collection works in concert
with the SqlDataSource's FilterExpression property, and it filters the
resultset after it is returned from the database server. This is analogous to
using a DataView and its FilterExpression property: Using SelectParameters
results in doing more work on the database server, while using FilterParameters
and FilterExpression results in doing more work on the Web server. There are
pros and cons to both approaches, but the latter can be very effective
(especially when the data source is being cached), because it reduces trips to
the database drastically and thus usually improves page performance. Display Titles At this point, when the page is first loaded it displays
the first 10 authors in the state of California. Thereafter, you can filter
these results by state or last name. Now you are ready to display titles
associated with an author. You need a way to select a row in the AuthorsGrid. You
could add another ColumnField with a button in it, but the GridView creates a
select button automatically if you set AutoGenerateSelectButton to true. Once
that is set, make the selected row stand out by using
SelectedRowStyle-BackColor="WhiteSmoke" to change its back color. Finally, you
need to specify which column to use to filter the TitlesGrid, so specify
DataKeyNames="au_id". Note that if you need to specify a composite key
(multiple columns), you could separate each column name with a comma. Now add another SqlDataSource to the page to represent the
titles, and define it like this: connectionstring="server=aspsmith; _ database=pubs;uid=sa;pwd=" selectcommand="SELECT t.*, _ ta.au_id FROM Titles
t INNER JOIN TitleAuthor ta on
t.title_id = ta.title_id" filterexpression="au_id='@au_id'" enablecaching="true" cacheduration="300" > name="au_id"
propertyname="SelectedValue" /> Then define the GridView that uses this data source: DataSourceID="PubsTitlesDataSource"
AllowSorting="true" autogeneratecolumns="false"> headertext="Title" sortexpression="title" /> headertext="Type" sortexpression="type" /> headertext="Price" sortexpression="price" dataformatstring="{0:c}"/> headertext="Date
Published" sortexpression="pubdate" /> You've now met all the requirements for this scenario
except allowing edits of the titles. Although you certainly could do this using
the existing TitlesGrid control (with no code!), I'll use the new DetailsView
control for demonstration purposes and to let me access more of the fields than
listed in the TitlesGrid. Sort Out the Details The DetailsView control provides a way of working with a
single result-set element and uses a vertical layout with each column of the
result on a line of its own, rather than laying out the results horizontally as
in the GridView control. To complete the requirements of the scenario, add a
select button to the TitlesGrid and have that selection populate a DetailsView
in edit mode. You also need to add a button for adding new titles. The
DetailsView shares a lot of the same methods and properties with the GridView,
so it's pretty easy to get working if you're familiar with the GridView. I pretty much follow the same pattern as the TitlesGrid:
Add another SqlDataSource (this one named PubsTitleDetailsDataSource), then add
the DetailsView (named TitleDetails). This SqlDataSource includes more than a
SelectCommand, though; it also includes commands for Insert, Update, and
Delete, to support the editing requirements of the scenario. Figure 2 shows the
final data-source declaration for the PubsTitleDetailsDataSource control. runat="server" connectionstring="server=aspsmith; _ database=pubs;uid=sa;pwd=" selectcommand="SELECT * FROM titles" updatecommand="UPDATE titles SET title=@title, type=@type, pub_id=@pub_id,
price=convert(money,@price), advance=convert(money,@advance), royalty=@royalty, ytd_sales=@ytd_sales,
notes=@notes, pubdate=@pubdate WHERE
title_id=@title_id" deletecommand="DELETE titleauthor WHERE title_id=@title_id DELETE titles WHERE title_id=@title_id" insertcommand="INSERT INTO titles (title_id, title, type,
pub_id, price, advance, royalty, ytd_sales,
notes, pubdate) VALUES (@title_id,
@title, @type, @pub_id, convert(money,@price),
convert(money,@advance), @royalty, @ytd_sales,
@notes, @pubdate) INSERT titleauthor
(title_id, au_id) VALUES (@title_id,
@au_id)" filterexpression="title_id='@title_id'" enablecaching="false" > name="au_id"
propertyname="SelectedValue" /> name="title_id" propertyname="SelectedValue" /> Figure 2.This is
the code for PubsTitleDetailsDataSource, supporting insert, update, delete, and
select operations. Note that you don't need to define the parameters for the
insert and update statements explicitly so long as their naming convention
matches the column they represent. The DetailsView definition then follows the definition of
the TitlesGrid, except for the obvious fact that it's a DetailsView control,
not a GridView. To enable full editing capabilities, including update, delete,
and insert, I've specified that the appropriate buttons for these commands be
generated automatically: datasourceid="PubsTitleDetailsDataSource" datakeynames="title_id" autogeneratedeletebutton="true" autogenerateeditbutton="true" autogenerateinsertbutton="true"
/> Finishing Touches (and Code) At this point, you have basic functionality meeting the
requirements of the scenario. When you change the currently selected author,
however, the titles do not update automatically. Similarly, when you select an
author and see his titles, the title details do not update or disappear
automatically. You need to manage these behaviors with some run-time code in
the event handlers for the controls involved. A bit of code to reset the
selected index of the AuthorsGrid whenever it is re-bound, and to rebind the
TitlesGrid as well, solves the first problem. Similar code for the TitlesGrid
ensures the DetailsView is displayed only while an item in the TitleGrid is
selected. Download the complete source code for this article from the asp.netPRO Web site to see these
finishing touches in full (see the Download box for details). Figure 3 shows
the final page with the master and child GridViews and the DetailsView for a
title. You easily can produce rich, data-driven Web forms in
ASP.NET 2.0 using virtually no run-time code, especially with tools such as
Visual Studio .NET. The new Data Source Controls, coupled with powerful new
data display controls such as GridView and DetailsView, make many of the most
common data manipulation and display tasks simple and code-free. The sample code in this
article is available for download. Steven A. Smith (mailto:[email protected]) owns
ASPAlliance.com (http://www.ASPAlliance.com)
and AspAdvice.com (http://www.AspAdvice.com),
two popular ASP.NET developer community sites. He has been recognized as a
Microsoft MVP and ASPInsider, is a regular INETA speaker, and is co-author of ASP.NET Developer's Cookbook (Sams). Steven
provides custom, onsite .NET training through his training company,
ASPSmith.com. 
Figure 1. This shows the output of
the page after the Last Name column has been clicked on twice (resulting in a
descending sort).
Figure 3. Here's the master detail
page, including paging, sorting, and editing - and no code.
