Chapter 16. Using the ObjectDataSource Control

<feature>

IN THIS CHAPTER

</feature>

The ObjectDataSource control enables you to bind DataBound controls such as the GridView, DetailsView, and FormView controls to a component. You can use the ObjectDataSource control to easily build multi-tier applications with the ASP.NET Framework. Unlike the SqlDataSource control, which mixes data access logic in the User Interface Layer, the ObjectDataSource control enables you to cleanly separate your User Interface Layer from your Business Logic and Data Access Layers.

In this chapter, you learn how to use the ObjectDataSource control to represent different types of objects. For example, you learn how to use the ObjectDataSource control with components that represent database data. You also learn how to use the ObjectDataSource control to represent different types of method parameters.

In the course of this chapter, we tackle a number of advanced topics. For example, you learn how to page, sort, and filter database records represented by the ObjectDataSource control. You learn how to page and sort through large database tables efficiently.

In the final section of this chapter, you learn how to extend the ObjectDataSource control to represent specialized data sources. You also learn how to extend the ObjectDataSource control with custom parameters.

Representing Objects with the ObjectDataSource Control

The ObjectDataSource control includes five main properties:

  • TypeName—. The name of the type of object that the ObjectDataSource control represents.

  • SelectMethod—. The name of a method that the ObjectDataSource calls when selecting data.

  • UpdateMethod—. The name of a method that the ObjectDataSource calls when updating data.

  • InsertMethod—. The name of a method that the ObjectDataSource calls when inserting data.

  • DeleteMethod—. The name of a method that the ObjectDataSource calls when deleting data.

An ObjectDataSource control can represent any type of object in the .NET Framework. This section discusses several types of objects you might want to represent. For example, you learn how to use the ObjectDataSource control with components that represent collections, ADO.NET DataReaders, DataSets, LINQ to SQL queries, and web services.

Note

You can use the ObjectDataSource control to represent any object (any class that derives from the System.Object class). If the object does not support the IEnumerable interface, the ObjectDataSource control automatically wraps the object in a new object that supports the IEnumerable interface. You can even represent an ASP.NET ListBox control with an ObjectDataSource (not that a ListBox has any interesting methods).

Binding to a Component

Let’s start with a really simple component. The component in Listing 16.1 is named MovieCollection. It contains one method named GetMovies(), which returns a collection of movie titles.

Example 16.1. MovieCollection.cs

using System;
using System.Web.Configuration;
using System.Collections.Generic;
public class MovieCollection
{
    public List<string> GetMovies()
    {
        List<string> movies = new List<string>();
        movies.Add("Star Wars");
        movies.Add("Independence Day");
        movies.Add("War of the Worlds");
        return movies;
    }

}

You can use the page in Listing 16.2 to display the list of movies returned by the GetMovies() method in a GridView control. The page contains an ObjectDataSource control that represents the MovieCollection component.

Example 16.2. ShowMovieCollection.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Movie Collection</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="MovieCollection"
        SelectMethod="GetMovies"
        Runat="server" />

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

In Listing 16.2, the ObjectDataSource control includes two properties named TypeName and SelectMethod. The TypeName property contains the name of the component that you want to represent with the ObjectDataSource control. The SelectMethod property represents the method of the component that you want to call when selecting data.

Notice that the GridView control is bound to the ObjectDataSource control through its DataSourceID property. When you open the page in Listing 16.2, the list of movies is retrieved from the MovieCollection component and displayed in the GridView.

The MovieCollection component contains instance methods. The ObjectDataSource automatically creates a new instance of the MovieCollection component before calling its GetMovies() method. It automatically destroys the object after it is finished using the object.

You also can use the ObjectDataSource control to call shared (static) methods. In that case, the ObjectDataSource doesn’t need to instantiate a component before calling the method.

Binding to a DataReader

Typically, you use the ObjectDataSource control to represent database data. The .NET Framework provides you with multiple ways of representing data. This section discusses how you can use an ObjectDataSource to represent a DataReader.

Note

The different ADO.NET objects are compared and contrasted in the next chapter, “Building Data Access Components with ADO.NET.”

The ADO.NET DataReader object provides you with a fast, read-only representation of database data. If you need to retrieve database records in the fastest possible way, then you should use a DataReader object.

For example, the component in Listing 16.3, the MovieDataReader component, returns all the movies from the Movies database table by using the SqlDataReader object. Notice that the component imports the System.Data.SqlClient namespace to use this Microsoft SQL Server–specific ADO.NET object.

Example 16.3. MovieDataReader.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class MovieDataReader
{
    private readonly string _conString;

    public SqlDataReader GetMovies()
    {
        // Create Connection
        SqlConnection con = new SqlConnection(_conString);

        // Create Command
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SELECT Id,Title,Director FROM Movies";

        // Return DataReader
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public MovieDataReader()
    {
        _conString = WebConfigurationManager.ConnectionStrings["Movies"]. ConnectionString;
    }
}

The component in Listing 16.3 actually uses three ADO.NET objects: the Connection, Command, and DataReader object. The SqlCommand object uses the SqlConnection object to connect to the database. The records are returned from the SqlCommand object and represented by the SqlDataReader object.

Notice that the WebConfigurationManager class is used to retrieve the database connection string from the web configuration file. To use this class, you need to import the System.Web.Confiugration namespace (and have a reference to the System.Web.dll assembly).

The ObjectDataSource control in Listing 16.4 represents the MovieDataReader object. It binds the movies to a GridView control.

Example 16.4. ShowMovieDataReader.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Movie DataReader</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="MovieDataReader"
        SelectMethod="GetMovies"
        Runat="server" />

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

Binding to a DataSet

You also can use the ObjectDataSource when you need to represent an ADO.NET DataSet. Using a DataSet is slower than using a DataReader. However, you can perform advanced operations, such as filtering and sorting, on data represented with a DataSet.

The component in Listing 16.5 returns all the records from the Movies database table. However, it uses a DataSet instead of a DataReader object.

Example 16.5. MovieDataSet.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class MovieDataSet
{
    private readonly string _conString;

    public DataSet GetMovies()
    {
        // Create DataAdapter
        string commandText = "SELECT Id,Title,Director FROM Movies";
        SqlDataAdapter dad = new SqlDataAdapter(commandText, _conString);

        // Return DataSet
        DataSet dstMovies = new DataSet();
        using (dad)
        {
            dad.Fill(dstMovies);
        }
        return dstMovies;
    }


    public MovieDataSet()
    {
        _conString = WebConfigurationManager.ConnectionStrings["Movies"]. ConnectionString;
    }
}

The component in Listing 16.5 uses two ADO.NET objects: a DataAdapter and a DataSet. The SqlDataAdapter is used to represent the SQL select command, and it populates the DataSet with the results of executing the command. Notice that the WebConfigurationManager class is used to read the database connection string from the web configuration file.

The page in Listing 16.6 binds the list of movies to a DropDownList control.

Example 16.6. ShowMovieDataSet.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Movie DataSet</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        Runat="server" />
    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="MovieDataReader"
        SelectMethod="GetMovies"
        Runat="server" />

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

Binding to a LINQ to SQL Query

LINQ to SQL is the preferred method of data access in the .NET Framework 3.5. The expectation is that you will use LINQ to SQL instead of ADO.NET to interact with a database. Chapter 18, “Data Access with LINQ to SQL,” is devoted to the topic of LINQ.

Let’s do a quick sample of binding an ObjectDataSource to a component that represents a LINQ to SQL query. The component that contains the LINQ query is contained in Listing 16.7.

Example 16.7. Employee.cs

using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;

public partial class Employee
{
    public static IEnumerable<Employee> Select()
    {
        EmployeesDataContext db = new EmployeesDataContext();
        return db.Employees.OrderBy( e=>e.LastName );
    }
}

Before you can use the component in Listing 16.7, you first must create the EmployeesDataContext. The easiest way to create the DataContext is to select the menu option Website, Add New Item and select the LINQ to SQL Classes template. Name the LINQ to SQL Classes Employees.

After the Object Relational Designer appears, drag the Employees database table onto the Designer surface from the Database Explorer window. At this point, the EmployeesDataContext will be ready.

The page in Listing 16.8 contains an ObjectDataSource that represents the Employee class.

Example 16.8. ShowLINQ.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Show LINQ</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdEmployees"
        DataSourceID="srcEmployees"
        runat="server" />

    <asp:ObjectDataSource
        id="srcEmployees"
        TypeName="Employee"
        SelectMethod="Select"
        Runat="server" />

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

Binding to a Web Service

Web services enable you to share information across the Internet. When you communicate with a remote web service, you use a local proxy class to represent the web service located on the remote machine. You can use the ObjectDataSource to represent this proxy class.

For example, the file in Listing 16.9 contains a simple web service that returns the current server time. You can create this file in Visual Web Developer by selecting the menu option Web Site, Add New Item, and selecting the Web Service item.

Example 16.9. TimeService.asmx

<%@ WebService Language="C#" Class="TimeService" %>
using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class TimeService  : System.Web.Services.WebService {

    [WebMethod]
    public DateTime GetServerTime() {
        return DateTime.Now;
    }
}

After you create the web service in Listing 16.9, you can communicate with the service from anywhere in the world (or the galaxy, or the universe). Just as long as a computer is connected to the Internet, the computer can call the GetServerTime() method.

Before you can call the web service, you need to create a web service proxy class. If you are using Visual Web Developer, select the menu option Web Site, Add Web Reference and enter the URL of the TimeService.asmx file (You can click the Web services in this solution link to list all the web services in your current project.) Change the name of the web reference to LocalServices and click Add Reference (see Figure 16.1).

Adding a Web Reference in Visual Web Developer.

Figure 16.1. Adding a Web Reference in Visual Web Developer.

Note

If you are not using Visual Web Developer, you can create a web service proxy class from the command line by using the Wsdl.exe (Web Services Description Language) tool.

When you click Add Reference, a new folder is added to your project named App_WebReferences. The App_WebReferences folder contains a subfolder named LocalServices. Finally, your web configuration file is updated to include the URL to the TimeService web service.

Now that we have a consumable web service, we can represent the Web service using the ObjectDataSource control. The page in Listing 16.10 displays the server time using a FormView control bound to an ObjectDataSource control (see Figure 16.2).

Retrieving the time from a web service.

Figure 16.2. Retrieving the time from a web service.

Example 16.10. ShowWebService.aspx

<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        html
        {
            background-color:silver;
        }
        .serverTime
        {
            background-color:white;
            font:16px Georgia,Serif;
        }
        .serverTime td
        {
            padding:40px;
        }
    </style>
    <title>Show Web Service</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:FormView
        id="frmServerTime"
        DataSourceID="srcServerTime"
        CssClass="serverTime"
        Runat="server">
        <ItemTemplate>
        The remote server date and time is: <%# Container.DataItem %>
        </ItemTemplate>
    </asp:FormView>

    <asp:ObjectDataSource
        id="srcServerTime"
        TypeName="LocalServices.TimeService"
        SelectMethod="GetServerTime"
        Runat="server" />

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

Notice that the ObjectDataSource control’s TypeName property contains both the namespace and name of the web service proxy class (the web reference). In other words, it contains the fully qualified name of the proxy class. The SelectMethod property contains the name of the web method represented by the proxy class.

Note

If you open the ShowWebService.aspx page from the CD that accompanies this book, you receive an error. Before the page will work correctly, you need to update the web configuration file with the correct path to the web service on your computer.

Using Parameters with the ObjectDataSource Control

You can use parameters when calling a method with the ObjectDataSource control. The ObjectDataSource control includes five parameter collections:

  • SelectParameters—. Collection of parameters passed to the method represented by the SelectMethod property.

  • InsertParameters—. Collection of parameters passed to the method represented by the InsertMethod property.

  • UpdateParameters—. Collection of parameters passed to the method represented by the UpdateMethod property.

  • DeleteParameters—. Collection of parameters passed to the method represented by the DeleteParameters property.

  • FilterParameters—. Collection of parameters used by the FilterExpression property.

DataBound controls—such as the GridView, DetailsView, and FormView controls—can build the necessary parameter collections for you automatically.

For example, the component in Listing 16.11 enables you select movies and update a particular movie in the Movies database table. The UpdateMovie() method has four parameters: id, title, director, and dateReleased.

Example 16.11. Movies.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class Movies
{
    private readonly string _conString;

    public void UpdateMovie(int id, string title, string director, DateTime dateReleased)
    {
        // Create Command
        SqlConnection con = new SqlConnection(_conString);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "UPDATE Movies SET Title=@Title,Director=@Director,DateReleased=
        // Add parameters
        cmd.Parameters.AddWithValue("@Title", title);
        cmd.Parameters.AddWithValue("@Director", director);
        cmd.Parameters.AddWithValue("@DateReleased", dateReleased);
        cmd.Parameters.AddWithValue("@Id", id);

        // Execute command
        using (con)
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }

    public SqlDataReader GetMovies()
    {
        // Create Connection
        SqlConnection con = new SqlConnection(_conString);

        // Create Command
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SELECT Id,Title,Director,DateReleased FROM Movies";

        // Return DataReader
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public Movies()
    {
            _conString = WebConfigurationManager.ConnectionStrings["Movies"]. ConnectionString;
    }
}

The page in Listing 16.12 contains a GridView and ObjectDataSource control. Notice that the ObjectDataSource control includes an UpdateMethod property that points to the UpdateMovie() method.

Example 16.12. ShowMovies.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Movies</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        DataKeyNames="Id"
        AutoGenerateEditButton="true"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="Movies"
        SelectMethod="GetMovies"
        UpdateMethod="UpdateMovie"
        Runat="server"/>

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

In Listing 16.12, the GridView automatically adds the update parameters to the ObjectDataSource control’s UpdateParameters collection. As an alternative, you can declare the parameters used by the ObjectDataSource control explicitly. For example, the page in Listing 16.13 declares all the parameters passed to the UpdateMovie() method.

Example 16.13. ExplicitShowMovies.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Movies</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        DataKeyNames="Id"
        AutoGenerateEditButton="true"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="Movies"
        SelectMethod="GetMovies"
        UpdateMethod="UpdateMovie"
        Runat="server">
        <UpdateParameters>
        <asp:Parameter Name="title" />
        <asp:Parameter Name="director" />
        <asp:Parameter Name="dateReleased" Type="DateTime" />
        <asp:Parameter Name="id" Type="Int32" />
        </UpdateParameters>
    </asp:ObjectDataSource>

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

The ObjectDataSource uses reflection to match its parameters against the parameters of the method that it calls. The order of the parameters does not matter and the case of the parameters does not matter. However, the one thing that does matter is the names of the parameters.

You specify the type of a parameter with the Type property, which represents a member of the TypeCode enumeration. The TypeCode enumeration represents an enumeration of common .NET Framework data types such as Int32, Decimal, and DateTime. If the enumeration does not include a data type that you need, then you can use the TypeCode.Object member from the enumeration.

Using Different Parameter Types

You can use all the same types of parameters with the ObjectDataSource control that you can use with the SqlDataSource control:

  • Parameter—. Represents an arbitrary static value.

  • ControlParameter—. Represents the value of a control or page property.

  • CookieParameter—. Represents the value of a browser cookie.

  • FormParameter—. Represents the value of an HTML form field.

  • ProfileParameter—. Represents the value of a Profile property.

  • QueryStringParameter—. Represents the value of a query string field.

  • SessionParameter—. Represents the value of an item stored in Session state.

For example, the page in Listing 16.14 contains a DropDownList control and a GridView control, which enables you to view movies that match a selected category (see Figure 16.3).

Displaying movies by category.

Figure 16.3. Displaying movies by category.

Example 16.14. ShowMoviesByCategory.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
    .movies
    {
        border:Solid 1px black;
    }
    .movies td,.movies th
    {
        padding:5px;
    }
    </style>
    <title>Show Movies by Category</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:DropDownList
        id="ddlMovieCategory"
        DataSourceID="srcMovieCategories"
        DataTextField="Name"
        DataValueField="Id"
        ToolTip="Movie Category"
        Runat="server" />
     <asp:Button
        id="btnSelect"
        Text="Select"
        Runat="server" />

     <asp:GridView
         id="grdMovies"
         DataSourceID="srcMovies"
         CssClass="movies"
         GridLines="None"
         Runat="server" />

     <asp:ObjectDataSource
         id="srcMovieCategories"
         TypeName="MovieCategories"
         SelectMethod="GetCategories"
         Runat="server" />

     <asp:ObjectDataSource
         id="srcMovies"
         TypeName="MovieCategories"
         SelectMethod="GetMovies"
         Runat="server">
         <SelectParameters>
         <asp:ControlParameter
             Name="CategoryId"
             ControlID="ddlMovieCategory" />
         </SelectParameters>
     </asp:ObjectDataSource>

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

The ObjectDataSource control in Listing 16.14 is bound to the component contained in Listing 16.15. Notice that the ObjectDataSource control includes a SelectParameters collection. The SelectParameters collection contains a ControlParameter, which represents the current value of the ddlMovieCategory DropDownList control.

Example 16.15. MovieCategories.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class MovieCategories
{
    private readonly string _conString;

    public SqlDataReader GetMovies(int categoryId)
    {
        // Create Connection
        SqlConnection con = new SqlConnection(_conString);

        // Create Command
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SELECT Id,Title,Director,DateReleased "
            + " FROM Movies WHERE CategoryId=@CategoryId";

        // Add parameters
        cmd.Parameters.AddWithValue("@CategoryId", categoryId);

        // Return DataReader
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public SqlDataReader GetCategories()
    {
        // Create Connection
        SqlConnection con = new SqlConnection(_conString);

        // Create Command
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SELECT Id,Name FROM MovieCategories";

        // Return DataReader
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public MovieCategories()
    {
        _conString = WebConfigurationManager.ConnectionStrings["Movies"]. ConnectionString;
    }
}

Passing Objects as Parameters

Passing long lists of parameters to methods can make it difficult to maintain an application. If the list of parameters changes, you need to update every method that accepts the list of parameters.

Rather than pass a list of parameters to a method, you can pass a particular object. For example, you can pass a CompanyEmployee object to a method used to update an employee, rather than a list of parameters that represent employee properties.

If you specify a value for an ObjectDataSource control’s DataObjectTypeName property, then you can pass an object rather than a list of parameters to the methods that an ObjectDataSource represents. In that case, the ObjectDataSource parameters represent properties of the object.

For example, the EmployeeData component in Listing 16.16 contains an InsertEmployee() method for creating a new employee. This method is passed an instance of the CompanyEmployee object that represents a particular employee. The CompanyEmployee class also is included in Listing 16.16.

Example 16.16. EmployeeData.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web.Configuration;

public class EmployeeData
{
    string _connectionString;

    public void UpdateEmployee(CompanyEmployee employeeToUpdate)
    {
        // Initialize ADO.NET objects
        SqlConnection con = new SqlConnection(_connectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "UPDATE Employees SET FirstName=@FirstName," +
            "LastName=@LastName,Phone=@Phone WHERE Id=@Id";
        cmd.Connection = con;

        // Create parameters
        cmd.Parameters.AddWithValue("@Id", employeeToUpdate.Id);
        cmd.Parameters.AddWithValue("@FirstName", employeeToUpdate.FirstName);
        cmd.Parameters.AddWithValue("@LastName", employeeToUpdate.LastName);
        cmd.Parameters.AddWithValue("@Phone", employeeToUpdate.Phone);

        // Execute command
        using (con)
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }
    public List<CompanyEmployee> GetEmployees()
    {
        List<CompanyEmployee> employees = new List<CompanyEmployee>();

        SqlConnection con = new SqlConnection(_connectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "SELECT Id,FirstName,LastName,Phone FROM Employees";
        cmd.Connection = con;
        using (con)
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
               CompanyEmployee newEmployee = new CompanyEmployee();
               newEmployee.Id = (int)reader["Id"];
               newEmployee.FirstName = (string)reader["FirstName"];
               newEmployee.LastName = (string)reader["LastName"];
               newEmployee.Phone = (string)reader["Phone"];
               employees.Add(newEmployee);
            }
       }
       return employees;
    }

    public EmployeeData()
    {
       _connectionString = WebConfigurationManager.ConnectionStrings["Employees"]. ConnectionString;
    }
}

public class CompanyEmployee
{
    private int _id;
    private string _firstName;
    private string _lastName;
    private string _phone;

    public int Id
    {
        get { return _id; }
        set { _id = value; }
    }
    public string FirstName
    {
        get { return _firstName; }
        set { _firstName = value; }
    }

    public string LastName
    {
        get { return _lastName; }
        set { _lastName = value; }
    }

    public string Phone
    {
        get { return _phone; }
        set { _phone = value; }
    }
}

The page in Listing 16.17 contains a DetailsView control and an ObjectDataSource control. The DetailsView control enables you to update existing employees in the Employees database table.

Example 16.17. UpdateEmployees.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Update Employees</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:DetailsView ID="DetailsView1"
        DataSourceID="srcEmployees"
        DataKeyNames="Id"
        AutoGenerateRows="True"
        AutoGenerateEditButton="True"
        AllowPaging="true"
        Runat="server" />
    <asp:ObjectDataSource
        id="srcEmployees"
        TypeName="EmployeeData"
        DataObjectTypeName="CompanyEmployee"
        SelectMethod="GetEmployees"
        UpdateMethod="UpdateEmployee"
        Runat="server" />

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

Notice that the ObjectDataSource control includes a DataObjectTypeName property. This property contains the name of an object that is used with the UpdateEmployee() method. When the UpdateEmployee() method is called, an instance of the CompanyEmployee component is created and passed to the method.

Note

The DataObjectTypeName property has an effect on only the methods represented by the InsertMethod, UpdateMethod, and DeleteMethod properties. It does not have an effect on the method represented by the SelectMethod property.

There is one important limitation when using the DataObjectTypeName property. The object represented by this property must have a parameterless constructor. For example, you could not use the following CompanyEmployee class with the DataObjectTypeName property:

public class CompanyEmployee
{
    private string _firstName;

    public string FirstName
    {
        get
        {
            return _firstName;
        }
    }

    public void CompanyEmployee(string firstName)
    {
        _firstName = firstName;
    }
}

The problem with this class is that it initializes its FirstName property in its constructor. Its constructor requires a firstName parameter. Instead, you need to use a class that looks like this:

public class CompanyEmployee
{
    private string _firstName;

    public string FirstName
    {
        get
        {
            return _firstName;
        }
        set
        {
            _firstName = value;
        }
   }
}

This class has a parameterless constructor. The FirstName property is a read/write property.

If you really have the need, you can get around this limitation by handling the Inserting, Updating, or Deleting event. When you handle one of these events, you can pass any object that you need to a method. These events are discussed later in this chapter in the section entitled “Handling ObjectDataSource Events.”

Paging, Sorting, and Filtering Data with the ObjectDataSource Control

The ObjectDataSource control provides you with two options for paging and sorting database data. You can take advantage of either user interface or data source paging and sorting. The first option is easy to configure, and the second option has much better performance. In this section, you learn how to take advantage of both options.

You also learn how to take advantage of the ObjectDataSource control’s support for filtering. When you combine filtering with caching, you can improve the performance of your data-driven web pages dramatically.

User Interface Paging

Imagine that you want to use a GridView control to display the results of a database query in multiple pages. The easiest way to do this is to take advantage of user interface paging.

For example, the page in Listing 16.18 uses a GridView and ObjectDataSource control to display the records from the Movies database table in multiple pages (see Figure 16.4).

Displaying multiple pages with user interface paging.

Figure 16.4. Displaying multiple pages with user interface paging.

Example 16.18. ShowUIPaging.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        .movies td,.movies th
        {
            padding:5px;
        }
    </style>
    <title>Show User Interface Paging</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        AllowPaging="true"
        PageSize="3"
        CssClass="movies"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="MovieUIPaging"
        SelectMethod="GetMoviesDataSet"
        Runat="server" />

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

The GridView control in Listing 16.18 includes an AllowPaging property that is set to the value True. Setting this property enables user interface paging.

The ObjectDataSource control in Listing 16.18 represents the MovieUIPaging component in Listing 16.19. This component includes a GetMoviesDataSet() method that returns an ADO.NET DataSet object.

To take advantage of user interface paging, you must bind the GridView control to the right type of data source. The right type of data source includes a collection, a DataSet, a DataTable, and a DataView. The right type of data source does not include, for example, a DataReader.

Example 16.19. MovieUIPaging.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class MovieUIPaging
{
    private readonly string _conString;

    public DataSet GetMoviesDataSet()
    {
        // Create DataAdapter
        string commandText = "SELECT Id,Title,Director FROM Movies";
        SqlDataAdapter dad = new SqlDataAdapter(commandText, _conString);

        // Return DataSet
        DataSet dstMovies = new DataSet();
        using (dad)
        {
            dad.Fill(dstMovies);
        }
        return dstMovies;
    }

    public MovieUIPaging()
    {
        _conString = WebConfigurationManager.ConnectionStrings["Movies"]. ConnectionString;
    }
}

User interface paging is convenient because you can enable it by setting a single property. However, there is a significant drawback to this type of paging. When user interface paging is enabled, all the movie records must be loaded into server memory. If the Movies database table contains 3 billion records, and you are displaying 3 records a page, then all 3 billion records must be loaded to display the 3 records. This places an incredible burden on both the web server and database server. In the next section, you learn how to use data source paging, which enables you to work efficiently with large sets of records.

Data Source Paging

Data source paging enables you to write custom logic for retrieving pages of database records. You can perform the paging in the component, a stored procedure, or a LINQ to SQL query.

If you want the best performance, then you should write your paging logic in either a stored procedure or a LINQ query. We’ll examine both approaches in this section.

The page in Listing 16.20 contains an ObjectDataSource control with data source paging enabled.

Note

Chapter 18 is devoted to the topic of LINQ.

Example 16.20. ShowDSPaging.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        .movies td,.movies th
        {
            padding:5px;
        }
    </style>
    <title>Show Data Source Paging</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        AllowPaging="true"
        PageSize="3"
        CssClass="movies"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="MoviesDSPaging"
        SelectMethod="GetMovies"
        SelectCountMethod="GetMovieCount"
        EnablePaging="True"
        Runat="server" />

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

Notice that the ObjectDataSource control includes an EnablePaging property that has the value True. The ObjectDataSource also includes a SelectCountMethod property that represents the name of a method that retrieves a record count from the data source.

Notice, furthermore, that the GridView control includes both an AllowPaging and PageSize property. Even when using data source paging, you need to enable the AllowPaging property for the GridView so that the GridView can render its paging user interface.

When an ObjectDataSource control has its EnablePaging property set to the value True, the ObjectDataSource passes additional parameters when calling the method represented by its SelectMethod property. The two additional parameters are named StartRowIndex and MaximumRows.

Now that we have the page setup for data source paging, we need to create the component. Let’s start by using a LINQ to SQL query. This approach is the easiest and recommended way. The component in Listing 16.21 uses LINQ to SQL queries to implement both the GetMovies() and GetMovieCount() methods.

Example 16.21. MoviesLINQPaging.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Web;

public class MoviesDSPaging
{
    public static IEnumerable<Movie> GetMovies(int startRowIndex, int maximumRows)
    {
        MyDatabaseDataContext db = new MyDatabaseDataContext();
        return db.Movies.Skip(startRowIndex).Take(maximumRows);
    }

    public static int GetMovieCount()
    {
        HttpContext context = HttpContext.Current;
        if (context.Cache["MovieCount"] == null)
            context.Cache["MovieCount"] = GetMovieCountFromDB();
        return (int)context.Cache["MovieCount"];
    }

    private static int GetMovieCountFromDB()
    {

       MyDatabaseDataContext db = new MyDatabaseDataContext();
       return db.Movies.Count();
   }
}

Before you can use the component in Listing 16.21, you need to create a DataContext named MyDatabaseDataContext. You can create this DataContext by selecting the menu option Website, Add New Item, and adding a new LINQ to SQL Classes item to your website. Name the new LINQ to SQL Classes item MyDatabase.dbml. Next, after the Object Relational Designer opens, drag the Movies database table from the Database Explorer window onto the Designer surface.

Note

Unfortunately, when you drag the Movies database table onto the Object Relational Designer surface, the Designer creates a new entity named Movy. The Designer is attempting to singularize the word and it fails badly. You must rename the entity to Movie in the Properties window. (I hope this Visual Web Developer grammar bug will be fixed by the time you read this).

You are not required to use LINQ to SQL when you want to implement data source paging. As an alternative to LINQ to SQL, you can perform your paging logic within a SQL stored procedure. The component in Listing 16.22 contains ADO.NET code instead of LINQ to SQL queries.

Example 16.22. MoviesSQLPaging.cs

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class MoviesDSPaging
{
    private static readonly string _conString;

    public static SqlDataReader GetMovies(int startRowIndex, int maximumRows)
    {
        // Initialize connection
        SqlConnection con = new SqlConnection(_conString);

        // Initialize command
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "GetPagedMovies";
        cmd.CommandType = CommandType.StoredProcedure;

        // Add ADO.NET parameters
        cmd.Parameters.AddWithValue("@StartRowIndex", startRowIndex);
        cmd.Parameters.AddWithValue("@MaximumRows", maximumRows);

        // Execute command
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public static int GetMovieCount()
    {
        HttpContext context = HttpContext.Current;
        if (context.Cache["MovieCount"] == null)
            context.Cache["MovieCount"] = GetMovieCountFromDB();
        return (int)context.Cache["MovieCount"];
    }

    private static int GetMovieCountFromDB()
    {
        int result = 0;

        // Initialize connection
        SqlConnection con = new SqlConnection(_conString);

        // Initialize command
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SELECT Count(*) FROM Movies";

        // Execute command
        using (con)
        {
            con.Open();
            result = (int)cmd.ExecuteScalar();
        }
        return result;
    }
    static MoviesDSPaging()
    {
       _conString = WebConfigurationManager.ConnectionStrings["Movies"].ConnectionString;
    }
}

To improve performance, the GetMovieCount() method attempts to retrieve the total count of movie records from the server cache. If the record count cannot be retrieved from the cache, the count is retrieved from the database.

The GetMovies() method calls a stored procedure named GetPagedMovies to retrieve a particular page of movies. The StartRowIndex and MaximumRows parameters are passed to the stored procedure. The GetPagedMovies stored procedure is contained in Listing 16.23.

Example 16.23. GetPagedMovies.sql

CREATE PROCEDURE dbo.GetPagedMovies
(
    @StartRowIndex INT,
    @MaximumRows INT
)
AS

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
    IndexId INT IDENTITY (1, 1) NOT NULL,
    RecordId INT
)

-- INSERT into the temp table
INSERT INTO #PageIndex (RecordId)
SELECT Id FROM Movies

-- Get a page of movies
SELECT
    Id,
    Title,
    Director,
    DateReleased
FROM
    Movies
    INNER JOIN #PageIndex WITH (nolock)
    ON Movies.Id = #PageIndex.RecordId
WHERE
    #PageIndex.IndexID > @startRowIndex
    AND #PageIndex.IndexID < (@startRowIndex + @maximumRows + 1)
ORDER BY
    #PageIndex.IndexID

The GetPagedMovies stored procedure returns a particular page of database records. The stored procedure creates a temporary table named #PageIndex that contains two columns: an identity column and a column that contains the primary key values from the Movies database table. The temporary table fills in any holes in the primary key column that might result from deleting records.

Next, the stored procedure retrieves a certain range of records from the #PageIndex table and joins the results with the Movies database table. The end result is that only a single page of database records is returned.

When you open the page in Listing 16.20, the GridView displays its paging interface, which you can use to navigate between different pages of records (see Figure 16.5).

Displaying multiple pages with data source paging.

Figure 16.5. Displaying multiple pages with data source paging.

Note

The paging mechanism described in this section is based on the mechanism used by the Microsoft ASP.NET forums at http://www.asp.net/forums and the XBOX forums at http://www.xbox.com. Both of these websites handle an incredible number of message posts every day. The forums software was written with ASP.NET, and it is available from TelligentSystems (www.telligentsystems.com) as part of their Community Server product.

If temporary tables make you anxious, you have an alternative when working with Microsoft SQL Server 2005. You can take advantage of the new ROW_NUMBER() function to select a range of rows. The ROW_NUMBER() function automatically calculates the sequential number of a row within a resultset.

The modified stored procedure in Listing 16.24 does the same thing as the stored procedure in Listing 16.23. However, the modified stored procedure avoids any temporary tables.

Example 16.24. GetPagedMovies2005.sql

CREATE PROCEDURE dbo.GetPagedMovies2005
(
    @StartRowIndex INT,
    @MaximumRows INT
)
AS

WITH OrderedMovies AS
(
SELECT
    Id,
    ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
FROM Movies
)

SELECT
    OrderedMovies.RowNumber,
    Movies.Id,
    Movies.Title,
    Movies.Director
FROM
    OrderedMovies
    JOIN Movies
    ON OrderedMovies.Id = Movies.Id
WHERE
    RowNumber BETWEEN (@StartRowIndex + 1) AND (@startRowIndex + @maximumRows + 1)

User Interface Sorting

If you need to sort the records displayed by the GridView control, then the easiest type of sorting to enable is user interface sorting. When you take advantage of user interface sorting, the records are sorted in the server’s memory.

For example, the page in Listing 16.25 contains a GridView that has its AllowSorting property set to the value True. The GridView is bound to an ObjectDataSource that represents the Employees database table (see Figure 16.6).

Sorting records with user interface sorting.

Figure 16.6. Sorting records with user interface sorting.

Example 16.25. ShowUISorting.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show User Interface Sorting</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdEmployees"
        DataSourceID="srcEmployees"
        AllowSorting="True"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcEmployees"
        TypeName="EmployeesUISorting"
        SelectMethod="GetEmployees"
        Runat="server" />

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

The ObjectDataSource control in Listing 16.25 is bound to the component in Listing 16.26. Notice that the GetEmployees() method returns an ADO.NET DataSet object. When taking advantage of user interface sorting, the ObjectDataSource control must represent the right type of data source. The right type of data source includes a DataSet, a DataTable, and a DataView.

Example 16.26. EmployeesUISorting.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class EmployeesUISorting
{
    private static readonly string _conString;

    public static DataSet GetEmployees()
    {
        // Initialize ADO.NET objects
        string selectText = "SELECT Id,FirstName,LastName,Phone FROM Employees";
        SqlDataAdapter dad = new SqlDataAdapter(selectText, _conString);
        DataSet dstEmployees = new DataSet();

        // Fill the DataSet
        using (dad)
        {
            dad.Fill(dstEmployees);
        }
        return dstEmployees;
     }

     static EmployeesUISorting()
     {
         _conString = WebConfigurationManager.ConnectionStrings["Employees"]. ConnectionString;
     }
}

User interface sorting is convenient. You can enable this type of sorting by setting a single property of the GridView control. Unfortunately, just as with user interface paging, some serious performance drawbacks result from user interface sorting. All the records from the underlying database must be loaded and sorted in memory. This is a particular problem when you want to enable both sorting and paging at the same time. In the next section, you learn how to implement data source sorting, which avoids this performance issue.

Data Source Sorting

Imagine that you are working with a database table that contains 3 billion records and you want to enable users to both sort the records contained in this table and page through the records contained in this table. In that case, you’ll want to implement both data source sorting and paging.

The page in Listing 16.27 contains a GridView and ObjectDataSource control. The GridView has both its AllowSorting and AllowPaging properties enabled (see Figure 16.7).

Example 16.27. ShowDSSorting.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
 "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
    .employees td,.employees th
    {
        font:16px Georgia,Serif;
        padding:5px;
    }
    a
    {
        color:blue;
    }
    </style>
    <title>Show Data Source Sorting</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdEmployees"
        DataSourceID="srcEmployees"
        AllowSorting="true"
        AllowPaging="true"
        PageSize="3"
        CssClass="employees"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcEmployees"
        TypeName="EmployeesDSSorting"
        SelectMethod="GetEmployees"
        SelectCountMethod="GetEmployeeCount"
        EnablePaging="true"
        SortParameterName="sortExpression"
        Runat="server" />

    </div>
    </form>
</body>
</html>
Paging and sorting database records.

Figure 16.7. Paging and sorting database records.

The ObjectDataSource control in Listing 16.27 represents the EmployeesDSSorting component in Listing 16.28. Notice that the ObjectDataSource control includes a SortParameterName property. When this property is present, the ObjectDataSource control uses data source sorting instead of user interface sorting.

Example 16.28. EmployeesDSSorting.cs

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Public Class EmployeesDSSorting

    Private Shared ReadOnly _conString As String

    Public Shared Function GetEmployees(ByVal sortExpression As String, ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As SqlDataReader
        ' Initialize connection
        Dim con As New SqlConnection(_conString)

        ' Initialize command
        Dim cmd As New SqlCommand()
        cmd.Connection = con
        cmd.CommandText = "GetSortedEmployees"
        cmd.CommandType = CommandType.StoredProcedure

        ' Create parameters
        cmd.Parameters.AddWithValue("@SortExpression", sortExpression)
        cmd.Parameters.AddWithValue("@StartRowIndex", startRowIndex)
        cmd.Parameters.AddWithValue("@MaximumRows", maximumRows)

        ' Execute command
        con.Open()
        Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
    End Function


    Public Shared Function GetEmployeeCount() As Integer
        Dim context As HttpContext = HttpContext.Current
        If context.Cache("EmployeeCount") Is Nothing Then
            context.Cache("EmployeeCount") = GetEmployeeCountFromDB()
        End If
        Return CType(context.Cache("EmployeeCount"), Integer)
    End Function
    Private Shared Function GetEmployeeCountFromDB() As Integer
        Dim result As Integer = 0

        ' Initialize connection
        Dim con As SqlConnection = New SqlConnection(_conString)

        ' Initialize command
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.Connection = con
        cmd.CommandText = "SELECT Count(*) FROM Employees"

        ' Execute command
        Using con
            con.Open()
            result = CType(cmd.ExecuteScalar(), Integer)
        End Using
        Return result
    End Function



    Shared Sub New()
        _conString = WebConfigurationManager.ConnectionStrings("Employees"). ConnectionString
    End Sub
End Class

The GetEmployees() method in the component in Listing 16.28 calls a stored procedure to sort and page records. The stored procedure, named GetSortedEmployees, returns a sorted page of records from the Employees database table. This stored procedure is contained in Listing 16.29.

Example 16.29. GetSortedEmployees.sql

CREATE PROCEDURE GetSortedEmployees
(
    @SortExpression NVarChar(100),
    @StartRowIndex INT,
    @MaximumRows INT
)
AS

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
    IndexId INT IDENTITY (1, 1) NOT NULL,
    RecordId INT
)

-- INSERT into the temp table
INSERT INTO #PageIndex (RecordId)
SELECT Id FROM Employees
ORDER BY
CASE WHEN @SortExpression='Id' THEN Id END ASC,
CASE WHEN @SortExpression='Id DESC' THEN Id END DESC,
CASE WHEN @SortExpression='FirstName' THEN FirstName END ASC,
CASE WHEN @SortExpression='FirstName DESC' THEN FirstName END DESC,
CASE WHEN @SortExpression='LastName' THEN LastName END ASC,
CASE WHEN @SortExpression='LastName DESC' THEN LastName END DESC,
CASE WHEN @SortExpression='Phone' THEN Phone END ASC,
CASE WHEN @SortExpression='Phone DESC' THEN Phone END DESC

-- Get a page of records
SELECT
    Id,
    FirstName,
    LastName,
    Phone
FROM
    Employees
    INNER JOIN #PageIndex WITH (nolock)
    ON Employees.Id = #PageIndex.RecordId
WHERE
    #PageIndex.IndexID > @StartRowIndex
    AND #PageIndex.IndexID < (@StartRowIndex + @MaximumRows + 1)
ORDER BY
    #PageIndex.IndexID

Notice that the stored procedure in Listing 16.29 uses SQL CASE functions to sort the records before they are added to the temporary table. Unfortunately, you can’t use a parameter with an ORDER BY clause, so the sort columns must be hard-coded in the CASE functions. Next, a page of records is selected from the temporary table.

Note

As an alternative to the data source sorting method described in this section, you can use LINQ to SQL. For more information on LINQ to SQL, see Chapter 18.

Filtering Data

You can supply the ObjectDataSource control with a filter expression. The filter expression is applied to the data returned by the control’s select method. A filter is particularly useful when used in combination with caching. You can load all the data into the cache and then apply different filters to the cached data.

Note

You learn how to cache data with the ObjectDataSource control in Chapter 25, “Caching Application Pages and Data.”

For example, the page in Listing 16.30 contains a DropDownList and GridView control. The DropDownList displays a list of movie categories, and the GridView displays matching movies (see Figure 16.8).

Filtering movies with the ObjectDataSource control.

Figure 16.8. Filtering movies with the ObjectDataSource control.

Example 16.30. ShowFilteredMovies.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Filtered Movies</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:DropDownList
        id="ddlMovieCategory"
        DataSourceID="srcMovieCategories"
        DataTextField="Name"
        DataValueField="Id"
        Runat="server" />
    <asp:Button
        id="btnSelect"
        Text="Select"
        Runat="server" />

    <hr />

    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        AutoGenerateColumns="false"
        Runat="server">
        <Columns>
        <asp:BoundField
            DataField="Title"
            HeaderText="Movie Title" />
        <asp:BoundField
            DataField="Director"
            HeaderText="Movie Director" />
        </Columns>
    </asp:GridView>

    <asp:ObjectDataSource
        id="srcMovieCategories"
        TypeName="FilterMovies"
        SelectMethod="GetMovieCategories"
        EnableCaching="true"
        CacheDuration="Infinite"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="FilterMovies"
        SelectMethod="GetMovies"
        EnableCaching="true"
        CacheDuration="Infinite"
        FilterExpression="CategoryID={0}"
        Runat="server">
        <FilterParameters>
        <asp:ControlParameter
            Name="Category"
            ControlID="ddlMovieCategory" />
        </FilterParameters>
    </asp:ObjectDataSource>


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

Both ObjectDataSource controls in Listing 16.30 have caching enabled. Furthermore, the second ObjectDataSource control includes a FilterExpression property that filters the cached data, using the selected movie category from the DropDownList control.

Both ObjectDataSource controls represent the component in Listing 16.31.

Example 16.31. FilterMovies.cs

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class FilterMovies
{
    private readonly string _conString;

    public DataSet GetMovies()
    {
        // Initialize connection
        SqlConnection con = new SqlConnection(_conString);

        // Initialize DataAdapter
        string commandText = "SELECT Title,Director,CategoryId FROM Movies";
        SqlDataAdapter dad = new SqlDataAdapter(commandText, con);

        // Return DataSet
        DataSet dstMovies = new DataSet();
        using (con)
        {
            dad.Fill(dstMovies);
        }
        return dstMovies;
    }

    public DataSet GetMovieCategories()
    {
        // Initialize connection
        SqlConnection con = new SqlConnection(_conString);

        // Initialize DataAdapter
        string commandText = "SELECT Id,Name FROM MovieCategories";
        SqlDataAdapter dad = new SqlDataAdapter(commandText, con);

        // Return DataSet
        DataSet dstCategories = new DataSet();
        using (con)
        {
            dad.Fill(dstCategories);
        }
        return dstCategories;
    }

    public FilterMovies()
    {
        _conString = WebConfigurationManager.ConnectionStrings["Movies"]. ConnectionString;
    }
}

The ObjectDataSource enables you to filter data only when the data is represented by a DataSet, DataTable, or DataView object. This means that if you use filtering, the data must be returned as one of these objects.

Note

Behind the scenes, the ObjectDataSource control uses the DataView.RowFilter property to filter database rows. You can find detailed documentation on proper filter syntax by looking up the DataColumn.Expression property in the .NET Framework SDK Documentation.

Handling ObjectDataSource Control Events

The ObjectDataSource control supports the following events:

  • Deleting—. Occurs immediately before the method represented by the DeleteMethod property is called.

  • Deleted—. Occurs immediately after the method represented by the DeleteMethod property is called.

  • Inserting—. Occurs immediately before the method represented by the InsertMethod property is called.

  • Inserted—. Occurs immediately after the method represented by the InsertMethod property is called.

  • Selecting—. Occurs immediately before the method represented by the SelectMethod property is called.

  • Selected—. Occurs immediately after the method represented by the InsertMethod property is called.

  • Updating—. Occurs immediately before the method represented by the InsertMethod property is called.

  • Updated—. Occurs immediately after the method represented by the InsertMethod property is called.

  • Filtering—. Occurs immediately before the filter expression is evaluated.

  • ObjectCreating—. Occurs immediately before the object represented by the ObjectDataSource control is created.

  • ObjectCreated—. Occurs immediately after the object represented by the ObjectDataSource control is created.

  • ObjectDisposing—. Occurs before the object represented by the ObjectDataSource control is destroyed.

Notice that most of these events come in pairs. One event happens immediately before a method is called, and one event happens immediately after a method is called.

You can handle these events to modify the parameters and objects represented by an ObjectDataSource control. You can also handle these events to handle any errors that might result from calling methods with the ObjectDataSource control.

Adding and Modifying Parameters

You can handle the Selecting, Inserting, Updating, and Deleting events to modify the parameters that are passed to the methods called by the ObjectDataSource control. There are several situations in which you might want to do this.

First, if you are working with an existing component, you might need to change the names of the parameters passed to the component. For example, instead of passing a parameter named id to an update method, you might want to rename the parameter to movieId.

Second, you might want to pass additional parameters to the method being called. For example, you might need to pass the current username, the current IP address, or the current date and time as a parameter to a method.

For example, imagine that you want to create a guestbook and automatically associate the IP address of the user making an entry with each entry in the guestbook. The page in Listing 16.32 illustrates how you can do this with the help of a FormView control and an ObjectDataSource control (see Figure 16.9).

Displaying a guestbook.

Figure 16.9. Displaying a guestbook.

Example 16.32. ShowGuestbook.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">

    protected void srcGuestbook_Inserting(object sender, ObjectDataSourceMethodEventArgs e)
    {
        e.InputParameters.Add("IPAddress", Request.UserHostAddress);
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <style type="text/css">
        .guestbook td,.guestbook th
        {
            padding:5px;
            font:14px Arial,Sans-Serif;
        }
    </style>
    <title>Show Guestbook</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:FormView
        id="frmGuestbook"
        DataSourceID="srcGuestbook"
        DefaultMode="Insert"
        Runat="server">
        <InsertItemTemplate>
        <asp:Label
            ID="lblComment"
            Text="Comment:"
            AssociatedControlID="txtComment"
            Runat="server" />
        <br />
        <asp:TextBox
            id="txtComment"
            Text='<%# Bind("comment") %>'
            TextMode="MultiLine"
            Columns="50"
            Rows="4"
            Runat="server" />
        <br />
        <asp:Button
            id="btnInsert"
            Text="Add Entry"
            CommandName="Insert"
            Runat="server" />
         </InsertItemTemplate>
    </asp:FormView>

    <hr />

    <asp:GridView
        id="grdGuestbook"
        DataSourceID="srcGuestbook"
        CssClass="guestbook"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcGuestbook"
        TypeName="Guestbook"
        SelectMethod="GetEntries"
        InsertMethod="AddEntry"
        OnInserting="srcGuestbook_Inserting"
        Runat="server" />

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

The page in Listing 16.32 includes an Inserting event handler. When the insert method is called, the IP address of the current user is added to the parameters collection.

The ObjectDataSource control in Listing 16.32 is bound to the Guestbook component in Listing 16.33.

Example 16.33. Guestbook.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
public class Guestbook
{
    private string _conString;

    public SqlDataReader GetEntries()
    {
        // Initialize connection
        SqlConnection con = new SqlConnection(_conString);

        // Initialize command
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SELECT Id,IPAddress,Comment,EntryDate FROM Guestbook";

        // Execute command
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
     }

     public void AddEntry(string IPAddress, string comment)
     {
         // Initialize connection
         SqlConnection con = new SqlConnection(_conString);

         // Initialize command
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = con;
         cmd.CommandText = "INSERT Guestbook (IPAddress,Comment)" +
             " VALUES (@IPAddress, @Comment)";

         // Add ADO.NET parameters
         cmd.Parameters.AddWithValue("@IPAddress", IPAddress);
         cmd.Parameters.AddWithValue("@Comment", comment);

         // Execute command
         using (con)
         {
             con.Open();
             cmd.ExecuteNonQuery();
         }
     }

     public Guestbook()
     {
         _conString = WebConfigurationManager.ConnectionStrings["Guestbook"]. ConnectionString;
     }
}

Realize that you can manipulate the parameters collection in any way that you need. You can change the names, types, or values of any of the parameters.

Handling Method Errors

You can handle the Selected, Inserted, Updated, or Deleted events in order to handle any errors that might result from calling a method. For example, the page in Listing 16.34 handles the Inserting event to capture any errors raised when the method represented by the ObjectDataSource control’s InsertMethod property is called.

Example 16.34. HandleErrors.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">

    protected void srcMovies_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
    {
        if (e.Exception != null)
        {
            e.ExceptionHandled = true;
            lblError.Text = "Could not insert movie";
        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        html
        {
            background-color:silver;
        }
        .insertForm
        {
            background-color:white;
        }
        .insertForm td,.insertForm th
        {
           padding:10px;
        }
        .error
        {
            color:red;
            font:bold 14px Arial,Sans-Serif;
        }
    </style>
    <title>Handle Errors</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:Label
        id="lblError"
        EnableViewState="false"
        CssClass="error"
        Runat="server" />

    <h1>Insert Movie</h1>
    <asp:DetailsView
        id="dtlMovies"
        DataSourceID="srcMovies"
        DefaultMode="Insert"
        AutoGenerateInsertButton="true"
        AutoGenerateRows="false"
        CssClass="insertForm"
        GridLines="None"
        Runat="server">
        <Fields>
        <asp:BoundField
            DataField="Title"
            HeaderText="Title:"/>
        <asp:BoundField
            DataField="Director"
            HeaderText="Director:" />
        </Fields>
    </asp:DetailsView>
    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="InsertMovie"
        InsertMethod="Insert"
        Runat="server" OnInserted="srcMovies_Inserted" />

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

In Listing 16.34, the Inserted event handler checks for an exception. If an exception exists, then the exception is handled and an error message is displayed (see Figure 16.10).

Handling method errors gracefully.

Figure 16.10. Handling method errors gracefully.

The page in Listing 16.34 is bound to the component in Listing 16.35.

Example 16.35. InsertMovie.cs

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class InsertMovie
{
    private static readonly string _conString;

    public static SqlDataReader GetMovies()
    {
        // Initialize connection
        SqlConnection con = new SqlConnection(_conString);

        // Initialize command
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SELECT Id,Title,Director FROM Movies";

        // Execute command
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
     }

     public static void Insert(string title, string director)
     {
         // Initialize connection
         SqlConnection con = new SqlConnection(_conString);

         // Initialize command
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = con;
         cmd.CommandText = "INSERT Movies (Title,Director)" +
             " VALUES (@Title,@Director)";

         // Add ADO.NET parameters
         cmd.Parameters.AddWithValue("@Title", title);
         cmd.Parameters.AddWithValue("@Director", director);

         // Execute command
         using (con)
         {
             con.Open();
             cmd.ExecuteNonQuery();
         }
     }
     static InsertMovie()
     {
         _conString = WebConfigurationManager.ConnectionStrings["Movies"]. ConnectionString;
     }
}

You can create an exception by entering a new movie record and not supplying a value for one of the fields. For example, the Title column in the Movies database table does not accept null values.

Note

Instead of handling errors at the level of the DataSource control, you can handle errors at the level of the DataBound control. For example, the DetailsView control supports an ItemInserted event.

Handling the ObjectCreating Event

By default, the ObjectDataSource control can represent only components that have a constructor that does not require any parameters. If you are forced to use a component that does require parameters for its constructor, then you can handle the ObjectDataSource control’s ObjectCreating event.

For example, the component in Listing 16.36 must be initialized with a movie category parameter. The component returns only movies in the specified category.

Example 16.36. MoviesByCategory.cs

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class MoviesByCategory
{
    private readonly string _conString;
    private readonly string _movieCategory;

    public SqlDataReader GetMovies()
    {
        // Initialize connection
        SqlConnection con = new SqlConnection(_conString);

        // Initialize command
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SELECT Title,Director,DateReleased FROM Movies"
            + " JOIN MovieCategories ON Movies.CategoryId=MovieCategories.Id"
            + " WHERE MovieCategories.Name=@CategoryName";

        // Create ADO.NET parameters
        cmd.Parameters.AddWithValue("@CategoryName", _movieCategory);

        // Execute command
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public MoviesByCategory(string movieCategory)
    {
        _movieCategory = movieCategory;
        _conString = WebConfigurationManager.ConnectionStrings["Movies"]. ConnectionString;
    }
}

The page in Listing 16.37 contains an ObjectDataSource control that represents the MoviesByCategory component. The page includes a handler for the ObjectCreating event so that it can assign an initialized instance of the MoviesByCategory component to the ObjectDataSource control.

Example 16.37. ShowAdventureMovies.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">

    protected void srcMovies_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
    {
        MoviesByCategory movies = new MoviesByCategory("Adventure");
        e.ObjectInstance = movies;
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Adventure Movies</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <h1>Adventure Movies</h1>

    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="MoviesByCategory"
        SelectMethod="GetMovies"
        OnObjectCreating="srcMovies_ObjectCreating"
        Runat="server" />

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

Notice that even though the MoviesByCategory component is initialized in the ObjectCreating event handler, you still must assign the name of the component to the ObjectDataSource control’s TypeName property. The ObjectDataSource control needs to know what type of object it is representing when it calls its methods.

Note

The ObjectCreating event is not raised when a shared method is called.

Concurrency and the ObjectDataSource Control

Imagine that two users open the same page for editing the records in the movies database table at the same time. By default, if the first user submits changes before the second user, then the first user’s changes are overwritten. In other words, the last user to submit changes wins.

This default behavior of the ObjectDataSource control can be problematic in an environment in which a lot of users are working with the same set of data. You can modify this default behavior by modifying the ObjectDataSource control’s ConflictDetection property. This property accepts the following two values:

  • CompareAllValues—. Causes the ObjectDataSource control to track both the original and new values of its parameters.

  • OverwriteChanges—. Causes the ObjectDataSource to overwrite the original values of its parameters with new values (the default value).

When you set the ConflictDetection property to the value CompareAllValues, you should add an OldValuesParameterFormatString property to the ObjectDataSource control. You use this property to indicate how the original values the database columns should be named.

The page in Listing 16.38 contains a GridView and ObjectDataSource control, which you can use to edit the movies in the Movies database table. The ObjectDataSource control includes a ConflictDetection property with the value CompareAllValues and an OldValuesParameterFormatString property with the value original_{0}.

Example 16.38. ShowConflictDetection.aspx

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">

    protected void srcMovies_Updated(object sender, ObjectDataSourceStatusEventArgs e)
    {
        if (e.Exception != null)
        {
            e.ExceptionHandled = true;
            lblError.Text = "Could not update record";
        }
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        .error
        {
           color:red;
           font:bold 16px Arial,Sans-Serif;
        }
        a
        {
           color:blue;
        }
    </style>
    <title>Show Conflict Detection</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:Label
        id="lblError"
        EnableViewState="false"
        CssClass="error"
        Runat="server" />

    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        DataKeyNames="Id"
        AutoGenerateEditButton="true"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcMovies"
        ConflictDetection="CompareAllValues"
        OldValuesParameterFormatString="original_{0}"
        TypeName="ConflictedMovies"
        SelectMethod="GetMovies"
        UpdateMethod="UpdateMovie"
        OnUpdated="srcMovies_Updated"
        Runat="server" />

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

The ObjectDataSource control in Listing 16.38 is bound to the component in Listing 16.39.

Example 16.39. ConflictedMovies.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public class ConflictedMovies
{
    private static readonly string _conString;

    public static SqlDataReader GetMovies()
    {
        // Initialize connection
        SqlConnection con = new SqlConnection(_conString);

        // Initialize command
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SELECT Id,Title,Director FROM Movies";

        // Execute command
        con.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
     }

     public static void UpdateMovie(string title, string director, string original_title, string original_director, int original_id)
     {
        // Initialize connection
        SqlConnection con = new SqlConnection(_conString);

        // Initialize command
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "UPDATE Movies SET Title=@Title,Director=@Director"
            + " WHERE Id=@original_Id AND Title=@original_Title AND Director=@original_Director";

        // Create parameters
        cmd.Parameters.AddWithValue("@Title", title);
        cmd.Parameters.AddWithValue("@Director", director);
        cmd.Parameters.AddWithValue("@original_Id", original_id);
        cmd.Parameters.AddWithValue("@original_Title", original_title);
        cmd.Parameters.AddWithValue("@original_Director", original_director);

        using (con)
        {
            con.Open();
            int rowsAffected = cmd.ExecuteNonQuery();
            if (rowsAffected == 0)
                throw new Exception("Could not update movie record");
        }
    }

    static ConflictedMovies()
    {
        _conString = WebConfigurationManager.ConnectionStrings["Movies"]. ConnectionString;
    }
}

The component in Listing 16.39 includes an UpdateMovie() method. Notice that this method accepts five parameters: the original_title, title, original_director, director, and original_id parameters.

The UpdateMovie() method raises an exception when the original parameter values don’t match the current values in the Movies database table. Notice that the command executed by the Command object looks like this:

UPDATE Movies SET Title=@Title, Director=@Director
WHERE Id=@original_id AND Title=@original_Title AND Director=@original_Director

This statement updates a row in the database only when the current values from the row match the original values selected from the row. If the original and current values don’t match, no records are affected and the UpdateMovie() method raises an exception.

Extending the ObjectDataSource Control

In this final section, we examine two methods of extending the ObjectDataSource control. You learn how to create a custom data source control by deriving a new control from the ObjectDataSource control. You also learn how to create custom parameters that can be used with the ObjectDataSource (and other DataSource controls).

Creating a Custom ObjectDataSource Control

If you discover that you are declaring an ObjectDataSource control with the same properties on multiple pages, then it makes sense to derive a new control from the ObjectDataSource control that has these properties by default. That way, you can simply declare the derived control in a page.

For example, if you are displaying a list of movies in multiple pages in your website, then it would make sense to create a specialized MovieDataSource control.

The control in Listing 16.40, named the MovieDataSource control, derives from the base ObjectDataSource control class. The MovieDataSource control represents the MoviesComponent, which is also contained in Listing 16.40.

Example 16.40. MovieDataSource.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Web.UI.WebControls;

namespace AspNetUnleashed.Samples
{
    public class MovieDataSource : ObjectDataSource
    {
        public MovieDataSource()
        {
            this.TypeName = "AspNetUnleashed.Samples.MoviesComponent";
            this.SelectMethod = "GetMovies";
        }
    }

    public class MoviesComponent
    {
        private readonly string _conString;

        public SqlDataReader GetMovies()
        {
            // Initialize connection
            SqlConnection con = new SqlConnection(_conString);

            // Initialize command
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT Title,Director,DateReleased FROM Movies";
            // Execute command
            con.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public MoviesComponent()
        {

            _conString = WebConfigurationManager.ConnectionStrings["Movies"]. ConnectionString;
        }
    }
}

The MovieDataSource control initializes the base ObjectDataSource control’s TypeName and SelectMethod properties in its constructor. The TypeName is assigned the fully qualified name of the MoviesComponent.

The page in Listing 16.41 illustrates how you can use the MovieDataSource control in a page (see Figure 16.11).

Using the MovieDataSource control to display movies.

Figure 16.11. Using the MovieDataSource control to display movies.

Example 16.41. ShowMovieDataSource.aspx

<%@ Page Language="C#" %>
<%@ Register TagPrefix="custom" Namespace="AspNetUnleashed.Samples" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Movie DataSource</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        Runat="server" />

    <custom:MovieDataSource
        id="srcMovies"
        Runat="server" />

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

Notice that the custom control must be registered with a <%@ Register %> directive at the top of Listing 16.41. After you register the control, you can simply declare the MovieDataSource control in the page to represent the contents of the Movies database table.

Note

As an alternative to registering the MovieDataSource control in a page, you can register the control for an entire application in the web configuration file within the <pages> element.

Creating Custom Parameter Objects

The standard DataSource Parameter objects included in the ASP.NET Framework enable you to represent objects such as query string values, items from Session state, and values of control properties. If none of the standard Parameter objects satisfy your requirements, you always have the option of creating a custom Parameter object.

You create a custom Parameter object by deriving a new class from the base Parameter class. In this section, we create two custom parameters. The first is a UsernameParameter that automatically represents the current username. Next is a PagePropertyParameter that represents the current value of a property contained in the page.

Creating a Username Parameter

The UsernameParameter class is contained in Listing 16.42. Notice that the class in Listing 16.42 derives from the Parameter class and overrides the Evaluate() method of the base class. The Evaluate() method determines what the parameter represents.

Example 16.42. UsernameParameter.cs

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

namespace MyControls
{
    public class UsernameParameter : Parameter
    {
        protected override object Evaluate(HttpContext context, Control control)
        {
            if (context != null)
                return context.User.Identity.Name;
            else
                return null;
        }
    }
}

The UsernameParameter returns the current username. The parameter retrieves this information from the current HttpContext passed to the Evaluate() method. The UsernameParameter is used in the page in Listing 16.43.

Example 16.43. ShowUsernameParameter.aspx

<%@ Page Language="C#" %>
<%@ Register TagPrefix="custom" Namespace="MyControls" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        .guestbook td,.guestbook th
        {
            padding:5px;
            font:14px Arial,Sans-Serif;
        }
    </style>
    <title>Show Username Parameter</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:FormView
        id="frmGuestbook"
        DataSourceID="srcGuestbook"
        DefaultMode="Insert"
        Runat="server">
        <InsertItemTemplate>
        <asp:Label
            ID="lblComment"
            Text="Comment:"
            AssociatedControlID="txtComment"
            Runat="server" />
        <br />
        <asp:TextBox
            id="txtComment"
            Text='<%# Bind("comment") %>'
            TextMode="MultiLine"
            Columns="50"
            Rows="4"
            Runat="server" />
        <br />
        <asp:Button
            id="btnInsert"
            Text="Add Entry"
            CommandName="Insert"
            Runat="server" />
        </InsertItemTemplate>
    </asp:FormView>

    <hr />

    <asp:GridView
        id="grdGuestbook"
        DataSourceID="srcGuestbook"
        CssClass="guestbook"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcGuestbook"
        TypeName="GuestbookComponent"
        SelectMethod="GetEntries"
        InsertMethod="AddEntry"
        Runat="server">
        <InsertParameters>
            <custom:UsernameParameter name="username" />
        </InsertParameters>
    </asp:ObjectDataSource>

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

The UsernameParameter is declared in the ObjectDataSource control’s InsertParameters collection. When you add a new entry to the guestbook, your username is added automatically (see Figure 16.12).

Inserting records with the UsernameParameter.

Figure 16.12. Inserting records with the UsernameParameter.

Creating a Page Property Parameter

The PagePropertyParameter enables you to represent an arbitrary property of the current page. The property being represented can return whatever type of value you want. The code for the PagePropertyParameter is contained in Listing 16.44.

Example 16.44. PagePropertyParameter.cs

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

namespace MyControls
{
    public class PagePropertyParameter : Parameter
    {
        private string _propertyName;

        protected override object Evaluate(HttpContext context, Control control)
        {
            return DataBinder.Eval(control.Page, PropertyName);
        }

        public string PropertyName
        {
            get { return _propertyName; }
            set { _propertyName = value; }
        }

    }
}

The component in Listing 16.44 overrides the Evaluate method of the base Parameter class. The DataBinder.Eval() method is used to return the value of a property of the current page.

The page in Listing 16.45 uses the PagePropertyParameter to represent a property of the page named CurrentUsername. This property returns the current username.

Example 16.45. ShowPagePropertyParameter.aspx

<%@ Page Language="C#" %>
<%@ Register TagPrefix="custom" Namespace="MyControls" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
    Public ReadOnly Property CurrentUsername() As String
        Get
            Return User.Identity.Name
        End Get
    End Property

</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <style type="text/css">
        .guestbook td,.guestbook th
        {
            padding:5px;
            font:14px Arial,Sans-Serif;
        }
    </style>
    <title>Show Page Property Parameter</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:FormView
        id="frmGuestbook"
        DataSourceID="srcGuestbook"
        DefaultMode="Insert"
        Runat="server">
        <InsertItemTemplate>
        <asp:Label
            ID="lblComment"
            Text="Comment:"
            AssociatedControlID="txtComment"
            Runat="server" />
        <br />
        <asp:TextBox
            id="txtComment"
            Text='<%# Bind("comment") %>'
            TextMode="MultiLine"
            Columns="50"
            Rows="4"
            Runat="server" />
        <br />
        <asp:Button
            id="btnInsert"
            Text="Add Entry"
            CommandName="Insert"
            Runat="server" />
        </InsertItemTemplate>
    </asp:FormView>

    <hr />

    <asp:GridView
        id="grdGuestbook"
        DataSourceID="srcGuestbook"
        CssClass="guestbook"
        Runat="server" />

    <asp:ObjectDataSource
        id="srcGuestbook"
        TypeName="GuestbookComponent"
        SelectMethod="GetEntries"
        InsertMethod="AddEntry"
        Runat="server">
        <InsertParameters>
        <custom:PagePropertyParameter
            Name="Username"
            PropertyName="CurrentUsername" />
        </InsertParameters>
    </asp:ObjectDataSource>

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

In Listing 16.45, the PagePropertyParameter is used to represent the current username. Because the PagePropertyParameter can represent any page property, the parameter could represent any type of value.

Summary

In this chapter, you learned how to use the ObjectDataSource control to represent different types of objects. In the first section, you were provided with sample code that demonstrated how you can use the ObjectDataSource control to represent a collection, a DataReader, and a DataSet, a LINQ to SQL query, and a web service.

We also discussed how you can use the ObjectDataSource control to page, sort, and filter data. You learned how to implement both user interface paging and data source paging, which enables you to efficiently work with very large sets of records.

Next, we examined how you can handle ObjectDataSource control events. You learned how to add and modify the parameters represented by the ObjectDataSource control. You also learned how to gracefully handle errors raised when executing an ObjectDataSource control method.

Finally, we discussed two methods of extending the ObjectDataSource control. You learned how to derive a new control from the base ObjectDataSource control to represent specialized data sources such as a Product data source. We also discussed how you can create custom Parameter objects that can be used with the ObjectDataSource control.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset