Modificando dados com procedimentos armazenados
Os procedimentos armazenados podem aceitar dados como parâmetros de entrada e podem retornar dados como parâmetros de saída, conjuntos de resultados ou valores de retorno. O exemplo abaixo ilustra como ADO.NET envia e recebe parâmetros de entrada, parâmetros de saída e valores de retorno. O exemplo insere um novo registro em uma tabela onde a coluna de chave primária é uma coluna de identidade em um banco de dados do SQL Server.
Nota
Se você estiver usando procedimentos armazenados do SQL Server para editar ou excluir dados usando um SqlDataAdapter, certifique-se de não usar SET NOCOUNT ON na definição de procedimento armazenado. Isso faz com que a contagem de linhas afetadas retornada seja zero, o que o DataAdapter interpreta como um conflito de simultaneidade. Neste caso, um DBConcurrencyException será lançado.
Exemplo
O exemplo usa o procedimento armazenado a seguir para inserir uma nova categoria na tabela NorthwindCategories . O procedimento armazenado usa o valor na coluna CategoryName como um parâmetro de entrada e usa a função SCOPE_IDENTITY() para recuperar o novo valor do campo de identidade, CategoryID, e retorná-lo em um parâmetro de saída. A instrução RETURN usa a função @@ROWCOUNT para retornar o número de linhas inseridas.
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
O exemplo de código a seguir usa o InsertCategory procedimento armazenado mostrado acima como a fonte para o InsertCommand .SqlDataAdapter O @Identity parâmetro de saída será refletido DataSet no após o registro ter sido inserido no banco de dados quando o Update método do SqlDataAdapter é chamado. O código também recupera o valor de retorno.
Nota
Ao usar o OleDbDataAdapter, você deve especificar parâmetros com um ParameterDirection de ReturnValue antes dos outros 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