Paging, Sorting, and Filtering Data with the ObjectDataSource
Control
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.
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, and web services.
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).
Let’s start with a really simple component. The component in Listing 15.1 is named MovieCollection
. It contains one method named GetMovies()
, which returns a collection of movie titles.
Example 15.1. MovieCollection.vb
Imports System Imports System.Web.Configuration Imports System.Collections.Generic Public Class MovieCollection Public Function GetMovies() As List(Of String) Dim movies As New List(Of String)() movies.Add("Star Wars") movies.Add("Independence Day") movies.Add("War of the Worlds") Return movies End Function End Class
You can use the page in Listing 15.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 15.2. ShowMovieCollection.aspx
<%@ Page Language="VB" %> <!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 15.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 15.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.
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
.
The different ADO.NET objects are compared and contrasted in the next chapter, “Building Data Access Components.”
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 15.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 15.3. MovieDataReader.vb
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class MovieDataReader Private ReadOnly _conString As String Public Function GetMovies() As SqlDataReader ' Create Connection Dim con As New SqlConnection(_conString) ' Create Command Dim cmd As New SqlCommand() cmd.Connection = con cmd.CommandText = "SELECT Id,Title,Director FROM Movies" ' Return DataReader con.Open() Return cmd.ExecuteReader(CommandBehavior.CloseConnection) End Function Public Sub New() _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString End Sub End Class
The component in Listing 15.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 15.4 represents the MovieDataReader
object. It binds the movies to a GridView
control.
Example 15.4. ShowMovieDataReader.aspx
<%@ Page Language="VB" %> <!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>
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 15.5 returns all the records from the Movies database table. However, it uses a DataSet
instead of a DataReader
object.
Example 15.5. MovieDataSet.vb
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class MovieDataSet Private ReadOnly _conString As String Public Function GetMovies() As DataSet ' Create DataAdapter Dim commandText As String = "SELECT Id,Title,Director FROM Movies" Dim dad As New SqlDataAdapter(commandText, _conString) ' Return DataSet Dim dstMovies As New DataSet() Using dad dad.Fill(dstMovies) End Using Return dstMovies End Function Public Sub New() _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString End Sub End Class
The component in Listing 15.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 15.6 binds the list of movies to a DropDownList
control.
Example 15.6. ShowMovieDataSet.aspx
<%@ Page Language="VB" %> <!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>
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 15.7 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 Website, Add New Item, and selecting the Web Service item.
Example 15.7. TimeService.asmx
<%@ WebService Language="VB" Class="TimeService" %> Imports System Imports System.Web Imports System.Web.Services Imports System.Web.Services.Protocols <WebService(Namespace:="http://tempuri.org/")> _ <WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _ Public Class TimeService Inherits System.Web.Services.WebService <WebMethod()> _ Public Function GetServerTime() As DateTime Return DateTime.Now End Function End Class
After you create the web service in Listing 15.7, 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 15.1).
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 15.8 displays the server time using a FormView control bound to an ObjectDataSource
control (see Figure 15.2).
Example 15.8. ShowWebService.aspx
<%@ Page Language="VB" %> <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.
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 15.9 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 15.9. Movies.vb
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class Movies Private ReadOnly _conString As String Public Sub UpdateMovie(ByVal id As Integer, ByVal title As String, ByVal director As String, ByVal dateReleased As DateTime) ' Create Command Dim con As New SqlConnection(_conString) Dim cmd As New SqlCommand() cmd.Connection = con cmd.CommandText = "UPDATE Movies SET Title=@Title,Director=@Director,DateReleased=@DateReleased WHERE Id=@Id" ' 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() End Using End Sub Public Function GetMovies() As SqlDataReader ' Create Connection Dim con As New SqlConnection(_conString) ' Create Command Dim cmd As SqlCommand = New SqlCommand() cmd.Connection = con cmd.CommandText = "SELECT Id,Title,Director,DateReleased FROM Movies" ' Return DataReader con.Open() Return cmd.ExecuteReader(CommandBehavior.CloseConnection) End Function Public Sub New() _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString End Sub End Class
The page in Listing 15.10 contains a GridView
and ObjectDataSource
control. Notice that the ObjectDataSource
control includes an UpdateMethod
property that points to the UpdateMovie()
method.
Example 15.10. ShowMovies.aspx
<%@ Page Language="VB" %> <!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 15.10, 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 15.11 declares all the parameters passed to the UpdateMovie()
method.
Example 15.11. ExplicitShowMovies.aspx
<%@ Page Language="VB" %> <!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.
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 15.12 contains a DropDownList
control and a GridView
control, which enables you to view movies that match a selected category (see Figure 15.3).
Example 15.12. ShowMoviesByCategory.aspx
<%@ Page Language="VB" %> <!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 15.12 is bound to the component contained in Listing 15.13. 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 15.13. MovieCategories.vb
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class MovieCategories Private ReadOnly _conString As String Public Function GetMovies(ByVal categoryId As Integer) As SqlDataReader ' Create Connection Dim con As New SqlConnection(_conString) ' Create Command Dim cmd As 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) End Function Public Function GetCategories() As SqlDataReader ' Create Connection Dim con As New SqlConnection(_conString) ' Create Command Dim cmd As SqlCommand = New SqlCommand() cmd.Connection = con cmd.CommandText = "SELECT Id,Name FROM MovieCategories" ' Return DataReader con.Open() Return cmd.ExecuteReader(CommandBehavior.CloseConnection) End Function Public Sub New() _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString End Sub End Class
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 an Employee
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 15.14 contains an InsertEmployee()
method for creating a new employee. This method is passed an instance of the Employee
object that represents a particular employee. The Employee
class also is included in Listing 15.14.
Example 15.14. EmployeeData.vb
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Collections.Generic Imports System.Web.Configuration Public Class EmployeeData Dim _connectionString As String Public Sub UpdateEmployee(ByVal employeeToUpdate As Employee) ' Initialize ADO.NET objects Dim con As SqlConnection = New SqlConnection(_connectionString) Dim cmd As SqlCommand = 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() End Using End Sub Public Function GetEmployees() As List(Of Employee) Dim employees As New List(Of Employee)() Dim con As SqlConnection = New SqlConnection(_connectionString) Dim cmd As SqlCommand = New SqlCommand() cmd.CommandText = "SELECT Id,FirstName,LastName,Phone FROM Employees" cmd.Connection = con Using con con.Open() Dim reader As SqlDataReader = cmd.ExecuteReader() While reader.Read() Dim NewEmployee As New Employee() NewEmployee.Id = CType(reader("Id"), Integer) NewEmployee.FirstName = CType(reader("FirstName"), String) NewEmployee.LastName = CType(reader("LastName"), String) NewEmployee.Phone = CType(reader("Phone"), String) employees.Add(NewEmployee) End While End Using Return employees End Function Public Sub New() _connectionString = WebConfigurationManager.ConnectionStrings("Employees").ConnectionString End Sub End Class Public Class Employee Private _id As Integer Private _firstName As String Private _lastName As String Private _phone As String Public Property Id() As Integer Get Return _id End Get Set(ByVal Value As Integer) _id = value End Set End Property Public Property FirstName() As String Get Return _firstName End Get Set(ByVal Value As String) _firstName = value End Set End Property Public Property LastName() As String Get Return _lastName End Get Set(ByVal Value As String) _lastName = value End Set End Property Public Property Phone() As String Get Return _phone End Get Set(ByVal Value As String) _phone = value End Set End Property End Class
The page in Listing 15.15 contains a DetailsView
control and an ObjectDataSource
control. The DetailsView
control enables you to update existing employees in the Employees database table.
Example 15.15. UpdateEmployees.aspx
<%@ Page Language="VB" %> <!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="Employee" 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 Employee
component is created and passed to the method.
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 Employee
class with the DataObjectTypeName
property:
Public Class Employee Private _firstName As String Public ReadOnly Property FirstName() As String Get Return _firstName End Get End Property Public Sub New(ByVal firstName As String) _firstName = firstName End Sub End Class
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 Employee Private _firstName As String Public Property FirstName() As String Get Return _firstName End Get Set(ByVal value As String) _firstName = value End Set End Property Public Sub New() End Sub End Class
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.”
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.
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 15.16 uses a GridView
and ObjectDataSource
control to display the records from the Movies database table in multiple pages (see Figure 15.4).
Example 15.16. ShowUIPaging.aspx
<%@ Page Language="VB" %> <!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 15.16 includes an AllowPaging
property that is set to the value True
. Setting this property enables user interface paging.
The ObjectDataSource
control in Listing 15.16 represents the MovieUIPaging
component in Listing 15.17. 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 15.17. MovieUIPaging.vb
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class MovieUIPaging Private ReadOnly _conString As String Public Function GetMoviesDataSet() As DataSet ' Create DataAdapter Dim commandText As String = "SELECT Id,Title,Director FROM Movies" Dim dad As SqlDataAdapter = New SqlDataAdapter(commandText, _conString) ' Return DataSet Dim dstMovies As DataSet = New DataSet() Using dad dad.Fill(dstMovies) End Using Return dstMovies End Function Public Sub New() _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString End Sub End Class
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 enables you to write custom logic for retrieving pages of database records. You can perform the paging in a component or you can perform the paging in a stored procedure.
If you want the best performance then you should write your paging logic in a stored procedure. That’s the approach taken in this section.
The page in Listing 15.18 contains an ObjectDataSource
control with data source paging enabled.
Example 15.18. ShowDSPaging.aspx
<%@ Page Language="VB" %> <!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
.
The ObjectDataSource
in Listing 15.18 represents a component named MoviesDSPaging
. The control calls the component’s GetMovies()
and GetMovieCount()
methods. The MoviesDSPaging
component is contained in Listing 15.19.
Example 15.19. MoviesDSPaging.vb
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class MoviesDSPaging Private Shared ReadOnly _conString As String Public Shared Function GetMovies(ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As SqlDataReader ' Initialize connection Dim con As SqlConnection = New SqlConnection(_conString) ' Initialize command Dim cmd As SqlCommand = 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) End Function Public Shared Function GetMovieCount() As Integer Dim context As HttpContext = HttpContext.Current If context.Cache("MovieCount") Is Nothing Then context.Cache("MovieCount") = GetMovieCountFromDB() End If Return CType(context.Cache("MovieCount"), Integer) End Function Private Shared Function GetMovieCountFromDB() 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 Movies" ' Execute command Using con con.Open() result = CType(cmd.ExecuteScalar(), Integer) End Using Return result End Function Shared Sub New() _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString End Sub End Class
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 15.20.
Example 15.20. 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 15.18, the GridView
displays its paging interface, which you can use to navigate between different pages of records (see Figure 15.5).
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 15.21 does the same thing as the stored procedure in Listing 15.20. However, the modified stored procedure avoids any temporary tables.
Example 15.21. 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)
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 15.22 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 15.6).
Example 15.22. ShowUISorting.aspx
<%@ Page Language="VB" %> <!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 15.22 is bound to the component in Listing 15.23. 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
, a DataView
, and a collection.
Example 15.23. EmployeesUISorting.vb
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class EmployeesUISorting Private Shared ReadOnly _conString As String Public Shared Function GetEmployees() As DataSet ' Initialize ADO.NET objects Dim selectText As String = "SELECT Id,FirstName,LastName,Phone FROM Employees" Dim dad As New SqlDataAdapter(selectText, _conString) Dim dstEmployees As New DataSet() ' Fill the DataSet Using dad dad.Fill(dstEmployees) End Using Return dstEmployees End Function Shared Sub New() _conString = WebConfigurationManager.ConnectionStrings("Employees").ConnectionString End Sub End Class
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.
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 15.24 contains a GridView
and ObjectDataSource
control. The GridView
has both its AllowSorting
and AllowPaging
properties enabled (see Figure 15.7).
Example 15.24. ShowDSSorting.aspx
<%@ Page Language="VB" %> <!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>
The ObjectDataSource
control in Listing 15.24 represents the EmployeesDSSorting
component in Listing 15.25. 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 15.25. EmployeesDSSorting.vb
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 15.25 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 15.26.
Example 15.26. 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 15.26 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.
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.
You learn how to cache data with the ObjectDataSource
control in Chapter 23, “Caching Application Pages and Data.”
For example, the page in Listing 15.27 contains a DropDownList
and GridView
control. The DropDownList
displays a list of movie categories, and the GridView
displays matching movies (see Figure 15.8).
Example 15.27. ShowFilteredMovies.aspx
<%@ Page Language="VB" %> <!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 15.27 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 15.28.
Example 15.28. FilterMovies.vb
Imports System Imports System.Web Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class FilterMovies Private ReadOnly _conString As String Public Function GetMovies() As DataSet ' Initialize DataAdapter Dim commandText As String = "SELECT Title,Director,CategoryId FROM Movies" Dim dad As SqlDataAdapter = New SqlDataAdapter(commandText, _conString) ' Return DataSet Dim dstMovies As New DataSet() Using dad dad.Fill(dstMovies) End Using Return dstMovies End Function Public Function GetMovieCategories() As DataSet ' Initialize DataAdapter Dim commandText As String = "SELECT Id,Name FROM MovieCategories" Dim dad As New SqlDataAdapter(commandText, _conString) ' Return DataSet Dim dstCategories As New DataSet() Using dad dad.Fill(dstCategories) End Using Return dstCategories End Function Public Sub New() _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString End Sub End Class
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.
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.
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 15.29 illustrates how you can do this with the help of a FormView
control and an ObjectDataSource
control (see Figure 15.9).
Example 15.29. ShowGuestbook.aspx
<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> Protected Sub srcGuestbook_Inserting(ByVal sender As Object, ByVal e As ObjectDataSourceMethodEventArgs) e.InputParameters.Add("IPAddress", Request.UserHostAddress) End Sub </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 15.29 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 15.29 is bound to the Guestbook
component in Listing 15.30.
Example 15.30. Guestbook.vb
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class Guestbook Private _conString As String Public Function GetEntries() As SqlDataReader ' Initialize connection Dim con As New SqlConnection(_conString) ' Initialize command Dim cmd As New SqlCommand() cmd.Connection = con cmd.CommandText = "SELECT Id,IPAddress,Comment,EntryDate FROM Guestbook" ' Execute command con.Open() Return cmd.ExecuteReader(CommandBehavior.CloseConnection) End Function Public Sub AddEnTry(ByVal IPAddress As String, ByVal comment As String) ' Initialize connection Dim con As New SqlConnection(_conString) ' Initialize command Dim cmd As 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() End Using End Sub Public Sub New() _conString = WebConfigurationManager.ConnectionStrings("Guestbook").ConnectionString End Sub End Class
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.
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 15.31 handles the Inserting
event to capture any errors raised when the method represented by the ObjectDataSource
control’s InsertMethod
property is called.
Example 15.31. HandleErrors.aspx
<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> Protected Sub srcMovies_Inserted(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs) If Not e.Exception Is Nothing Then e.ExceptionHandled = True lblError.Text = "Could not insert movie" End If End Sub </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 15.31, 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 15.10).
The page in Listing 15.31 is bound to the component in Listing 15.32.
Example 15.32. InsertMovie.vb
Imports System Imports System.Web Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class InsertMovie Private Shared ReadOnly _conString As String Public Shared Function GetMovies() As SqlDataReader ' Initialize connection Dim con As New SqlConnection(_conString) ' Initialize command Dim cmd As New SqlCommand() cmd.Connection = con cmd.CommandText = "SELECT Id,Title,Director FROM Movies" ' Execute command con.Open() Return cmd.ExecuteReader(CommandBehavior.CloseConnection) End Function Public Shared Sub Insert(ByVal title As String, ByVal director As String) ' Initialize connection Dim con As New SqlConnection(_conString) ' Initialize command Dim cmd As 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() End Using End Sub Shared Sub New() _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString End Sub End Class
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.
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 15.33 must be initialized with a movie category parameter. The component returns only movies in the specified category.
Example 15.33. MoviesByCategory.vb
Imports System Imports System.Web Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class MoviesByCategory Private ReadOnly _conString As String Private ReadOnly _movieCategory As String Public Function GetMovies() As SqlDataReader ' Initialize connection Dim con As New SqlConnection(_conString) ' Initialize command Dim cmd As 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) End Function Public Sub New(ByVal movieCategory As String) _movieCategory = movieCategory _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString End Sub End Class
The page in Listing 15.34 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 15.34. ShowAdventureMovies.aspx
<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> Protected Sub srcMovies_ObjectCreating(ByVal sender As Object, ByVal e As ObjectDataSourceEventArgs) Dim movies As New MoviesByCategory("Adventure") e.ObjectInstance = movies End Sub </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.
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 15.35 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 15.35. ShowConflictDetection.aspx
<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> Protected Sub srcMovies_Updated(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs) If Not e.Exception Is Nothing Then e.ExceptionHandled = True lblError.Text = "Could not update record" End If End Sub </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 15.35 is bound to the component in Listing 15.36.
Example 15.36. ConflictedMovies.vb
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class ConflictedMovies Private Shared ReadOnly _conString As String Public Shared Function GetMovies() As SqlDataReader ' Initialize connection Dim con As New SqlConnection(_conString) ' Initialize command Dim cmd As New SqlCommand() cmd.Connection = con cmd.CommandText = "SELECT Id,Title,Director FROM Movies" ' Execute command con.Open() Return cmd.ExecuteReader(CommandBehavior.CloseConnection) End Function Public Shared Sub UpdateMovie(ByVal title As String, ByVal director As String, ByVal original_title As String, ByVal original_director As String, ByVal original_id As Integer) ' Initialize connection Dim con As New SqlConnection(_conString) ' Initialize command Dim cmd As 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() Dim rowsAffected As Integer = cmd.ExecuteNonQuery() If rowsAffected = 0 Then Throw New Exception("Could not update movie record") End If End Using End Sub Shared Sub New() _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString End Sub End Class
The component in Listing 15.36 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.
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).
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 15.37, named the MovieDataSource
control, derives from the base ObjectDataSource
control class. The MovieDataSource
control represents the MoviesComponent
, which is also contained in Listing 15.37.
Example 15.37. MovieDataSource.vb
Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Imports System.Web.UI.WebControls Namespace AspNetUnleashed.Samples Public Class MovieDataSource Inherits ObjectDataSource Public Sub New() Me.TypeName = "AspNetUnleashed.Samples.MoviesComponent" Me.SelectMethod = "GetMovies" End Sub End Class Public Class MoviesComponent Private ReadOnly _conString As String Public Function GetMovies() As SqlDataReader ' Initialize connection Dim con As New SqlConnection(_conString) ' Initialize command Dim cmd As New SqlCommand() cmd.Connection = con cmd.CommandText = "SELECT Title,Director,DateReleased FROM Movies" ' Execute command con.Open() Return cmd.ExecuteReader(CommandBehavior.CloseConnection) End Function Sub New() _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString End Sub End Class End Namespace
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 15.38 illustrates how you can use the MovieDataSource
control in a page (see Figure 15.11).
Example 15.38. ShowMovieDataSource.aspx
<%@ Page Language="VB" %> <%@ 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 15.38. After you register the control, you can simply declare the MovieDataSource
control in the page to represent the contents of the Movies database table.
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 the 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.
The UsernameParameter
class is contained in Listing 15.39. Notice that the class in Listing 15.39 derives from the Parameter
class and overrides the Evaluate()
method of the base class. The Evaluate()
method determines what the parameter represents.
Example 15.39. UsernameParameter.vb
Imports System Imports System.Web Imports System.Web.UI Imports System.Web.UI.WebControls Namespace MyControls Public Class UsernameParameter Inherits Parameter Protected Overrides Function Evaluate(ByVal context As HttpContext, ByVal control As Control) As Object If Not context Is Nothing Then Return context.User.Identity.Name Else Return Nothing End If End Function End Class End Namespace
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 15.40.
Example 15.40. ShowUsernameParameter.aspx
<%@ Page Language="VB" %> <%@ 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 15.12).
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 15.41.
Example 15.41. PagePropertyParameter.vb
Imports System Imports System.Web Imports System.Web.UI Imports System.Web.UI.WebControls Namespace MyControls Public Class PagePropertyParameter Inherits Parameter Private _propertyName As String Protected Overrides Function Evaluate(ByVal context As HttpContext, ByVal control As Control) As Object Return DataBinder.Eval(control.Page, PropertyName) End Function Public Property PropertyName() As String Get Return _propertyName End Get Set(ByVal Value As String) _propertyName = value End Set End Property End Class End Namespace
The component in Listing 15.41 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 15.42 uses the PagePropertyParameter
to represent a property of the page named CurrentUsername
. This property returns the current username.
Example 15.42. ShowPagePropertyParameter.aspx
<%@ Page Language="VB" %> <%@ 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 15.42, 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.
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
.
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.