Modyfikowanie danych za pomocą procedur składowanych
Procedury składowane mogą akceptować dane jako parametry wejściowe i zwracać dane jako parametry wyjściowe, zestawy wyników lub zwracać wartości. W poniższym przykładzie pokazano, jak ADO.NET wysyła i odbiera parametry wejściowe, parametry wyjściowe i zwracane wartości. Przykład wstawia nowy rekord do tabeli, w której kolumna klucza podstawowego jest kolumną tożsamości w bazie danych programu SQL Server.
Uwaga
Jeśli używasz procedur składowanych programu SQL Server do edytowania lub usuwania danych przy użyciu elementu SqlDataAdapter, upewnij się, że nie używasz funkcji SET NOCOUNT ON w definicji procedury składowanej. Powoduje to, że liczba wierszy, których dotyczy problem, zwraca wartość zero, co DataAdapter interpretuje jako konflikt współbieżności. W tym przypadku zostanie zgłoszony element DBConcurrencyException .
Przykład
W przykładzie użyto następującej procedury składowanej, aby wstawić nową kategorię do tabeli NorthwindCategories . Procedura składowana przyjmuje wartość w kolumnie CategoryName jako parametr wejściowy i używa funkcji SCOPE_IDENTITY(), aby pobrać nową wartość pola tożsamości, CategoryID i zwrócić ją w parametrze wyjściowym. Instrukcja RETURN używa funkcji @@ROWCOUNT, aby zwrócić liczbę wstawionych wierszy.
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
W poniższym przykładzie kodu użyto InsertCategory procedury składowanej pokazanej powyżej jako źródła elementu InsertCommandSqlDataAdapter. Parametr @Identity wyjściowy zostanie odzwierciedlony w DataSet pliku po wstawieniu rekordu do bazy danych po Update wywołaniu metody SqlDataAdapter . Kod pobiera również wartość zwracaną.
Uwaga
W przypadku korzystania z elementu OleDbDataAdapternależy określić parametry z wartością ParameterDirection ReturnValue przed innymi parametrami.
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