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