Configurar parámetros con Microsoft. Data.SqlClient

Los parámetros envían valores a un comando por separado de su texto Transact-SQL (T-SQL). Usa objetos SqlParameter en lugar de concatenar o interpolar valores en sentencias SQL. Los parámetros ayudan a proteger contra la inyección de SQL y a preservar el tipo de base de datos del valor.

Los ejemplos de este artículo asumen que un connection abierto llamado SqlConnection apunta a la base de datos de ejemplo de AdventureWorks.

Los ejemplos de C# requieren using Microsoft.Data.SqlClient;. Los ejemplos que usan SqlDbType también requieren using System.Data;.

Añadir parámetros de entrada

Añade un parámetro para cada marcador de posición de valor en el texto del comando. Especifica el tipo de SQL Server del parámetro y asigna su valor:

const string sql = """
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
      AND ListPrice >= @minimumPrice
    ORDER BY ListPrice;
    """;

using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@subcategoryId", SqlDbType.Int).Value = 2;
command.Parameters.Add("@minimumPrice", SqlDbType.Money).Value = 1_000m;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetInt32(0)}: {reader.GetString(1)} ({reader.GetDecimal(2):C})");
}

Usa las mismas mayúsculas y minúsculas para el nombre de un parámetro en el texto del comando y en el SqlParameter. SQL Server compara los nombres de las variables utilizando la collation del servidor, por lo que una discrepancia provoca un error del tipo Debe declararse la variable escalar en un servidor con una collation que distingue entre mayúsculas y minúsculas.

Trabajar con marcadores de posición de parámetros

Microsoft.Data.SqlClient utiliza parámetros con nombre con el formato @parameterName. No se admiten marcadores de posición de signo de interrogación posicional (?).

Los marcadores de posición de parámetros representan valores, no identificadores SQL ni sintaxis. No puedes usar un parámetro para el nombre de una tabla, el nombre de la columna, la dirección de ordenación o un fragmento SQL entero. Si una aplicación selecciona un identificador dinámicamente, valídelo frente a una lista de permisos y añádalo al texto del comando solo después de la validación. Sigue pasando todos los valores de datos como parámetros.

Para comandos de texto, Microsoft. Data.SqlClient envía sentencias parametrizadas a SQL Server a través de sp_executesql. SQL Server puede reutilizar un plan de ejecución cuando el texto de la sentencia y las definiciones de parámetros permanecen iguales.

Especificación de tipos de datos de parámetro

Establezca SqlDbType de forma explícita cuando la aplicación conoce el tipo de SQL Server objetivo. También establece metadatos específicos de tipo como Size, Precision, y Scale cuando corresponda.

Este ejemplo compara las columnas nvarchar(50) y datetime en Person.Person:

const string sql = """
    SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE LastName = @lastName
      AND ModifiedDate >= @modifiedSince
    ORDER BY BusinessEntityID;
    """;

using var command = new SqlCommand(sql, connection);

SqlParameter lastName = command.Parameters.Add(
    "@lastName",
    SqlDbType.NVarChar,
    50);
lastName.Value = "O'Brien";

SqlParameter modifiedSince = command.Parameters.Add(
    "@modifiedSince",
    SqlDbType.DateTime);
modifiedSince.Value = new DateTime(2013, 1, 1);

Emparejar los metadatos de parámetros con la columna objetivo evita conversiones implícitas y evita que cambios en la longitud de la cadena o en la escala decimal cambien la definición de parámetros.

Para valores decimales, establece precisión y escala explícitamente. La Weight columna en Production.Product usa decimal(8, 2):

const string sql = """
    SELECT ProductID, Name, Weight
    FROM Production.Product
    WHERE Weight >= @minimumWeight
    ORDER BY Weight;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter minimumWeight = command.Parameters.Add(
    "@minimumWeight",
    SqlDbType.Decimal);
minimumWeight.Precision = 8;
minimumWeight.Scale = 2;
minimumWeight.Value = 10.25m;

Pasar valores NULL de la base de datos

Asignar Value para enviar una base de datos NULL. C# null no representa un NULL de SQL Server para los parámetros escalares ordinarios:

const string sql = """
    SELECT SalesOrderID, OrderDate, SalesPersonID
    FROM Sales.SalesOrderHeader
    WHERE (@salesPersonId IS NULL OR SalesPersonID = @salesPersonId)
    ORDER BY SalesOrderID;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter salesPersonId = command.Parameters.Add(
    "@salesPersonId",
    SqlDbType.Int);

int? selectedSalesPersonId = null;
salesPersonId.Value = selectedSalesPersonId.HasValue
    ? selectedSalesPersonId.Value
    : DBNull.Value;

Utilizar metadatos inferidos de forma selectiva

El AddWithValue método infiere metadatos de parámetros a partir del valor en tiempo de ejecución. La inferencia es conveniente cuando los metadatos inferidos son estables y coinciden con el tipo de base de datos. Puede producir diferentes definiciones de parámetros cuando cambian la longitud de las cadenas, la precisión decimal u otras características de valor.

Preferir Add con un explícito SqlDbType para comandos de producción cuyo esquema objetivo es conocido. No pases C# null a AddWithValue; especifica el tipo y asigna DBNull.Value en su lugar.

Reutilizar parámetros de comando

Crea un parámetro una vez y cambia solo su Value cuando ejecutes el mismo comando con valores diferentes:

const string sql = """
    SELECT TOP (1) Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
    ORDER BY ListPrice DESC;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter subcategoryId = command.Parameters.Add(
    "@subcategoryId",
    SqlDbType.Int);

foreach (int id in new[] { 1, 2, 3 })
{
    subcategoryId.Value = id;

    using SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        Console.WriteLine($"{id}: {reader.GetString(0)}");
    }
}

No añadas otro parámetro con el mismo nombre cada vez que se ejecute. Reutiliza el parámetro existente o borra la colección antes de reconstruirla.

Establecer la dirección del parámetro

La Direction propiedad identifica cómo un comando utiliza un parámetro. Establézcalo en un ParameterDirection valor:

Direction	Uso
Input	Envía un valor a una sentencia o procedimiento almacenado. Este es el valor predeterminado.
Output	Recibe un valor de un parámetro de salida de procedimiento almacenado.
InputOutput	Envía un valor inicial y recibe el valor actualizado.
ReturnValue	Recibe el valor entero desde una instrucción T-SQL RETURN.

Para los parámetros de salida, entrada/salida y valor de retorno, lee Value solo después de que termine el comando. Si un comando devuelve filas, cierra o elimina el SqlDataReader antes de leer esos valores.

Para ver ejemplos completos de procedimientos almacenados, consulte Modificar datos con procedimientos almacenados.

Usar parámetros con un procedimiento almacenado

Establece StoredProcedure y utiliza los nombres de los parámetros del procedimiento. Este ejemplo llama al dbo.uspGetEmployeeManagers procedimiento incluido en AdventureWorks:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(
    "@BusinessEntityID",
    SqlDbType.Int).Value = 6;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetString(2)} {reader.GetString(3)}");
}

El nombre del parámetro debe coincidir con el nombre declarado por el procedimiento almacenado.

Configurar parámetros con valores de tabla

Un parámetro con valores de tabla envía varias filas a un procedimiento almacenado o a una sentencia parametrizada. Para configurar uno:

1. Establezca SqlDbType.Structured.
2. Establece TypeName en el tipo de tabla definida por el usuario de la base de datos.
3. Asigna una fuente de fila DataTable, DbDataReader o IEnumerable<SqlDataRecord>.

Un DataTable con columnas definidas y sin filas envía una tabla vacía. Un IEnumerable<SqlDataRecord> vacío lanza una excepción ArgumentException; asigna null de C# para enviar cero filas para ese formulario de entrada. No asignes DBNull.Value a un parámetro con valores de tabla.

Para la creación de tipos, mapeo de filas y ejemplos completos, véase Parámetros con valores en tabla.

Configurar valores en streaming

Asigna un Stream, TextReader o XmlReader a Value para proporcionar un valor grande de forma incremental. Para la transferencia con memoria limitada, utiliza un tipo de parámetro (max), xml o json. Microsoft.Data.SqlClient almacena en búfer el origen antes de la ejecución para los tipos que no sean de caracteres (max) ni binarios, así como para los tipos heredados de objetos grandes.

El tipo de parámetro debe coincidir con el valor:

Value	Valores compatibles SqlDbType
Stream	Binary, VarBinary, o el tipo legado Image
TextReader	Char, NChar, NVarChar, VarChar, los tipos heredados Text y NText, Xml, o Json
XmlReader	Char, NChar, NVarChar, VarChar, los tipos heredados Text y NText, Xml, o Json

Microsoft.Data.SqlClient lanza ArgumentException, SqlTypeException o SqlDbType cuando el tipo de objeto, InvalidCastException, o el tamaño declarado es incompatible. Para ejemplos completos de subida y descarga, consulta soporte de streaming de SqlClient.

Obtener información del parámetro

DeriveParametersconsulta SQL Server para obtener los metadatos de parámetros de un procedimiento almacenado y reemplaza la colección de parámetros existente del comando:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(command);
command.Parameters["@BusinessEntityID"].Value = 6;

DeriveParameters requiere un viaje extra de ida y vuelta a la base de datos cada vez que se llama. Derivar metadatos durante el desarrollo cuando sea necesario y luego definir parámetros explícitamente en rutas sensibles al rendimiento.

Contenido relacionado

• Ejecutar un comando
• Modificación de datos con procedimientos almacenados
• Parámetros con valores de tabla
• Compatibilidad con streaming de SqlClient