Modificación de datos con procedimientos almacenados

Establece CommandType en CommandType.StoredProcedure y establece CommandText en el nombre del procedimiento cualificado por esquema. Añade un parámetro para cada parámetro de procedimiento, con tipo, tamaño y dirección que coincidan.

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

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

Llamada a un procedimiento almacenado

AdventureWorks incluye dbo.uspGetEmployeeManagers, que acepta un parámetro de entrada y devuelve un conjunto de resultados:

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)}");
}

Utiliza los nombres de parámetros declarados por el procedimiento. La calificación del esquema evita la ambigüedad en la resolución de nombres y variantes innecesarias del plan de procedimiento.

Ejecutar un procedimiento de modificación de datos

AdventureWorks también incluye procedimientos que modifican los datos. HumanResources.uspUpdateEmployeePersonalInfo sustituye todos los campos personales de un empleado. El siguiente ejemplo ejecuta estos pasos dentro de una transacción:

1. Lee los valores actuales del empleado.
2. Cambia el estado civil.
3. Vuelve a leer el cambio.
4. Revierte la transacción para que la base de datos de ejemplo no cambie.

const int businessEntityId = 1;
using SqlTransaction transaction = connection.BeginTransaction();

string nationalIdNumber;
DateTime birthDate;
string maritalStatus;
string gender;

using (var select = new SqlCommand(
    """
    SELECT NationalIDNumber, BirthDate, MaritalStatus, Gender
    FROM HumanResources.Employee
    WHERE BusinessEntityID = @BusinessEntityID;
    """,
    connection,
    transaction))
{
    select.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;

    using SqlDataReader reader = select.ExecuteReader();
    if (!reader.Read())
    {
        throw new InvalidOperationException(
            $"Employee {businessEntityId} wasn't found.");
    }

    nationalIdNumber = reader.GetString(0);
    birthDate = reader.GetDateTime(1);
    maritalStatus = reader.GetString(2);
    gender = reader.GetString(3);
}

string newMaritalStatus = maritalStatus == "M" ? "S" : "M";

using (var command = new SqlCommand(
    "HumanResources.uspUpdateEmployeePersonalInfo",
    connection,
    transaction))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;
    command.Parameters.Add(
        "@NationalIDNumber",
        SqlDbType.NVarChar,
        15).Value = nationalIdNumber;
    command.Parameters.Add(
        "@BirthDate",
        SqlDbType.DateTime).Value = birthDate;
    command.Parameters.Add(
        "@MaritalStatus",
        SqlDbType.NChar,
        1).Value = newMaritalStatus;
    command.Parameters.Add(
        "@Gender",
        SqlDbType.NChar,
        1).Value = gender;

    command.ExecuteNonQuery();
}

using (var verify = new SqlCommand(
    """
    SELECT MaritalStatus
    FROM HumanResources.Employee
    WHERE BusinessEntityID = @BusinessEntityID;
    """,
    connection,
    transaction))
{
    verify.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;
    Console.WriteLine(
        $"Marital status: {maritalStatus} -> {verify.ExecuteScalar()}");
}

transaction.Rollback();

El procedimiento establece SET NOCOUNT ON, así que ExecuteNonQuery devuelve -1 en lugar de un conteo de filas. No uses el valor de retorno para confirmar la actualización. Lee la fila de vuelta.

Verifica el contrato y los efectos secundarios de un procedimiento antes de ejecutarlo. Cada comando que se ejecute en una conexión con una transacción local pendiente debe establecer Transaction. Asegúrate de que la gestión de transacciones del procedimiento respalde la transacción externa. Este procedimiento captura los errores y los registra con dbo.uspLogError en lugar de transmitirlos al llamante.

Leer conjuntos de resultados

Usar ExecuteReader o ExecuteReaderAsync cuando un procedimiento devuelva filas. Cuando devuelva más de un conjunto de resultados, llame a NextResult o NextResultAsync hasta que devuelva false.

Libera el lector antes de leer los parámetros de salida, de entrada/salida o de valor de retorno. Esos valores no son definitivos mientras el lector está abierto.

Configura las direcciones de los parámetros

Establezca Direction para cada parámetro que no sea solo de entrada:

SqlParameter output = command.Parameters.Add(
    "@outputValue",
    SqlDbType.NVarChar,
    50);
output.Direction = ParameterDirection.Output;

SqlParameter inputOutput = command.Parameters.Add(
    "@counter",
    SqlDbType.Int);
inputOutput.Direction = ParameterDirection.InputOutput;
inputOutput.Value = 1;

SqlParameter returnValue = command.Parameters.Add(
    "@returnValue",
    SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;

Establece Size para parámetros de salida y entrada/salida de longitud variable. Un parámetro de entrada/salida también necesita un Value inicial. Un parámetro de valor de retorno recibe el entero de la sentencia RETURN Transact-SQL (T-SQL) del procedimiento. No recibe un conjunto de resultados ni un valor seleccionado por el procedimiento.

Lee el Value de cada parámetro solo después de que finalice la ejecución y se haya cerrado cualquier lector:

command.ExecuteNonQuery();

Console.WriteLine(output.Value);
Console.WriteLine(inputOutput.Value);
Console.WriteLine(returnValue.Value);

Cuando un procedimiento no asigna un parámetro de salida, su valor puede ser DBNull.Value.

Elige un método de ejecución

Utiliza el método de ejecución que coincida con el resultado del procedimiento:

ExecuteNonQuery devuelve los conteos de filas afectadas cuando están disponibles. Un procedimiento que use SET NOCOUNT ON puede modificar filas y aún así devolver -1. Utiliza un parámetro de salida o valor de retorno cuando el procedimiento define uno como su contrato de éxito.

Tratamiento de los errores

Captura el SqlException en torno a la llamada de ejecución. Un procedimiento puede gestionar su propia gestión de transacciones y errores, pero el cliente aún debe decidir si la operación es segura para intentar de nuevo y si su conexión sigue siendo utilizable.

Para errores y PRINT mensajes del servidor, véase Manejar errores y mensajes.

Contenido relacionado

• Configuración de parámetros
• Ejecutar un comando
• Parámetros con valores de tabla
• Gestionar errores y mensajes