Using Parameters with the ObjectDataSource Control

The ObjectDataSource control calls business object methods based on the name of the method identified in the SelectMethod, InsertMethod, UpdateMethod, or DeleteMethod property, and based additionally on the parameter names that make up the business object method's signature. When you create methods in a business object, you must ensure that the parameter names and types accepted by the business object method match the parameter names and types that the ObjectDataSource control passes. (Parameter order is not important.)

Like all data source controls, the ObjectDataSource control accepts input parameters at run time and manages them in parameter collections. Each data operation has a related parameter collection. For select operations, you can use the SelectParameters collection; for updates, you can use the UpdateParameters collection; and so on.

You can specify a name, type, direction, and default value for each parameter. Parameters that get values from a specific object, such as a control, session variable, or the user profile, require you to set additional properties. For example, a ControlParameter object requires that you set the ControlID property to identify the control to take the parameter value, and set the PropertyName property to identify the property that contains the parameter value. For more information, see Using Parameters with Data Source Controls for Filtering.

The following code example shows a select method that can be called by an ObjectDataSource control. The method takes a parameter and selects a single record from the data source.

<DataObjectMethod(DataObjectMethodType.Select)> _
Public Shared Function GetEmployee(EmployeeID As Integer) As DataTable

  If Not _initialized Then Initialize()

  Dim conn As SqlConnection  = New SqlConnection(_connectionString)
  Dim da  As SqlDataAdapter  = _
    New SqlDataAdapter("SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode FROM Employees WHERE EmployeeID = @EmployeeID", conn) 
  da.SelectCommand.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID

  Dim ds As DataSet =  New DataSet() 

  Try      
    conn.Open()
    da.Fill(ds, "Employees")
  Catch e As SqlException
    ' Handle exception. 
  Finally
    conn.Close()
  End Try 

  If ds.Tables("Employees") IsNot Nothing Then _
    Return ds.Tables("Employees")

  Return Nothing 
End Function

The ObjectDataSource control determines the method to call for an insert, update, or delete operation based on the InsertParameters, UpdateParameters, and DeleteParameters collections, respectively. Additionally, the ObjectDataSource control will automatically create parameters based on values passed by a data-bound control, such as a GridView or FormView control, that supports automatic update, insert, and delete operations. For more information, see Using Parameters with Data Source Controls for Inserting and Updating.

The following code example shows a method callable by an ObjectDataSource control. The method updates employee information in the Northwind sample database.

<DataObjectMethod(DataObjectMethodType.Update)> _
Public Shared Function UpdateEmployee(EmployeeID As Integer, _
                                      FirstName As String, _
                                      LastName As String, _
                                      Address As String, _
                                      City As String, _
                                      Region As String, _
                                      PostalCode As String) As Boolean 

  If String.IsNullOrEmpty(FirstName) Then _
    Throw New ArgumentException("FirstName cannot be null or an empty string.")
  If String.IsNullOrEmpty(LastName) Then _
    Throw New ArgumentException("LastName cannot be null or an empty string.")

  If Address    Is Nothing Then Address    = String.Empty 
  If City       Is Nothing Then City       = String.Empty 
  If Region     Is Nothing Then Region     = String.Empty 
  If PostalCode Is Nothing Then PostalCode = String.Empty 

  If Not _initialized Then Initialize()

  Dim conn As SqlConnection  = New SqlConnection(_connectionString)
  Dim cmd  As SqlCommand     = New SqlCommand("UPDATE Employees " & _
                                              "  SET FirstName=@FirstName, LastName=@LastName, " & _
                                              "  Address=@Address, City=@City, Region=@Region, " & _
                                              "  PostalCode=@PostalCode " & _
                                              "  WHERE EmployeeID=@EmployeeID", conn)  

  cmd.Parameters.Add("@FirstName",  SqlDbType.VarChar, 10).Value = FirstName
  cmd.Parameters.Add("@LastName",   SqlDbType.VarChar, 20).Value = LastName
  cmd.Parameters.Add("@Address",    SqlDbType.VarChar, 60).Value = Address
  cmd.Parameters.Add("@City",       SqlDbType.VarChar, 15).Value = City
  cmd.Parameters.Add("@Region",     SqlDbType.VarChar, 15).Value = Region
  cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode
  cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = EmployeeID

  Try      
    conn.Open()

    If cmd.ExecuteNonQuery() <> 0 Then _
      Return False 
  Catch e As SqlException
    ' Handle exception. 
  Finally
    conn.Close()
  End Try 

  Return True 
End Function

The code example assumes that the ConflictDetection property of the ObjectDataSource control has been set to OverwriteChanges. If the ConflictDetection property is set to CompareAllValues, the business object method must accept parameters for the original values of the data fields. You can differentiate between parameters for current and original values using the OldValuesParameterFormatString property. You set the OldValuesParameterFormatString property to a string expression that is used to format the names for original-value parameters, where the {0} characters represent the field name. For example, if you set the OldValuesParameterFormatString property to original_{0}, the current value for a field named FirstName would be passed in a parameter named FirstName, and the original value for the field would be passed in a parameter named original_FirstName.

In addition to specifying SelectParameters objects for a Select business object method, you can include parameters for sorting and paging. This enables you to sort data in the data source object as well as restrict the results returned from your data source object to only the requested page of data.

You can specify a sort parameter for a Select business object method using the ObjectDataSource control's SortParameterName property. The SortParameterName property identifies the name of the parameter used to pass sort column names to the business object method. The parameter is of type string.

Certain data-bound controls such as the GridView control can pass sort parameters to the ObjectDataSource control automatically. When a data-bound control that supports sorting is bound to the ObjectDataSource control, the data-bound control passes a sort expression that identifies the data columns to use for sorting the results. For example, the GridView control passes sort values in its SortExpression property. The ObjectDataSource control sets the value of the parameter identified by the SortParameterName property based on the sort expression passed to it. The sort expression can specify more than one column; if so, the column names are separated by commas. To specify a descending sort, the sort expression can include a sort column name followed by the DESC modifier. For example, a sort expression that identifies the LastName and FirstName columns as the columns to use for sorting would be "LastName, FirstName" for an ascending sort and "LastName, FirstName DESC" for a descending sort.

You can specify additional parameters for a Select method that identify a page of data to be returned. The ObjectDataSource control supports two properties that identify paging parameters:

  • The StartRowIndexParameterName property identifies the name of a parameter in the business object's select method that is used to specify the starting row of the page of data.

  • The MaximumRowsParameterName property identifies the name of a parameter in a in the business object's select method that is used to specify the number of rows in the page of data.

Both parameters identified by the StartRowIndexParameterName and MaximumRowsParameterName properties are of type Int32.

The following code example shows an ObjectDataSource control configured to pass sort and page parameters to the Select method of the specified business object:

<asp:ObjectDataSource 
  ID="EmployeesObjectDataSource" 
  runat="server" 
  TypeName="Samples.AspNet.Controls.NorthwindEmployee" 
  SortParameterName="SortColumns"
  EnablePaging="true"
  StartRowIndexParameterName="StartRecord"
  MaximumRowsParameterName="MaxRecords" 
  SelectMethod="GetAllEmployees" >
</asp:ObjectDataSource>

The following code example shows the Select business object method called in the preceding example. The business object method returns a page of data from the Northwind sample database, sorted in the specified order.

Public Shared Sub Initialize()    
  ' Initialize data source. Use "Northwind" connection string from configuration. 

  If ConfigurationManager.ConnectionStrings("Northwind") Is Nothing OrElse _
     ConfigurationManager.ConnectionStrings("Northwind").ConnectionString.Trim() = "" Then       
    Throw New Exception("A connection string named 'Northwind' with a valid connection string " & _
                        "must exist in the <connectionStrings> configuration section for the application.")
  End If

  _connectionString = _
    ConfigurationManager.ConnectionStrings("Northwind").ConnectionString

  _initialized = True 
End Sub 



' Select all employees.

<DataObjectMethod(DataObjectMethodType.Select, True)> _
Public Shared Function GetAllEmployees(sortColumns As String, startRecord As Integer, maxRecords As Integer) As DataTable

  VerifySortColumns(sortColumns)

  If Not _initialized Then Initialize()

  Dim sqlCommand As String = "SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode FROM Employees " 

  If sortColumns.Trim() = "" Then
    sqlCommand &= "ORDER BY EmployeeID" 
  Else
    sqlCommand &= "ORDER BY " & sortColumns
  End If 

  Dim conn As SqlConnection  = New SqlConnection(_connectionString)
  Dim da   As SqlDataAdapter = New SqlDataAdapter(sqlCommand, conn) 

  Dim ds As DataSet =  New DataSet() 

  Try
    conn.Open()
    da.Fill(ds, startRecord, maxRecords, "Employees")
  Catch e As SqlException
    ' Handle exception. 
  Finally      
    conn.Close()
  End Try 

  If ds.Tables("Employees") IsNot Nothing Then _
    Return ds.Tables("Employees")

  Return Nothing 
End Function 


''''' 
' Verify that only valid columns are specified in the sort expression to aSub a SQL Injection attack. 

Private Shared Sub VerifySortColumns(sortColumns As String)

  If sortColumns.ToLowerInvariant().EndsWith(" desc") Then _
    sortColumns = sortColumns.Substring(0, sortColumns.Length - 5)

  Dim columnNames() As String = sortColumns.Split(",")

  For Each columnName As String In columnNames      
    Select Case columnName.Trim().ToLowerInvariant()        
      Case "employeeid" 
      Case "lastname" 
      Case "firstname" 
      Case "" 
      Case Else 
        Throw New ArgumentException("SortColumns contains an invalid column name.")
    End Select 
  Next 
End Sub

By default, all parameters for business object methods are input parameters. If the business object method includes an out parameter that returns a value to the ObjectDataSource control, you must explicitly specify a parameter with a parameter direction as described in Using Parameters with Data Source Controls for Filtering.

The following code example shows an ObjectDataSource control configured to accept an output parameter of type Int32. The out parameter returns an automatically generated primary key value from the method specified by the InsertMethod property.

<asp:ObjectDataSource 
  ID="EmployeeDetailsObjectDataSource" 
  runat="server" 
  TypeName="Samples.AspNet.Controls.NorthwindEmployee" 
  SelectMethod="GetEmployee" 
  UpdateMethod="UpdateEmployee"
  DeleteMethod="DeleteEmployee"
  InsertMethod="InsertEmployee" 
  OnInserted="EmployeeDetailsObjectDataSource_OnInserted" >
  <SelectParameters>
    <asp:Parameter Name="EmployeeID" />  
  </SelectParameters>
  <InsertParameters>
    <asp:Parameter Name="NewEmployeeID" Direction="Output" 
                   Type="Int32" DefaultValue="0" />
  </InsertParameters>
</asp:ObjectDataSource>

The following code example shows the Insert business object method that returns the primary key value as an out parameter:

<DataObjectMethod(DataObjectMethodType.Insert)> _
Public Shared Function InsertEmployee(ByRef NewEmployeeID As Integer, _
                                      FirstName As String, _
                                      LastName As String, _
                                      Address As String, _
                                      City As String, _
                                      Region As String, _
                                      PostalCode As String) As Boolean 

  If String.IsNullOrEmpty(FirstName) Then _
    Throw New ArgumentException("FirstName cannot be null or an empty string.")
  If String.IsNullOrEmpty(LastName) Then _
    Throw New ArgumentException("LastName cannot be null or an empty string.")

  If Address    Is Nothing Then Address    = String.Empty 
  If City       Is Nothing Then City       = String.Empty 
  If Region     Is Nothing Then Region     = String.Empty 
  If PostalCode Is Nothing Then PostalCode = String.Empty 

  If Not _initialized Then Initialize()

  NewEmployeeID = -1

  Dim conn As SqlConnection  = New SqlConnection(_connectionString)
  Dim cmd  As SqlCommand     = New SqlCommand("INSERT INTO Employees " & _ 
                                              "  (FirstName, LastName, Address, City, Region, PostalCode) " & _
                                              "  Values(@FirstName, @LastName, @Address, @City, @Region, @PostalCode) " & _
                                              "SELECT @EmployeeID = SCOPE_IDENTITY()", conn)  

  cmd.Parameters.Add("@FirstName",  SqlDbType.VarChar, 10).Value = FirstName
  cmd.Parameters.Add("@LastName",   SqlDbType.VarChar, 20).Value = LastName
  cmd.Parameters.Add("@Address",    SqlDbType.VarChar, 60).Value = Address
  cmd.Parameters.Add("@City",       SqlDbType.VarChar, 15).Value = City
  cmd.Parameters.Add("@Region",     SqlDbType.VarChar, 15).Value = Region
  cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = PostalCode
  Dim p As SqlParameter = cmd.Parameters.Add("@EmployeeID", SqlDbType.Int)
  p.Direction = ParameterDirection.Output

  Try
    conn.Open()

    cmd.ExecuteNonQuery()

    NewEmployeeID = CInt(p.Value)
  Catch e As SqlException
    ' Handle exception. 
  Finally
    conn.Close()
  End Try 

  Return True 
End Function

By default, all parameters for business object methods are typed as Object. If your business object method includes parameters of different types, you must explicitly specify strongly typed parameters. For details, see Using Parameters with Data Source Controls for Filtering.

Most business object method signatures take parameters of type String and Int32. However, you might be working with a business object method that takes one or more parameters typed as a complex or user-defined type. To work with complex or user-defined parameter types, you can use the ObjectDataSource control's DataObjectTypeName property.

In your business object, creating methods with long parameter lists that map control values one-to-one to data store values can result in code that is not easily reusable. A better practice is to encapsulate your data in a custom class and then pass an instance of the class as a parameter. That way, the data that makes up an instance of the class, such as an employee record, can change without requiring any changes to the public interfaces exposed by the data source object. The following code example shows a class named NorthwindExployee that defines the employee data and that can be passed as a parameter to a business object.

Public Class NorthwindEmployee
    Public Sub New()
    End Sub

    Private _empId As String
    Public Property EmpId() As Integer
        Get
            Return _empId
        End Get
        Set
            _empId = value
        End Set
    End Property 

    Private _firstName As String
    Public Property FirstName() As String
        Get
            Return _firstName
        End Get
        Set
            _firstName = value
        End Set
    End Property

    ' Additional code for the class.
End Class

To accept an instance of the preceding class as a parameter, the business object's UpdateEmployeeInfo method might be defined using the following signature:

Public Sub UpdateEmployeeInfo(emp As NorthwindEmployee)
End Sub

Although you cannot set the Type of a parameter to the name of a custom class, you can set the ObjectDataSource control's DataObjectTypeName property to the name of a custom user-defined type, such as the NorthwindEmployee class, and then pass an instance of the type to a business object data method. To pass user-defined objects to a data source object, the following conditions must be met:

  • The user-defined type must have a default constructor (a constructor that takes no parameters).

  • The user-defined type must define public properties whose names match those of the dictionary entries passed to the data source control from data-bound controls such as GridView and DetailsView. For details about these dictionaries, see Using Parameters with Data Source Controls for Filtering.

  • The data source object's public properties must expose both get and set accessors.

The following example shows an ObjectDataSource control that performs an update operation by calling the UpdateEmployeeInfo method of a business object named EmployeeLogic. The ObjectDataSource control is configured to pass an instance of the NorthwindEmployee class to the update method.

<asp:objectdatasource
  runat="server"
  id="ObjectDataSource1"
  typename="EmployeeLogic"
  selectmethod="GetAllEmployees"
  updatemethod="UpdateEmployeeInfo"
  dataobjecttypename="NorthwindEmployee" />

In some cases, the business object method will have a parameter list that contains multiple complex parameter types. In that case, you can use the ObjectDataSource control, but you must add your parameters to the ObjectDataSource control programmatically. To do this, handle the event that is raised before the data operation is performed, such as the Inserting, Updating, or Deleting event, and set values in the InputParameters collection exposed by the ObjectDataSourceMethodEventArgs class.

Show: