ONDotNet.com    
 Published on ONDotNet.com (http://www.ondotnet.com/)
 See this if you're having trouble printing code examples


Liberty on Whidbey

Data Binding in ASP.NET 2.0

by Jesse Liberty
02/22/2005

In a previous article I demonstrated how to bind data within a Windows Form. In this article I will show how to bind data to ASP.NET controls. Not only has Microsoft made radical changes in how this is done between ASP.NET 1.x and 2.0, but it has also created significant differences between how this is done in Windows Forms and ASP.NET in 2.0. This keeps life interesting (he said, gnashing his teeth).

The code used in this example has been tested with the November Community Technical Preview of Beta 2 of Whidbey.

As the previous article noted, in the next version of the .NET Framework a great deal of the ADO.NET object model has been incorporated into controls that let you interact with data declaratively, and that spare you from writing boilerplate code to create data sets, extract tables, bind tables or views to controls, and so forth. In this article, you'll create a simple Web Form that lets you page through the details of the Orders table from the Northwind database.

Start with the Fundamentals

Begin by creating a new ASP.NET application (call it ASPNW), which you will use to display information from the Northwind database. (For the location, choose File System and put it in whatever directory is convenient for you. The downloadable source code will use C:\WebSites\Oreilly\ASPNW.)

We'll be working with the Customers table in the Northwind database, so rename your aspx file from default.aspx to Customers.aspx. (Don't forget to change the class name both in the code file and in the page directive!)

Create a Data Connection

You need a connection to the database. You can explicitly create one, or you can use a control that depends on having a connection, and one will be created for you. Let's start by explicitly creating one. Drag a DataSource control onto the form, as shown in Figure 1:

Figure 1
Figure 1: Dragging a DataSource control

The SqlDataSource control will appear on your form. Click on the smart tag and choose Configure Data Source, as shown in Figure 2.

Figure 2
Figure 2: Using the smart tag

(If you do not see the SqlDataSource control, choose View -> Non Visual Controls.)

Clicking on the Configure Data Source option opens the wizard where you can choose your data source and data provider. Now things get tricky. When Visual Studio 2005 ships, the preferred database will be SQL Server or SQL Server Express. As of this writing, however, Microsoft has not yet provided Northwind for SqlServerExpress; so for this article we'll make a connection to Microsoft SQL Server. (Adjusting for Access is left as an exercise for the reader but is shown in the figure.) Figure 3 shows how to specify the data provider.

Figure 3
Figure 3: Specifying the data provider

Once you've chosen your data provider, you have the option to save the connection string in the application file. (The alternative is to save the connection string in the page as a property of the control.) The next step is to specify your query or to pick the columns you want from a specific table. For this example, you'll choose all the columns from the Customers table, as shown in Figure 4.

Figure 4
Figure 4: Choosing columns

While you are here, click on the Advanced button to see that you can instruct the wizard to generate the Update commands you'll need later in this chapter to update the database. For now, you can leave this unchecked. The next step in the wizard lets you test your query; clicking on Finish creates the connection.

In ASP.NET 2.0, the entire ADO.NET object model is abstracted into DataSource controls. That may be the most important sentence in this article. Every interaction with the database will be through one or more DataSource controls. Once you grok that, the rest is easy.

DataSource Controls

The DataSource control provides a single object that you can define either declaratively (in your web page) or programmatically (in your code-behind) that will own the connection information, the query information, and the parameters and the behavior (such as paging and caching). You can then "bind" the object to various UI objects for display on your web page.

Related Reading

ADO.NET in a Nutshell
By Bill Hamilton, Matthew MacDonald

There are a variety of DataSource controls, including controls for accessing SQL from SQL Server, from ODBC or OLE DB servers, from XML files, and from business objects. All of these DataSource controls expose the same properties and methods, and all of them bind to UI objects (such as DataList and GridView) in the same way.

Thus you have a variety of UI controls all binding in the same way to a variety of underlying data sources, and the details (the plumbing) are handled by the DataSource controls for you, greatly simplifying even complex data manipulation tasks in web applications.

ASP.NET 1.x developers will find significant changes (and simplifications) in how your ASP.NET application interacts with underlying data. The ADO.NET object model has been encapsulated in controls, and the details (data sources, tables, relations, and so on) have been pushed down into the framework layer. This frees you from worrying about the details and provides a much more uniform API for interacting with various types of data sources.

Binding Data Controls with Drag and Drop

Now that we have a working DataSource control, let's hook it up to a control to display the data we've retrieved. Drag a GridView onto the page. The GridView recognizes that there is already a SqlDataSource on the page and so does not create its own. Instead, its smart tag opens and asks you to choose your data source, as shown in Figure 5:

Figure 5
Figure 5: Choosing a data source

As soon as you set the data source, the DataGrid is redrawn, with a column for each field returned by the data source. Notice that the column headers have also been filled in for you. Switch to source view, and examine the declaration of the DataGrid. Visual Studio 2005 has done a lot of work for you. It has examined the data source and created BoundField for each column in the data. Further, it has set the HeaderText to the name of the DataField. Finally, you'll notice on the third line of the declaration of the DataGrid that it has set AutoGenerateColumns to false.

If you were creating the DataGrid by hand, and if you were going to let the DataGrid create all the columns right from the retrieved data, you could greatly simplify the code by just setting AutoGenerateColumns to true.

So why bother with turning off AutoGenerateColumns and creating the columns by hand? The answer is that doing so gives you much greater control: you can, for example, set the headings on the columns (for instance, changing ContactTitle to Title), leave columns you don't need, and add new columns for manipulating the rows.

You can make these changes by hand-coding the HTML in the source view, or by clicking on the smart tag for the DataGrid and choosing Edit Columns. Doing so brings up the Fields dialog box, as shown in Figure 6.

Figure 6
Figure 6: Specifying the fields

The dialog box is divided into three main areas: the list of available fields, the list of selected fields (with arrows to remove or reorder the list), and the Bound Filed Properties window on the right. Click on a selected field (such as ContactTitle), and you can set the attributes of how that field will be displayed in the DataGrid (for example, changing the header to Title).

Adding features to the grid

While we're examining what we can do with the DataGrid, click on its smart tag and click on AutoFormat to choose one of the nice formatting options. You can, of course, format it by hand, but why work so hard? I'll choose BrownSugar because it shows up well on this page. While you're at it, click on Enable Sorting. (Hey! Presto! The columns can be sorted.) Run the application. See Figure 7.

Figure 7
Figure 7: The completed grid

Adding Insert, Update, and Delete Statements

The DataSource control that you've created currently has only a Select statement to extract data from the database:


<asp:SqlDataSource ID="SqlDataSource1" 
runat="server" SelectCommand="SELECT * FROM [Customers]" 
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" />

You can, however, ask your DataSource control to create the remaining three CRUD (Create, Retrieve, Update, and Delete) commands, using a wizard to make your work much easier. Switch to data view, click on the SqlDataSource's smart tag, and choose Configure Data Source. The Configure Data Source wizard opens, displaying your current connection string. Click on Next, and the Configure Select Statement dialog is displayed. Click on the Advanced button, as shown in Figure 8.

Figure 8
Figure 8: Selecting all columns

This opens the Advanced SQL Generation options. This is similar to the Data Adapter wizard in ASP.NET 1.1. Check the Generate Insert, Update and Delete statements check box, as shown in Figure 9.

Figure 9
Figure 9: Advanced SQL generation

Clicking on this check box instructs the wizard to create the remaining three CRUD methods, and it also enables the second check box: Use optimistic concurrency. Do not check this yet. Click on OK, Next, and Finish. You will be asked to update your grid, which unfortunately will wipe out all your careful work setting titles, and so on (is this a beta bug?), but the good news is that you are now bound to a DataSource control that provides all four CRUD methods. Take a look at the HTML generated for the DataSource control. You'll find Delete, Update, and Insert commands, complete with the parameters needed for each command.

Multiuser Updates

As things stand now, you read data from the database through the SqlDataSource and into your DataGrid. You have now added the ability to update (or delete) that information. Of course, more than one person may be interacting with the database at the same time. (Few web applications support only single-user access.) You can imagine that this could cause tremendous data corruption problems. Imagine, for example, that two people download a record:

Company: Liberty Associates, Inc. / City: Boston / Contact Name: Jesse Liberty 
The first editor changes the city from Boston to New York (never happen, I'm not moving back). The second editor changes the contact name from Jesse Liberty to Milo Liberty (my dog). Now things get interesting. The first editor writes back the data record, and the database has

Company: Liberty Associates, Inc. / City: New York / Contact Name: Jesse Liberty 

A moment later, the second person updates the database and the database now has

Company: Liberty Associates, Inc. / City: Boston / Contact Name: Milo Liberty 

These earlier updated values are overwritten, overwritten, and lost. The technical term for this is bad.

To prevent this kind of problem, you may use any of the following strategies:

Odd as it may seem at first, it turns out that the best approach to managing concurrency is to try the update and then respond to errors as they arise. After all, most of the time there are no concurrency issues, and this is far faster and safer than the alternatives. For this approach to be effective, however, you must craft your Update command so that it will fail if someone else has updated the record in any way.

Since the data source can tell you the original values that it received from the database, you need pass only those values back into the stored procedure as parameters, and then add them to the Where clause in your Update command. When you update the record, the original values are checked against the values in the database. If they are different, no records will be updated until you fix the problem (which could only have been caused by someone else updating the records before you did).

The wizard makes this a snap. Reopen it and check the Use Optimistic Concurrency check box (look back to Figure 9). Click on OK. Click on Next and then on Finish. Once more, examine the HTML source code. It has become somewhat more complicated, as the Update command now checks to make sure the record is unchanged.

UpdateCommand="UPDATE [Customers] 
SET [CompanyName] = @CompanyName, 
    [ContactName] = @ContactName, 
    [ContactTitle] = @ContactTitle, 
    [Address] = @Address, 
    [City] = @City, 
    [Region] = @Region, 
    [PostalCode] = @PostalCode, 
    [Country] = @Country, 
    [Phone] = @Phone, 
    [Fax] = @Fax 
WHERE [CustomerID] = @original_CustomerID AND 
      [CompanyName] = @original_CompanyName AND 
      [ContactName] = @original_ContactName AND 
      [ContactTitle] = @original_ContactTitle AND 
      [Address] = @original_Address AND 
      [City] = @original_City AND 
      [Region] = @original_Region AND 
      [PostalCode] = @original_PostalCode AND 
      [Country] = @original_Country AND 
      [Phone] = @original_Phone AND 
      [Fax] = @original_Fax" 
 
<UpdateParameters>
    <asp:Parameter Name="CompanyName" Type="String" />
    <asp:Parameter Name="ContactName" Type="String" />
    <asp:Parameter Name="ContactTitle" Type="String" />
    <asp:Parameter Name="Address" Type="String" />
    <asp:Parameter Name="City" Type="String" />
    <asp:Parameter Name="Region" Type="String" />
    <asp:Parameter Name="PostalCode" Type="String" />
    <asp:Parameter Name="Country" Type="String" />
    <asp:Parameter Name="Phone" Type="String" />
    <asp:Parameter Name="Fax" Type="String" />
    <asp:Parameter Name="original_CustomerID" Type="String" />
    <asp:Parameter Name="original_CompanyName" Type="String" />
    <asp:Parameter Name="original_ContactName" Type="String" />
    <asp:Parameter Name="original_ContactTitle" Type="String" />
    <asp:Parameter Name="original_Address" Type="String" />
    <asp:Parameter Name="original_City" Type="String" />
    <asp:Parameter Name="original_Region" Type="String" />
    <asp:Parameter Name="original_PostalCode" Type="String" />
    <asp:Parameter Name="original_Country" Type="String" />
    <asp:Parameter Name="original_Phone" Type="String" />
    <asp:Parameter Name="original_Fax" Type="String" />
</UpdateParameters>

Creating the modified Update (and Delete) command is done for you, as is setting up the parameters, and the original values and new values are tracked automatically by the GridView.

Displaying and Updating the Grid

Now that your DataSource object is ready to go, you have only to set up your DataGrid. Step 1, click on the smart tag and choose Edit Columns, restoring the titles to the way you want.
Step 2, click on the smart tag's check boxes to enable editing and deleting, as shown in Figure 10.

Figure 10
Figure 10: Enabling Editing and Deleting

If you would prefer to have buttons for Edit and Delete rather than links, click on the smart tag and click on Edit Columns. When the fields dialog box opens, click on Selected Fields in the Command Field entry. This brings up the field properties in the right-hand window, where you can change the ButtonType from Link to Button, as shown in Figure 11.

Figure 11
Figure 11: Specifying the button type

The result is that the commands (Edit and Delete) are now displayed as buttons, as shown in Figure 12.

Figure 12
Figure 12: Our new buttons

Tracking the Update with Events

Some programmers get nervous when a control does so much work invisibly. After all, when all goes well, it's great not to have to sweat the details, but if something goes wrong, how can you tell whether your connection failed, no records were updated, an exception was thrown, or something else happened?

The ASP.NET controls overcome these concerns by providing numerous events that you can handle. For example, the DataGrid has almost two dozen events. There is an event that fires when you press the Save button after editing a row (RowUpdating) and there is a second event after the row has been updated (RowUpdated). There are events for when the data is about to be bound, as well as when it has been bound, when the row is created, when it is about to be deleted, when it has been deleted, and more.

To see this at work, you'll create an event handler for the RowUpdated event. The easiest way to do so is to click on Design View, then click on the grid. In the properties window, click on the lightning bolt and scroll to the RowUpdated event. Click in the box to the right of the event name. Visual Studio 2005 will create an event handler for you and put you in the text editor to implement the event.

protected void GridView1_RowUpdated(object sender, GridViewUpdatedEventArgs e)

Notice that the second argument to this method is of type GridViewUpdatedEventArgs. This object has useful information about the update, including:

Here's the exception handler:

protected void GridView1_RowUpdated(object sender, GridViewUpdatedEventArgs e)
{
   if (e.ExceptionHandled)
   {
      string ex = e.Exception.Message; // display this?
   }
   else
   {
      int numRowsChanged = e.AffectedRows;
      foreach ( DictionaryEntry myDE in e.NewValues )
      {
         if (myDE.Value != null)
         {
            string key = myDE.Key.ToString();
            string val = myDE.Value.ToString();
         }
      }
   }
}

Conclusion

ASP.NET DataBinding has come a long way. The improved designer support for data binding makes it much easier in the next version of ASP.NET.

Jesse Liberty is a senior program manager for Microsoft Silverlight where he is responsible for the creation of tutorials, videos and other content to facilitate the learning and use of Silverlight. Jesse is well known in the industry in part because of his many bestselling books, including O'Reilly Media's Programming .NET 3.5, Programming C# 3.0, Learning ASP.NET with AJAX and the soon to be published Programming Silverlight.


Read more Liberty on Whidbey columns.

Return to ONDotnet.com.

Copyright © 2009 O'Reilly Media, Inc.