Export (0) Print
Expand All

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.

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 static DataTable GetEmployee(int EmployeeID)
{
  if (!_initialized) { Initialize(); }

  SqlConnection conn = new SqlConnection(_connectionString);
  SqlDataAdapter da  = 
    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;

  DataSet ds =  new DataSet(); 

  try
  {
    conn.Open();
    da.Fill(ds, "Employees");
  }
  catch (SqlException e)
  {
    // Handle exception.
  }
  finally
  {
    conn.Close();
  }

  if (ds.Tables["Employees"] != null)
    return ds.Tables["Employees"];

  return null;
}


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 How a Data Source Control Creates Parameters for Data-bound Fields.

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 static bool UpdateEmployee(int EmployeeID, string FirstName, string LastName, 
                                  string Address, string City, string Region, string PostalCode)
{
  if (String.IsNullOrEmpty(FirstName))
    throw new ArgumentException("FirstName cannot be null or an empty string.");
  if (String.IsNullOrEmpty(LastName))
    throw new ArgumentException("LastName cannot be null or an empty string.");

  if (Address    == null) { Address    = String.Empty; }
  if (City       == null) { City       = String.Empty; }
  if (Region     == null) { Region     = String.Empty; }
  if (PostalCode == null) { PostalCode = String.Empty; }

  if (!_initialized) { Initialize(); }

  SqlConnection conn = new SqlConnection(_connectionString);
  SqlCommand    cmd  = 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)
      return false;
  }
  catch (SqlException e)
  {
    // Handle exception.
  }
  finally
  {
    conn.Close();
  }

  return true;
}


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.

Identifying a Sort Parameter

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.

Identifying Paging Parameters

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 static void Initialize()
{
  // Initialize data source. Use "Northwind" connection string from configuration.

  if (ConfigurationManager.ConnectionStrings["Northwind"] == null ||
      ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString.Trim() == "")
  {
    throw new Exception("A connection string named 'Northwind' with a valid connection string " + 
                        "must exist in the <connectionStrings> configuration section for the application.");
  }

  _connectionString = 
    ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

  _initialized = true;
}


// Select all employees.

[DataObjectMethod(DataObjectMethodType.Select, true)]
public static DataTable GetAllEmployees(string sortColumns, int startRecord, int maxRecords)
{
  VerifySortColumns(sortColumns);

  if (!_initialized) { Initialize(); }

  string sqlCommand = "SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode FROM Employees ";

  if (sortColumns.Trim() == "")
    sqlCommand += "ORDER BY EmployeeID";
  else
    sqlCommand += "ORDER BY " + sortColumns;

  SqlConnection conn = new SqlConnection(_connectionString);
  SqlDataAdapter da  = new SqlDataAdapter(sqlCommand, conn); 

  DataSet ds =  new DataSet(); 

  try
  {
    conn.Open();
    da.Fill(ds, startRecord, maxRecords, "Employees");
  }
  catch (SqlException e)
  {
    // Handle exception.
  }
  finally
  {
    conn.Close();
  }

  if (ds.Tables["Employees"] != null)
    return ds.Tables["Employees"];

  return null;
}


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

private static void VerifySortColumns(string sortColumns)
{
  if (sortColumns.ToLowerInvariant().EndsWith(" desc"))
    sortColumns = sortColumns.Substring(0, sortColumns.Length - 5);

  string[] columnNames = sortColumns.Split(',');

  foreach (string columnName in columnNames)
  {
    switch (columnName.Trim().ToLowerInvariant())
    {
      case "employeeid":
        break;
      case "lastname":
        break;
      case "firstname":
        break;
      case "":
        break;
      default:
        throw new ArgumentException("SortColumns contains an invalid column name.");
        break;
    }
  }
}


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.

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 static bool InsertEmployee(out int NewEmployeeID, string FirstName, string LastName, 
                                  string Address, string City, string Region, string PostalCode)
{
  if (String.IsNullOrEmpty(FirstName))
    throw new ArgumentException("FirstName cannot be null or an empty string.");
  if (String.IsNullOrEmpty(LastName))
    throw new ArgumentException("LastName cannot be null or an empty string.");

  if (Address    == null) { Address    = String.Empty; }
  if (City       == null) { City       = String.Empty; }
  if (Region     == null) { Region     = String.Empty; }
  if (PostalCode == null) { PostalCode = String.Empty; }

  if (!_initialized) { Initialize(); }

  NewEmployeeID = -1;

  SqlConnection conn = new SqlConnection(_connectionString);
  SqlCommand    cmd  = 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;
  SqlParameter p = cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
  p.Direction = ParameterDirection.Output;

  try
  {
    conn.Open();

    cmd.ExecuteNonQuery();

    NewEmployeeID = (int)p.Value;
  }
  catch (SqlException e)
  {
    // Handle exception.
  }
  finally
  {
    conn.Close();
  }

  return true;
}


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.

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 NorthwindEmployee() { }
    private int _empId;
    private string _firstName;
    public int EmpId {
      get { return _empId; }
      set { _empId = value; }
    }
    public string FirstName {
      get { return _firstName; }
      set { _firstName = value; }
    }
    // Additional code for the 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 void UpdateEmployeeInfo(NorthwindEmployee emp) {
}

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.

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

Community Additions

ADD
Show:
© 2014 Microsoft