7.3 Binding to Database Sources | Essential ASP.NET With Examples in C#

As we have seen, it is possible to bind to many different types of collections in .NET. The most common type of binding by far, however, is to bind to a result set retrieved from a database query. ADO.NET provides two ways of retrieving result sets from a database: the streaming IDataReader interface and the disconnected DataSet class. We will look at each of these in turn .

7.3.1 IDataReader Binding

The most efficient way to retrieve data for binding from a database is to use the streaming IDataReader interface from ADO.NET. This interface provides access to the results of a query in a stream, in forward-only fashion, and makes no additional copy of the data. In all the existing ADO.NET data providers, the data reader implementation classes also support IEnumerable so that they are compatible with data binding, and in general, it should be safe to assume that any data reader implementation provides an IEnumerable interface implementation as well.

In our first example of using a data reader, shown in Listing 7-1, we bound to a DataGrid , which provides a tabular rendering of the data. If you are trying to bind a data reader to a single column control such as a ListBox or a DropDownList , however, it is ambiguous which fields of the result set should be mapped to the strings and values of the control. To deal with this, controls like the DropDownList define two additional fields: DataTextField and DataValueField. These fields can be initialized to the appropriate column names of the data reader to specify the column from which the data should be drawn when the control populates itself with data. Listing 7-3 shows an example of binding an IDataReader to a drop-down list.

Listing 7-3 Binding a Data Reader to a DropDownList

 <! File: DataReaderBind.aspx > <%@Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script language="C#" runat="server"> protected void Page_Load(Object src, EventArgs e) {   if (!IsPostBack)   {     IDbConnection conn =     new SqlConnection("server=.;uid=sa;pwd=;database=Pubs");     IDbCommand cmd = conn.CreateCommand();     cmd.CommandText = "SELECT * FROM Authors";     try     {       conn.Open();       IDataReader reader = cmd.ExecuteReader();       _authors.DataSource = reader;       _authors.DataTextField = "au_lname";       _authors.DataValueField = "au_id";       _authors.DataBind();     }     finally     {       conn.Dispose();     }   }   else   {     _message.Text =              string.Format("You selected employee #{0}",                            _authors.SelectedItem.Value);   } } </script></head> <form runat=server>   <asp:DropDownList id="_authors" runat=server />   <br/>   <asp:Label id="_message" runat=server/>   <br/>   <input type=submit value="Submit" /> </form> </body> </html>

In this example, we are only populating the DropDownList control if the incoming request is the initial GET request to the page, not a subsequent POST back to the same page. By default, all the data-bound controls retain their state across post-backs, so it is not necessary to repopulate them on a post-back. Also note in this example that we are able to associate two data values with each item in the DropDownList . The DataTextField determines what string is displayed in the control, and the DataValueField determines what value is associated with that field. This is a convenient mechanism for retaining primary-key information for table values without resorting to additional data structures. Figure 7-4 shows a sample instance of this page running.

Figure 7-4. DataReaderBind.aspx Page Running

graphics/07fig04.gif

7.3.2 DataSet Binding

It is also common to bind DataSet s to controls for display. Because a DataSet can represent the results of multiple database queries, however, you need to specify which portion of the DataSet should be used for binding. If you simply bind to the entire DataSet , the default view of the first table in the DataSet is used. If you want more control over which table within a DataSet to use during binding, data-bound controls support an additional field, DataMember , that indicates which "set" of data to bind to the control. For a DataSet , this means which table to bind. Alternatively, you can be explicit about it and bind to a table within a DataSet , in which case the default view of that table is used. Or you can be even more explicit and use the DataView that provides a "view" into a data set and can be created with custom sorting and filtering rules.

Listing 7-4 shows an example of binding data from a DataSet to a pair of controls. Note that the first control is bound directly to the DataSet , which implicitly binds to the default view of the DataSet . The second control is bound to an explicitly created DataView , whose Filter property has been populated to show only authors with last names beginning with G and whose Sort property has been set to the au_id field of the table.

Listing 7-4 Binding to a DataView

 <! File: DataViewBind.aspx > <%@ Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script language="C#" runat="server"> protected void Page_Load(Object src, EventArgs e) {   if (!IsPostBack)   {     SqlConnection conn =     new SqlConnection("server=.;uid=sa;pwd=;database=pubs");     SqlDataAdapter da =           new SqlDataAdapter("select * from Authors", conn);     DataSet ds = new DataSet();     da.Fill(ds, "Authors");     _lb1.DataSource = ds;     _lb1.DataTextField = "au_lname";     _lb1.DataValueField = "au_id";     DataView view = new DataView(ds.Tables["Authors"]);     view.RowFilter = "au_lname like 'G%'";     view.Sort = "au_lname";     _lb2.DataSource = view;     _lb2.DataTextField = "au_lname";     _lb2.DataValueField = "au_id";     DataBind();   }   else   {     _message.Text =       string.Format("LB1 = {0}, LB2 = {1}",                     _lb1.SelectedItem.Value,                     _lb2.SelectedItem.Value);   } } </script> <body> <form runat=server>   <asp:ListBox id="_lb1" runat=server /> <br/>   <asp:ListBox id="_lb2" runat=server /> <br/>   <asp:Label id="_message" runat=server/> <br/>   <input type=submit value="Submit"/> </form> </body> </html>

The biggest difference between binding to a DataSet and binding to data readers is that the DataSet makes a local copy of the data, and the connection to the database is closed immediately after the call to the SqlDataAdapter.Fill() method completes. Figure 7-5 shows the DataViewBind.aspx page running.

Figure 7-5. DataViewBind.aspx Page Instance

graphics/07fig05.gif

7.3.3 DataSet versus DataReader for Data Binding

As we have seen, both the DataSet and the DataReader can be used to bind data to a control, but when should you choose one over the other? A good rule of thumb to answer this question is, if you are not taking advantage of the DataSet 's cache of the data, you should probably use a DataReader . If you use a DataSet simply to retrieve data from a data source and then immediately bind it to a control, subsequently discarding the DataSet , you are creating an unnecessary duplicate copy of the data, since all data-bound controls keep their own local copy of any data to which they are bound.

In addition, because data-bound controls guarantee that they will retain any data you bind to them, even across post-backs, it is quite easy to generate three distinct copies of the data in-memory in the server, which can be problematic for large rowsets. The first copy of the data is loaded into the DataSet 's cache after it is filled from the data source. The second copy is created when you perform a DataBind on the control, which takes the data from the DataSet and make its own local copy using its own internal storage mechanism. The third, and final, copy exists in the ViewState state bag, which the control populates with its data to ensure that it can be fully restored on subsequent post-backs. Figure 7-6 shows this inefficient data propagation scenario.

Figure 7-6. The Hazards of Na ve Data Binding

graphics/07fig06.gif

To avoid this duplication of data during data binding, you can take two steps. First, use a DataReader in place of a DataSet when you are doing nothing with the data but binding it to a control. Second, disable the view state of the data-bound control by setting the control's EnableViewState flag to false (although there are caveats to doing this, discussed later). Performing both of these steps removes the two extra copies of the data, leaving only the local copy managed by the control, as shown in Figure 7-7.

Figure 7-7. Efficient Data Binding

graphics/07fig07.gif

Unfortunately, you cannot always disable the view state of a control without impacting its behavior, which you may be relying on in your application. For example, server-side events rely on the EnableViewState flag being enabled for a control, so if you have added handlers for any server-side events issued by a data-bound control, you have no choice but to leave view state enabled. Also, by disabling view state, you must take care to explicitly repopulate the data-bound control on each request. It is no longer sufficient merely to populate it once on the initial GET request and assume that it will retain its state on subsequent POST requests to the same page. If you can live with these restrictions, however, you can save significant bandwidth space and server memory by disabling view state on your data-bound controls.

There are also occasions where using the DataSet makes more sense than using a DataReader . As mentioned earlier, if you are taking advantage of the fact that the DataSet creates a local cache of the data, by all means use it. One case in which to consider a DataSet is when you bind one set of data to multiple controls, especially by taking advantage of the DataView mechanism of the DataSet to provide filtering and ordering during the binding process. Another appealing application of the DataSet is to keep an instance of a DataSet in the cache (discussed in Chapter 9) for binding to controls without having to go back to the database over and over. This works especially well for small result sets, such as lookup tables.