Modificación de datos con procedimientos almacenados

  • Artículo

Se aplica a: .NET Framework .NET .NET Standard

Descargar ADO.NET

Los procedimientos almacenados pueden aceptar datos como parámetros de entrada y pueden devolver datos como parámetros de salida, conjuntos de resultados o valores de retorno. En el ejemplo siguiente se muestra cómo el proveedor de datos SqlClient de Microsoft para SQL Server envía y recibe parámetros de entrada, parámetros de salida y valores devueltos. En el ejemplo se inserta un nuevo registro en una tabla en la que la columna de clave principal es una columna de identidad.

Nota:

Si usa procedimientos almacenados para editar o eliminar datos mediante un elemento SqlDataAdapter, asegúrese de que no usa SET NOCOUNT ON en la definición del procedimiento almacenado. Esto hace que el recuento de filas afectadas vuelva a cero, lo que DataAdapter interpreta como un conflicto de simultaneidad. En este caso, se iniciará una DBConcurrencyException.

Ejemplo

En el ejemplo se utiliza el siguiente procedimiento almacenado para insertar una nueva categoría en la tabla Categories de Northwind. El procedimiento almacenado recibe el valor de la columna CategoryName como parámetro de entrada y usa la función SCOPE_IDENTITY() para recuperar el nuevo valor del campo de identidad, CategoryID, y devolverlo en un parámetro de salida. La instrucción RETURN utiliza la función @@ROWCOUNT para devolver el número de filas insertadas.

CREATE PROCEDURE dbo.InsertCategory  
  @CategoryName nvarchar(15),  
  @Identity int OUT  
AS  
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)  
SET @Identity = SCOPE_IDENTITY()  
RETURN @@ROWCOUNT

En el siguiente ejemplo de código se utiliza el anterior procedimiento almacenado InsertCategory como origen de la propiedad InsertCommand de SqlDataAdapter. El parámetro de salida @Identity se reflejará en DataSet una vez que se haya insertado el registro en la base de dados al llamar al método Update del SqlDataAdapter. El código también recupera el valor devuelto.

using System;
using System.Data;
using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        ReturnIdentity(connectionString);
        // Console.ReadLine();
    }

    private static void ReturnIdentity(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Create a SqlDataAdapter based on a SELECT query.
            SqlDataAdapter adapter = new SqlDataAdapter(
                "SELECT CategoryID, CategoryName FROM dbo.Categories", connection);

            // Create a SqlCommand to execute the stored procedure.
            adapter.InsertCommand = new SqlCommand("InsertCategory", connection);
            adapter.InsertCommand.CommandType = CommandType.StoredProcedure;

            // Create a parameter for the ReturnValue.
            SqlParameter parameter = adapter.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int);
            parameter.Direction = ParameterDirection.ReturnValue;

            // Create an input parameter for the CategoryName.
            // You do not need to specify direction for input parameters.
            adapter.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName");

            // Create an output parameter for the new identity value.
            parameter = adapter.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID");
            parameter.Direction = ParameterDirection.Output;

            // Create a DataTable and fill it.
            DataTable categories = new DataTable();
            adapter.Fill(categories);

            // Add a new row.
            DataRow categoryRow = categories.NewRow();
            categoryRow["CategoryName"] = "New Beverages";
            categories.Rows.Add(categoryRow);

            // Update the database.
            adapter.Update(categories);

            // Retrieve the ReturnValue.
            Int rowCount = (Int)adapter.InsertCommand.Parameters["@RowCount"].Value;

            Console.WriteLine("ReturnValue: {0}", rowCount.ToString());
            Console.WriteLine("All Rows:");
            foreach (DataRow row in categories.Rows)
            {
                    Console.WriteLine("  {0}: {1}", row[0], row[1]);
            }
        }
    }

    static private string GetConnectionString()
    {
        // To avoid storing the connection string in your code,
        // you can retrieve it from a configuration file.
        return "Data Source=(local);Initial Catalog=Northwind;Integrated Security=true";
    }
}

Vea también