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.)
Using Parameters
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 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
[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;
}
Passing Parameters to Insert, Update, and Delete Methods
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 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
[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.
Identifying Parameters for Sorting and Paging
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>
<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
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;
}
}
}
Parameter Direction
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>
<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
[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;
}
Parameter Data Types
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.
Passing Custom Parameter Types
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.
}
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 void UpdateEmployeeInfo(NorthwindEmployee emp) {
}
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.
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.