Working with the ASP.NET 2.0 ObjectDataSource Control

  • Article

 

Stephen Walther
Superexpert

October 2004
Updated March 2005

Applies to:
   Microsoft ASP.NET 2.0
   Microsoft Visual Studio 2005

Summary: The SqlDataSource make two-tiered data access easy in ASP.NET 2.0. However, this control isn't as helpful in n-tier applications, where the ObjectDataSource provides that same ease of use for business objects. Learn how to use the ObjectDataSource control to build a proper multi-tier Web application with the ASP.NET 2.0 Framework. (17 printed pages)

Contents

Introduction
Displaying Data with the ObjectDataSource Control
Using Parameters with the ObjectDataSource Control
Editing Data with the ObjectDataSource Control
Using the ObjectDataSource Control with the SqlDataSource Control
Conclusion

Introduction

Database access has been dramatically simplified in the Microsoft ASP.NET 2.0 Framework. By taking advantage of the new SqlDataSource control, you can select, update, insert, and delete database data without writing a single line of code.

The SqlDataSource control is a great control to use when building a simple application. If you need to quickly slap together a Web page that enables a user to display and edit database records, then the SqlDataSource control enables you to build this type of page in minutes.

For example, I just timed myself. By using the SqlDataSource control with the GridView control, I was able to build a page that displays the contents of the Northwind Products database table in 1 minute and 15 seconds (It's not pretty, but it does display the data). Wow, that's fast!

However, there is one problem with the SqlDataSource control. If you use the SqlDataSource control, then you are doing something bad. The SqlDataSource control is bad since the control forces you to jumble together your user interface layer with your business logic layer. Mixing layers, as any application architect will tell you, is just shameful.

When building a proper multi-tier Web application, you should have distinct user interface, business logic, and data access layers. Referring to SQL statements or stored procedures in the user interface layer, as the SqlDataSource control forces you to do, is just plain wrong.

So why should you care? Well, in many cases you shouldn't. If you are creating a simple Web application, there is no reason not to use the SqlDataSource control. For example, if you need to build an application that consists of a single page that displays the contents of a database table, then performing the work of dividing the application into multiple application layers would be silly.

Unfortunately (or fortunately if you get paid for this), not all Web applications are simple. After an application reaches a certain level of complexity, it becomes easier to build and maintain the application by dividing it into more than one application layer.

Dividing an application into multiple application layers has many advantages. If you have a distinct business logic layer, then you can create a library of methods you can call from multiple pages. In other words, creating a distinct business logic layer promotes code reuse. Also, creating distinct and independent application layers makes an application easier to modify. For example, distinct layers enable you to modify the user interface without modifying the data access code.

If you do need to build a proper multi-tier Web application with the ASP.NET Framework, then you can take advantage of another new control introduced with the ASP.NET 2.0 Framework: the ObjectDataSource control. The ObjectDataSource control enables you to bind user interface controls, such as the GridView and DropDownList controls, to a middle-tier component.

The ObjectDataSource control is the topic of this article. In this article, you learn how to use this control to display and edit database data. We also examine how you can use the ObjectDataSource control with the SqlDataSource control to simplify database access.

Displaying Data with the ObjectDataSource Control

Imagine that you need to create a Web page that displays the contents of the Products database table. Imagine, furthermore, that you have an existing business component that contains a method for retrieving this data.

For example, the component in Listing 1 contains a method named GetProducts that returns a DataReader that represents the contents of the Products database table.

Listing 1. ProductInfo.cs (C#)

using System;
using System.Data;
using System.Data.SqlClient;

public class ProductInfo
{
    const string conString = 
      "Server=localhost;Trusted_Connection=true;Database=Northwind";

    public static SqlDataReader GetProducts()
    {
        SqlConnection con = new SqlConnection(conString);
        string selectString = "SELECT * FROM Products";
        SqlCommand cmd = new SqlCommand(selectString, con);
        con.Open();
        SqlDataReader dtr = 
          cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return dtr;
    }
}

Listing 1. ProductInfo.vb (Visual Basic .NET)

Imports System.Data
Imports System.Data.SqlClient

Public Class ProductInfo
    Const conString As String = _
      "Server=localhost;Trusted_Connection=true;Database=Northwind"

    Public Function GetProducts() As SqlDataReader
        Dim con As New SqlConnection(conString)
        Dim selectString As String = "SELECT * FROM Products"
        Dim cmd As New SqlCommand(selectString, con)
        con.Open()
        Dim dtr As SqlDataReader =  _
          cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Return dtr
    End Function

End Class

If you add the class contained in Listing 1 to the App_Code directory of your application, then the class will be automatically compiled by the ASP.NET Framework. In other words, as long as you add the class to the App_Code directory, you can immediately start using the class in your ASP.NET pages.

We'll use a GridView control (The replacement for the DataGrid control in the ASP.NET 2.0 Framework) to display the database records returned by the GetProducts method. The ASP.NET page in Listing 2 contains a GridView bound to an ObjectDataSource control.

Listing 2. ShowProducts.aspx

<html>
<head>
    <title>Show Products</title>
</head>
<body>
    <form id="form1" runat="server">

    <asp:GridView
        ID="GridView1"
        DataSourceID="ObjectDataSource1"
        Runat="Server" />
        
     <asp:ObjectDataSource
        ID="ObjectDataSource1"
        TypeName="ProductInfo"
        SelectMethod="GetProducts"
        Runat="Server" />

    </form>
</body>
</html>

The ObjectDataSource control declared in Listing 2 contains two important properties. The TypeName property indicates the name of a class and the SelectMethod property indicates the name of a method to call on the class when selecting data.

In Listing 2, the ObjectDataSource control is used to call the GetProducts method on the ProductInfo class. Since the GridView control is bound to the ObjectDataSource control, through the GridView control's DataSourceID property, the GridView control displays the list of products (see Figure 1).

ms379547.asp2objectdatasource_fig01(en-US,VS.80).gif

Figure 1. Displaying Products with the ObjectDataSource control

You can use the ObjectDataSource control with any of the standard ASP.NET data-bound controls such as the GridView, DropDownList, TreeView, and Repeater controls. The ObjectDataSource control enables you to bind any of these standard controls to a component.

The SelectMethod property can refer to either a static (shared in Visual Basic .NET) or instance method. If you use an instance method, the ObjectDataSource control automatically creates an instance of the component before calling the method. The component is automatically destroyed after the method is called.

Using Parameters with the ObjectDataSource Control

You can use parameters with the methods that you call with the ObjectDataSource control. This is useful when you need to pass values such as the value of a control property or query string to a method when you call it.

In the previous section, we used the ObjectDataSource control to create a page that displays all of the records from the Products database table. In this section, we'll modify the page so that it allows a user to select a category of products to display from a DropDownList control (see Figure 2).

ms379547.asp2objectdatasource_fig02(en-US,VS.80).gif

Figure 2. Selecting product categories from a DropDownList

The modified ProductInfo component is contained in Listing 3.

Listing 3. ProductInfo2.cs (C#)

using System;
using System.Data;
using System.Data.SqlClient;

public class ProductInfo2
{
    const string conString = 
      "Server=localhost;Trusted_Connection=true;Database=Northwind";

    public SqlDataReader GetProducts(string category)
    {
        SqlConnection con = new SqlConnection(conString);
        string selectString = "SELECT Products.* " +
          "FROM Products INNER JOIN Categories " +
          "ON Products.CategoryID=Categories.CategoryId " +
          "WHERE CategoryName=@CategoryName";
        SqlCommand cmd = new SqlCommand(selectString, con);
        cmd.Parameters.AddWithValue("@CategoryName", category);
        con.Open();
        SqlDataReader dtr = 
          cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return dtr;
    }
}

Listing 3. ProductInfo2.vb (Visual Basic .NET)

Imports System.Data
Imports System.Data.SqlClient

Public Class ProductInfo2

    Const conString As String = _
      "Server=localhost;Trusted_Connection=true;Database=Northwind"

    Public Function GetProducts(ByVal category As String) _
      As SqlDataReader
        Dim con As New SqlConnection(conString)
        Dim selectString As String = "SELECT Products.* " & 
          "FROM Products INNER JOIN Categories " & _
          "ON Products.CategoryID=Categories.CategoryId " & _
          "WHERE CategoryName=@CategoryName"
        Dim cmd As New SqlCommand(selectString, con)
        cmd.Parameters.AddWithValue("@CategoryName", category)
        con.Open()
        Dim dtr As SqlDataReader = _
          cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Return dtr
    End Function

End Class

The modified ProductInfo component in Listing 3 contains a modified GetProducts method that includes a parameter for the category name. This parameter is used to restrict the products returned from the database.

The page in Listing 4 contains a DropDownList, GridView, and ObjectDataSource control enabling you to pick different categories of products to display.

Listing 4. ShowProducts2.aspx

<html>
<head>
    <title>Show Products</title>
</head>
<body>
    <form id="form1" runat="server">

    <asp:DropDownList
        id="DropCategories"
        AutoPostBack="true"
        Runat="Server">
        <asp:ListItem Value="Beverages" />    
        <asp:ListItem Value="Seafood" />    
    </asp:DropDownList>
    
    <br /><br />
    
    <asp:GridView
        ID="GridView1"
        DataSourceID="ObjectDataSource1"
        Runat="Server" />
      
     <asp:ObjectDataSource
        ID="ObjectDataSource1"
        TypeName="ProductInfo2"
        SelectMethod="GetProducts"         
        Runat="Server">
        <SelectParameters>
            <asp:ControlParameter 
                Name="category"
                ControlID="DropCategories" />
        </SelectParameters>
     </asp:ObjectDataSource>

    </form>
</body>
</html>

When you select a new category from the DropDownList control in Listing 4, the GridView control automatically displays only the products from the selected category.

Notice that the ObjectDataSource control in Listing 4 contains a SelectParameters element. This element lists all of the parameters used when calling the method indicated by the ObjectDataSource control's SelectMethod property. In this case, the SelectedParameters element contains a single parameter named category. This parameter represents the value from the SelectedValue property of the DropCategories DropDownList control. If you want to be explicit about the control property that you want to represent with a ControlParameter, then you can assign the name of the property to the ControlParameter’s PropertyName property.

The ControlParameter is only one example of a parameter that you can use with the ObjectDataSource control. The ObjectDataSource, like the other Datasource controls, supports any of the following types of parameters:

·ControlParameter – Represents the value of a property of a control.

·CookieParameter – Represents the value of a browser cookie.

·FormParameter – Represents the value of a form field.

·ProfileParameter – Represents the value of a profile property.

·SessionParameter – Represents the value of an item stored in Session state.

·QueryStringParameter – Represents the value of a query string field.

Editing Data with the ObjectDataSource Control

The ObjectDataSource control contains four important properties: the SelectMethod property, the UpdateMethod property, the InsertMethod property, and the DeleteMethod property. Collectively, these properties enable you to indicate all the methods that you need in order to perform standard database operations.

For example, you can use the ObjectDataSource control to edit database data. The modified ProductInfo class in Listing 5 includes a new UpdateProduct and DeleteProduct method.

Listing 5. ProductInfo3.cs (C#)

using System;
using System.Data;
using System.Data.SqlClient;

public class ProductInfo3
{
    const string conString = 
      "Server=localhost;Trusted_Connection=true;Database=Northwind";

    public static SqlDataReader GetProducts()
    {
        SqlConnection con = new SqlConnection(conString);
        string selectString = "SELECT ProductId,ProductName, " +
          "UnitPrice FROM Products ORDER BY ProductId";
        SqlCommand cmd = new SqlCommand(selectString, con);
        con.Open();
        SqlDataReader dtr = 
          cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return dtr;
    }

    public static void UpdateProduct(int original_productId, 
      string productName, decimal unitPrice)
    {
        SqlConnection con = new SqlConnection(conString);
        string updateString = "UPDATE Products SET " + 
          "ProductName=@ProductName,UnitPrice=@UnitPrice " +
          "WHERE ProductID=@ProductID";
        SqlCommand cmd = new SqlCommand(updateString, con);
        cmd.Parameters.AddWithValue("@ProductName", productName);
        cmd.Parameters.AddWithValue("@UnitPrice", unitPrice);
        cmd.Parameters.AddWithValue("@ProductId", original_productId);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static void DeleteProduct(int original_productId)
    {
        SqlConnection con = new SqlConnection(conString);
        string deleteString = "DELETE Products " +
          "WHERE ProductID=@ProductID";
        SqlCommand cmd = new SqlCommand(deleteString, con);
        cmd.Parameters.AddWithValue("@ProductId", original_productId);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

Listing 5. ProductInfo3.vb (Visual Basic .NET)

Imports System.Data
Imports System.Data.SqlClient

Public Class ProductInfo3

    Const conString As String = _
      "Server=localhost;Trusted_Connection=true;Database=Northwind"

    Public Shared Function GetProducts() As SqlDataReader
        Dim con As New SqlConnection(conString)
        Dim selectString As String = "SELECT ProductId, " & _
          "ProductName,UnitPrice FROM Products ORDER BY ProductId"
        Dim cmd As New SqlCommand(selectString, con)
        con.Open()
        Dim dtr As SqlDataReader = _
          cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Return dtr
    End Function

    Public Shared Sub UpdateProduct(ByVal original_productId _
      As Integer, ByVal productName As String, _
      ByVal unitPrice As Decimal)
        Dim con As New SqlConnection(conString)
        Dim updateString As String = "UPDATE Products " & _
          "SET ProductName=@ProductName,UnitPrice=@UnitPrice " & _
          "WHERE ProductID=@ProductID"
        Dim cmd As New SqlCommand(updateString, con)
        cmd.Parameters.AddWithValue("@ProductName", productName)
        cmd.Parameters.AddWithValue("@UnitPrice", unitPrice)
        cmd.Parameters.AddWithValue("@ProductId", original_productId)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub

    Public Shared Sub DeleteProduct(ByVal original_productId _
      As Integer)
        Dim con As New SqlConnection(conString)
        Dim deleteString As String = "DELETE Products " & _
          "WHERE ProductID=@ProductID"
        Dim cmd As New SqlCommand(deleteString, con)
        cmd.Parameters.AddWithValue("@ProductId", original_productId)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub

End Class

You can use this modified ProductInfo class with the ObjectDataSource control contained in Listing 6 to edit the contents of the Products database table.

Listing 6. ShowProducts3.aspx

<html>
<head>
    <title>Show Products</title>
</head>
<body>
    <form id="form1" runat="server">

    <asp:GridView
        ID="GridView1"
        DataSourceID="ObjectDataSource1"
        DataKeyNames="ProductId"
        AutoGenerateColumns="false"
        AutoGenerateEditButton="true"
        AutoGenerateDeleteButton="true"
        Runat="Server">
        <Columns>
            <asp:BoundField 
                DataField="ProductName"/>
            <asp:BoundField
                DataField="UnitPrice" 
                DataFormatString="{0:c}"/>
        </Columns>
     </asp:GridView>   
        
     <asp:ObjectDataSource
        ID="ObjectDataSource1"
        TypeName="ProductInfo3"
        SelectMethod="GetProducts"
        UpdateMethod="UpdateProduct"        
        DeleteMethod="DeleteProduct" 
        Runat="Server" />

    </form>
</body>
</html>

In Listing 6, the GridView control displays the values of two columns: the ProductName and UnitPrice columns. Since the GridView control has both its AutoGenerateEditButton and AutoGenerateDeleteButton properties set to the value True, the GridView automatically generates the user interface for editing and deleting product rows (see Figure 3).

ms379547.asp2objectdatasource_fig03(en-US,VS.80).gif

Figure 3. Editing data with the ObjectDataSource control

When you click the Update link to update a product, the ObjectDataSource control calls the UpdateProduct method. The GridView control automatically creates three parameters when calling the UpdateProduct method: original_productID, productName, and unitPrice. The productName and unitPrice parameters correspond to the two BoundFields displayed by the GridView. One parameter requires additional discussion. We need to pass the value of the ProductID column of the row being updated to the UpdateProduct method. Since we are not displaying the ProductID column in the GridView, we must assign the ProductID column to the GridView control's DataKeyNames property. The name of this column becomes original_productId instead of productId since we are passing the unedited version of the ProductID column to the update method.

When you click the Delete link, the ObjectDataSource control calls the DeleteProduct method. Once again, since the GridView control's DataKeyNames property has the value ProductId, a parameter named original_productId is automatically passed to the DeleteProduct method.

Using the ObjectDataSource Control with the SqlDataSource Control

To this point, we've used the ObjectDataSource control with components that use the SqlDataReader object to retrieve database data. There is another option here. Instead of using ADO.NET objects—such as the SqlDataReader or DataSet—within a component, you can use the SqlDataSource control.

The fact that you can use the SqlDataSource control within a component might seem strange. Normally, you don't use controls within a component since, normally, a control has a visual representation and the control participates in the page execution lifecycle. However, the DataSource controls are special in this regard.

If you want to simplify your database access code within a component, you can use the SqlDataSource control within the component. This approach is illustrated in Listing 7.

Listing 7. ProductInfo4.cs (C#)

using System;
using System.Collections;
using System.Web.UI;
using System.Web.UI.WebControls;

public class ProductInfo4
{
    const string conString = 
      "Server=localhost;Trusted_Connection=true;Database=Northwind";

    public static IEnumerable GetProducts()
    {
        string selectString = "SELECT * FROM Products";
        SqlDataSource dsrc = new SqlDataSource(conString, 
          selectString);
        return dsrc.Select(DataSourceSelectArguments.Empty);
    }
}

Listing 7. ProductInfo4.vb (Visual Basic .NET)

Imports System.Collections
Imports System.Web.UI
Imports System.Web.UI.WebControls

Public Class ProductInfo4

    Const conString As String = _
      "Server=localhost;Trusted_Connection=true;Database=Northwind"

    Public Shared Function GetProducts() As IEnumerable
        Dim selectString As String = "SELECT * FROM Products"
        Dim dsrc As New SqlDataSource(conString, selectString)
        Return dsrc.Select(DataSourceSelectArguments.Empty)
    End Function

End Class

In Listing 7, a new instance of the SqlDataSource control is instantiated. The constructor for the SqlDataSource accepts a parameter for a database connection string and a parameter for the command text used with the select command. Next, the Select method is called on the instance of the SqlDataSource control and a DataView is returned that represents all the records from the Products database table.

You can use the ProductInfo class in Listing 7 with the ObjectDataSource control contained in Listing 8:

Listing 8. ShowProducts4.aspx

<html>
<head>
    <title>Show Products</title>
</head>
<body>
    <form id="form1" runat="server">

    <asp:GridView
        ID="GridView1"
        DataSourceID="ObjectDataSource1"
        Runat="Server" />
        
     <asp:ObjectDataSource
        ID="ObjectDataSource1"
        TypeName="ProductInfo4"
        SelectMethod="GetProducts"         
        Runat="Server" />

    </form>
</body>
</html>

In Listing 8, the GridView control is bound to the ObjectDataSource control. The ObjectDataSource control, in turn, calls out to the GetProducts method of the ProductInfo4 class to retrieve the data displayed by the GridView. Finally, the GetProducts method uses the SqlDataSource control to retrieve the database data.

Conclusion

The ASP.NET 2.0 Framework dramatically simplifies database access making it easier to build both simple and complex ASP.NET applications. If you need to create a simple database driven Web application, then you can take advantage of the new SqlDataSource control. If you need to build a more complicated application—for example, a traditional three tier application—then you can use the new ObjectDataSource control.

The ObjectDataSource control enables you to continue to use middle-tier components in your data-driven pages. It has the major advantage that it dramatically simplifies your user interface layer by enabling you to bind to a component without writing any code. Using the ObjectDataSource control, I can build a component and page that displays the Products database table in 3 minutes and 20 seconds. While this takes more time than the SqlDataSource control, I feel much better about the architecture of the page.

 

About the author

Stephen Walther wrote the best-selling book on ASP.NET, ASP.NET Unleashed. He was also the architect and lead developer of the ASP.NET Community Starter Kit, a sample ASP.NET application produced by Microsoft. He has provided ASP.NET training to companies across the United States, including NASA and Microsoft, through his company Superexpert (https://www.superexpert.com).

© Microsoft Corporation. All rights reserved.