Recipe 2.3. Generating a Quick-and-Dirty Tabular Display

Problem

You want to display data from a database in a table, and you're not overly concerned about performance or your ability to control the arrangement of the data items within the display.

Solution

Use a GridView control and bind the data to it.

In the .aspx file, add the GridView control responsible for displaying the data.

In the code-behind class for the page, use the .NET language of your choice to:

Create a SqlDataSource.
Set the ConnectionString, DataSourceMode, ProviderName, and SelectCommand properties of the SqlDataSource.
Assign the data source to the GridView control and bind it.

Figure 2-1 shows the appearance of a typical GridView in a browser. Examples 2-1 through 2-3 show the .aspx and VB and C# code-behind files for the application that produces this result.

Figure 2-1. Quick-and-dirty GridView output

Discussion

Implementing a simple GridView requires little coding. You must first add a GridView control to the .aspx file for your application and set a few of its attributes, as shown in Example 2-1. The GridView control has many attributes you can use to control the creation of a GridView object, but only three are required for this example: the id, runat, and AutoGenerateColumns attributes. The id and runat attributes are required by all server controls. When the AutoGenerateColumns attribute is set to true, it causes the GridView to create the required columns automatically along with their headings from the data source.

The code required to read and bind the data to the GridView goes into the code-behind class associated with the .aspx file as shown in Examples 2-2 (VB) and 2-3 (C#). In our example, this code is placed in the Page_Load method for convenience of illustration. It creates a SqlDataSource, sets the properties to define the source of the data and the SELECT command to retrieve the data from the database, and binds the data source to the GridView control. When the DataBind method of the GridView method is executed, the data source opens a connection to the database, retrieves the data, closes the connection to the database, and then binds the data to the GridView.

Setting the AutoGenerateColumns attribute of a DataGrid to true is a simple way to format your data, but it has two drawbacks. First, using the attribute causes a column to be created for every column specified in the Select statement, so you should be careful to include only the data you want to see in the GridView in the statement. In other words, use the SELECT * statement with caution. Second, the columns you SELECT will be given the same names as the columns in the database. You can get around this problem by using the AS clause in your SELECT statement to rename the columns when the data is read into the data reader.

 <%@ Page Language="VB" MasterPageFile="~/ASPNetCookbookVB.master" AutoEventWireup="false" CodeFile="CH02QuickAndDirtyGridViewVB.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH02QuickAndDirtyGridViewVB" Title=" Quick and Dirty GridView" %> <asp:Content  Runat="server" ContentPlaceHolder>  <div align="center" > Quick and Dirty GridView With Data From Database (VB) </div> <asp:GridView  Runat="Server" AutoGenerateColumns="true" BorderColor="#000080" BorderWidth="2px" HorizontalAlign="Center" Width="90%" > </asp:GridView> </asp:Content>

Example 2-2. Quick-and-dirty DataGrid code-behind (.vb)

 Option Explicit On Option Strict On Imports Microsoft.VisualBasic Imports System.Configuration Imports System.Data Imports System.Data.OleDb Namespace ASPNetCookbook.VBExamples ''' <summary> ''' This class provides the code behind for ''' CH02QuickAndDirtyGridViewVB.aspx ''' </summary> Partial Class CH02QuickAndDirtyGridViewVB Inherits System.Web.UI.Page '''*********************************************************************** ''' <summary> ''' This routine provides the event handler for the page load event. It ''' is responsible for initializing the controls on the page. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Private Sub Page_Load(ByVal sender As Object, _   ByVal e As System.EventArgs) Handles Me.Load Dim dSource As SqlDataSource = Nothing If (Not Page.IsPostBack) Then 'configure the data source to get the data from the database dSource = New SqlDataSource() dSource.ConnectionString = ConfigurationManager. _ ConnectionStrings("dbConnectionString").ConnectionString dSource.DataSourceMode = SqlDataSourceMode.DataReader dSource.ProviderName = "System.Data.OleDb" dSource.SelectCommand = "SELECT Title, ISBN, Publisher " & _ "FROM Book " & _ "ORDER BY Title" 'set the source of the data for the gridview control and bind it gvQuick.DataSource = dSource gvQuick.DataBind() End If End Sub 'Page_Load End Class 'CH02 QuickAndDirtyGridViewVB End Namespace

Example 2-3. Quick-and-dirty DataGrid code-behind (.cs)

 using System; using System.Configuration; using System.Data; using System.Data.OleDb; using System.Web.UI.WebControls; namespace ASPNetCookbook.CSExamples {  /// <summary>  /// This class provides the code behind for  /// CH02QuickAndDirtyGridViewCS.aspx  /// </summary>  public partial class CH02QuickAndDirtyGridViewCS : System.Web.UI.Page {  ///***********************************************************************  /// <summary>  /// This routine provides the event handler for the page load event.  /// It is responsible for initializing the controls on the page.  /// </summary>  /// <param name="sender">Set to the sender of the event</param>  /// <param name="e">Set to the event arguments</param>  protected void Page_Load(object sender, EventArgs e)  { SqlDataSource dSource = null; if (!Page.IsPostBack) { // configure the data source to get the data from the database dSource = new SqlDataSource(); dSource.ConnectionString = ConfigurationManager.  ConnectionStrings["dbConnectionString"].ConnectionString; dSource.DataSourceMode = SqlDataSourceMode.DataReader; dSource.ProviderName = "System.Data.OleDb"; dSource.SelectCommand = "SELECT Title, ISBN, Publisher " + "FROM Book " + "ORDER BY Title"; // set the source of the data for the gridview control and bind it gvQuick.DataSource = dSource; gvQuick.DataBind(); } } // Page_Load } // CH02QuickAndDirtyGridViewCS }

Recipe 2.3. Generating a Quick-and-Dirty Tabular Display