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

Данное руководство устарело. Актуальное руководство: по ADO.NET и работе с базами данных в .NET 6

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

Объект SqlCommand имеет встроенную поддержку хранимых процедур. В частности у него определено свойство CommandType, которое в качестве значения принимает значение из перечисления System.Data.CommandType. И значение System.Data.CommandType.StoredProcedure как раз указывает, что будет использоваться хранимая процедура.

Но чтобы использовать хранимые процедуры, нам надо их вначале создать. Для этого перейдем в SQL Server Management Studio к нашей базе данных usersdb, раскроем ее узел и далее выберем Programmability->Stored Procedures. Нажмем на этот узел правой кнопкой мыши и в контекстном меню выберем пункт Stored Procedure...:

В центральной части программы открывает код процедуры, который генерируется по умолчанию. Заменим этот код следующим:

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.

И затем нажмем на кнопку Execute. После этого в базу данных добавляется хранимая процедура.

Подобным образом добавим еще одну процедуру, которая будет возвращать объекты:

CREATE PROCEDURE [dbo].[sp_GetUsers]
AS
    SELECT * FROM Users 
GO

И также для ее добавления нажмем на кнопку Execute.

Теперь перейдем к коду C# и определим следующую программу:

class Program
{
    static string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";
    static void Main(string[] args) 
    {
        Console.Write("Введите имя пользователя:");
        string name = Console.ReadLine();

        Console.Write("Введите возраст пользователя:");
        int age = Int32.Parse(Console.ReadLine());

        AddUser(name, age);
		Console.WriteLine();
        GetUsers();

        Console.Read();
	}
	// добавление пользователя
	private static void AddUser(string name, int age)
	{
        // название процедуры
        string sqlExpression = "sp_InsertUser"; 

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlCommand command = new SqlCommand(sqlExpression, connection);
            // указываем, что команда представляет хранимую процедуру
            command.CommandType = System.Data.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 result = command.ExecuteScalar();
            // если нам не надо возвращать id
            //var result = command.ExecuteNonQuery();

            Console.WriteLine("Id добавленного объекта: {0}", result);
        }
    }

	// вывод всех пользователей
    private static void GetUsers()
    {
        // название процедуры
        string sqlExpression = "sp_GetUsers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlCommand command = new SqlCommand(sqlExpression, connection);
            // указываем, что команда представляет хранимую процедуру
            command.CommandType = System.Data.CommandType.StoredProcedure;
            var reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                Console.WriteLine("{0}\t{1}\t{2}", reader.GetName(0), reader.GetName(1), reader.GetName(2));

                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    int age = reader.GetInt32(2);
                    Console.WriteLine("{0} \t{1} \t{2}", id, name, age);
                }
            }
            reader.Close();
        }
    }
}

Для упрощения кода обращения к процедурам здесь вынесены в отдельные методы. В методе AddUser вызывается процедура sp_InsertUser. Ее название передается в конструктор объекта SqlCommand также, как и обычное sql-выражение. И с помощью выражения command.CommandType = System.Data.CommandType.StoredProcedure устанавливается, что это выражение система будет рассматривать как хранимую процедуру.

Поскольку процедура получает данные через параметры, то соответственно нам надо определить эти параметры с помощью объектов SqlParameter. Ему передается название параметра и значение. Названия параметров должны соответствовать тем названиям, которые мы определили в коде процедуры.

С помощью метода command.Parameters.Add() параметры добавляются к процедуре. И затем происходит выполнение.

Так как в коде процедуры добавления мы определили возвращение id новой записи, то есть возвращение скалярного значения, то для выполнения команды и его получения мы можем использовать метод ExecuteScalar(). Но мы также можем использовать и метод ExecuteNonOuery(), только он вернет количество добавленных записей, а не id.

В случае второго метода все еще проще: объекту команды просто передается название процедуры, и так как процедура фактически выполняет выражение SELECT и возвращает набор данных, то для выполнения команды мы можем использовать метод ExecuteReader(). И с помощью ридера получить все данные.

Запустим программу и введем какие-либо данные на добавление: