对 ObjectDataSource 控件使用参数
更新：2007 年 11 月
ObjectDataSource 控件基于 SelectMethod、InsertMethod、UpdateMethod 或 DeleteMethod 属性中所标识的方法名称以及组成业务对象方法签名的参数名来调用业务对象方法。在业务对象中创建方法时，必须确保业务对象方法所接受的参数名和类型与 ObjectDataSource 控件传递的参数名和类型匹配。（参数顺序并不重要。）
使用参数
同所有的数据源控件一样，ObjectDataSource 控件在运行时接受输入参数，并在参数集合中对参数进行管理。每一项数据操作都有一个相关的参数集合。对于选择操作，可以使用 SelectParameters 集合；对于更新操作，可以使用 UpdateParameters 集合，依此类推。
可以为每个参数指定名称、类型、方向和默认值。对于从特定对象（例如，控件、会话变量或用户配置文件）获取值的参数，需要设置其他属性。例如，对于 ControlParameter 对象，需要设置 ControlID 属性以标识要获取参数值的控件，以及设置 PropertyName 属性以标识包含参数值的属性。有关更多信息，请参见对数据源控件使用参数。
下面的代码示例演示一个可以通过 ObjectDataSource 控件调用的 Select 方法。该方法从数据源获取参数并选择单个记录。
<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;
}
为 Insert、Update 和 Delete 方法传递参数
ObjectDataSource 控件基于 InsertParameters、UpdateParameters 和 DeleteParameters 集合，分别确定要为插入、更新或删除操作调用的方法。另外，ObjectDataSource 控件将基于支持自动更新、插入和删除操作的数据绑定控件（例如 GridView 或 FormView 控件）所传递的值自动创建参数。有关更多信息，请参见数据源控件如何为数据绑定字段创建参数。
下面的代码示例演示一个可由 ObjectDataSource 控件调用的方法。该方法可以更新 Northwind 示例数据库中的雇员信息。
<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;
}
该代码示例假定已将 ObjectDataSource 控件的 ConflictDetection 属性设置为 OverwriteChanges。如果 ConflictDetection 属性设置为 CompareAllValues，业务对象方法必须接受数据字段原始值的参数。可以使用 OldValuesParameterFormatString 属性来区分当前值和原始值的参数。将 OldValuesParameterFormatString 属性设置为一个字符串表达式，该表达式用于设置原始值参数名称的格式，其中 {0} 字符表示字段名称。例如，如果将 OldValuesParameterFormatString 属性设置为 original_{0}，名为 FirstName 的字段的当前值将由一个名为 FirstName 的参数传入，该字段的原始值将由一个名为 original_FirstName 的参数传入。
标识排序和分页参数
除了为 Select 业务对象方法指定 SelectParameters 对象之外，还可以包括排序和分页参数。这样您就可以对数据源对象中的数据进行排序，并将从数据源对象返回的结果限定为只返回所请求的数据页。
标识排序参数
使用 ObjectDataSource 控件的 SortParameterName 属性，可以为 Select 业务对象方法指定排序参数。SortParameterName 属性标识用于向业务对象方法传递排序列名称的参数名称。该参数为类型字符串。
某些数据绑定控件（例如 GridView 控件）可以自动向 ObjectDataSource 控件传递排序参数。在将支持排序的数据绑定控件绑定到 ObjectDataSource 控件时，该数据绑定控件将传递一个排序表达式，标识要用于对结果进行排序的数据列。例如，GridView 控件通过其 SortExpression 属性传递排序值。ObjectDataSource 控件基于为其传递的排序表达式，设置由 SortParameterName 属性所标识的参数的值。排序表达式可指定一个以上的列；指定多个列时，列名以逗号分隔。若要指定按降序排序，可以在排序表达式的排序列名称后加上 DESC 修饰符。例如，对于将 LastName 和 FirstName 列标识为排序依据列的排列表达式，使用“LastName, FirstName”可以按升序排序，使用“LastName, FirstName DESC”可以按降序排序。
标识分页参数
可以为 Select 方法指定其他参数，标识要返回的数据页。ObjectDataSource 控件支持两个标识分页参数的属性：
StartRowIndexParameterName 属性，标识在业务对象的 Select 方法中用于指定数据页起始行的参数的名称。
MaximumRowsParameterName 属性，标识在业务对象的 Select 方法中用于指定数据页中的行数的参数名称。
StartRowIndexParameterName 和 MaximumRowsParameterName 属性所标识的参数都是 Int32 类型。
下面的代码示例演示一个 ObjectDataSource 控件，该控件配置为向指定业务对象的 Select 方法传递排序和分页参数：
<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>
下面的代码示例演示在上述示例中调用的 Select 业务对象方法。该业务对象方法从 Northwind 示例数据库中返回数据页，并按指定顺序排序。
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;
}
}
}
参数方向
默认情况下，业务对象方法的所有参数都是输入参数。如果业务对象方法包含向 ObjectDataSource 控件返回值的输出参数，必须显式指定参数和参数方向，如对数据源控件使用参数 中所述。
下面的代码示例演示一个配置为接受 Int32 类型的输出参数的 ObjectDataSource 控件。out 参数返回一个由 InsertMethod 属性所指定的方法自动生成的主键值。
<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>
下面的代码示例演示以输出参数的形式返回主键值的 Insert 业务对象方法。
<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;
}
参数数据类型
默认情况下，业务对象方法的所有参数都是 Object 类型。如果业务对象方法包含不同类型的参数，则必须显式指定强类型参数。有关详细信息，请参见对数据源控件使用参数。
传递自定义参数类型
大多数业务对象方法签名都采用 String 和 Int32 类型的参数。但是，可能会使用采用一个或多个复杂类型或用户定义类型的参数的业务对象方法。若要使用复杂参数类型或用户定义参数类型，可以使用 ObjectDataSource 控件的 DataObjectTypeName 属性。
在业务对象中创建方法时，如果采用很长的参数列表将控件值一对一映射到数据存储值，这样生成的代码可重用性很差。比较好的做法是将数据封装在自定义类中，然后将类的实例作为参数传递。采用该方法，可以更改组成类的实例的数据（例如雇员记录），而无需对由数据源对象公开的公共接口进行任何更改。下面的代码示例演示一个名为 NorthwindExployee 的类，该类定义雇员数据，并可作为参数传递给业务对象。
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
若要作为参数接受上述类的实例，必须使用以下签名来定义业务对象的 UpdateEmployeeInfo 方法：
public void UpdateEmployeeInfo(NorthwindEmployee emp) {
}
Public Sub UpdateEmployeeInfo(emp As NorthwindEmployee)
End Sub
尽管无法将参数的 Type 设置为自定义类的名称，但是可以将 ObjectDataSource 控件的 DataObjectTypeName 属性设置为自定义的用户定义类型的名称（例如 NorthwindEmployee 类），然后将该类型的实例传递给业务对象数据方法。若要将用户定义的对象传递给数据源对象，必须满足下列条件：
用户定义的类型必须具有默认构造函数（不采用任何参数的构造函数）。
用户定义的类型必须定义相应的公共属性，这些公共属性的名称与从数据绑定控件（例如 GridView 和 DetailsView）传递给数据源控件的字典项的名称匹配。有关这些字典的详细信息，请参见 对数据源控件使用参数。
数据源对象的公共属性必须公开 get 和 set 访问器。
下面的示例演示了一个 ObjectDataSource 控件，该控件通过调用名为 EmployeeLogic 的业务对象的 UpdateEmployeeInfo 方法执行更新操作。ObjectDataSource 控件配置为向 update 方法传递一个 NorthwindEmployee 类的实例。
<asp:objectdatasource
runat="server"
id="ObjectDataSource1"
typename="EmployeeLogic"
selectmethod="GetAllEmployees"
updatemethod="UpdateEmployeeInfo"
dataobjecttypename="NorthwindEmployee" />
在某些情况下，业务对象方法会使用一个包含多个复杂参数类型的参数列表。在此情况下，可以使用 ObjectDataSource 控件，但必须以编程方式向 ObjectDataSource 控件添加参数。为此，必须在执行数据操作之前对引发的事件（例如 Inserting、Updating 或 Deleting 事件）进行处理，并在由 ObjectDataSourceMethodEventArgs 类公开的 InputParameters 集合中设置值。