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.
Use a DataGrid control and bind the data to it.
In the .aspx file, add the
DataGridcontrol responsible for displaying the
data.
In the code-behind class for the page, use the .NET language of your choice to:
Open a connection to the database.
Build a query string, and read the desired data from the database.
Assign the data source to the
DataGridcontrol and bind it.
Figure 1-1 shows the appearance of a typical
DataGrid in a browser. Example 1-1 through Example 1-3 show the
.aspx and VB and C# code-behind files for the
application that produces this result.
Implementing a simple DataGrid requires very
little coding. You must first add a DataGrid tag
to the .aspx file for your application and set a
few of its attributes, as shown in Example 1-1. The
DataGrid tag has many attributes you can use to
control the creation of a DataGrid object, but
only three are required: 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 DataGrid to
automatically create the required columns along with their headings
from the data source.
The code required to read the data and bind it to the
DataGrid goes into the code-behind class
associated with the .aspx file, as shown in
Example 1-2 (VB) and Example 1-3
(C#). In our example, this code is placed in the
Page_Load method, for convenience of illustration.
It opens a connection to the database, reads the data from the
database using an OleDbCommand and an
OleDbDataReader, binds the data reader to the
DataGrid control, and then performs the necessary
cleanup.
Warning
When using the data reader objects, be sure to close the connection to the database. Failing to close the connection will tie up system resources, because the garbage collector will not close database connections. In addition, be aware that while the data reader is using the database connection, no other operations can be performed with the data connection, other than closing it.
Setting the AutoGenerateColumns attribute of a
DataGrid to True is a simple
way to format your data, but it has a couple of 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 in the statement only the data you want to see
in the DataGrid. 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.
For more information on the DataGrid control, see
ASP.NET in a Nutshell and Programming
ASP.NET (O’Reilly); search for
OleDBCommand and
OleDbDataReader: on the MSDN Library; other
sources for ADO.NET-specific information are ADO.NET in a
Nutshell and ADO.NET Cookbook
(O’Reilly).
Example 1-1. Quick-and-dirty DataGrid (.aspx)
<%@ Page Language="vb" AutoEventWireup="false"
Codebehind="CH01QuickAndDirtyDatagridVB.aspx.vb"
Inherits="ASPNetCookbook.VBExamples.CH01QuickAndDirtyDatagridVB" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Quick and Dirty Datagrid</title>
<link rel="stylesheet" href="css/ASPNetCookbook.css">
</head>
<body leftmargin="0" marginheight="0" marginwidth="0" topmargin="0">
<form id="frmData" method="post" runat="server">
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr>
<td align="center">
<img src="images/ASPNETCookbookHeading_blue.gif">
</td>
</tr>
<tr>
<td class="dividerLine">
<img src="images/spacer.gif" height="6" border="0"></td>
</tr>
</table>
<table width="90%" align="center" border="0">
<tr>
<td><img src="images/spacer.gif" height="10" border="0"></td>
</tr>
<tr>
<td align="center" class="PageHeading">
Quick and Dirty DataGrid With Data From Database (VB)</td>
</tr>
<tr>
<td><img src="images/spacer.gif" height="10" border="0"></td>
</tr>
<tr>
<td align="center">
<!-- Minimal datagrid -->
<asp:DataGrid id="dgQuick"
runat="server"
BorderColor="000080"
BorderWidth="2px"
AutoGenerateColumns="True"
width="100%" />
</td>
</tr>
</table>
</form>
</body>
</html>Example 1-2. Quick-and-dirty DataGrid code-behind (.vb)
Option Explicit On
Option Strict On
'-----------------------------------------------------------------------------
'
' Module Name: CH01QuickAndDirtyDatagridVB.aspx.vb
'
' Description: This class provides the code behind for
' CH01QuickAndDirtyDatagridVB.aspx
'
'*****************************************************************************
Imports Microsoft.VisualBasic
Imports System.Configuration
Imports System.Data
Imports System.Data.OleDb
Namespace ASPNetCookbook.VBExamples
Public Class CH01QuickAndDirtyDatagridVB
Inherits System.Web.UI.Page
'controls on form
Protected dgQuick As System.Web.UI.WebControls.DataGrid
'*************************************************************************
'
' ROUTINE: Page_Load
'
' DESCRIPTION: This routine provides the event handler for the page load
' event. It is responsible for initializing the controls
' on the page.
'
'-------------------------------------------------------------------------
Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
Dim dbConn As OleDbConnection
Dim dCmd As OleDbCommand
Dim dReader As OleDbDataReader
Dim strConnection As String
Dim strSQL As String
If (Not Page.IsPostBack) Then
Try
'get the connection string from web.config and open a connection
'to the database
strConnection = _
ConfigurationSettings.AppSettings("dbConnectionString")
dbConn = New OleDb.OleDbConnection(strConnection)
dbConn.Open( )
'build the query string and get the data from the database
strSQL = "SELECT Title, ISBN, Publisher " & _
"FROM Book " & _
"ORDER BY Title"
dCmd = New OleDbCommand(strSQL, dbConn)
dReader = dCmd.ExecuteReader( )
'set the source of the data for the datagrid control and bind it
dgQuick.DataSource = dReader
dgQuick.DataBind( )
Finally
'cleanup
If (Not IsNothing(dReader)) Then
dReader.Close( )
End If
If (Not IsNothing(dbConn)) Then
dbConn.Close( )
End If
End Try
End If
End Sub 'Page_Load
End Class 'CH01QuickAndDirtyDatagridVB
End NamespaceExample 1-3. Quick-and-dirty DataGrid code-behind (.cs)
//----------------------------------------------------------------------------
//
// Module Name: CH01QuickAndDirtyDatagridCS.aspx.cs
//
// Description: This class provides the code behind for
// CH01QuickAndDirtyDatagridCS.aspx
//
//****************************************************************************
using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
namespace ASPNetCookbook.CSExamples
{
public class CH01QuickAndDirtyDatagridCS : System.Web.UI.Page
{
// controls on form
protected System.Web.UI.WebControls.DataGrid dgQuick;
//************************************************************************
//
// ROUTINE: Page_Load
//
// DESCRIPTION: This routine provides the event handler for the page
// load event. It is responsible for initializing the
// controls on the page.
//
//------------------------------------------------------------------------
private void Page_Load(object sender, System.EventArgs e)
{
OleDbConnection dbConn = null;
OleDbCommand dCmd = null;
OleDbDataReader dReader = null;
String strConnection = null;
String strSQL = null;
if (!Page.IsPostBack)
{
try
{
// get the connection string from web.config and open a connection
// to the database
strConnection =
ConfigurationSettings.AppSettings["dbConnectionString"];
dbConn = new OleDbConnection(strConnection);
dbConn.Open( );
// build the query string and get the data from the database
strSQL = "SELECT Title, ISBN, Publisher " +
"FROM Book " +
"ORDER BY Title";
dCmd = new OleDbCommand(strSQL, dbConn);
dReader = dCmd.ExecuteReader( );
// set the source of the data for the datagrid control and bind it
dgQuick.DataSource = dReader;
dgQuick.DataBind( );
} // try
finally
{
// cleanup
if (dReader != null)
{
dReader.Close( );
}
if (dbConn != null)
{
dbConn.Close( );
}
} // finally
}
} // Page_Load
} // CH01QuickAndDirtyDatagridCS
}Get ASP.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
