Utilizar parámetros con DataAdapter
DataAdapter dispone de cuatro propiedades que se usan para recuperar y actualizar datos en un origen de datos. La propiedad SelectCommand devuelve datos a partir del origen de datos. Las propiedades InsertCommand, UpdateCommand y DeleteCommand se utilizan para realizar cambios en el origen de datos. La propiedad SelectCommand se debe establecer antes de llamar al método Fill de DataAdapter. Las propiedades InsertCommand, UpdateCommand o DeleteCommand deben establecerse antes de llamar al método Update de DataAdapter, en función de los cambios realizados en los datos de DataSet. Por ejemplo, si se han agregado filas, se debe establecer la propiedad InsertCommand antes de llamar a Update. Cuando Update procesa una fila insertada, actualizada o eliminada, DataAdapter utiliza la propiedad Command adecuada para la acción en cuestión. La información actual relacionada con la fila modificada se pasa al objeto Command a través de la colección Parameters.
Por ejemplo, al actualizar una fila en el origen de datos, se llama a la instrucción UPDATE que utiliza un identificador único para identificar la fila de la tabla que debe actualizarse. El identificador único suele ser el valor del campo de clave principal. La instrucción UPDATE utiliza parámetros que contienen el identificador único y las columnas y valores que se van a actualizar, como muestra la siguiente instrucción SQL.
UPDATE Customers SET CompanyName = @CompanyName WHERE CustomerID = @CustomerID
En este ejemplo, el campo CompanyName se actualiza con el valor del parámetro @CompanyName de la fila cuyo CustomerID coincida con el valor del parámetro @CustomerID. Los parámetros recuperan información de la fila modificada mediante la propiedad SourceColumn del objeto Parameter. A continuación se muestran los parámetros del ejemplo anterior de la instrucción UPDATE.
custDA.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName")
Dim myParm As SqlParameter = custDA.UpdateCommand.Parameters.Add("@CustomerID", _
SqlDbType.NChar, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
El método Add de la colección Parameters toma de DataTable el nombre del parámetro, el tipo específico de DataAdapter, el tamaño (si corresponde al tipo) y el nombre de SourceColumn. Observe que el valor de SourceVersion del parámetro @CustomerID está establecido a Original. De esta forma se garantiza que la fila existente en el origen de datos se actualiza cuando el valor de la columna o columnas identificadas ha cambiado en la fila DataRow modificada. En ese caso, el valor de la fila Original coincidiría con el valor actual en el origen de datos y el valor de la fila Current contendría el valor actualizado. No se asigna ningún valor a SourceVersion para el parámetro @CompanyName por lo que se usa el predeterminado, el de la fila Current.
En el ejemplo siguiente se muestran instrucciones SQL de ejemplo que se pueden usar como CommandText para las propiedades SelectCommand, InsertCommand, UpdateCommand y DeleteCommand de DataAdapter. En el caso de los objetos OleDbDataAdapter y OdbcDataAdapter, debe usar signos de interrogación de cierre (?) como marcadores de posición para identificar los parámetros. En el objeto SqlDataAdapter debe usar parámetros con nombre.
SqlClient
Dim selectSQL As String = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City"
Dim insertSQL As String = "INSERT INTO Customers (CustomerID, CompanyName) " & _
"VALUES (@CustomerID, @CompanyName)"
Dim updateSQL As String = "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " & _
"WHERE CustomerID = @OldCustomerID"
Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = @CustomerID"
[C#]
string selectSQL = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City";
string insertSQL = "INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (@CustomerID, @CompanyName)";
string updateSQL = "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
"WHERE CustomerID = @OldCustomerID";
string deleteSQL = "DELETE FROM Customers WHERE CustomerID = @CustomerID";
OleDb u Odbc
Dim selectSQL As String = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? 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 = ?"
[C#]
string selectSQL = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? 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 = ?";
Las instrucciones de consulta con parámetros definen qué parámetros de entrada y de salida se deben crear. Para crear un parámetro, se utiliza el método Parameters.Add o el constructor Parameter con el fin de especificar el nombre de columna, tipo de datos y tamaño. En el caso de tipos de datos intrínsecos, como Integer, no es necesario incluir el tamaño o puede especificar el tamaño predeterminado.
En el ejemplo de código siguiente se crean los parámetros para la instrucción SQL del ejemplo anterior y se llena un DataSet.
SqlClient
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")
Dim custDA As SqlDataAdapter = New SqlDataAdapter
Dim selectCMD AS SqlCommand = New SqlCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD
' Add parameters and set values.
selectCMD.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = "London"
Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlDataAdapter custDA = new SqlDataAdapter();
SqlCommand selectCMD = new SqlCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;
// Add parameters and set values.
selectCMD.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = "London";
DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
OleDb
Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
Dim custDA As OleDbDataAdapter = New OleDbDataAdapter
Dim selectCMD AS OleDbCommand = New OleDbCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD
' Add parameters and set values.
selectCMD.Parameters.Add("@Country", OleDbType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OleDbType.VarChar, 15).Value = "London"
Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" +
"Integrated Security=SSPI;Initial Catalog=northwind;");
OleDbDataAdapter custDA = new OleDbDataAdapter();
OleDbCommand selectCMD = new OleDbCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;
// Add parameters and set values.
selectCMD.Parameters.Add("@Country", OleDbType.VarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", OleDbType.VarChar, 15).Value = "London";
DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
Odbc
Dim nwindConn As OdbcConnection = New OdbcConnection("Driver={SQL Server};Server=localhost;" & _
"Trusted_Connection=yes;Database=northwind")
Dim custDA As OdbcDataAdapter = New OdbcDataAdapter
Dim selectCMD AS OdbcCommand = New OdbcCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD
' Add Parameters and set values.
selectCMD.Parameters.Add("@Country", OdbcType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London"
Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;" +
"Trusted_Connection=yes;Database=northwind;");
OdbcDataAdapter custDA = new OdbcDataAdapter();
OdbcCommand selectCMD = new OdbcCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;
//Add Parameters and set values.
selectCMD.Parameters.Add("@Country", OdbcType.VarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London";
DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
Nota Si no se proporciona un nombre para un parámetro, toma un nombre predeterminado y secuencial del tipo ParameterN, comenzando por "Parameter1". Se recomienda no asignar a los parámetros nombres del tipo "ParameterN", ya que el nombre en cuestión podría entrar en conflicto con un nombre de parámetro predeterminado existente en ParameterCollection. Si el nombre proporcionado ya existe, se iniciará una excepción.
Parameter.DbType
El tipo de un parámetro es específico del proveedor de datos de .NET Framework de que se trate. Al especificar el tipo, el valor de Parameter se convierte al tipo del proveedor de datos de .NET Framework antes de pasarlo al origen de datos. Si no se especifica el tipo, ADO.NET lo deducirá del objeto Parameter para el proveedor de datos .NET Framework a partir del tipo de .NET Framework de Value del objeto.
También puede especificar el tipo de un objeto Parameter de forma genérica si asigna un determinado System.Data.DbType a la propiedad DbType del objeto Parameter. Además, ADO.NET deducirá el tipo de Parameter para el proveedor de datos de .NET Framework a partir del DbType del objeto Parameter.
El tipo del proveedor de datos de .NET Framework del objeto Parameter se deduce a partir del tipo de .NET Framework del valor Value del objeto Parameter o a partir del DbType del objeto Parameter. En la siguiente tabla se muestra el tipo Parameter inferido en función del objeto que se ha pasado como valor Parameter o del DbType especificado.
| Tipo de .NET Framework | System.Data.DbType | SqlDbType | OleDbType | OdbcType | OracleType |
|---|---|---|---|---|---|
| bool | Boolean | Bit | Boolean | Bit | Byte |
| byte | Byte | TinyInt | UnsignedTinyInt | TinyInt | Byte |
| byte[] | Binary | VarBinary. Esta conversión implícita genera un error en el caso de que la matriz de bytes sea mayor que el tamaño máximo de VarBinary, que es de 8.000 bytes. En el caso de matrices de bytes mayores de 8.000 bytes se debe establecer explícitamente el valor de SqlDbType. | VarBinary | Binary | Raw |
| char | No es posible deducir el valor de SqlDbType a partir de char. | Char | Char | Byte | |
| DateTime | DateTime | DateTime | DBTimeStamp | DateTime | DateTime |
| Decimal | Decimal | Decimal | Decimal | Numeric | Number |
| double | Double | Float | Double | Double | Double |
| float | Single | Real | Single | Real | Float |
| Guid | Guid | UniqueIdentifier | Guid | UniqueIdentifier | Raw |
| Int16 | Int16 | SmallInt | SmallInt | SmallInt | Int16 |
| Int32 | Int32 | Int | Int | Int | Int32 |
| Int64 | Int64 | BitInt | BigInt | BigInt | Number |
| object | Object | Variant <Att L=Component>VB 6.0 | Variant <Att L=Component>VB 6.0 | No es posible deducir el valor de OdbcType a partir de Object. | Blob |
| string | String | NVarChar. Esta conversión implícita genera un error en el caso de que la cadena sea mayor que el tamaño máximo de NVarChar, que es 4.000 caracteres. En el caso de cadenas mayores de 4.000 caracteres, el valor de SqlDbType se debe establecer de forma explícita. | VarWChar | NVarChar | NVarChar |
| Timespan | Time | No es posible deducir el valor de SqlDbType a partir de TimeSpan. | DBTime | Time | DateTime |
| UInt16 | UInt16 | No es posible deducir el valor de SqlDbType a partir de UInt16. | UnsignedSmallInt | Int | UInt16 |
| UInt32 | UInt32 | No es posible deducir el valor de SqlDbType a partir de UInt32. | UnsignedInt | BigInt | UInt32 |
| UInt64 | UInt64 | No es posible deducir el valor de SqlDbType a partir de UInt64. | UnsignedBigInt | Numeric | Number |
| AnsiString | VarChar | VarChar | VarChar | VarChar | |
| AnsiStringFixedLength | Char | Char | Char | Char | |
| Currency | Money | Currency | No es posible deducir el valor de OdbcType a partir de Currency. | Number | |
| Date | No es posible deducir el valor de SqlType a partir de Date. | DBDate | Date | DateTime | |
| SByte | No es posible deducir el valor de SqlType a partir de SByte. | TinyInt | No es posible deducir el valor de OdbcType a partir de SByte. | SByte | |
| StringFixedLength | NChar | WChar | NChar | NChar | |
| Time | No es posible deducir el valor de SqlType a partir de Time. | DBTime | Time | DateTime | |
| VarNumeric | No es posible deducir el valor de SqlDbType a partir de VarNumeric. | VarNumeric | No es posible deducir el valor de OdbcType a partir de VarNumeric. | Number |
Nota Los proveedores de datos de .NET Framework que se incluyen con la versión 1.0 de .NET Framework no verifican los valores Precision y Scale de los valores de parámetros de tipo Decimal, lo que puede provocar que se inserten datos truncados en el origen de datos. Si está utilizando la versión 1.0 de .NET Framework, valide los valores Precision y Scale de los valores de tipo Decimal antes de establecer el valor de parámetro.
En el caso de la versión 1.1 y versiones posteriores de .NET Framework, al configurar un valor de parámetro Decimal con un valor Precision que no es válido, se inicia una excepción. Los valores Scale que sobrepasan la escala del parámetro Decimal siguen truncados.
Parameter.Direction
En la tabla siguiente se muestran los valores que puede usar con la enumeración ParameterDirection para establecer el valor de Direction del Parameter.
| Nombre del miembro | Descripción |
|---|---|
| Input | El parámetro es de entrada. Éste es el valor predeterminado. |
| InputOutput | El parámetro puede ser de entrada y de salida. |
| Output | El parámetro es de salida. |
| ReturnValue | El parámetro representa un valor devuelto. |
En el ejemplo de código siguiente se muestra cómo establecer el valor Direction de Parameter.
myParm.Direction = ParameterDirection.Output
Parameter.SourceColumn, Parameter.SourceVersion
SourceColumn y SourceVersion se pueden pasar como argumentos al constructor Parameter o también se pueden establecer como propiedades de un Parameter existente. SourceColumn es el nombre de la columna DataColumn de la fila DataRow desde la que se va a recuperar el valor de Parameter. SourceVersion especifica qué versión de DataRow debe usar DataAdapter para recuperar el valor.
En la tabla siguiente se muestran los valores de la enumeración DataRowVersion disponibles para ser utilizados con SourceVersion.
| Nombre del miembro | Descripción |
|---|---|
| Current | El parámetro utiliza el valor actual de la columna. Éste es el valor predeterminado. |
| Default | El parámetro utiliza el valor DefaultValue de la columna. |
| Original | El parámetro utiliza el valor original de la columna. |
| Proposed | El parámetro utiliza un valor propuesto. |
En el ejemplo de código siguiente se define una instrucción UPDATE en la que la columna CustomerID se usa como SourceColumn para dos parámetros: @CustomerID (SET CustomerID = @CustomerID) y @OldCustomerID (WHERE CustomerID = @OldCustomerID). El parámetro @CustomerID se utiliza para actualizar la columna CustomerID de forma que tenga el valor actual de DataRow. En consecuencia, se usa CustomerID SourceColumn con el valor Current para SourceVersion. El parámetro @OldCustomerID se utiliza para identificar la fila actual en el origen de datos. Dado que el valor de la columna que coincide con @OldCustomerID se encuentra en la versión Original de la fila, también se usa el mismo SourceColumn (CustomerID) con el valor Original para SourceVersion.
SqlClient
custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")
Dim myParm As SqlParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", _
SqlDbType.NChar, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
SqlParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;
OleDb
custDA.UpdateCommand.Parameters.Add("@CustomerID", OleDbType.Char, 5,"CustomerID")
custDA.UpdateCommand.Parameters.Add("@CompanyName", OleDbType.VarChar, 40, "CompanyName")
Dim myParm As OleDbParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", _
OleDbType.Char, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", OleDbType.Char, 5, "CustomerID");
custDA.UpdateCommand.Parameters.Add("@CompanyName", OleDbType.VarChar, 40, "CompanyName");
OleDbParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", OleDbType.Char, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;
Odbc
custDA.UpdateCommand.Parameters.Add("@CustomerID", OdbcType.Char, 5, "CustomerID")
custDA.UpdateCommand.Parameters.Add("@CompanyName", OdbcType.VarChar, 40, "CompanyName")
Dim myParm As OdbcParameter = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", _
OdbcType.Char, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", OdbcType.Char, 5, "CustomerID");
custDA.UpdateCommand.Parameters.Add("@CompanyName", OdbcType.VarChar, 40, "CompanyName");
OdbcParameter myParm = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", OdbcType.Char, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;
UpdatedRowSource
Puede controlar la forma en que los valores devueltos desde el origen de datos se asignan al DataSet. Para ello, puede usar la propiedad UpdatedRowSource del objeto Command. Al asignar a la propiedad UpdatedRowSource uno de los valores de la enumeración UpdateRowSource, puede determinar si los parámetros que devuelve el comando DataAdapter se deben omitir o aplicar a la fila cambiada en el DataSet. También puede especificar si la primera fila devuelta (si existe) se aplica a la fila modificada en el DataSet.
En la tabla siguiente se describen los distintos valores de la enumeración UpdateRowSource y la forma en que afectan al comportamiento del comando usado con DataAdapter.
| UpdateRowSource | Descripción |
|---|---|
| Both | Tanto los parámetros de salida como la primera fila del conjunto de resultados devuelto se pueden asignar a la fila modificada en DataSet. |
| FirstReturnedRecord | Sólo los datos de la primera fila del conjunto de resultados devuelto se pueden asignar a la fila modificada en el DataSet. |
| None | Se pasan por alto todos los parámetros de salida y las filas del conjunto de resultados devuelto. |
| OutputParameters | Sólo los parámetros de salida se pueden asignar a la fila modificada del DataSet. |
Vea también
Utilizar proveedores de datos de .NET Framework para obtener acceso a datos | Utilizar procedimientos almacenados con un comando | DataRowVersion (Enumeración) | OleDbDataAdapter (Clase) | OdbcDataAdapter (Clase) | ParameterDirection (Enumeración) | SqlDataAdapter (Clase)