Хранимые процедуры являются еще одной формой выполнения запросов к базе данных. Но по сравнению с ранее рассмотренными запросами, которые посылаются из приложения базе данных, хранимые процедуры определяются на сервере и предоставляют большую производительность и являются более безопасными.
Объект SqlCommand имеет встроенную поддержку хранимых процедур. В частности у него определено свойство CommandType
, которое
в качестве значения принимает значение из перечисления System.Data.CommandType
. И значение System.Data.CommandType.StoredProcedure
как раз указывает, что будет использоваться хранимая процедура.
Но чтобы использовать хранимые процедуры, нам надо их вначале создать. Для создания хранимых процедур в T-SQL применяется команда CREATE PROCEDURE, которую опять же можно выполнить из кода C#.
Например, определим две хранимых процедуры. Одна будет возвращать id добавленной строки, а вторая будет возвращать все строки из таблицы:
using System; using System.Threading.Tasks; using Microsoft.Data.SqlClient; namespace HelloApp { class Program { static async Task Main(string[] args) { string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;"; string proc1 = @"CREATE PROCEDURE [dbo].[sp_InsertUser] @name nvarchar(50), @age int AS INSERT INTO Users (Name, Age) VALUES (@name, @age) SELECT SCOPE_IDENTITY() GO"; string proc2 = @"CREATE PROCEDURE [dbo].[sp_GetUsers] AS SELECT * FROM Users GO"; using (SqlConnection connection = new SqlConnection(connectionString)) { await connection.OpenAsync(); SqlCommand command = new SqlCommand(proc1, connection); // добавляем первую процедуру await command.ExecuteNonQueryAsync(); // добавляем вторую процедуру command.CommandText = proc2; await command.ExecuteNonQueryAsync(); Console.WriteLine("Хранимые процедуры добавлены в базу данных."); } Console.Read(); } } }
Разберем код процедур. Первая процедура добавляет объект в БД и возвращает id добавленной строки:
CREATE PROCEDURE [dbo].[sp_InsertUser] @name nvarchar(50), @age int AS INSERT INTO Users (Name, Age) VALUES (@name, @age) SELECT SCOPE_IDENTITY() GO
После выражения CREATE PROCEDURE
идет название процедуры, то есть процедура называется "sp_InsertUser", и по этому названию мы
ее будем вызывать в коде C#. Название процедуры может быть любое.
Процедура имеет два входных параметра: @name и @age. Через эти параметры будут передаваться значения для имени и возраста пользователя.
В самом теле процедуры после выражения AS идет стандартное sql-выражение INSERT, которое выполняет добавление данных. И в конце с помощью
выражения SELECT возвращается результат. Выражение SCOPE_IDENTITY()
возвращает id добавленной записи, поэтому на выходе из процедуры мы получим id новой записи.
И завершается процедура ключевым словом GO.
Подобным образом добавляется вторая процедура, которая будет возвращать объекты из БД:
CREATE PROCEDURE [dbo].[sp_GetUsers] AS SELECT * FROM Users GO
И после выполнения программы в SQL Server Object Explorer (либо в SQL Server Management Studio) через узел Programmability->Stored Procedures можно увидет добавленные процедуры:
После добавления хранимых процедур используем их для добавления и получения данных. И для этого определим следующую программу:
using System; using System.Data; using System.Threading.Tasks; using Microsoft.Data.SqlClient; namespace HelloApp { class Program { static string connectionString = "Server=(localdb)\\mssqllocaldb;Database=adonetdb;Trusted_Connection=True;"; static async Task Main(string[] args) { Console.Write("Введите имя пользователя: "); string name = Console.ReadLine(); Console.Write("Введите возраст пользователя: "); int age = Int32.Parse(Console.ReadLine()); await AddUserAsync(name, age); Console.WriteLine(); await GetUsersAsync(); Console.Read(); } // добавление пользователя private static async Task AddUserAsync(string name, int age) { // название процедуры string sqlExpression = "sp_InsertUser"; using (SqlConnection connection = new SqlConnection(connectionString)) { await connection.OpenAsync(); SqlCommand command = new SqlCommand(sqlExpression, connection); // указываем, что команда представляет хранимую процедуру command.CommandType = CommandType.StoredProcedure; // параметр для ввода имени SqlParameter nameParam = new SqlParameter { ParameterName = "@name", Value = name }; // добавляем параметр command.Parameters.Add(nameParam); // параметр для ввода возраста SqlParameter ageParam = new SqlParameter { ParameterName = "@age", Value = age }; command.Parameters.Add(ageParam); // выполняем процедуру var id = await command.ExecuteScalarAsync(); // если нам не надо возвращать id //var id = await command.ExecuteNonQueryAsync(); Console.WriteLine($"Id добавленного объекта: {id}"); } } // вывод всех пользователей private static async Task GetUsersAsync() { // название процедуры string sqlExpression = "sp_GetUsers"; using (SqlConnection connection = new SqlConnection(connectionString)) { await connection.OpenAsync(); SqlCommand command = new SqlCommand(sqlExpression, connection); // указываем, что команда представляет хранимую процедуру command.CommandType = CommandType.StoredProcedure; using (SqlDataReader reader = await command.ExecuteReaderAsync()) { if (reader.HasRows) { Console.WriteLine($"{reader.GetName(0)}\t{reader.GetName(2)}\t{reader.GetName(1)}"); while (await reader.ReadAsync()) { int id = reader.GetInt32(0); string name = reader.GetString(2); int age = reader.GetInt32(1); Console.WriteLine($"{id} \t{name} \t{age}"); } } } } } } }
Для упрощения кода обращения к процедурам здесь вынесены в отдельные методы. В методе AddUserAsync вызывается процедура sp_InsertUser. Ее название передается
в конструктор объекта SqlCommand также, как и обычное sql-выражение. И с помощью выражения command.CommandType = System.Data.CommandType.StoredProcedure
устанавливается, что это выражение система будет рассматривать как хранимую процедуру.
Поскольку процедура получает данные через параметры, то соответственно нам надо определить эти параметры с помощью объектов SqlParameter. Ему передается название параметра и значение. Названия параметров должны соответствовать тем названиям, которые мы определили в коде процедуры.
С помощью метода command.Parameters.Add()
параметры добавляются к процедуре. И затем происходит выполнение.
Так как в коде процедуры добавления мы определили возвращение id новой записи, то есть возвращение скалярного значения, то для выполнения команды и его получения
мы можем использовать метод ExecuteScalarAsync()
. Но мы также можем использовать и метод ExecuteNonOueryAsync()
, только он вернет количество добавленных записей, а не id.
В случае второго метода все еще проще: объекту команды просто передается название процедуры, и так как процедура фактически выполняет
выражение SELECT и возвращает набор данных, то для выполнения команды мы можем использовать метод ExecuteReaderAsync()
. И с помощью ридера
получить все данные.
Запустим программу и введем какие-либо данные на добавление:
Введите имя пользователя: Tom Введите возраст пользователя: 36 Id добавленного объекта: 19 Id Name Age 2 Alice 32 3 Bob 28 19 Tom 36