Хранимые процедуры

Рассмотрим, как вызывать хранимые процедуры из кода на C# через Entity Framework Core на примере БД MS SQL Server.

Пусть наша база данных, как и в прошлой теме, описывается следующим контекстом данных и сущностями:

using Microsoft.EntityFrameworkCore;

public class Company
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public List<User> Users { get; set; } = new();
}

public class User
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public int Age { get; set; }
    public int CompanyId { get; set; }
    public Company? Company { get; set; }
}
public class ApplicationContext : DbContext
{
    public DbSet<User> Users { get; set; } = null!; 
    public DbSet<Company> Companies { get; set; } = null!;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=helloappdb;Trusted_Connection=True;");
    }
}

Вначале создадим саму хранимую процедуру, к которой мы будем обращаться. Для этого в Visual Studio в окне SQL Server Object Explorer откроем узел базы данных. В узле базы данных откроем подузел Programmability -> Stored Procedures:

Далее нажмем на этот узел правой кнопкой мыши и выберем Add New Stored Procedure:

После этого Visual Studio генерирует и октрывает файл скрипта хранимой процедуры со следующим содержимым:

CREATE PROCEDURE [dbo].[Procedure]
	@param1 int = 0,
	@param2 int
AS
	SELECT @param1, @param2
RETURN 0

Изменим скрипт следующим образом:

CREATE PROCEDURE [dbo].[GetUsersByCompany]
    @name nvarchar(50) 
AS
    SELECT * FROM Users 
    WHERE CompanyId=(SELECT Id FROM Companies WHERE Name=@name)
GO

Данная процедура ищет все строки, где значение столбца название компании равно строке, переданной через параметр @name.

Далее нажмем на кнопку Update для добавления хранимой процедуры:

И затем в появившемся окошке нажимаем кнопку Update Database:

После этого в узле Stored Procedures появится новая хранимая процедура, и мы сможем ее использовать.

Теперь обратимся к процедуре в коде C#:

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

using (ApplicationContext db = new ApplicationContext())
{
    SqlParameter param = new("@name", "Microsoft");
    var users = db.Users.FromSqlRaw("GetUsersByCompany @name", param).ToList();
    foreach (var p in users)
        Console.WriteLine($"{p.Name} - {p.Age}");
}

Параметр в методе FromSqlRaw принимает название процедуры, после которого идет перечисление параметров: GetUsersByCompany @name

Выходные параметры процедуры

В некоторых случаях процедура может возвращать не набор данных из таблиц, а какие-то отдельные данные. Как правило, для этого используются выходные параметры. Например, нам надо получить имя пользователя с максимальным возрастом. Для этого определим следующую хранимую процедуру:

CREATE PROCEDURE [dbo].[GetUserWithMaxAge]
	@name varchar(50) OUTPUT
AS
	SELECT @name = [Name] FROM Users WHERE Age = (SELECT MAX(Age) FROM Users)
RETURN 0

Параметр name здесь определен как выходной с ключевым словом OUTPUT. Через него будет передаваться имя пользователя.

Обратимся к этой процедуре в коде C#:

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

using (ApplicationContext db = new ApplicationContext())
{
    SqlParameter param = new()
    {
        ParameterName = "@userName",
        SqlDbType = System.Data.SqlDbType.VarChar,
        Direction = System.Data.ParameterDirection.Output,
        Size = 50
    };
    db.Database.ExecuteSqlRaw("GetUserWithMaxAge @userName OUT", param);
    Console.WriteLine(param.Value);
}

Здесь параметр userName определен как выходной. Так как нам в данном случае не надо возвращать набор данных, который соответствует одной из моделей, то для выполнения запроса используется метод db.Database.ExecuteSqlRaw(). И после его выполнения через свойство param.Value мы сможем получить значение, переданное через параметр.