DataAdapter 参数DataAdapter Parameters

DbDataAdapter 具有四个用于从数据源检索数据和更新数据源中数据的属性:SelectCommand 属性返回数据源中的数据;InsertCommandUpdateCommandDeleteCommand 属性用于管理数据源中的更改。The DbDataAdapter has four properties that are used to retrieve data from and update data to the data source: the SelectCommand property returns data from the data source; and the InsertCommand , UpdateCommand, and DeleteCommand properties are used to manage changes at the data source. 调用 SelectCommandFill 方法之前必须设置 DataAdapter 属性。The SelectCommand property must be set before you call the Fill method of the DataAdapter. 在调用 InsertCommandUpdateCommand 方法之前必须设置 DeleteCommandUpdateDataAdapter 属性,具体取决于对 DataTable 中的数据做了哪些更改。The InsertCommand, UpdateCommand, or DeleteCommand properties must be set before the Update method of the DataAdapter is called, depending on what changes were made to the data in the DataTable. 例如,如果已添加行,在调用 InsertCommand 之前必须设置 UpdateFor example, if rows have been added, the InsertCommand must be set before you call Update. Update 正在处理已插入、已更新或已删除的行时,DataAdapter 将使用相应的 Command 属性来处理该操作。When Update is processing an inserted, updated, or deleted row, the DataAdapter uses the respective Command property to process the action. 有关已修改行的当前信息将通过 Command 集合传递到 Parameters 对象。Current information about the modified row is passed to the Command object through the Parameters collection.

在更新的行的数据源时,可调用 UPDATE 语句,使用的唯一标识符来标识要更新的表中的行。When you update a row at the data source, you call the UPDATE statement, which uses a unique identifier to identify the row in the table to be updated. 该唯一标识符通常是主键字段的值。The unique identifier is typically the value of a primary key field. UPDATE 语句使用的参数既包含唯一标识符又包含要更新的列和值,如下面的 Transact-SQL 语句所示。The UPDATE statement uses parameters that contain both the unique identifier and the columns and values to be updated, as shown in the following Transact-SQL statement.

UPDATE Customers SET CompanyName = @CompanyName   
  WHERE CustomerID = @CustomerID  

备注

参数占位符的语法取决于数据源。The syntax for parameter placeholders depends on the data source. 此示例显示 SQL Server 数据源的占位符。This example shows placeholders for a SQL Server data source. 使用问号 (?) 占位符代表 System.Data.OleDbSystem.Data.Odbc 参数。Use question mark (?) placeholders for System.Data.OleDb and System.Data.Odbc parameters.

在此 Visual Basic 示例中,CompanyName字段已更新的值@CompanyName行的参数位置CustomerID等于的值@CustomerID参数。In this Visual Basic example, the CompanyName field is updated with the value of the @CompanyName parameter for the row where CustomerID equals the value of the @CustomerID parameter. 检索信息从已修改的行中使用的参数SourceColumn属性的SqlParameter对象。The parameters retrieve information from the modified row using the SourceColumn property of the SqlParameter object. 下面是上一示例 UPDATE 语句的参数。The following are the parameters for the previous sample UPDATE statement. 代码假定变量 adapter 表示有效的 SqlDataAdapter 对象。The code assumes that the variable adapter represents a valid SqlDataAdapter object.

adapter.Parameters.Add( _  
  "@CompanyName", SqlDbType.NChar, 15, "CompanyName")  
Dim parameter As SqlParameter = _  
  adapter.UpdateCommand.Parameters.Add("@CustomerID", _  
  SqlDbType.NChar, 5, "CustomerID")  
parameter.SourceVersion = DataRowVersion.Original  

Add 集合的 Parameters 方法接受参数的名称、数据类型、大小(如果适用于该类型)以及 SourceColumn 中的 DataTable 的名称。The Add method of the Parameters collection takes the name of the parameter, the data type, the size (if applicable to the type), and the name of the SourceColumn from the DataTable. 请注意,SourceVersion 参数的 @CustomerID 设置为 OriginalNotice that the SourceVersion of the @CustomerID parameter is set to Original. 这样可以保证,如果标识列的值已经在修改后的 DataRow 中被更改,就一定会更新数据源中的现有行。This guarantees that the existing row in the data source is updated if the value of the identifying column or columns has been changed in the modified DataRow. 在这种情况下,Original 行值将匹配数据源中的当前值,而 Current 行值将包含更新的值。In that case, the Original row value would match the current value at the data source, and the Current row value would contain the updated value. 没有设置 SourceVersion 参数的 @CompanyName,而将使用默认的 Current 行值。The SourceVersion for the @CompanyName parameter is not set and uses the default, Current row value.

备注

两个Fill的操作DataAdapterGet方法的DataReader,从.NET Framework 数据提供程序返回的类型推断的.NET Framework 类型。For both the Fill operations of the DataAdapter and the Get methods of the DataReader, the .NET Framework type is inferred from the type returned from the .NET Framework data provider. 中所述的.NET Framework 的推断的类型和 Microsoft SQL Server、 OLE DB 和 ODBC 数据类型的访问器方法ADO.NET 中的数据类型映射The inferred .NET Framework types and accessor methods for Microsoft SQL Server, OLE DB, and ODBC data types are described in Data Type Mappings in ADO.NET.

Parameter.SourceColumn,Parameter.SourceVersionParameter.SourceColumn, Parameter.SourceVersion

SourceColumnSourceVersion 可以作为自变量传递给 Parameter 构造函数,也可以设置为现有 Parameter 的属性。The SourceColumn and SourceVersion may be passed as arguments to the Parameter constructor, or set as properties of an existing Parameter. SourceColumn 是将要从中检索 DataColumn 值的 DataRow 中的 Parameter 的名称。The SourceColumn is the name of the DataColumn from the DataRow where the value of the Parameter will be retrieved. SourceVersion 指定 DataRow 用于检索该值的 DataAdapter 版本。The SourceVersion specifies the DataRow version that the DataAdapter uses to retrieve the value.

下表显示可以与 DataRowVersion 一起使用的 SourceVersion 枚举值。The following table shows the DataRowVersion enumeration values available for use with SourceVersion.

DataRowVersion 枚举DataRowVersion Enumeration 描述Description
Current 该参数使用列的当前值。The parameter uses the current value of the column. 这是默认设置。This is the default.
Default 该参数使用列的 DefaultValueThe parameter uses the DefaultValue of the column.
Original 该参数使用列的原始值。The parameter uses the original value of the column.
Proposed 该参数使用建议值。The parameter uses a proposed value.

下一节中的 SqlClient 代码示例为 UpdateCommand 定义了一个参数,在该示例中 CustomerID 列用作以下两个参数的 SourceColumn@CustomerID (SET CustomerID = @CustomerID) 和 @OldCustomerID (WHERE CustomerID = @OldCustomerID)。The SqlClient code example in the next section defines a parameter for an UpdateCommand in which the CustomerID column is used as a SourceColumn for two parameters: @CustomerID (SET CustomerID = @CustomerID), and @OldCustomerID (WHERE CustomerID = @OldCustomerID). @CustomerID参数用于更新CustomerID列中的当前值DataRowThe @CustomerID parameter is used to update the CustomerID column to the current value in the DataRow. 因此, CustomerID SourceColumnSourceVersionCurrent使用。As a result, the CustomerID SourceColumn with a SourceVersion of Current is used. @OldCustomerID参数用于标识数据源中的当前行。The @OldCustomerID parameter is used to identify the current row in the data source. 由于在该行的 Original 版本中找到了匹配列值,所以将使用 SourceColumnCustomerID 的相同 SourceVersion (Original)。Because the matching column value is found in the Original version of the row, the same SourceColumn (CustomerID) with a SourceVersion of Original is used.

使用 SqlClient 参数Working with SqlClient Parameters

下面的示例演示如何创建 SqlDataAdapter 并将 MissingSchemaAction 设置为 AddWithKey,以便从数据库中检索其他架构信息。The following example demonstrates how to create a SqlDataAdapter and set the MissingSchemaAction to AddWithKey in order to retrieve additional schema information from the database. SelectCommandInsertCommandUpdateCommandDeleteCommand 属性集及其相应的 SqlParameter 对象已添加到 Parameters 集合。The SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties set and their corresponding SqlParameter objects added to the Parameters collection. 该方法返回一个 SqlDataAdapter 对象。The method returns a SqlDataAdapter object.

public static SqlDataAdapter CreateSqlDataAdapter(SqlConnection connection)
{
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

    // Create the commands.
    adapter.SelectCommand = new SqlCommand(
        "SELECT CustomerID, CompanyName FROM CUSTOMERS", connection);
    adapter.InsertCommand = new SqlCommand(
        "INSERT INTO Customers (CustomerID, CompanyName) " +
        "VALUES (@CustomerID, @CompanyName)", connection);
    adapter.UpdateCommand = new SqlCommand(
        "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
        "WHERE CustomerID = @oldCustomerID", connection);
    adapter.DeleteCommand = new SqlCommand(
        "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection);

    // Create the parameters.
    adapter.InsertCommand.Parameters.Add("@CustomerID", 
        SqlDbType.Char, 5, "CustomerID");
    adapter.InsertCommand.Parameters.Add("@CompanyName", 
        SqlDbType.VarChar, 40, "CompanyName");

    adapter.UpdateCommand.Parameters.Add("@CustomerID", 
        SqlDbType.Char, 5, "CustomerID");
    adapter.UpdateCommand.Parameters.Add("@CompanyName", 
        SqlDbType.VarChar, 40, "CompanyName");
    adapter.UpdateCommand.Parameters.Add("@oldCustomerID", 
        SqlDbType.Char, 5, "CustomerID").SourceVersion = 
        DataRowVersion.Original;

    adapter.DeleteCommand.Parameters.Add("@CustomerID", 
        SqlDbType.Char, 5, "CustomerID").SourceVersion = 
        DataRowVersion.Original;

    return adapter;
}
Public Function CreateSqlDataAdapter( _
    ByVal connection As SqlConnection) As SqlDataAdapter

    Dim adapter As SqlDataAdapter = New SqlDataAdapter
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

    ' Create the commands.
    adapter.SelectCommand = New SqlCommand( _
        "SELECT CustomerID, CompanyName FROM CUSTOMERS", connection)
    adapter.InsertCommand = New SqlCommand( _
        "INSERT INTO Customers (CustomerID, CompanyName) " & _
         "VALUES (@CustomerID, @CompanyName)", connection)
    adapter.UpdateCommand = New SqlCommand( _
        "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = " & _
        "@CompanyName WHERE CustomerID = @oldCustomerID", connection)
    adapter.DeleteCommand = New SqlCommand( _
        "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection)

    ' Create the parameters.
    adapter.InsertCommand.Parameters.Add("@CustomerID", _
        SqlDbType.Char, 5, "CustomerID")
    adapter.InsertCommand.Parameters.Add("@CompanyName", _
        SqlDbType.VarChar, 40, "CompanyName")

    adapter.UpdateCommand.Parameters.Add("@CustomerID", _
        SqlDbType.Char, 5, "CustomerID")
    adapter.UpdateCommand.Parameters.Add("@CompanyName", _
        SqlDbType.VarChar, 40, "CompanyName")
    adapter.UpdateCommand.Parameters.Add("@oldCustomerID", _
        SqlDbType.Char, 5, "CustomerID").SourceVersion = _
        DataRowVersion.Original

    adapter.DeleteCommand.Parameters.Add("@CustomerID", _
        SqlDbType.Char, 5, "CustomerID").SourceVersion = _
        DataRowVersion.Original

    Return adapter
End Function

OleDb 参数占位符OleDb Parameter Placeholders

对于 OleDbDataAdapter 对象和 OdbcDataAdapter 对象,必须使用问号 (?) 占位符来标识参数。For the OleDbDataAdapter and OdbcDataAdapter objects, you must use question mark (?) placeholders to identify the parameters.

Dim selectSQL As String = _  
  "SELECT CustomerID, CompanyName FROM Customers " & _  
  "WHERE CountryRegion = ? AND City = ?"  
Dim insertSQL AS String = _  
  "INSERT INTO Customers (CustomerID, CompanyName) VALUES (?, ?)"  
Dim updateSQL AS String = _  
  "UPDATE Customers SET CustomerID = ?, CompanyName = ? " & _  
  WHERE CustomerID = ?"  
Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = ?"  
string selectSQL =   
  "SELECT CustomerID, CompanyName FROM Customers " +  
  "WHERE CountryRegion = ? AND City = ?";  
string insertSQL =   
  "INSERT INTO Customers (CustomerID, CompanyName) " +  
  "VALUES (?, ?)";  
string updateSQL =   
  "UPDATE Customers SET CustomerID = ?, CompanyName = ? " +  
  "WHERE CustomerID = ? ";  
string deleteSQL = "DELETE FROM Customers WHERE CustomerID = ?";  

参数化查询语句定义必须创建的输入和输出参数。The parameterized query statements define which input and output parameters must be created. 若要创建参数,请使用 Parameters.Add 方法或 Parameter 构造函数来指定列名称、数据类型和大小。To create a parameter, use the Parameters.Add method or the Parameter constructor to specify the column name, data type, and size. 对于内部数据类型(如 Integer)无需包含大小,也可以指定默认大小。For intrinsic data types, such as Integer, you do not have to include the size, or you can specify the default size.

下面的代码示例创建 SQL 语句的参数,然后填充 DataSetThe following code example creates the parameters for a SQL statement and then fills a DataSet.

OleDb 示例OleDb Example

' Assumes that connection is a valid OleDbConnection object.  
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter   
  
Dim selectCMD AS OleDbCommand = New OleDbCommand(selectSQL, connection)  
adapter.SelectCommand = selectCMD  
  
' Add parameters and set values.  
selectCMD.Parameters.Add( _  
  "@CountryRegion", OleDbType.VarChar, 15).Value = "UK"  
selectCMD.Parameters.Add( _  
  "@City", OleDbType.VarChar, 15).Value = "London"  
  
Dim customers As DataSet = New DataSet  
adapter.Fill(customers, "Customers")  
// Assumes that connection is a valid OleDbConnection object.  
OleDbDataAdapter adapter = new OleDbDataAdapter();  
  
OleDbCommand selectCMD = new OleDbCommand(selectSQL, connection);  
adapter.SelectCommand = selectCMD;  
  
// Add parameters and set values.  
selectCMD.Parameters.Add(  
  "@CountryRegion", OleDbType.VarChar, 15).Value = "UK";  
selectCMD.Parameters.Add(  
  "@City", OleDbType.VarChar, 15).Value = "London";  
  
DataSet customers = new DataSet();  
adapter.Fill(customers, "Customers");  

Odbc 参数Odbc Parameters

' Assumes that connection is a valid OdbcConnection object.  
Dim adapter As OdbcDataAdapter = New OdbcDataAdapter  
  
Dim selectCMD AS OdbcCommand = New OdbcCommand(selectSQL, connection)  
adapter.SelectCommand = selectCMD  
  
' Add Parameters and set values.  
selectCMD.Parameters.Add("@CountryRegion", OdbcType.VarChar, 15).Value = "UK"  
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London"  
  
Dim customers As DataSet = New DataSet  
adapter.Fill(customers, "Customers")  
// Assumes that connection is a valid OdbcConnection object.  
OdbcDataAdapter adapter = new OdbcDataAdapter();  
  
OdbcCommand selectCMD = new OdbcCommand(selectSQL, connection);  
adapter.SelectCommand = selectCMD;  
  
//Add Parameters and set values.  
selectCMD.Parameters.Add("@CountryRegion", OdbcType.VarChar, 15).Value = "UK";  
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London";  
  
DataSet customers = new DataSet();  
adapter.Fill(customers, "Customers");  

备注

如果未为参数提供参数名称,该参数提供参数的默认名称N 使用从"Parameter1"开始。If a parameter name is not supplied for a parameter, the parameter is given an incremental default name of ParameterN , starting with "Parameter1". 我们建议避免使用 ParameterN命名约定时提供参数名称,因为所提供的名称可能与中现有的默认参数名称发生冲突ParameterCollectionWe recommend that you avoid the ParameterN naming convention when you supply a parameter name, because the name that you supply might conflict with an existing default parameter name in the ParameterCollection. 如果提供的名称已经存在,将引发异常。If the supplied name already exists, an exception is thrown.

请参阅See also