Two types of ASP.NET data binding exist: single-value binding and repeated-value binding. Single-value data binding is by far the simpler of the two, whereas repeated-value binding provides the foundation for the most advanced ASP.NET data controls.

Data binding works a little differently depending on whether you're using single-value or repeated-value binding. To use single-value binding, you must insert a data binding expression into the markup in the .aspx file (not the code-behind file). To use repeated-value binding, you must set one or more properties of a data control. Typically, you'll perform this initialization when the Page.Load event fires. You'll see examples of both these techniques later in this chapter.

Once you specify data binding, you need to activate it. You accomplish this task by calling the DataBind() method. The DataBind() method is a basic piece of functionality supplied in the Control class. It automatically binds a control and any child controls that it contains. With repeated-value binding, you can use the DataBind() method of the specific list control you're using. Alternatively, you can bind the whole page at once by calling the DataBind() method of the current Page object. Once you call this method, all the data binding expressions in the page are evaluated and replaced with the specified value.

Typically, you call the DataBind() method in the Page.Load event handler. If you forget to use it, ASP.NET will ignore your data binding expressions, and the client will receive a page that contains empty values.

This is a general description of the whole process. To really understand what's happening, you need to work with some specific examples.

This section shows a simple example of single-value data binding. The example has been stripped to the bare minimum amount of detail needed to illustrate the concept.

You start with a variable defined in your Page class, which is called TransactionCount:

public partial class SimpleDataBinding : System.Web.UI.Page
{
    protected int TransactionCount;

    // (Additional code omitted.)
}

Note that this variable must be designated as public, protected, or internal, but not private. If you make the variable private, ASP.NET will not be able to access it when it's evaluating the data binding expression.

Now, assume that this value is set in the Page.Load event handler using some database lookup code. For testing purposes, the example skips this step and hard-codes a value:

protected void Page_Load(object sender, EventArgs e)
{
    // (You could use database code here
    // to look up a value for TransactionCount.)
    TransactionCount = 10;

    // Now convert all the data binding expressions on the page.
    this.DataBind();
}

Two actions actually take place in this event handler: the TransactionCount variable is set to 10, and all the data binding expressions on the page are bound. Currently, no data binding expressions exist, so this method has no effect. Notice that this example uses the this keyword to refer to the current page. You could just write DataBind() without the this keyword, because the default object is the current Page object. However, using the this keyword makes it a bit clearer what object is being used.

To make this data binding accomplish something, you need to add a data binding expression. Usually, it's easiest to add this value directly to the markup in the .aspx file. To do so, click the Source button at the bottom of the web page designer window. Figure 15-1 shows an example with a Label control.

Figure 15.1. Source view in the web page designer

To add your expression, find the tag for the Label control. Modify the text inside the label as shown here:

<asp:Label id="lblDynamic" runat="server" Font-Size="X-Large">
There were <%# TransactionCount %> transactions today.
I see that you are using <%# Request.Browser.Browser %>.
</asp:Label>

This example uses two separate data binding expressions, which are inserted along with the normal static text. The first data binding expression references the TransactionCount variable, and the second uses the built-in Request object to determine some information about the user's browser. When you run this page, the output looks like Figure 15-2.

Figure 15.2. The result of data binding

The data binding expressions have been automatically replaced with the appropriate values. If the page is posted back, you could use additional code to modify TransactionCount, and as long as you call the DataBind() method, that information will be popped into the page in the data binding expression you've defined.

If, however, you forget to call the DataBind() method, the data binding expressions will be ignored, and the user will see a somewhat confusing window that looks like Figure 15-3.

Figure 15.3. The non-data-bound page

The previous example uses a data binding expression to set static text information inside a label tag. However, you can also use single-value data binding to set other types of information on your page, including control properties. To do this, you simply have to know where to put the data binding expression in the web page markup.

For example, consider the following page, which defines a variable named URL and uses it to point to a picture in the application directory:

public partial class DataBindingUrl : System.Web.UI.Page
{
    protected string URL;

    protected void Page_Load(Object sender, EventArgs e)
    {
        URL = "Images/picture.jpg";
        this.DataBind();
    }
}

You can now use this URL to create a label, as shown here:

<asp:Label id="lblDynamic" runat="server"><%# URL %></asp:Label>

You can also use it for a check box caption:

<asp:CheckBox id="chkDynamic" Text="<%# URL %>" runat="server" />

or you can use it for a target for a hyperlink:

<asp:Hyperlink id="lnkDynamic" Text="Click here!" NavigateUrl="<%# URL %>"
 runat="server" />

You can even use it for a picture:

<asp:Image id="imgDynamic" ImageUrl="<%# URL %>" runat="server" />

The only trick is that you need to edit these control tags manually. Figure 15-4 shows what a page that uses all these elements would look like.

Figure 15.4. Multiple ways to bind the same data

To examine this example in more detail, try the sample code for this chapter.

Before you start using single-value data binding techniques in every aspect of your ASP.NET programs, you should consider some of the serious drawbacks this approach can present:

Of course, some developers love the flexibility of single-value data binding and use it to great effect, making the rest of their code more economical and streamlined. It's up to you to be aware of (and avoid) the potential drawbacks.

If you decide not to use single-value data binding, you can accomplish the same thing using code. For example, you could use the following event handler to display the same output as the first label example:

protected void Page_Load(Object sender, EventArgs e)
{
    TransactionCount = 10;
    lblDynamic.Text = "There were " + TransactionCount.ToString();
    lblDynamic.Text += " transactions today. ";
    lblDynamic.Text += "I see that you are using " + Request.Browser.Browser;
}

This code dynamically fills in the label without using data binding. The trade-off is more code.

When you use data binding expressions, you end up complicating your markup with additional details about your code (such as the names of the variables in your code-behind class). When you use the code-only approach, you end up doing the reverse—complicating your code with additional details about the page markup (like the text you want to display). In many cases, the best approach depends on your specific scenario. Data binding expressions are great for injecting small bits of information into an otherwise detailed page. The dynamic code approach gives you more flexibility, and works well when you need to perform more extensive work to shape the page (for example, interacting with multiple controls, changing content and formatting, retrieving the information you want to display from different sources, and so on).

In some ways, data binding to a list control is the simplest kind of data binding. You need to follow only three steps:

This process is the same whether you're using the ListBox, the DropDownList, the CheckBoxList, the RadioButtonList, or even the HtmlSelect control. All these controls provide the same properties and work the same way. The only difference is in the way they appear on the final web page.

To try this type of data binding, add a ListBox control to a new web page. Next, import the System.Collections namespace in your code. Finally, use the Page.Load event handler to create an ArrayList collection to use as a data source as follows:

ArrayList fruit = new ArrayList();
fruit.Add("Kiwi");
fruit.Add("Pear");
fruit.Add("Mango");
fruit.Add("Blueberry");
fruit.Add("Apricot");
fruit.Add("Banana");
fruit.Add("Peach");
fruit.Add("Plum");

Now, you can link this collection to the ListBox control:

lstItems.DataSource = fruit;

Because an ArrayList is a straightforward, unstructured type of object, this is all the information you need to set. If you were using a DataTable (which has more than one field) or a DataSet (which has more than one DataTable), you would have to specify additional information.

To activate the binding, use the DataBind() method:

this.DataBind();

You could also use lstItems.DataBind() to bind just the ListBox control. Figure 15-5 shows the resulting web page.

Figure 15.5. A data-bound list

This technique can save quite a few lines of code. This example doesn't offer a lot of savings because the collection is created just before it's displayed. In a more realistic application, however, you might be using a function that returns a ready-made collection to you:

ArrayList fruit;
fruit = GetFruitsInSeason("Summer");

In this case, it's extremely simple to add the extra two lines needed to bind and display the collection in the window:

lstItems.DataSource = fruit;
this.DataBind();

or you could even change it to the following, even more compact, code:

lstItems.DataSource = GetFruitsInSeason("Summer");
this.DataBind();

On the other hand, consider the extra trouble you would have to go through if you didn't use data binding. This type of savings compounds rapidly, especially when you start combining data binding with multiple controls, advanced objects such as DataSets, or advanced controls that apply formatting through templates.

You can use data binding with the Hashtable and ArrayList, two of the more useful collection classes in the System.Collections namespace. However, as you learned in Chapter 3, .NET includes a more stringent set of collections in another namespace: System.Collections.Generic. These collections are ideal in cases where you want your collection to hold just a single type of object (for example, just strings). When you use the generic collections, you choose the item type you want to use, and the collection object is "locked in" to your choice (which is similar to how an array works). This means if you try to add another type of object that doesn't belong in the collection, you'll get a compile-time error. Similarly, when you pull an item out of the collection, you don't need to write casting code to convert it to the right type, because the compiler already knows what type of objects you're using. This behavior is safer and more convenient, and it's what you'll want most of the time.

To use a generic collection, you must import the right namespace:

using System.Collections.Generic

The generic version of the ArrayList class is named List. Here's how you create a List collection object that can only store strings:

List<string> fruit = new List<string>();
fruit.Add("Kiwi");
fruit.Add("Pear");

The only real difference is that you need to specify the type of data you want to use when you declare the List object.

You can bind the same data list object to multiple different controls. Consider the following example, which compares all the types of list controls at your disposal by loading them with the same information:

protected void Page_Load(Object sender, EventArgs e)
{
    // Create and fill the collection.
    List<string> fruit = new List<string>();
    fruit.Add("Kiwi");
    fruit.Add("Pear");
    fruit.Add("Mango");
    fruit.Add("Blueberry");
    fruit.Add("Apricot");
    fruit.Add("Banana");
    fruit.Add("Peach");
    fruit.Add("Plum");

    // Define the binding for the list controls.
    MyListBox.DataSource = fruit;
    MyDropDownListBox.DataSource = fruit;
    MyHtmlSelect.DataSource = fruit;
    MyCheckBoxList.DataSource = fruit;
    MyRadioButtonList.DataSource = fruit;

    // Activate the binding.
    this.DataBind();
}

Figure 15-6 shows the rendered page.

Figure 15.6. Multiple bound lists

This is another area where ASP.NET data binding may differ from what you have experienced in a desktop application. In traditional data binding, all the different controls are sometimes treated like "views" on the same data source, and you can work with only one record from the data source at a time. In this type of data binding, when you select Pear in one list control, the other list controls automatically refresh so that they too have Pear selected (or the corresponding information from the same row). This isn't how ASP.NET uses data binding. If you want this sort of effect, you need to write custom code to pull it off.

A dictionary collection is a special kind of collection in which every item (or definition, to use the dictionary analogy) is indexed with a specific key (or dictionary word). This is similar to the way that built-in ASP.NET collections such as Session, Application, and Cache work.

Dictionary collections always need keys. This makes it easier to retrieve the item you want. In ordinary collections, like the ArrayList or List, you need to find the item you want by its index number position, or—more often—by traveling through the whole collection until you come across the right item. With a dictionary collection, you retrieve the item you want using its key. Generally, ordinary collections make sense when you need to work with all the items at once, while dictionary collections make sense when you frequently retrieve a single specific item.

You can use two basic dictionary-style collections in .NET. The Hashtable collection (in the System.Collections namespace) allows you to store any type of object and use any type of object for the key values. The Dictionary collection (in the System.Collections.Generic namespace) uses generics to provide the same "locking in" behavior as the List collection. You choose the item type and the key type upfront to prevent errors and reduce the amount of casting code you need to write.

The following example uses the Dictionary collection class, which it creates once—the first time the page is requested. You create a Dictionary object in much the same way you create an ArrayList or List collection. The only difference is that you need to supply a unique key for every item. This example uses the lazy practice of assigning a sequential number for each key:

protected void Page_Load(Object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        // Use integers to index each item. Each item is a string.
        Dictionary<int, string> fruit = new Dictionary<int, string>();

        fruit.Add(1, "Kiwi");
        fruit.Add(2, "Pear");
        fruit.Add(3, "Mango");
        fruit.Add(4, "Blueberry");
        fruit.Add(5, "Apricot");
        fruit.Add(6, "Banana");
        fruit.Add(7, "Peach");
        fruit.Add(8, "Plum");

        // Define the binding for the list controls.
        MyListBox.DataSource = fruit;

        // Choose what you want to display in the list.
        MyListBox.DataTextField = "Value";

        // Activate the binding.
        this.DataBind();
    }
}

There's one new detail here. It's this line:

MyListBox.DataTextField = "Value";

Each item in a dictionary-style collection has both a key and a value associated with it. If you don't specify which property you want to display, ASP.NET simply calls the ToString() method on each collection item. This may or may not produce the result you want. However, by inserting this line of code, you control exactly what appears in the list. The page will now appear as expected, with all the fruit names.

You might want to experiment with what other types of collections you can bind to a list control. One interesting option is to use a built-in ASP.NET control such as the Session object. An item in the list will be created for every currently defined Session variable, making this trick a nice little debugging tool to quickly check current session information.

Along with the DataTextField property, all list controls that support data binding also provide a DataValueField property, which adds the corresponding information to the value attribute in the control element. This allows you to store extra (undisplayed) information that you can access later. For example, you could use these two lines to define your data binding with the previous example:

MyListBox.DataTextField = "Value";
MyListBox.DataValueField = "Key";

The control will appear the same, with a list of all the fruit names in the collection. However, if you look at the rendered HTML that's sent to the client browser, you'll see that value attributes have been set with the corresponding numeric key for each item:

<select name="MyListBox" id="MyListBox" >
    <option value="1">Kiwi</option>
    <option value="2">Pear</option>
    <option value="3">Mango</option>
    <option value="4">Blueberry</option>
    <option value="5">Apricot</option>
    <option value="6">Banana</option>
    <option value="7">Peach</option>
    <option value="8">Plum</option>
</select>

You can retrieve this value later using the SelectedItem property to get additional information. For example, you could set the AutoPostBack property of the list control to true, and add the following code:

protected void MyListBox_SelectedIndexChanged(Object sender,
 EventArgs e)
{
    lblMessage.Text = "You picked: " + MyListBox.SelectedItem.Text;
    lblMessage.Text += " which has the key: " + MyListBox.SelectedItem.Value;
}

Figure 15-7 demonstrates the result. This technique is particularly useful with a database. You could embed a unique ID into the value property and be able to quickly look up a corresponding record depending on the user's selection by examining the value of the SelectedItem object.

Figure 15.7. Binding to the key and value properties

Note that for this to work, you can't regenerate the list after every postback. If you do, the selected item information will be lost and an error will occur. The preceding example handles this by checking the Page.IsPostBack property. If it's false (which indicates that the page is being requested for the first time), the page builds the list. When the page is rendered, the current list of items is stored in view state. When the page is posted back, the list of items already exists and doesn't need to be re-created.

So far, the examples in this chapter have dealt with data binding that doesn't involve databases or any part of ADO.NET. Although this is an easy way to familiarize yourself with the concepts, and a useful approach in its own right, you get the greatest advantage of data binding when you use it in conjunction with a database.

When you're using data binding with the information drawn from a database, the data binding process takes place in the same three steps. First you create your data source, which will be a DataReader or DataSet object. A DataReader generally offers the best performance, but it limits your data binding to a single control because it is a forward-only reader. As it fills a control, it traverses the results from beginning to end. Once it's finished, it can't go back to the beginning; so it can't be used in another data binding operation. For this reason, a DataSet is a more common choice.

The next example creates a DataSet and binds it to a list. In this example, the DataSet is filled by hand, but it could just as easily be filled using a DataAdapter object, as you saw in the previous chapter.

To fill a DataSet by hand, you need to follow several steps:

Here's how the code unfolds:

// Define a DataSet with a single DataTable.
DataSet dsInternal = new DataSet();
dsInternal.Tables.Add("Users");

// Define two columns for this table.
dsInternal.Tables["Users"].Columns.Add("Name");
dsInternal.Tables["Users"].Columns.Add("Country");

// Add some actual information into the table.
DataRow rowNew = dsInternal.Tables["Users"].NewRow();
rowNew["Name"] = "John";
rowNew["Country"] = "Uganda";
dsInternal.Tables["Users"].Rows.Add(rowNew);

rowNew = dsInternal.Tables["Users"].NewRow();
rowNew["Name"] = "Samantha";
rowNew["Country"] = "Belgium";
dsInternal.Tables["Users"].Rows.Add(rowNew);

rowNew = dsInternal.Tables["Users"].NewRow();
rowNew["Name"] = "Rico";
rowNew["Country"] = "Japan";
dsInternal.Tables["Users"].Rows.Add(rowNew);

Next, you bind the DataTable from the DataSet to the appropriate control. Because list controls can only show a single column at a time, you also need to choose the field you want to display for each item by setting the DataTextField property:

// Define the binding.
lstUser.DataSource = dsInternal.Tables["Users"];
lstUser.DataTextField = "Name";

Alternatively, you could use the entire DataSet for the data source, instead of just the appropriate table. In that case, you would have to select a table by setting the control's DataMember property. This is an equivalent approach, but the code is slightly different:

// Define the binding.
lstUser.DataSource = dsInternal;
lstUser.DataMember = "Users";
lstUser.DataTextField = "Name";

As always, the last step is to activate the binding:

this.DataBind();

The final result is a list with the information from the specified database field, as shown in Figure 15-8. The list box will have an entry for every single record in the table, even if it appears more than once, from the first row to the last.

Figure 15.8. DataSet binding

The next example is more practical. It's a good example of how you might use data binding in a full ASP.NET application. This example allows the user to select a record and update one piece of information by using data-bound list controls.

The first step is to add the connection string to your web.config file. This example uses the Products table from the Northwind database included with many versions of SQL Server. Here's how you can define the connection string for SQL Server Express:

<configuration>
  <connectionStrings>
    <add name="Northwind" connectionString=
"Data Source=localhostSQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" />

</connectionStrings>
  ...
</configuration>

To use the full version of SQL Server, remove the SQLEXPRESS portion. To use a database server on another computer, supply the computer name for the Data Source connection string property. (For more details about connection strings, refer to Chapter 14.)

The next step is to retrieve the connection string and store it in a private variable in the Page class so that every part of your page code can access it easily. Once you've imported the System.Web.Configuration namespace, you can create a member variable in your code-behind class that's defined like this:

private string connectionString =
  WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

The next step is to create a drop-down list that allows the user to choose a product for editing. The Page.Load event handler takes care of this task—retrieving the data, binding it to the drop-down list control, and then activating the binding. Before you go any further, make sure you've imported the System.Data.SqlClient namespace, which allows you to use the SQL Server provider to retrieve data.

protected void Page_Load(Object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        // Define the ADO.NET objects for selecting products from the database.
        string selectSQL = "SELECT ProductName, ProductID FROM Products";
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(selectSQL, con);

        // Open the connection.
        con.Open();

        // Define the binding.
        lstProduct.DataSource = cmd.ExecuteReader();
        lstProduct.DataTextField = "ProductName";
        lstProduct.DataValueField = "ProductID";

        // Activate the binding.
        this.DataBind();

        con.Close();

        // Make sure nothing is currently selected in the list box.
        lstProduct.SelectedIndex = −1;
    }
}

Once again, the list is only filled the first time the page is requested (and stored in view state automatically). If the page is posted back, the list keeps its current entries. This reduces the amount of database work, and keeps the page working quickly and efficiently. You should also note that this page doesn't attempt to deal with errors. If you were using it in a real application, you'd need to use the exception handling approach demonstrated in Chapter 14.

The actual database code is similar to what was used in the previous chapter. The example uses a Select statement but carefully limits the returned information to just the ProductName and ProductID fields, which are the only pieces of information it will use. The resulting window lists all the products defined in the database, as shown in Figure 15-9.

Figure 15.9. Product choices

The drop-down list enables AutoPostBack, so as soon as the user makes a selection, a lstProduct.SelectedItemChanged event fires. At this point, your code performs the following tasks:

This logic appears fairly involved, but it's really just an application of what you've learned over the past two chapters. The full listing is as follows:

protected void lstProduct_SelectedIndexChanged(object sender, EventArgs e)
{
    // Create a command for selecting the matching product record.
    string selectProduct = "SELECT ProductName, QuantityPerUnit, " +
     "CategoryName FROM Products INNER JOIN Categories ON " +
     "Categories.CategoryID=Products.CategoryID " +
     "WHERE ProductID=@ProductID";

    // Create the Connection and Command objects.
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmdProducts = new SqlCommand(selectProduct, con);
    cmdProducts.Parameters.AddWithValue("@ProductID",
      lstProduct.SelectedItem.Value);

    // Retrieve the information for the selected product.
    using (con)
    {
        con.Open();
        SqlDataReader reader = cmdProducts.ExecuteReader();
        reader.Read();

        // Update the display.
        lblRecordInfo.Text = "<b>Product:</b> " +
          reader["ProductName"] + "<br />";
        lblRecordInfo.Text += "<b>Quantity:</b> " +
          reader["QuantityPerUnit"] + "<br />";
        lblRecordInfo.Text += "<b>Category:</b> " + reader["CategoryName"];

        // Store the corresponding CategoryName for future reference.
        string matchCategory = reader["CategoryName"].ToString();

        // Close the reader.
        reader.Close();

        // Create a new Command for selecting categories.
        string selectCategory = "SELECT CategoryName, " +
          "CategoryID FROM Categories";
        SqlCommand cmdCategories = new SqlCommand(selectCategory, con);

        // Retrieve the category information, and bind it.
        lstCategory.DataSource = cmdCategories.ExecuteReader();
        lstCategory.DataTextField = "CategoryName";

lstCategory.DataValueField = "CategoryID";
        lstCategory.DataBind();

        // Highlight the matching category in the list.
        lstCategory.Items.FindByText(matchCategory).Selected = true;
   }

    pnlCategory.Visible = true;
}

You could improve this code in several ways. It probably makes the most sense to remove these data access routines from this event handler and put them into more generic functions. For example, you could use a function that accepts a ProductID and returns a single DataRow with the associated product information. Another improvement would be to use a stored procedure to retrieve this information.

The end result is a window that updates itself dynamically whenever a new product is selected, as shown in Figure 15-10.

Figure 15.10. Product information

This example still has one more trick in store. If the user selects a different category and clicks Update, the change is made in the database. Of course, this means creating new Connection and Command objects, as follows:

protected void cmdUpdate_Click(object sender, EventArgs e)
{
    // Define the Command.
    string updateCommand = "UPDATE Products " +

"SET CategoryID=@CategoryID WHERE ProductID=@ProductID";

    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand(updateCommand, con);

    cmd.Parameters.AddWithValue("@CategoryID", lstCategory.SelectedItem.Value);
    cmd.Parameters.AddWithValue("@ProductID", lstProduct.SelectedItem.Value);

    // Perform the update.
    using (con)
    {
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

You could easily extend this example so that it allows you to edit all the properties in a product record. But before you try that, you might want to experiment with the rich data controls that are shown in the next chapter. Using these controls, you can create sophisticated lists and grids that provide automatic features for selecting, editing, and deleting records.

Data source controls can perform two key tasks:

Before you can use the data source controls, you need to understand the page life cycle. The following steps explain the sequence of stages your page goes through in its lifetime. The two steps in bold (4 and 6) are the steps where the data source controls will spring into action:

In the rest of this chapter, you'll take a closer look at the SqlDataSource control, and you'll use it to build the record editor example demonstrated earlier—with a lot less code.

Data source controls turn up in the .aspx markup portion of your web page like ordinary controls. Here's an example:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ... />

The SqlDataSource represents a database connection that uses an ADO.NET provider. However, this has a catch. The SqlDataSource needs a generic way to create the Connection, Command, and DataReader objects it requires. The only way this is possible is if your data provider includes something called a data provider factory. The factory has the responsibility of creating the provider-specific objects that the SqlDataSource needs to access the data source. Fortunately, .NET includes a data provider factory for each of its four data providers:

You can use all of these providers with the SqlDataSource. You choose your data source by setting the provider name. Here's a SqlDataSource that connects to a SQL Server database using the SQL Server provider:

<asp:SqlDataSource ProviderName="System.Data.SqlClient" ... />

Technically, you can omit this piece of information, because the System.Data.SqlClient provider factory is the default.

The next step is to supply the required connection string—without it, you cannot make any connections. Although you can hard-code the connection string directly in the SqlDataSource tag, it's always better to keep it in the <connectionStrings> section of the web.config file to guarantee greater flexibility and ensure you won't inadvertently change the connection string.

To refer to a connection string in your .aspx markup, you use a special syntax in this format:

<%$ ConnectionStrings:[NameOfConnectionString] %>

This looks like a data binding expression, but it's slightly different. (For one thing, it begins with the character sequence <%$ instead of <%#.)

For example, if you have a connection string named Northwind in your web.config file that looks like this:

<configuration>
  <connectionStrings>
    <add name="Northwind" connectionString=
"Data Source=localhostSQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" />
  </connectionStrings>
  ...
</configuration>

you would specify it in the SqlDataSource using this syntax:

<asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:Northwind %>" ... />

Once you've specified the provider name and connection string, the next step is to add the query logic that the SqlDataSource will use when it connects to the database.

You can use each SqlDataSource control you create to retrieve a single query. Optionally, you can also add corresponding commands for deleting, inserting, and updating rows. For example, one SqlDataSource is enough to query and update the Customers table in the Northwind database. However, if you need to independently retrieve or update Customers and Orders information, you'll need two SqlDataSource controls.

The SqlDataSource command logic is supplied through four properties—SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand—each of which takes a string. The string you supply can be inline SQL (in which case the corresponding SelectCommandType, InsertCommandType, UpdateCommandType, or DeleteCommandType property should be Text, the default) or the name of a stored procedure (in which case the command type is StoredProcedure). You need to define commands only for the types of actions you want to perform. In other words, if you're using a data source for read-only access to a set of records, you need to define only the SelectCommand property.

Here's a complete SqlDataSource that defines a Select command for retrieving product information from the Products table:

<asp:SqlDataSource ID="sourceProducts" runat="server"
  ConnectionString="<%$ ConnectionStrings:Northwind %>"
  SelectCommand="SELECT ProductName, ProductID FROM Products"
/>

This is enough to build the first stage of the record editor example shown earlier—namely, the drop-down list box that shows all the products. All you need to do is set the DataSourceID property to point to the SqlDataSource you've created. The easiest way to do this is using the Properties window, which provides a drop-down list of all the data sources on your current web page. At the same time, make sure you set the DataTextField and DataValueField properties. Once you make these changes, you'll wind up with a control tag like this:

<asp:DropDownList ID="lstProduct" runat="server" AutoPostBack="True"
  DataSourceID="sourceProducts" DataTextField="ProductName"
  DataValueField="ProductID" />

The best part about this example is that you don't need to write any code. When you run the page, the DropDownList control asks the SqlDataSource for the data it needs. At this point, the SqlDataSource executes the query you defined, fetches the information, and binds it to the DropDownList. The whole process unfolds automatically.

In the previous example (which used the SqlDataSource to retrieve a list of products), the complete query was hard-coded. Often, you won't have this flexibility. Instead, you'll want to retrieve a subset of data, such as all the products in a given category or all the employees in a specific city.

The record editor that you considered earlier offers an ideal example. Once you select a product, you want to execute another command to get the full details for that product. (You might just as easily execute another command to get records that are related to this product.) To make this work, you need two data sources. You've already created the first SqlDataSource, which fetches limited information about every product. Here's the second SqlDataSource, which gets more extensive information about a single product (the following query is split over several lines to fit the printed page):

<asp:SqlDataSource ID="sourceProductDetails" runat="server"
  ProviderName="System.Data.SqlClient"
  ConnectionString="<%$ ConnectionStrings:Northwind %>"
  SelectCommand="SELECT * FROM Products WHERE ProductID=@ProductID"
/>

But this example has a problem. It defines a parameter (@ProductID) that identifies the ID of the product you want to retrieve. How do you fill in this piece of information? It turns out you need to add a <SelectParameters> section to the SqlDataSource tag. Inside this section, you must define each parameter that's referenced by your SelectCommand and tell the SqlDataSource where to find the value it should use. You do that by mapping the parameter to a value in a control.

Here's the corrected command:

<asp:SqlDataSource ID="sourceProductDetails" runat="server"
  ProviderName="System.Data.SqlClient"
  ConnectionString="<%$ ConnectionStrings:Northwind %>"
  SelectCommand="SELECT * FROM Products WHERE ProductID=@ProductID">
  <SelectParameters>
    <asp:ControlParameter ControlID="lstProduct" Name="ProductID"
     PropertyName="SelectedValue" />
  </SelectParameters>
</asp:SqlDataSource>

You always indicate parameters with an @ symbol, as in @City. You can define as many parameters as you want, but you must map each one to a value using a separate element in the SelectParameters collection. In this example, the value for the @ProductID parameter comes from the lstProduct.SelectedValue property. In other words, you are binding a value that's currently in a control to place it into a database command. (You could also use the SelectedText property to get the currently displayed text, which is the ProductName in this example.)

Now all you need to do is bind the SqlDataSource to the remaining controls where you want to display information. This is where the example takes a slightly different turn. In the previous version of the record editor, you took the information and used a combination of values to fill in details in a label and a list control. This type of approach doesn't work well with data source controls. First, you can bind only a single data field to most simple controls such as lists. Second, each bound control makes a separate request to the SqlDataSource, triggering a separate database query. This means if you bind a dozen controls, you'll perform the same query a dozen times, with terrible performance. You can alleviate this problem with data source caching (see Chapter 23), but it indicates you aren't designing your application in a way that lends itself well to the data source control model.

The solution is to use one of the rich data controls, such as the GridView, DetailsView, or FormView. These controls have the smarts to show multiple fields at once, in a highly flexible layout. You'll learn about these three controls in detail in the next chapter, but the following example shows a simple demonstration of how to use the DetailsView.

The DetailsView is a rich data control that's designed to show multiple fields in a data source. As long as its AutoGenerateRows is true (the default), it creates a separate row for each field, with the field caption and value. Figure 15-12 shows the result.

Figure 15.12. Displaying full product information in a DetailsView

Here's the basic DetailsView tag that makes this possible:

<asp:DetailsView ID="detailsProduct" runat="server"
  DataSourceID="sourceProductDetails" />

As you can see, the only property you need to set is DataSourceID. That binds the DetailsView to the SqlDataSource you created earlier. This SqlDataSource gets the full product information for a single row, based on the selection in the list control. Best of all, this whole example still hasn't required a line of code.

<asp:SqlDataSource ID="sourceProducts" runat="server"
  ProviderName="System.Data.SqlClient"
  ConnectionString="<%$ ConnectionStrings:Northwind %>"
  SelectCommand="SELECT ProductName, ProductID FROM Products"
/>
<asp:DropDownList ID="lstProduct" runat="server" AutoPostBack="True"
  DataSourceID="sourceProducts" DataTextField="ProductName"
  DataValueField="ProductID" />

protected void cmdGo_Click(object sender, EventArgs e)
{
    if (lstProduct.SelectedIndex != −1)
    {
        Response.Redirect(
          "QueryParameter2.aspx?prodID=" + lstProduct.SelectedValue);
    }
}

<asp:SqlDataSource ID="sourceProductDetails" runat="server"
  ProviderName="System.Data.SqlClient"
  ConnectionString="<%$ ConnectionStrings:Northwind %>"
  SelectCommand="SELECT * FROM Products WHERE ProductID=@ProductID">
  <SelectParameters>
    <asp:QueryStringParameter Name="ProductID" QueryStringField="prodID" />
  </SelectParameters>
</asp:SqlDataSource>

<asp:DetailsView ID="detailsProduct" runat="server"
  DataSourceID="sourceProductDetails" />

Setting Parameter Values in Code

Sometimes you'll need to set a parameter with a value that isn't represented by any of the parameter classes in Table 15-1. Or, you might want to manually modify a parameter value before using it. In both of these scenarios, you need to use code to set the parameter value just before the database operation takes place.

For example, consider the page shown in Figure 15-13. It includes two data-bound controls. The first is a list of all the customers in the database. Here's the markup that defines the list and its data source:

<asp:SqlDataSource ID="sourceCustomers" runat="server"
  ProviderName="System.Data.SqlClient"
  ConnectionString="<%$ ConnectionStrings:Northwind %>"
  SelectCommand="SELECT CustomerID, ContactName FROM Customers"
/>
  <asp:DropDownList ID="lstCustomers" runat="server"
  DataSourceID="sourceCustomers" DataTextField="ContactName"
  DataValueField="CustomerID" AutoPostBack="True">
</asp:DropDownList>

Figure 15.13. Using parameters in a master-details page

When the user picks a customer from the list, the page is posted back (because AutoPostBack is set to true) and the matching orders are shown in a GridView underneath, using a second data source. This data source pulls the CustomerID for the currently selected customer from the drop-down list using a ControlParameter:

<asp:SqlDataSource ID="sourceOrders" runat="server"
 ProviderName="System.Data.SqlClient"
 ConnectionString="<%$ ConnectionStrings:Northwind %>"
 SelectCommand="SELECT OrderID,OrderDate,ShippedDate FROM Orders WHERE CustomerID=@CustomerID">
  <SelectParameters>
    <asp:ControlParameter Name="CustomerID"
     ControlID="lstCustomers" PropertyName="SelectedValue" />
  </SelectParameters>
</asp:SqlDataSource>

<asp:GridView ID="gridOrders" runat="server" DataSourceID="sourceOrders">
</asp:GridView>

Now, imagine you want to limit the order list so it only shows orders made in the last week. This is easy enough to accomplish with a Where clause that examines the OrderDate field. But there's a catch. It doesn't make sense to hard-code the OrderDate value in the query itself, because the range is set based on the current date. And there's no parameter that provides exactly the information you need. The easiest way to solve this problem is to add a new parameter—one that you'll be responsible for setting yourself:

<asp:SqlDataSource ID="sourceOrders" runat="server"
 ProviderName="System.Data.SqlClient"
 ConnectionString="<%$ ConnectionStrings:Northwind %>"

SelectCommand="SELECT OrderID,OrderDate,ShippedDate FROM Orders WHERE CustomerID=@CustomerID AND OrderDate>=@EarliestOrderDate"
 OnSelecting="sourceOrders_Selecting">
  <SelectParameters>
    <asp:ControlParameter Name="CustomerID"
     ControlID="lstCustomers" PropertyName="SelectedValue" />
    <asp:Parameter Name="EarliestOrderDate" DefaultValue="1900/01/01" />
  </SelectParameters>
</asp:SqlDataSource>

Although you can modify the value of any parameter, if you aren't planning to pull the value out of any of the places listed in Table 15-1, it makes sense to use an ordinary Parameter object, as represented by the <asp:Parameter> element. You can set the data type (if required) and the default value (as demonstrated in this example).

Now that you've created the parameter, you need to set its value before the command takes place. The SqlDataSource has a number of events that are perfect for setting parameter values. You can fill in parameters for a select operation by reacting to the Selecting event. Similarly, you can use the Updating, Deleting, and Inserting events when updating, deleting, or inserting a record. In these event handlers, you can access the command that's about to be executed, using the Command property of the custom EventArgs object (for example, SqlDataSourceSelectingEventArgs.Command). You can then modify its parameter values by hand. The SqlDataSource also provides similarly named Selected, Updated, Deleted, and Inserted events, but these take place after the operation has been completed, so it's too late to change the parameter value.

Here's the code that's needed to set the parameter value to a date that's seven days in the past, ensuring you see one week's worth of records:

protected void sourceOrders_Selecting(object sender,
  SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["@EarliestOrderDate"].Value =
      DateTime.Today.AddDays(-7);
}

Note

You'll have to tweak this code slightly if you're using it with the standard Northwind database. The data in the Northwind database is historical, and most orders bear dates around 1997. As a result, the previous code won't actually retrieve any records. But if you use the AddYears() method instead of AddDays(), you can easily move back 13 years or more, to the place you need to be.

When you deal with an outside resource such as a database, you need to protect your code with a basic amount of error-handling logic. Even if you've avoided every possible coding mistake, you still need to defend against factors outside your control—for example, if the database server isn't running or the network connection is broken.

You can count on the SqlDataSource to properly release any resources (such as connections) if an error occurs. However, the underlying exception won't be handled. Instead, it will bubble up to the page and derail your processing. As with any other unhandled exception, the user will receive a cryptic error message or an error page. This design is unavoidable—if the SqlDataSource suppressed exceptions, it could hide potential problems and make debugging extremely difficult. However, it's a good idea to handle the problem in your web page and show a more suitable error message.

To do this, you need to handle the data source event that occurs immediately after the error. If you're performing a query, that's the Selected event. If you're performing an update, delete, or insert operation, you would handle the Updated, Deleted, or Inserted event instead. (If you don't want to offer customized error messages, you could handle all these events with the same event handler.)

In the event handler, you can access the exception object through the SqlDataSourceStatusEventArgs.Exception property. If you want to prevent the error from spreading any further, simply set the SqlDataSourceStatusEventArgs.ExceptionHandled property to true. Then, make sure you show an appropriate error message on your web page to inform the user that the command was not completed.

Here's an example:

protected void sourceProducts_Selected(object sender,
 SqlDataSourceStatusEventArgs e)
{
    if (e.Exception != null)
    {
        lblError.Text = "An exception occurred performing the query.";

        // Consider the error handled.
        e.ExceptionHandled = true;
    }
}

Selecting data is only half the equation. The SqlDataSource can also apply changes. The only catch is that not all controls support updating. For example, the humble ListBox doesn't provide any way for the user to edit values, delete existing items, or insert new ones. Fortunately, ASP.NET's rich data controls—including the GridView, DetailsView, and FormView—all have editing features you can switch on.

Before you can switch on the editing features in a given control, you need to define suitable commands for the operations you want to perform in your data source. That means supplying commands for inserting (InsertCommand), deleting (DeleteCommand), and updating (UpdateCommand). If you know you will allow the user to perform only certain operations (such as updates) but not others (such as insertions and deletions), you can safely omit the commands you don't need.

You define the InsertCommand, DeleteCommand, and UpdateCommand in the same way you define the command for the SelectCommand property—by using a parameterized query. For example, here's a revised version of the SqlDataSource for product information that defines a basic update command to update every field:

<asp:SqlDataSource ID="sourceProductDetails" runat="server"
  ProviderName="System.Data.SqlClient"
  ConnectionString="<%$ ConnectionStrings:Northwind %>"
  SelectCommand="SELECT ProductID, ProductName, UnitPrice, UnitsInStock,
UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE ProductID=@ProductID"
  UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=@UnitPrice,
UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel,
Discontinued=@Discontinued WHERE ProductID=@ProductID">
  <SelectParameters>

<asp:ControlParameter ControlID="lstProduct" Name="ProductID"
     PropertyName="SelectedValue" />
  </SelectParameters>
</asp:SqlDataSource>

In this example, the parameter names aren't chosen arbitrarily. As long as you give each parameter the same name as the field it affects, and preface it with the @ symbol (so ProductName becomes @ProductName), you don't need to define the parameter. That's because the ASP.NET data controls automatically submit a collection of parameters with the new values before triggering the update. Each parameter in the collection uses this naming convention, which is a major time-saver.

You also need to give the user a way to enter the new values. Most rich data controls make this fairly easy—with the DetailsView, it's simply a matter of setting the AutoGenerateEditButton property to true, as shown here:

<asp:DetailsView ID="DetailsView1" runat="server"
  DataSourceID="sourceProductDetails" AutoGenerateEditButton="True" />

Now when you run the page, you'll see an edit link. When clicked, this link switches the DetailsView into edit mode. All fields are changed to edit controls (typically text boxes), and the Edit link is replaced with an Update link and a Cancel link (see Figure 15-14).

Figure 15.14. Editing with the DetailsView

Clicking the Cancel link returns the row to its initial state. Clicking the Update link triggers an update. The DetailsView extracts the field values, uses them to set the parameters in the SqlDataSource.UpdateParameters collection, and then triggers the SqlDataSource.UpdateCommand to apply the change to the database. Once again, you don't have to write any code.

You can create similar parameterized commands for the DeleteCommand and InsertCommand. To enable deleting and inserting, you need to set the AutoGenerateDeleteButton and AutoGenerateInsertButton properties of the DetailsView to true. To see a sample page that allows updating, deleting, and inserting, refer to the UpdateDeleteInsert.aspx page that's included with the downloadable samples for this chapter.

Strict Concurrency Checking

The update command in the previous example matches the record based on its ID. You can tell this by examining the Where clause:

UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=@UnitPrice,
 UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel,
 Discontinued=@Discontinued WHERE ProductID=@ProductID"

The problem with this approach is that it opens the door to an update that overwrites the changes of another user, if these changes are made between the time your page is requested and the time your page commits its update.

For example, imagine Chen and Lucy are viewing the same table of product records. Lucy commits a change to the price of a product. A few seconds later, Chen commits a name change to the same product record. Chen's update command not only applies the new name but it also overwrites all the other fields with the values from Chen's page—replacing the price Lucy entered with the price from the original page.

One way to solve this problem is to use an approach called match-all-values concurrency. In this situation, your update command attempts to match every field. As a result, if the original record has changed, the update command won't find it and the update won't be performed at all. So in the scenario described previously, using the match-all-values strategy, Chen would receive an error when he attempts to apply the new product name, and he would need to edit the record and apply the change again.

To use this approach, you need to add a Where clause that tries to match every field. Here's what the modified command would look like:

UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=@UnitPrice,
 UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder, ReorderLevel=@ReorderLevel,
 Discontinued=@Discontinued WHERE ProductID=@ProductID AND
 ProductName=@original_ProductName AND UnitPrice=@original_UnitPrice AND
 UnitsInStock=@original_UnitsInStock AND UnitsOnOrder=@original_UnitsOnOrder AND
 ReorderLevel=@original_ReorderLevel AND Discontinued=@original_Discontinued"

Although this makes sense conceptually, you're not finished yet. Before this command can work, you need to tell the SqlDataSource to maintain the old values from the data source and to give them parameter names that start with original_. You do this by setting two properties. First, set the SqlDataSource.ConflictDetection property to ConflictOptions.CompareAllValues instead of ConflictOptions.OverwriteChanges (the default). Next, set the long-winded OldValuesParameterFormatString property to the text "original_{0}". This tells the SqlDataSource to insert the text original_ before the field name to create the parameter that stores the old value. Now your command will work as written.

The SqlDataSource doesn't raise an exception to notify you if no update is performed. So, if you use the command shown in this example, you need to handle the SqlDataSource.Updated event and check the SqlDataSourceStatusEventArgs.AffectedRows property. If it's 0, no records have been updated, and you should notify the user about the concurrency problem so the update can be attempted again, as shown here:

protected void sourceProductDetails_Updated(object sender,
  SqlDataSourceStatusEventArgs e)
{
    if (e.AffectedRows == 0)
    {
        lblInfo.Text = "No update was performed. " +
"A concurrency error is likely, or the command is incorrectly written.";
    }
    else
    {
        lblInfo.Text = "Record successfully updated.";
    }
}

Figure 15-15 shows the result you'll get if you run two copies of this page in two separate browser windows, begin editing in both of them, and then try to commit both updates.

Figure 15.15. A concurrency error in action

Matching every field is an acceptable approach for small records, but it isn't the most efficient strategy if you have tables with huge amounts of data. In this situation, you have two possible solutions: you can match some of the fields (leaving out the ones with really big values) or you can add a timestamp field to your database table, and use that for concurrency checking.

Timestamps are special fields that the database uses to keep track of the state of a record. Whenever any change is made to a record, the database engine updates the timestamp field, giving it a new, automatically generated value. The purpose of a timestamp field is to make strict concurrency checking easier. When you attempt to perform an update to a table that includes a timestamp field, you use a Where clause that matches the appropriate unique ID value (like ProductID) and the timestamp field:

UpdateCommand="UPDATE Products SET ProductName=@ProductName, UnitPrice=@UnitPrice,
 UnitsInStock=@UnitsInStock, UnitsOnOrder=@UnitsOnOrder,
 ReorderLevel=@ReorderLevel, Discontinued=@Discontinued
 WHERE ProductID=@ProductID AND RowTimestamp=@RowTimestamp"

The database engine uses the ProductID to look up the matching record. Then, it attempts to match the timestamp in order to update the record. If the timestamp matches, you know the record hasn't been changed. The actual value of the timestamp isn't important, because that's controlled by the database. You just need to know whether it's changed.

Creating a timestamp is easy. In SQL Server, you create a timestamp field using the timestamp data type. In other database products, timestamps are sometimes called row versions.