GridView Examples for ASP.NET 2.0: Accessing Data with the DataSource Controls

 

Click here to return to the TOC.

One of the most common tasks Web developers face is working with data. The data might be from an XML file, from a database, or provided through a more abstract interface, such as through a set of classes acting as a data access layer. Needless to say, a large amount of time goes into rather mundane data-related tasks such as:

• Writing the code to access data from a data store.
• Writing the code to take a user's input and update the data within the data store.
• Writing the code to delete one or more records from the data store.

Oftentimes this data access code is very similar, leading to writing mundane and oft-repeated code. And not only is writing tedious code uninteresting and boring, but it's also a recipe for bugs. If you find yourself cutting and pasting code you might forget to change a table or column name in the SQL syntax of the pasted code, or you might forget that for the pasted code you need to handle NULL data differently than before.

To address these problems, ASP.NET 2.0 introduces a new class of server controls—the data source controls. These controls encapsulate data from varying data stores and can be configured entirely using a declarative syntax, meaning that to access data in ASP.NET 2.0 you don't need to write a lick of code. (Of course you can still access and modify data stores programmatically with ASP.NET 2.0, much like you do with ASP.NET 1.x.)

The main data source controls that you'll find yourself working with in conjunction with the GridView are:

• SqlDataSource—provides data access through data stores that understand the SQL syntax. This includes databases such as Microsoft SQL Server, Microsoft Access, Oracle, and so on.
• ObjectDataSource—provides data access through a developer-created class. (Well architected Web applications oftentimes have a set of classes that provide methods to manipulate or retrieve the data from an underlying data store. These classes provide a layer of encapsulation, hiding the details of the underlying data store.)
• XmlDataSource—provides data access through an XML document.

In a moment we'll look at using these DataSources to access data. But before we do, realize that when displaying data on a Web page, accessing the data is only one part of the puzzle. The other piece is displaying the data, and that's where the GridView control comes in. To start, you'll add a GridView to the ASP.NET page. Next, you'll need to specify what data the GridView should display. The GridView control, like all data Web controls in ASP.NET 2.0, has a DataSourceID property, which you can set to the ID of the data source control whose data you want to display. By setting the GridView's DataSourceID, the GridView will, by default, show all the fields of all the records returned by the data source control. Later on in this article we'll see how to customize what columns should be shown, how to format the columns, and so forth.

Accessing Data with the SqlDataSource

To add any of the data source controls to an ASP.NET page, simply drag them from the Visual Studio 2005 Toolbox onto the appropriate ASP.NET Web page's Design view. Once you do this, the control's Smart Tag will appear with an option titled "Configure Data Source." Choosing this option will launch a wizard that prompts for the requisite information.

For the SqlDataSource, the first screen prompts you to specify the connection string for data store you want to connect to (see Figure 1). The drop-down shown in Figure 1 contains connection strings that exist in the <connectionStrings> section of the application's Web.config file. If you haven't yet defined any connection strings, or don't see the one you want, you can create a new connection string by clicking the New button.

Figure 1

Once you have created or chosen an existing connection string, the next stage of the wizard prompts you to specify what data you are interested in working with (see Figure 2). You can either select a table or view from the drop-down, picking the fields you want returned, or you can provide your own SQL statement or stored procedure name to call. From this pane you can also specify a WHERE condition to limit the returned data, as well as indicate how the retrieved data should be ordered. Through the Advanced Options button you will find options to create not only a SELECT statement to retrieve data, but INSERT, UPDATE, and DELETE statements as well. (We'll examine creating INSERT, UPDATE, and DELETE statements later on in this article.)

Figure 2

The last step of the wizard allows you to run the SELECT statement specified against the data store to see what data, exactly, is returned.

That's all there is to configuring a SqlDataSource. After stepping through the SqlDataSource's wizard, we have specified the data we want to display. All that remains is to display the data, which is where the GridView comes in. Start by dragging and dropping a GridView control from the Toolbox onto the ASP.NET page. The Smart Tag will prompt you to select the GridView's DataSource. Choose the SqlDataSource you just added and, like magic, the GridView will automatically have columns corresponding to the fields returned by the data source control.

Figure 3 shows the Design view of an ASP.NET Web page after tying a GridView to a SqlDataSource that returns the ProductID, ProductName, QuantityPerUnit, UnitPrice, and UnitsInStock fields of the Products table in the Northwind database. Figure 4 shows this ASP.NET page when viewed through a browser.

Figure 3 (Click on the graphic for a larger image)

Figure 4

The data source controls, like any other ASP.NET control, have a corresponding declarative markup that is inserted into the ASP.NET page's HTML section. The markup below shows the complete markup for the page shown in Figure 4. Notice that we did not need to write a single line of server-side code in order to display data from a database.

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" 
  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:SqlDataSource ID="productsDataSource" Runat="server" 
          "SelectCommand="SELECT [ProductID], [ProductName], " & _
          "[QuantityPerUnit], [UnitPrice], [UnitsInStock] " & _
          "FROM [Products]"
            ConnectionString=
            "<%$ ConnectionStrings:NWConnectionString %>" 
                DataSourceMode="DataReader">
        </asp:SqlDataSource>
        <asp:GridView ID="productGridView" Runat="server" 
             DataSourceID="productsDataSource"
            DataKeyNames="ProductID" AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField ReadOnly="True" 
                  HeaderText="ProductID" InsertVisible="False" 
                  DataField="ProductID"
                    SortExpression="ProductID"></asp:BoundField>
                <asp:BoundField HeaderText="ProductName" 
                  DataField="ProductName" 
                  SortExpression="ProductName"></asp:BoundField>
                <asp:BoundField HeaderText="QuantityPerUnit" 
                    DataField="QuantityPerUnit" 
                    SortExpression="QuantityPerUnit"></asp:BoundField>
                <asp:BoundField HeaderText="UnitPrice" 
                   DataField="UnitPrice" SortExpression="UnitPrice">
                </asp:BoundField>
                <asp:BoundField HeaderText="UnitsInStock" 
                  DataField="UnitsInStock" 
                  SortExpression="UnitsInStock"></asp:BoundField>
            </Columns>
        </asp:GridView>
    
    </div>
    </form>
</body>
</html>

Accessing Data with the ObjectDataSource

With the SqlDataSource control, the ASP.NET page contains the specific details pertinent to data access. That is, buried in the SqlDataSource's declarative syntax you'll find a reference to a connection string, SQL statements for (potentially) SELECTs, INSERTs, UPDATEs, and DELETEs, and a list of parameters for each type of SQL statement. The downside of this approach is that it tightly binds the details of the data store to the ASP.NET page. If your database's underlying schema changes you'll have to modify all ASP.NET pages that touch the modified parts of the data store. Additionally, placing data access logic in the ASP.NET page increases the complexity of the pages. The data store's schema in your application may be large and particularly unwieldy to developers new to the project. By tying the data access logic to the ASP.NET page, these developers must have an intimate understanding of the data model and its relation to the program in order to create or modify ASP.NET pages.

A better approach for large Web applications is to divide the application's architecture into different layers. Typically Web applications are divided into three layers:

1. A Presentation Layer, which contains the ASP.NET Web pages.
2. A Data Access Layer (DAL), which consists of classes that serve as a ferry between the requests from the presentation layer and the backend data store layer.
3. A Data Store Layer, such as a Microsoft SQL Server database.

Assuming you have setup a Data Access Layer (DAL), the ASP.NET 2.0 ObjectDataSource control can be used to encapsulate information retrieved from the DAL. When using the ObjectDataSource you'll have to provide two bits of information:

1. The fully qualified type name of your DAL class that contains the method(s) to access the data.
2. The method names for accessing data, deleting data, updating data, and inserting data.

To demonstrate using the ObjectDataSource, I created a simple DAL to interact with the Northwind database. The DAL consists of a Product class with properties ProductID, ProductName, QuantityPerUnit, UnitPrice, and UnitsInStock, along with a class called ProductDAL, that contains a static method called GetProducts() that returns a List of Product instances. These two classes are shown below:

Product and ProductDAL (Visual Basic)

Imports Microsoft.VisualBasic
Public Class Product
#Region "Private Member Variables"
    Private _productID As Integer
    Private _productName As String
    Private _quantityPerUnit As String
    'Consider making _unitPrice & _unitInStock nullable fields,
    'since they are NULL in the Northwind database.
    Private _unitPrice As Decimal
    Private _unitsInStock As Integer
#End Region
#Region "Constructors"
    Public Sub New()
    End Sub
    Public Sub New(ByVal productID As Integer, _
      ByVal productName As String, ByVal quantityPerUnit As String, _
      ByVal unitPrice As Decimal, ByVal unitsInStock As Integer)
        Me._productID = productID
        Me._productName = productName
        Me._quantityPerUnit = quantityPerUnit
        Me._unitPrice = unitPrice
        Me._unitsInStock = unitsInStock
    End Sub
#End Region
#Region "Public Properties"
    Public Property ProductID() As Integer
        Get
            Return _productID
        End Get
        Set(ByVal value As Integer)
            If value < 0 Then
                Throw New ArgumentException("ProductID must be " & _
                  " greater than or equal to zero.")
            Else
                _productID = value
            End If
        End Set
    End Property
    Public Property ProductName() As String
        Get
            Return _productName
        End Get
        Set(ByVal value As String)
            _productName = value
        End Set
    End Property
    Public Property QuantityPerUnit() As String
        Get
            Return _quantityPerUnit
        End Get
        Set(ByVal value As String)
            _quantityPerUnit = value
        End Set
    End Property
    Public Property UnitPrice() As Decimal
        Get
            Return _unitPrice
        End Get
        Set(ByVal value As Decimal)
            If value < 0 Then
                Throw New ArgumentException("UnitPrice must be " & _
                   "greater than or equal to zero.")
            Else
                _unitPrice = value
            End If
        End Set
    End Property
    Public Property UnitsInStock() As Integer
        Get
            Return _unitsInStock
        End Get
        Set(ByVal value As Integer)
            If value < 0 Then
                Throw New ArgumentException("UnitsInStock must be " & _
                   "greater than or equal to zero.")
            Else
                _unitsInStock = value
            End If
        End Set
    End Property
#End Region
End Class
Imports Microsoft.VisualBasic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Public Class ProductDAL
    Public Shared Function GetProducts() As List(Of Product)
        ' returns a list of Product instances based on the
        ' data in the Northwind Products table
        Dim sql As String = "SELECT ProductID, ProductName, " & _
           "QuantityPerUnit, UnitPrice, UnitsInStock FROM Products"
        Dim myConnection As New & _
          SqlConnection( ConfigurationManager.ConnectionStrings( & _
          "NWConnectionString").ConnectionString)
        Dim myCommand As New SqlCommand(sql, myConnection)
        myConnection.Open()
        Dim reader As SqlDataReader = & _
         myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        Dim results As New List(Of Product)()
        While reader.Read()
            Dim product As New Product()
            product.ProductID = Convert.ToInt32(reader("ProductID"))
            product.ProductName = reader("ProductName").ToString()
            product.QuantityPerUnit = & _
              reader("QuantityPerUnit").ToString()
            If reader("UnitPrice").Equals(DBNull.Value) Then
                product.UnitPrice = 0
            Else
                product.UnitPrice = & _
                  Convert.ToDecimal(reader("UnitPrice"))
            End If
            If reader("UnitsInStock").Equals(DBNull.Value) Then
                product.UnitsInStock = 0
            Else
                product.UnitsInStock = & _
                  Convert.ToInt32(reader("UnitsInStock"))
            End If
            results.Add(product)
        End While
        reader.Close()
        myConnection.Close()
        Return results
    End Function
End Class

Product and ProductDAL (C#)

using System;
/// <summary>
/// Summary description for Product
/// </summary>
public class Product
{
    #region Private Member Variables
    private int productID;
    private string productName;
    private string quantityPerUnit;
    // Consider making _unitPrice & _unitInStock nullable fields,
    // since they are NULL in the Northwind database.
    private decimal unitPrice;
    private int unitsInStock;
    #endregion
    #region Constructors
    public Product() { }
    public Product(int productID, string productName, 
      string quantityPerUnit, decimal unitPrice, int unitsInStock)
    {
        this.productID = productID;
        this.productName = productName;
        this.quantityPerUnit = quantityPerUnit;
        this.unitPrice = unitPrice;
        this.unitsInStock = unitsInStock;
    }
    #endregion
    #region Public Properties
    public int ProductID
    {
        get
        {
            return productID;
        }
        set
        {
            if (productID < 0)
                throw new ArgumentException(@"ProductID must be 
                  greater than or equal to zero.");
            else
                productID = value;
        }
    }
    public string ProductName
    {
        get
        {
            return productName;
        }
        set
        {
            productName = value;
        }
    }
    public string QuantityPerUnit
    {
        get
        {
            return quantityPerUnit;
        }
        set
        {
            quantityPerUnit = value;
        }
    }
    public decimal UnitPrice
    {
        get
        {
            return unitPrice;
        }
        set
        {
            if (unitPrice < 0)
                throw new ArgumentException(@"UnitPrice must be 
                 greater than or equal to zero.");
            else
                unitPrice = value;
        }
    }
    public int UnitsInStock
    {
        get
        {
            return unitsInStock;
        }
        set
        {
            if (unitsInStock < 0)
                throw new ArgumentException(@"UnitsInStock must 
                  be greater than or equal to zero.");
            else
                unitsInStock = value;
        }
    }
    #endregion
}
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
/// <summary>
/// Summary description for ProductDAL
/// </summary>
public class ProductDAL
{
    public static List<Product> GetProducts()
    {
        // returns a list of Product instances based on the
        // data in the Northwind Products table
        string sql = @"SELECT ProductID, ProductName, QuantityPerUnit, 
         UnitPrice, UnitsInStock FROM Products";
        using (SqlConnection myConnection = new 
          SqlConnection(ConfigurationManager.ConnectionStrings[
          "NWConnectionString"].ConnectionString))
        {
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myConnection.Open();
            SqlDataReader reader = 
             myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            List<Product> results = new List<Product>();
            while (reader.Read())
            {
                Product product = new Product();
                product.ProductID = 
                 Convert.ToInt32(reader["ProductID"]);
                product.ProductName = reader["ProductName"].ToString();
                product.QuantityPerUnit = 
                 reader["QuantityPerUnit"].ToString();
                if (reader["UnitPrice"].Equals(DBNull.Value))
                    product.UnitPrice = 0;
                else
                    product.UnitPrice = 
                      Convert.ToDecimal(reader["UnitPrice"]);
                if (reader["UnitsInStock"].Equals(DBNull.Value))
                    product.UnitsInStock = 0;
                else
                    product.UnitsInStock = 
                      Convert.ToInt32(reader["UnitsInStock"]);
                results.Add(product);
            }
            reader.Close();
            myConnection.Close();
            return results;
        }
    }
}

As you can see in the source code, the GetProducts() method retrieves the pertinent fields from the Northwind database's Products table in a SqlDataReader and then loops through the retrieved results, creating a Product instance of each one and adding that instance to a List of Products. This List is then returned at the end of the method. These class files should be placed in the Web application's /app_code directory. Doing so guarantees that they will be automatically compiled and accessible to the ASP.NET pages in the application.

To display data from the DAL classes in an ASP.NET page, start by adding an ObjectDataSource to the page and click on its Configure Data Source link in its Smart Tag. This will launch a wizard similar to the SqlDataSource's, but instead of querying for the connection string, step 1 of the ObjectDataSource's wizard asks you to select the class that contains the methods for working with the data (see Figure 5). Once you have selected the class, the second step of the wizard prompts you to select the methods for selecting data, deleting data, updating data, and inserting data. Since we only have a method for accessing data, simply choose the GetProducts() method for selecting data and don't specify methods for deleting, updating, or inserting. Figure 6 shows the second step of the ObjectDataSource's wizard.

Figure 5

Figure 6

That's all that's involved for selecting data from an ObjectDataSource. At this point you can add a GridView to the page and set its Data Source to the ObjectDataSource you just configured. The declarative syntax of the ObjectDataSource and GridView is shown below. As with the SqlDataSource, note that to display the data returned by an object in the DAL requires no source code in the ASP.NET page.

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" 
  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ObjectDataSource ID="productsDataSource" 
            Runat="server" SelectMethod="GetProducts"
            TypeName="ProductDAL">
        </asp:ObjectDataSource>
        <asp:GridView ID="GridView1" Runat="server" 
           DataSourceID="productsDataSource">
        </asp:GridView>&nbsp;
    
    </div>
    </form>
</body>
</html>

Figure 7 shows a screenshot of the GridView bound to an ObjectDataSource when viewed through a browser. Notice that the GridView displays all records from the Products table with a column for each public property in the Product class.

Figure 7

Accessing Data with the XmlDataSource

In addition to providing access to data stored in traditional relational databases, or data encapsulated in objects, ASP.NET 2.0 also provides the XmlDataSource control for accessing data marked up in XML. Before you can use the XmlDataSource control you first need an XML file that contains the pertinent data. I created an XML file that contains a subset of the data in the Northwind Products table, which can be seen in Figure 8.

Figure 8

Just like all of the other data source controls, the XmlDataSource control has a wizard that prompts for the details it needs in order to grab the XML file's data. Unlike the other data source control wizards, however, the XmlDataSource's wizard has only one step, which is shown in Figure 9.

Figure 9

As Figure 9 shows, the wizard asks for four bits of information, only the first of which is required:

1. The path to the XML file, which is required.
2. A path to an XSD file that defines the schema for the XML file. You do not need to provide a schema, as the XmlDataSource does not use schema information at all. However, a data Web control might require type-specific information, which the schema file provides.
3. An optional path to an XSL transform file. If the XML in the file does not conform to a structure you want, you can specify a translation through this XSL transform file setting.
4. An optional XPath expression, useful for filtering the data returned by the XmlDataSource. By default, the entire contents of the XML file will be loaded by the XmlDataSource. By specifying an XPath expression, however, you can limit those elements retrieved.

As you can see in Figure 9, I provided only the path to the XML file.

To display the XML data retrieved by the XmlDataSource, simply drop a GridView onto the page and set its Data Source to the XmlDataSource just added. Once you have done this, the ASP.NET page's declarative syntax should look like:

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" 
  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:XmlDataSource ID="productsDataSource" 
         Runat="server" DataFile="~/DisplayingData/XML/Products.xml">
        </asp:XmlDataSource>
        <asp:GridView ID="GridView1" Runat="server" 
          DataSourceID="productsDataSource" 
          AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField HeaderText="ProductID" 
                   DataField="ProductID" SortExpression="ProductID">
                </asp:BoundField>
                <asp:BoundField HeaderText="ProductName" 
                   DataField="ProductName"
                   SortExpression="ProductName">
                </asp:BoundField>
                <asp:BoundField HeaderText="QuantityPerUnit" 
                  DataField="QuantityPerUnit" 
                  SortExpression="QuantityPerUnit"></asp:BoundField>
                <asp:BoundField HeaderText="UnitPrice" 
                   DataField="UnitPrice" 
                   SortExpression="UnitPrice"></asp:BoundField>
                <asp:BoundField HeaderText="UnitsInStock" 
                   DataField="UnitsInStock" 
                   SortExpression="UnitsInStock"></asp:BoundField>
            </Columns>
        </asp:GridView>
    
    </div>
    </form>
</body>
</html>

Figure 10

While XmlDataSources can be bound to GridViews, as the previous example showed, XmlDataSources are more commonly bound to hierarchical controls, such as the TreeView control or Menu control. Also, the XmlDataSource control is a read-only data source, meaning you cannot use an XmlDataSource as the backing store for an editable GridView.

Next Section: Formatting the GridView

© Microsoft Corporation. All rights reserved.