Gegevens wijzigen met opgeslagen procedures
Opgeslagen procedures kunnen gegevens accepteren als invoerparameters en kunnen gegevens retourneren als uitvoerparameters, resultatensets of retourwaarden. In het onderstaande voorbeeld ziet u hoe ADO.NET invoerparameters, uitvoerparameters en retourwaarden verzendt en ontvangt. In het voorbeeld wordt een nieuwe record ingevoegd in een tabel waarin de primaire-sleutelkolom een identiteitskolom is in een SQL Server-database.
Notitie
Als u opgeslagen SQL Server-procedures gebruikt om gegevens te bewerken of te verwijderen met behulp van een SqlDataAdapter, zorg er dan voor dat u SET NOCOUNT ON niet gebruikt in de definitie van de opgeslagen procedure. Dit zorgt ervoor dat het aantal geretourneerde rijen nul is, wat door de DataAdapter interpretatie als een gelijktijdigheidsconflict wordt geïnterpreteerd. In dit geval wordt er een DBConcurrencyException gegooid.
Opmerking
In het voorbeeld wordt de volgende opgeslagen procedure gebruikt om een nieuwe categorie in de tabel Northwind Categories in te voegen. De opgeslagen procedure gebruikt de waarde in de kolom CategoryName als invoerparameter en gebruikt de functie SCOPE_IDENTITY() om de nieuwe waarde van het identiteitsveld, CategoryID, op te halen en deze in een uitvoerparameter te retourneren. De instructie RETURN gebruikt de functie @@ROWCOUNT om het aantal ingevoegde rijen te retourneren.
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
In het volgende codevoorbeeld wordt de InsertCategory hierboven weergegeven opgeslagen procedure gebruikt als de bron voor de InsertCommand SqlDataAdapter. De @Identity uitvoerparameter wordt weergegeven in het DataSet nadat de record is ingevoegd in de database wanneer de Update methode van de SqlDataAdapter aangeroepen methode wordt aangeroepen. De code haalt ook de retourwaarde op.
Notitie
Wanneer u de OleDbDataAdapterfunctie gebruikt, moet u parameters opgeven met een ParameterDirection ReturnValue voor de andere parameters.
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]);
}
}
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
Throw New NotImplementedException()
End Function
End Module