Using Parameters with the ObjectDataSource Control


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 controlssuch as the GridView, DetailsView, and FormView controlscan 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.

Listing 15.9. Movies.vb

[View full width]

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.

Listing 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  runat="server">     <title>Show Movies</title> </head> <body>     <form  runat="server">     <div>     <asp:GridView                  DataSource         DataKeyNames="Id"         AutoGenerateEditButton="true"         Runat="server" />     <asp:ObjectDataSource                  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.

Listing 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  runat="server">     <title>Show Movies</title> </head> <body>     <form  runat="server">     <div>     <asp:GridView                  DataSource         DataKeyNames="Id"         AutoGenerateEditButton="true"         Runat="server" />     <asp:ObjectDataSource                  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 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).

Figure 15.3. Displaying movies by category.


Listing 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  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  runat="server">     <div>     <asp:DropDownList                  DataSource         DataTextField="Name"         DataValueField="Id"         ToolTip="Movie Category"         Runat="server" />     <asp:Button                  Text="Select"         Runat="server" />     <asp:GridView                  DataSource         Css         GridLines="None"         Runat="server" />     <asp:ObjectDataSource                  TypeName="MovieCategories"         SelectMethod="GetCategories"         Runat="server" />     <asp:ObjectDataSource                  TypeName="MovieCategories"         SelectMethod="GetMovies"         Runat="server">         <SelectParameters>         <asp:ControlParameter             Name="CategoryId"             Control />        </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.

Listing 15.13. MovieCategories.vb

[View full width]

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 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 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.

Listing 15.14. EmployeeData.vb

[View full width]

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.

Listing 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  runat="server">     <title>Update Employees</title> </head> <body>     <form  runat="server">     <div>     <asp:DetailsView          DataSource         DataKeyNames="Id"         AutoGenerateRows="True"         AutoGenerateEditButton="True"         AllowPaging="true"         Runat="server" />     <asp:ObjectDataSource                  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.

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 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."




ASP. NET 2.0 Unleashed
ASP.NET 2.0 Unleashed
ISBN: 0672328232
EAN: 2147483647
Year: 2006
Pages: 276

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net