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

Данное руководство устарело. Актуальное руководство: Руководство по Entity Framework Core 7

Не меньше хранимых функций для взаимодействия с базой данных применяются хранимые процедуры. Рассмотрим, как вызывать хранимые процедуры из кода на C# через Entity Framework Core.

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

public class Company
{
	public int Id { get; set; }
	public string Name { get; set; }

	public List<User> Users { get; set; } = new List<User>();
}

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<Company> Companies { get; set; }
	public DbSet<User> Users { get; set; }
        
	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(ApplicationContext db = new ApplicationContext())
{
    Microsoft.Data.SqlClient.SqlParameter param = new Microsoft.Data.SqlClient.SqlParameter("@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(ApplicationContext db = new ApplicationContext())
{
    var param = new Microsoft.Data.SqlClient.SqlParameter
	{
		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 мы сможем получить значение, переданное через параметр.