Binding Data to Controls

Developers are always looking for efficient ways to streamline an application and reduce lines of code to write. One of those ways is to link data directly to a web control so that it appears automatically every time the web page is displayed.

Linking data to a web control is called data binding and is the topic of this chapter. Data can come from a variety of sources, but the most common source is from a database. In this chapter, you’ll learn how to bind data to a web control and then use that data and web control within your application.

Data Binding Basics

In previous chapters you learned how to retrieve information from a database and display the information on your web page. Many times this information is displayed in a control such as a drop-down list box or a text box on a web form.

You can dynamically assign information from a database to a control by using a process called data binding. Data binding links the value property of a control to a data source while your application is running. The data source can be data from a database. The data source can also be an expression, methods, or properties of another control.

Each control has a DataSource property that specifies the source of the data for the control. Each control also has a DataBind() method. You call the DataBind() method whenever you want to bring the data source into the control.

Throughout this chapter we’ll show how to bind data from a database to a Repeater control, a drop-down list control, a radio button control, and a list box control.

The Repeater Control

The Repeater control is used to display records from a database and is declared by using the following ASP.NET tags. The <asp:Repeater> tag requires two attributes. These are ID and Runat, both of these you’ve learned about throughout this book. The ID attribute uniquely identifies the Repeater, and the Runat attribute specifies that this control runs on the server.

Within the Repeater tag is the ItemTemplate. The ItemTemplate specifies what is to be displayed. Here you reference data returned from the database. (You’ll see how to retrieve data later in this section.)

The ItemTemplate tag in turn contains a data binding expression, which is used to reference this data. This expression begins with <%# and ends with %>. The expression itself calls the DataItem() method of the Container object and passes it the column name that identifies the column from the bound data source that the control displays. A Container object is an object that contains other objects.

<asp:Repeater ID="RepeaterID"  Runat="Server">
   <ItemTemplate>
      <%# Container.DataItem("ColumnName") %>
   </ItemTemplate>
</asp:Repeater>

Data is retrieved from the database using techniques that you learned in Chapter 10. You’ll recall that you need to open a connection to the database and then pass the database management system (DBMS) a query. A reader is then used to access the result returned by the DBMS.

Data that is retrieved from the DBMS must be bound to the Repeater control. This is accomplished by assigning a reference to the reader to the DataSource property of the Repeater control, and then by calling the Repeater control’s DataBind() method. Every control has a DataBind() function that binds (links) the data source to the control. The control then displays the data once the data source is bound to the control.

Connecting to the database, running the query, and binding the control to the data source typically occurs once in your application. It makes sense to do this when the page is loaded. Therefore, place the code that links to the data in the Page_Load subroutine as shown here. This example connects to the Microsoft SQL Server database, but you can connect to another database, as illustrated in Chapter 10.

This example begins by declaring SqlConnection, SqlCommand, and SqlData-Reader variables. Next, a connection is opened to the DBMS by creating a    
SqlConnection object and passing it login information.

An instance of the SqlCommand object is then created, passing it the query that will be sent to the DBMS to retrieve our data. The connection is then opened by calling the Connection object’s Open() method, and data is returned to the reader by calling the SqlCommand object’s ExecuteReader() method.

A reference to the reader is assigned to the DataSource property of the Repeater control, and then the DataBind() method is called to bind the data to the Repeater control. The reader and the connection are then closed.

Sub Page_Load
  Dim conCust As SqlConnection
  Dim cmdSelectRows As SqlCommand
  Dim dtrCust As SqlDataReader
  conCust = New SqlConnection( "Server=server;UID=userID;
          PWD=password;
          Database=database" )
  cmdSelectRows = New SqlCommand( "query", conCust)
  conCust.Open()
  dtrCust = cmdSelectRows.ExecuteReader()
  RepeaterControlID.DataSource = dtrCust
  RepeaterControlID.DataBind()
  dtrCust.Close()
  conCust.Close()
End Sub

Let’s assemble these pieces and retrieve data from the Customers table that you created in Chapter 11. The following code shows how this is done. We begin by defining the Page_Load subroutine. The database is called CustomerContactData. Replace MyID and MyPassword with your user ID and password. We’ll use a very simple query that retrieves all the rows and all the columns from the Customers table.

Next, we create a form on our web page that contains the Repeater control. The Repeater control has a data binding expression that calls the DataItem() of the Container object to access the CustomerLastName column of the data returned to the application by the DBMS in response to our query.

<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
  Dim conCust As SqlConnection
  Dim cmdSelectRows As SqlCommand
  Dim dtrCust As SqlDataReader
  conCust = New SqlConnection(
  "Server=localhost; UID=MyID;PWD=MyPassword; Database=CustomerContactData" )
  cmdSelectRows = New SqlCommand( "Select * From custContact", conCust)
  conCust.Open()
  dtrCust = cmdSelectRows.ExecuteReader()
  rptCust.DataSource = dtrCust
  rptCust.DataBind()
  dtrCust.Close()
  conCust.Close()
End Sub
</Script>
<html>
   <head><title>Repeater Control Data Binding</title></head>
   <body>
      <form Runat="Server">
        <asp:Repeater ID="rptCust" Runat="Server">
           <ItemTemplate>
              <%# Container.DataItem("custLastName") %>
           </ItemTemplate>
         </asp:Repeater>
      </form>
   </body>
</html>

A Closer Look at Templates

The ItemTemplate for the Repeater control is just one type of template. Templates can contain data retrieved from the DBMS, HTML tags, and inline ASP.NET statements. There are five templates that can be used with the Repeater control:

• HeaderTemplate  Used to format the header section of the Repeater control
  
• ItemTemplate  Used to display and format data displayed in the Repeater control
  
• AlternatingItemTemplate  Used to display and format alternate data items
  
• SeparatorTemplate  Used to separate data displayed by the Repeater control
  
• FooterTemplate  Used to format the footer section of the Repeater control

The following example is a modification of our previous example and illustrates how to use these different templates in an application. All rows and columns are retrieved from the Customers table. We’ll display the CustomerFirstName and CustomerLastName columns in a table.

The HeaderTemplate is the first template used in this example. The HeaderTemplate contains HTML tags that create a table with two columns: Customer First Name and Customer Last Name.

The ItemTemplate appears next. This is nearly identical to the ItemTemplate in the previous example; however, besides having it access data from the DBMS, we’ve also included HTML tags that define a row and columns of the table. The data is displayed in a blue font.

The AlternatingItemTemplate defines the format for alternating items that appear in the Repeater control, that is, the second, fourth, and so on items. The format is the same as for the ItemTemplate; however, the data appears in red instead of blue.

The FooterTemplate is the last template in this example and contains the HTML closing tag for the table.

<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
  Dim conCust As SqlConnection
  Dim cmdSelectRows As SqlCommand
  Dim dtrCust As SqlDataReader
  conCust = New SqlConnection( "Server=localhost;UID=
MyID;PWD=MyPassword;Database=CustomerContact Data")
  cmdSelectRows = New SqlCommand( "Select * From Customers", conCust)
  conCust.Open()
  dtrCust = cmdSelectRows.ExecuteReader()
  rptCust.DataSource = dtrCust
  rptCust.DataBind()
  dtrCust.Close()
  ()
  conCust.Close()
End Sub
</Script>
<html>
<head><title> Repeater Control Data Binding </title></head>
<body>
   <form Runat="Server">
      <asp:Repeater ID="rptCust" Runat="Server">
        <HeaderTemplate>
            <table border=1 cellpadding=5>
            <tr>
              <th>Customer First Name</th>
              <th>Customer Last Name</th>
            </tr>
         </HeaderTemplate>
         <ItemTemplate>
           <font color="blue">
           <tr>
             <td><%# Container.DataItem("custFirstName") %></td>
             <td><%# Container.DataItem("custLastName") %></td>
           </tr>
         </ItemTemplate>
         <AlternatingItemTemplate>
           <font color="red">
           <tr>
             <td><%# Container.DataItem("CustFirstName") %></td>
             <td><%# Container.DataItem("CustLastName") %></td>
           </tr>
         </AlternatingItemTemplate>
         <FooterTemplate>
            </table>
         </FooterTemplate>
       </asp:Repeater>
    </form>
</body>
</html>

Drop-Down List

Data can also be bounded to the DropDownList control by assigning the column name to the DataTextField property of the DropDownList control in the Page_Load method. This is illustrated in the next example, where we load the last name of customers into a drop-down list when the page is loaded.

Notice that we connect to the database and execute the query in the Page_Load subroutine much as we did in the previous example, with one difference. The database connection is made within an If statement that evaluates the status of the IsPostBack property.

A postback occurs when the page calls itself. A visitor loads a page for the first time by entering the page’s URL into the browser address box or by clicking a hyperlink contained on a different page. This is not a postback. However, once the page is displayed, the page can request itself. This is a postback.

Figure 12-1.  The last names shown in the DropDownList control are from the database.

If the page is a postback, then the IsPostBack property is true; otherwise, the IsPostBack property is false. Data connection and data binding occur only when the page isn’t a postback. Therefore, we need to test the value of the IsPostBack property before connecting to the DBMS and binding the data. We do this by reversing the logic of the IsPostBack property. That is, if the IsPostBack property is false (the page is loaded the first time), then we make the condition expression true so that statements within the If statement (connect to the DBMS and bind the data) are executed.

The web page itself is different than the previous example because we created a form that contains the DropDownList. The DropDownList control is populated with the last name of customers from the Customers table (Figure 12-1).

There would be other controls in a real-world application such as a button that when selected causes the selected customer last name to be processed (see Chapter 8).

<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
  If Not IsPostBack Then
   Dim conCust As SqlConnection
   Dim cmdSelectRows As SqlCommand
   Dim dtrCust As SqlDataReader
   conCust = New SqlConnection( "Server=localhost;UID=
MyID;PWD=MyPassword;Database=CustomerContact Data")
   conCust.Open()
   cmdSelectRows = New SqlCommand( "Select cistLastName From
custContact", conCust)
   dtrCust = cmdSelectRows.ExecuteReader()
   deleteCust.DataSource = dtrCust
     deleteCust.DataTextField = "custLastName"
      deleteCust.DataBind()
      dtrCust.Close()
      conCust.Close()
    End If
End Sub
</Script>
<html>
<head><title>Drop-Down List Control Data Binding</title></head>
<body>
   <form Runat="Server">
      <asp:DropDownList ID="deleteCust" Runat="Server" />
   </form>
</body>
</html>

Please be sure to check back next week for the conclusion of this article!