Questions That Should Be Asked More Frequently

Displaying Data on Your Web Page

Of course, connecting to a database is just the first step. Once you've done that, you generally want to execute a query, retrieve the results, and display the data you've retrieved onto the Web page.

As with Windows Forms, you can write ASP.NET code to set properties of controls by hand using code such as the following:

Visual Basic .NET

TextBox1.Text = MyDataSet.Tables("MyTable").Rows(0)("MyColumn")

Visual C# .NET

TextBox1.Text = (string) MyDataSet.Tables["MyTable"].Rows[0]["MyColumn"];

You can also take advantage of data binding features in ASP.NET controls to simplify this process. Let's say you've added code to your Web application to execute a query and fetch the results in a DataSet. Now you want to display the contents of the DataSet in an HTML table on your Web page. You could generate the HTML for the table by writing a lot of code, or by using XML and XSLT, but you'll save yourself a lot of headaches by using ASP.NET's data binding features.

Bound ASP.NET controls convert the data into HTML for your Web page. ASP.NET data binding is a one-way street. If you bind a text box to a column in a DataTable, display the page, and edit the contents of the text box, the new contents of the text box aren't assigned to that column of data in the DataTable. The DataGrid control does offer features that let you build updating functionality into your Web pages, but for the most part bound ASP.NET controls are used for read-only data.

Just as with Windows data binding, you can bind single-value controls such as text boxes or multivalue controls such as grids to data sources. You can bind multivalue controls using the standard DataSource and DataMember properties. You can bind both single-value and multivalue controls using the DataBinder object that's static to the page.

Using DataBinder.Eval

Let's start working with ASP.NET data binding by binding a TextBox control using the DataBinder object's Eval method. The DataBinder object uses reflection to extract data from an object. For example, you could use the following code to return the value of the CompanyName column in the first row in a DataTable:

DataBinder.Eval(DataTable, "Rows[0].[CompanyName]")

This Eval method returns data using the generic Object data type, which you can then cast to the appropriate data type.

The Eval method is overloaded. There's another Eval method that lets you supply a format string. Say you're retrieving information for a particular product and you want to display the product's unit price in a text box. You could use the following call to the Eval method to format the contents of the UnitPrice column as currency:

DataBinder.Eval(DataTable, "Rows[0].[UnitPrice]", "{0:c}")

This Eval method returns data in a string, using the format you've supplied. For more information on expressions for formatting strings, see the documentation for the String object's Format method.

The DataBinder object isn't just for data access objects. You can use the DataBinder object to extract data from properties of other objects as well. For example, the following code returns the value of a TextBox control's Visible property:

DataBinder.Eval(TextBox1, "Visible")

This syntax is roughly equivalent to directly calling the TextBox control's Visible property, except that the DataBinder code is late bound. If you made a typo and accidentally wrote the following code:

DataBinder.Eval(TextBox1, "Visibile")

you would receive an error at run time rather than at compile time.

The fact that the DataBinder object uses late binding to return data makes the object more flexible but represents a small performance hit because there's more work to do at run time.

Binding a TextBox to a DataSet

Now that we've learned a little about the DataBinder object, let's use it to bind a TextBox to a column of data returned by a DataAdapter. The following code retrieves information from the Customers table into a DataSet, and then uses the DataBinder object to set the TextBox control's Text property to the CompanyName column for the first row returned by the query:

Visual Basic .NET

Dim strConn, strSQL, strExpression As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName FROM Customers " & _          "WHERE CustomerID = 'WOLZA'" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds, "Customers") strExpression = "Tables[Customers].Rows[0].[CompanyName]" TextBox1.Text = CStr(DataBinder.Eval(ds, strExpression))

Visual C# .NET

string strConn, strSQL, strExpression; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName FROM Customers " +          "WHERE CustomerID = 'WOLZA'"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds, "Customers"); strExpression = "Tables[Customers].Rows[0].[CompanyName]"; TextBox1.Text = (string) DataBinder.Eval(ds, strExpression);

Binding a TextBox to a DataReader

You can also use the DataBinder object to extract data from a DataReader object. The following code snippet uses a DataReader object to retrieve the results of a query. Because we're going to use only the first row returned by the query, the code uses the SingleRow constant in the CommandBehavior enumeration.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() strSQL = "SELECT CustomerID, CompanyName FROM Customers " & _          "WHERE CustomerID = 'WOLZA'" Dim cmd As New OleDbCommand(strSQL, cn) Dim rdr As OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow) rdr.Read() TextBox1.Text = CStr(DataBinder.Eval(rdr, "[CompanyName]")) rdr.Close() cn.Close()

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); strSQL = "SELECT CustomerID, CompanyName FROM Customers " +          "WHERE CustomerID = 'WOLZA'"; OleDbCommand cmd = new OleDbCommand(strSQL, cn); OleDbDataReader rdr; rdr = cmd.ExecuteReader(CommandBehavior.SingleRow); rdr.Read(); TextBox1.Text = (string) DataBinder.Eval(rdr, "[CompanyName]"); rdr.Close(); cn.Close();

Binding DataGrid Controls to the Results of Queries

ASP.NET includes a built-in DataGrid control that can transform the results of a query into an HTML table. The DataGrid control offers a number of powerful features such as paging, sorting, and updating. For now, let's just focus on using the DataGrid to display the results of a query.

Binding a DataGrid to a DataSet

The following code creates an OleDbDataAdapter and a DataSet, fetches the results of a query into the DataSet, and binds a Web Forms DataGrid control to the desired DataTable in the DataSet.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " & _          "FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds, "Customers") gridCustomers.DataSource = ds gridCustomers.DataMember = "Customers" gridCustomers.DataBind()

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " +          "FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds, "Customers"); gridCustomers.DataSource = ds; gridCustomers.DataMember = "Customers"; gridCustomers.DataBind();

The code is identical to the code required to bind a Windows Forms DataGrid to a DataSet, except for one small but important difference. After setting the DataSource and DataMember properties, the code calls the DataBind method on the DataGrid. With the Windows Forms DataGrid, the grid is bound to the data source as soon as you set the DataSource and DataMember properties. The same is not true with Web Forms data binding. The control will not display data from its data source unless you call its DataBind method. You can also call the page's DataBind method, which implicitly calls the DataBind method on the page's controls.

Binding a DataGrid to a DataReader

As I noted earlier, data on a Web page is generally read-only. DataSet objects offer a great deal more functionality (caching updates, simplifying navigating between related tables, and so on) that you might not need if you want to simply execute a query and display the results on a Web page. To provide better performance, ASP.NET controls also support binding to DataReader objects.

The following code snippet executes the same query but retrieves the results via an OleDbDataReader and binds a Web Forms DataGrid to that DataReader.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " & _          "FROM Customers" Dim cn As New OleDbConnection(strConn) cn.Open() Dim cmd As New OleDbCommand(strSQL, cn) Dim rdr As OleDbDataReader = cmd.ExecuteReader() gridCustomers.DataSource = rdr gridCustomers.DataBind() rdr.Close() cn.Close()

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " +          "FROM Customers"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open() OleDbCommand cmd = new OleDbCommand(strSQL, strConn); OleDbDataReader rdr = cmd.ExecuteReader(); gridCustomers.DataSource = rdr; gridCustomers.DataBind(); rdr.Close(); cn.Close();