asp.net.ph : Displaying Information From a Database

asp.net.ph

Home > Data Access / ADO.NET > Displaying information from a database > Displaying Data in a DataGrid

Accessing Data with ADO.NET

The <asp:DataGrid> control is the most versatile of the three controls. It displays tabular data and optionally supports selecting, sorting, paging and editing the data.

By default, DataGrid generates a bound column for each field in the data source ( AutoGenerateColumns=true ).

Each field in the data is rendered in a separate column, in the order it occurs in the data. Field names appear in the grid’s column headers, and values are rendered in text labels. A default format is applied to non-string values.

You can also specify which columns appear in the DataGrid.

This example shows how to access an SQL Server™ database that contains book titles and several key pieces of information about each book, and then displays the data using a DataGrid control, in the columns you specify.

NOTE: This sample illustrates using the SQL Server™ .NET Data Provider, though the same concepts apply to the corresponding OLEDB classes as well.

Import the proper namespaces into your page. This provides your code with access to the necessary classes.
```
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
```
In the <head> of the Web Forms page, implement a Page_Load function within a <script runat="server"> code declaration block that, essentially:
1. establishes a connection to the database,
2. creates an instance of a DataReader to contain the database information,
3. and then binds the DataGrid control to the Datareader.
This is shown in the following steps.
```
<script language="C#" runat="server">
   void Page_Load ( object src, EventArgs e ) {
```

Set up a connection to the database, in this case the SQL sample pubs database.

      SqlConnection myConn = new SqlConnection
         ( "server=(local)//NetSDK; trusted_connection=yes; database=pubs" );

Define the SQL Select query that will be used to fetch the data from the Titles table.

      SqlCommand myCmd = new SqlCommand ( "SELECT * FROM pubs_Titles", myConn );

Open the connection.
```
      myConn.Open ( );
```
Set the DataSource of the DataGrid control ( herein codenamed myGrid ) to the output of the ExecuteReader method. This method returns the result of the query command in the form of a DataReader, which we bind directly to the DataGrid.
```
      myGrid.DataSource = myCmd.ExecuteReader ( );
      myGrid.DataBind ( );
```
Close the data connection.
```
      myConn.Close ( );
   }
</script>
```

Within the <body> of the Web Forms page, declare the opening tag of the DataGrid control, setting it up to display the columns you define.

<body>

<asp:datagrid id="myGrid" runat="server"
   width="92%" cellpadding=5
   headerstyle-backcolor="lightsteelblue"
   headerstyle-font-bold
   itemstyle-verticalalign="top"
   autogeneratecolumns=false>

Set up the contents of each column using the DataGrid Columns property. In this case we are using a TemplateColumn along with several BoundColumns. Note that only the item in the template column uses a databinding expression; the bound columns use the value of the field specified in the DataField property for each item in the column.

   <columns>
      <asp:templatecolumn>
         <itemtemplate>
            <a href='details_title.aspx?id=<%# Eval ( "title_id" ) %>’>
               <img width=60 align="top" alt="Click for details" border=0
                  src='<%# Eval ( "title_id", "/asp.net.ph/shared/images/title-{0}.gif" ) %>’>
            </a>
      </asp:templatecolumn>

      <asp:boundcolumn headertext="Title"
         datafield="title" />
      <asp:boundcolumn headertext="Notes"
         datafield="notes" />
      <asp:boundcolumn headertext="Publication"
         datafield="pubdate"
         dataformatstring="{0:d}" />
      <asp:boundcolumn headertext="Price"
         datafield="price"
         dataformatstring="{0:c}"
         itemstyle-horizontalalign="right" />
   </columns>

NOTE: To avoid errors, the databinding expression must be written as a single line without any line breaks. The code examples shown here are done so only for readability.

Declare the closing tag of the DataGrid control and close the page.
```
</asp:datagrid>

</body>
```

For more information, see Working with the DataGrid Control.

The examples presented in this section apply several concepts and methods that are described elsewhere in this workshop. For particulars, see Data Binding in Web Forms and Web Forms Server Controls Templates.

Selecting information from a database

Suggested Reading

© 2025 Reynald Nuñez and asp.net.ph. All rights reserved.

If you have any question, comment or suggestion
about this site, please send us a note