Bind Your Data in ASP.NET 2.0 - 30 Oct 2009

Get to know the new Data Source Controls, code-free data binding, and the new GridView and DetailsView Web controls.

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" />

  

 


Figure 1. This shows the output of the page after the Last Name column has been clicked on twice (resulting in a descending sort).

 

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 [email protected], [email protected],

  [email protected]_id, price=convert(money,@price),

  advance=convert(money,@advance), [email protected],

  [email protected]_sales, [email protected],

  [email protected] WHERE [email protected]_id"

deletecommand="DELETE titleauthor WHERE [email protected]_id

  DELETE titles WHERE [email protected]_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.

 


Figure 3. Here's the master detail page, including paging, sorting, and editing - and no code.

 

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.

 

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish