Работа с хранимыми процедурами

Хранимые процедуры являются еще одной формой выполнения запросов к базе данных. Но по сравнению с ранее рассмотренными запросами, которые посылаются из приложения базе данных, хранимые процедуры определяются на сервере и предоставляют большую производительность и являются более безопасными.

Объект 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