Configuración de parámetros y tipos de datos de parámetros

Artículo
05/09/2023

Los objetos de comando usan parámetros para pasar valores a instrucciones SQL o procedimientos almacenados que permiten realizar operaciones de comprobación de tipos y validación. A diferencia del texto de comando, la entrada de parámetros se trata como un valor literal, y no como código ejecutable. De esta forma, se protege contra ataques por "inyección de código SQL", en los que un atacante inserta un comando que pone en peligro la seguridad del servidor en una instrucción SQL.

Los comandos parametrizados también pueden mejorar el rendimiento de ejecución de la consulta, ya que ayudan al servidor de bases de datos a que haga coincidir precisamente el comando entrante con un plan de consulta almacenado en caché adecuado. Para obtener más información, consulte Almacenar en caché y volver a utilizar un plan de ejecución y Parámetros y reutilización de un plan de ejecución. Además de las ventajas en la seguridad y el rendimiento, los comandos con parámetros proporcionan un método práctico para organizar los valores que se pasan a un origen de datos.

Para crear un objeto DbParameter , se puede usar su constructor o bien se puede agregar a DbParameterCollection mediante una llamada al método Add de la colección DbParameterCollection . El método Add acepta como entrada argumentos del constructor o cualquier objeto de parámetro ya existente, en función del proveedor de datos.

Provisión de la propiedad ParameterDirection

Cuando se agregan parámetros distintos de los parámetros de entrada, se debe proporcionar una propiedad ParameterDirection . En la tabla siguiente se muestran los valores de ParameterDirection que se pueden usar con la enumeración ParameterDirection .

Nombre del miembro	Descripción
Input	Se trata de un parámetro de entrada. Este es el valor predeterminado.
InputOutput	El parámetro se puede comportar tanto de entrada como de salida.
Output	Se trata de un parámetro de salida.
ReturnValue	El parámetro representa un valor devuelto de una operación como, por ejemplo, un procedimiento almacenado, una función integrada o una función definida por el usuario.

Trabajo con marcadores de posición de parámetros

La sintaxis de los marcadores de posición de parámetros depende del origen de datos. Los proveedores de datos .NET Framework administran la asignación de nombres y la especificación de parámetros y marcadores de posición de parámetros de diferente forma. Esta sintaxis se personaliza para un origen de datos específico, como se describe en la tabla siguiente.

Proveedor de datos	Sintaxis de nomenclatura de parámetros
System.Data.SqlClient	Usa parámetros con nombre, con el formato `@`nombreDeParámetro.
System.Data.OleDb	Usa marcadores de parámetro de posición, indicados por un signo de interrogación (`?`).
System.Data.Odbc	Usa marcadores de parámetro de posición, indicados por un signo de interrogación (`?`).
System.Data.OracleClient	Usa parámetros con nombre, con el formato `:`nombreDeParámetro (o nombreDeParámetro).

Especificación de tipos de datos de parámetro

El tipo de datos de un parámetro es específico del proveedor de datos de .NET Framework. Al especificar el tipo, el valor de Parameter se convierte en el tipo del proveedor de datos de .NET Framework antes de pasar el valor al origen de datos. Si lo desea, puede especificar el tipo de un objeto Parameter de forma genérica estableciendo la propiedad DbType del objeto Parameter en un DbTypedeterminado.

El tipo del proveedor de datos de .NET Framework de un objeto Parameter se deduce del tipo de .NET Framework del Value del objeto Parameter , o de DbType del objeto Parameter. En la siguiente tabla se muestra el tipo deducido de Parameter en función del objeto que se ha pasado como valor Parameter o del DbTypeespecificado.

Tipo de .NET Framework	DbType	SqlDbType	OleDbType	OdbcType	OracleType
Boolean	Boolean	bit	Boolean	bit	Byte
Byte	Byte	TinyInt	UnsignedTinyInt	TinyInt	Byte
byte[]	Binary	VarBinary. Esta conversión implícita generará un error en el caso de que la matriz de bytes tenga un tamaño superior al tamaño máximo de un tipo VarBinary, que es de 8000 bytes.En matrices de bytes con más de 8000 bytes, establezca de forma explícita el tipo SqlDbType.	VarBinary	Binary	Raw
Char		No se admite la deducción de un tipo SqlDbType a partir de char.	Char	Char	Byte
DateTime	DateTime	DateTime	DBTimeStamp	DateTime	DateTime
DateTimeOffset	DateTimeOffset	DateTimeOffset en SQL Server 2008. La deducción de un elemento SqlDbType a partir de DateTimeOffset no se admite en versiones de SQL Server anteriores a SQL Server 2008.			DateTime
Decimal	Decimal	Decimal	Decimal	Numeric	Number
Double	Double	Float	Double	Double	Double
Single	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	BigInt	BigInt	BigInt	Number
Object	Objeto	Variante	Variante	No se admite la deducción de un tipo OdbcType a partir de Object.	Blob
String	String	NVarChar. Esta conversión implícita generará un error en el caso de que la cadena tenga un tamaño superior al tamaño máximo de un tipo NVarChar, que es de 4.000 caracteres. En cadenas con más de 4.000 caracteres, establezca de forma explícita el tipo SqlDbType.	VarWChar	NVarChar	NVarChar
TimeSpan	Hora	Time en SQL Server 2008. La deducción de un elemento SqlDbType a partir de TimeSpan no se admite en versiones de SQL Server anteriores a SQL Server 2008.	DBTime	Time	DateTime
UInt16	UInt16	No se admite la deducción de un tipo SqlDbType a partir de UInt16.	UnsignedSmallInt	Int	UInt16
UInt32	UInt32	No se admite la deducción de un tipo SqlDbType a partir de UInt32.	UnsignedInt	BigInt	UInt32
UInt64	UInt64	No se admite la deducción de un tipo SqlDbType a partir de UInt64.	UnsignedBigInt	Numérica	Number
	AnsiString	VarChar	VarChar	VarChar	VarChar
	AnsiStringFixedLength	Char	Char	Char	Char
	Moneda	Money	Moneda	No es posible deducir el valor de `OdbcType` a partir de `Currency` .	Number
	Date	Date en SQL Server 2008. La deducción de un elemento SqlDbType a partir de Date no se admite en versiones de SQL Server anteriores a SQL Server 2008.	DBDate	Date	DateTime
	SByte	No se admite la deducción de un elemento SqlDbType a partir de SByte.	TinyInt	No se admite la deducción de un tipo `OdbcType` a partir de SByte.	SByte
	StringFixedLength	NChar	WChar	NChar	NChar
	Hora	Time en SQL Server 2008. La deducción de un elemento SqlDbType a partir de Time no se admite en versiones de SQL Server anteriores a SQL Server 2008.	DBTime	Time	DateTime
	VarNumeric	No se admite la deducción de un elemento SqlDbType a partir de VarNumeric.	VarNumeric	No se admite la deducción de un tipo `OdbcType` a partir de VarNumeric.	Number
tipo definido por el usuario (un objeto con SqlUserDefinedAggregateAttribute	Object o String según el proveedor (SqlClient siempre devuelve Object, ODBC siempre devuelve String y el proveedor de datos administrados OleDb puede ver ambos)	SqlDbType.Udt si SqlUserDefinedTypeAttribute está presente; de lo contrario, Variant.	OleDbType.VarWChar (si el valor es NULL); de lo contrario, OleDbType.Variant.	OdbcType.NVarChar	no admitido

Nota

Las conversiones de valores de tipo decimal en otros tipos de valor son conversiones de restricción que redondean el valor decimal al valor entero más próximo a cero. Si el resultado de la conversión no puede representarse en el tipo de destino, se produce OverflowException .

Nota

Cuando se envía un valor de parámetro nulo al servidor, se debe especificar DBNull, no null (Nothing en Visual Basic). El valor nulo en el sistema es un objeto vacío que no tiene ningún valor. Para representar los valores nulos, se usaDBNull . Para obtener más información sobre valores nulos de base de datos, consulte Handling Null Values.

Derivación de información de parámetros

Los parámetros también se pueden derivar de un procedimiento almacenado mediante la clase DbCommandBuilder . Las clases SqlCommandBuilder y OleDbCommandBuilder proporcionan un método estático, DeriveParameters, que rellena automáticamente la colección de parámetros de un objeto de comando que usa información de parámetros procedente de un procedimiento almacenado. Tenga en cuenta que DeriveParameters sobrescribirá toda la información de parámetros existente en el comando.

Nota

La derivación de información de parámetros afecta al rendimiento, ya que precisa un viaje adicional de ida y vuelta (round trip) al origen de datos para recuperar la información. Si la información de los parámetros se conoce en tiempo de diseño, se puede mejorar el rendimiento de la aplicación si se establecen los parámetros con los valores correspondientes de forma explícita.

Para obtener más información, vea Generar comandos con objetos CommandBuilder.

Uso de parámetros con SqlCommand y un procedimiento almacenado

Los procedimientos almacenados ofrecen numerosas ventajas en el caso de aplicaciones que procesan datos. Mediante el uso de procedimientos almacenados, las operaciones de bases de datos se pueden encapsular en un solo comando, optimizar para lograr el mejor rendimiento, y mejorar con seguridad adicional. Aunque es cierto que para llamar a un procedimiento almacenado basta con pasar en forma de instrucción SQL su nombre seguido de los argumentos de parámetros, el uso de la colección Parameters del objeto DbCommand de ADO.NET permite definir más explícitamente los parámetros del procedimiento almacenado, y tener acceso a los parámetros de salida y a los valores devueltos.

Nota:

Las instrucciones parametrizadas se ejecutan en el servidor mediante sp_executesql, lo que permite reutilizar el plan de consulta. Los cursores o las variables locales del lote de sp_executesql no son visibles para el lote que llama a sp_executesql. Los cambios en el contexto de base de datos solo se mantienen hasta el final de la instrucción sp_executesql . Para más información, consulte sp_executesql (Transact-SQL).

Cuando se usan parámetros con SqlCommand para ejecutar un procedimiento almacenado de SQL Server, los nombres de los parámetros agregados a la colección Parameters deben coincidir con los nombres de los marcadores de parámetro del procedimiento almacenado. El proveedor de datos de .NET Framework para SQL Server no admite el uso del marcador de posición de signo de interrogación de cierre (?) para pasar parámetros a una instrucción SQL o a un procedimiento almacenado. Este proveedor trata los parámetros del procedimiento almacenado como parámetros con nombre y busca marcadores de parámetros coincidentes. Por ejemplo, el procedimiento almacenado CustOrderHist se define usando un parámetro denominado @CustomerID. Cuando el código ejecuta el procedimiento almacenado, también debe usar un parámetro denominado @CustomerID.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Ejemplo

En este ejemplo se muestra cómo llamar a un procedimiento almacenado de SQL Server en la base de datos de ejemplo Northwind . El nombre del procedimiento almacenado es dbo.SalesByCategory e incluye un parámetro de entrada denominado @CategoryName con el tipo de datos nvarchar(15). El código crea una nueva clase SqlConnection dentro de un bloque en uso, de forma que la conexión se cierre cuando finalice el procedimiento. Se crean los objetos SqlCommand y SqlParameter , y se establecen sus propiedades. SqlDataReader ejecuta SqlCommand y devuelve el conjunto de resultados del procedimiento almacenado, mostrándolos en la ventana de consola.

Nota

En lugar de crear objetos SqlCommand y SqlParameter y, a continuación, establecer propiedades en instrucciones independientes, puede usar uno de los constructores sobrecargados para establecer varias propiedades en una única instrucción.

static void GetSalesByCategory(string connectionString,
    string categoryName)
{
    using (SqlConnection connection = new(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new()
        {
            Connection = connection,
            CommandText = "SalesByCategory",
            CommandType = CommandType.StoredProcedure
        };

        // Add the input parameter and set its properties.
        SqlParameter parameter = new()
        {
            ParameterName = "@CategoryName",
            SqlDbType = SqlDbType.NVarChar,
            Direction = ParameterDirection.Input,
            Value = categoryName
        };

        // Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
            reader.Close();
        }
    }
}

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Using reader As SqlDataReader = command.ExecuteReader()

            If reader.HasRows Then
                Do While reader.Read()
                    Console.WriteLine("{0}: {1:C}", _
                      reader(0), reader(1))
                Loop
            Else
                Console.WriteLine("No rows returned.")
            End If
        End Using
    End Using
End Sub

Utilizar parámetros con OleDbCommand o con OdbcCommand

Cuando se usan parámetros con OleDbCommand o con OdbcCommand, el orden de los parámetros agregados a la colección Parameters debe coincidir con el de los parámetros definidos en el procedimiento almacenado. El proveedor de datos de .NET Framework para OLE DB y el proveedor de datos de .NET Framework para ODBC consideran los parámetros de un procedimiento almacenado como marcadores de posición y aplican los valores de los parámetros en orden. Además, los parámetros de valores devueltos deben ser los primeros que se agreguen a la colección Parameters .

El proveedor de datos de .NET Framework para OLE DB y el proveedor de datos de .NET Framework para ODBC no admiten el uso de parámetros con nombre para pasar parámetros a una instrucción SQL o a un procedimiento almacenado. En este caso, se debe utilizar el marcador de posición de signo interrogación de cierre (?), como se muestra en el ejemplo siguiente.

SELECT * FROM Customers WHERE CustomerID = ?

Por eso, el orden en que se agregan los objetos Parameter a la colección Parameters debe coincidir exactamente con la posición del marcador de posición de interrogación de cierre correspondiente al parámetro.

Ejemplo de OleDb

Dim command As OleDbCommand = New OleDbCommand( _
  "SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OleDbParameter = command.Parameters.Add( _
  "RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OleDbCommand command = new OleDbCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;

OleDbParameter parameter = command.Parameters.Add(
  "RETURN_VALUE", OleDbType.Integer);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add(
  "@InputParm", OleDbType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add(
  "@OutputParm", OleDbType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

Ejemplo de Odbc

Dim command As OdbcCommand = New OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OdbcParameter = command.Parameters.Add("RETURN_VALUE", OdbcType.Int)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OdbcCommand command = new OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection);
command.CommandType = CommandType.StoredProcedure;

OdbcParameter parameter = command.Parameters.Add( _
  "RETURN_VALUE", OdbcType.Int);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

Compartir a través de

Configuración de parámetros y tipos de datos de parámetros

Provisión de la propiedad ParameterDirection

Trabajo con marcadores de posición de parámetros

Especificación de tipos de datos de parámetro

Derivación de información de parámetros

Uso de parámetros con SqlCommand y un procedimiento almacenado

Ejemplo

Utilizar parámetros con OleDbCommand o con OdbcCommand

Ejemplo de OleDb

Ejemplo de Odbc

Consulte también

Recursos adicionales