Modificar datos con procedimientos almacenados
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 ADO.NET envía y recibe parámetros de entrada, parámetros de salida y valores de retorno. El ejemplo inserta un nuevo registro en una tabla cuya columna de clave principal es una columna de identidad en una base de datos de SQL Server.
Nota
Si está utilizando procedimientos almacenados de SQL Server para editar o eliminar datos con SqlDataAdapter, asegúrese de que no utiliza 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.
Nota
En el caso de OleDbDataAdapter, los parámetros con un ParameterDirection con el valor ReturnValue se deben especificar antes que los restantes parámetros.
using System;
using System.Data;
using System.Data.SqlClient;
static class Program
{
static void Main()
{
var connectionString = GetConnectionString();
ReturnIdentity(connectionString);
// Console.ReadLine();
}
static void ReturnIdentity(string connectionString)
{
using (SqlConnection connection = new(connectionString))
{
// Create a SqlDataAdapter based on a SELECT query.
SqlDataAdapter adapter = new("SELECT CategoryID, CategoryName FROM dbo.Categories", connection)
{
// Create a SqlCommand to execute the stored procedure.
InsertCommand = new SqlCommand("InsertCategory", connection)
{
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();
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.
var 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 string GetConnectionString() =>
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=true";
}
Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Module Class1
Sub Main()
Dim connectionString As String = _
GetConnectionString()
ReturnIdentity(connectionString)
' Console.ReadLine()
End Sub
Private Sub ReturnIdentity(ByVal connectionString As String)
Using connection As SqlConnection = New SqlConnection( _
connectionString)
' Create a SqlDataAdapter based on a SELECT query.
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM dbo.Categories", _
connection)
' Create a SqlCommand to execute the stored procedure.
adapter.InsertCommand = New SqlCommand("dbo.InsertCategory", _
connection)
adapter.InsertCommand.CommandType = CommandType.StoredProcedure
' Create a parameter for the ReturnValue.
Dim parameter As SqlParameter = _
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.
Dim categories As DataTable = New DataTable
adapter.Fill(categories)
' Add a new row.
Dim newRow As DataRow = categories.NewRow()
newRow("CategoryName") = "New Category"
categories.Rows.Add(newRow)
' Update the database.
adapter.Update(categories)
' Retrieve the ReturnValue.
Dim rowCount As Int32 = _
CInt(adapter.InsertCommand.Parameters("@RowCount").Value)
Console.WriteLine("ReturnValue: {0}", rowCount.ToString())
Console.WriteLine("All Rows:")
Dim row As DataRow
For Each row In categories.Rows
Console.WriteLine(" {0}: {1}", row(0), row(1))
Next
End Using
End Sub
Private Function GetConnectionString() As String
' 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;"
End Function
End Module