Изменение данных с помощью хранимых процедур
Область применения:
платформа .NET Framework .NET Standard
Хранимые процедуры могут принимать данные в виде входных параметров и возвращать их в виде выходных параметров, результирующих наборов или возвращаемых значений. В примере ниже показано, как поставщик данных Microsoft SqlClient для SQL Server отправляет и получает входные параметры, выходные параметры и возвращаемые значения. В этом примере новая запись вставляется в таблицу, где столбец первичного ключа является столбцом идентификаторов.
Примечание.
Если для изменения или удаления данных с помощью SqlDataAdapter используются хранимые процедуры, убедитесь, что в определении хранимой процедуры не используется SET NOCOUNT ON. В таком случае возвращается число затронутых строк, равное нулю, что DataAdapter интерпретирует как конфликт параллелизма. Это событие вызовет исключение DBConcurrencyException.
Пример
Пример использует следующую хранимую процедуру для вставки новой категории в таблицу Northwind Categories. Хранимая процедура принимает значение столбца CategoryName в качестве входного параметра и с помощью функции SCOPE_IDENTITY() получает новое значения поля идентификатора CategoryID и возвращает его в выходном параметре. Инструкция RETURN использует функцию @@ROWCOUNT, чтобы вернуть количество вставленных строк.
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
RETURN @@ROWCOUNT
Следующий пример кода использует хранимую процедуру InsertCategory, показанную выше, в качестве источника для свойства InsertCommand класса SqlDataAdapter. Выходной параметр @Identity будет отражен в наборе данных DataSet после вставки записи в базу данных при вызове метода Update объекта SqlDataAdapter. Код также получает возвращаемое значение.
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";
}
}