Stosowanie procedur składowanych ma kilka niepodważalnych zalet. Jedną z nich jest hermetyzacja logiki biznesowej. W ten sposób możemy zachować spójność reguł/zasad pomiędzy różnymi programami wywołującymi procedury składowane: nie ważne czy mówimy o aplikacjach napisanych w języku C# czy arkuszach kalkulacyjnych programu Excel.

Procedury składowane zapewniają również większe bezpieczeństo: z jednej strony pomagają chronić przeciwko atakom SQL Injection a z drugiej pozwalają na ukrycie przed użytkownikami kodu faktycznie napisanego zapytania.

Kolejna zaleta wynika z ruchu sieciowego i możliwości ograniczenia wysyłania wielu poleceń od Kilenta do bazy danych w celu zrealizowania logiki procedury. W tym wypadku wysyłamy raz polecenie wykonania procedury składowanej, która następnie wykona naraz wiele poleceń (już po stronie bazy danych). Warto również pamiętać, że procedury są buforowane na serwerze bazy danych – po uruchomieniu są gotowe do ponownego użycia.

W tym wpisie zapoznamy się z następującymi metodami pozwalającymi na wywołanie procedury składowanej (EF Core 5.0):

• FromRawSql()
• FromSqlInterpolated()

Zanim przejdziemy dalej zapoznamy się z ograniczeniami wykonywania procedur składowanych przy pomocy powyższych metod:

• Wynik procedury musi być typem encji, tj. procedura składowana musi zwrócić wszystkie kolumny danej encji;
• Wynik procedury nie może zawierać powiązanych danych. Procedura nie może wykonać polecenia JOIN w celu zwrócenia wyników.

Jak wiecie z pierwszego wpisu tego cyklu nigdy nie preferowałem podejścia code-first, którego trzymamy się jednak non stop w kolejnych artykułach dotyczących Entity Framework Core. Przy tworzeniu procedur składowanych poznamy sposób pozwalający na utworzenie (i sprawdzenie) procedury składowanej po stronie SQL Server a następnie używając migracji oraz ręcznej modyfikacji wygenerowanego kodu dodamy procedurę do naszego kroku migracyjnego.

Posługując się bazą danych z poprzedniego wpisu dodamy prostą procedurę pozwalającą na wyszukiwanie Klientów po ich nazwie:

CREATE PROCEDURE [dbo].[GetClients]
        @FullName varchar(50)
    AS
    BEGIN
		-- wyłączamy komunikat o liczbe zwróconych wierszy
        SET NOCOUNT ON;
        SELECT * FROM Customer where FullName like @FullName +'%'
	END

EXEC GetClients @FullName = 'Paweł'
-- W moim przypadku został zwrócony oczekiwany rekord – przechodzimy dalej

Do tej pory modyfikowaliśmy kod po stronie projektu, dodawaliśmy migracje (z automatycznie wygenerowanym kodem) oraz dokonywaliśmy aktualizacji bazy danych. Tym razem dodamy pustą migrację przy pomocy poniższego polecenia:

add-migration sp-GetClients

public partial class spGetClients : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {

    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }
}

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"CREATE PROCEDURE [dbo].[GetClients]
    @FullName varchar(50)
        AS
        BEGIN
			-- wyłączamy komunikat o liczbe zwróconych wierszy
            SET NOCOUNT ON;
            SELECT * FROM Customer where FullName like @FullName +'%'
		END");
}

Wykorzystanie powyższych metod wiąże się z bezpieczeństem. Ich zasada działania jest taka sama. Jako programiści musimy pamiętać, że dodawanie parametrów do nieprzetworzonych zapytań może się wiązać z atakmi SQL Injection. Dobrą praktyką, zalecaną również przez Microsoft (Nieprzetworzone zapytania SQL), jest nieprzekazywanie parametrów (w postaci łączenia ciągów lub interpolacji) do metody FromRawSql(). Zamiast tego powinniśmy korzystać z metody FromSqlInterpolated(), która pozwala na użycie interpolacji łańcuchów znaków w formie, która zabezpiecza przed atakami SQL Injection.

Metoda omówiona w poprzednim wpisie: FromRawSql() pozwala na wykonywanie nieprzetworzonych zapytań a co za tym idzie również procedur składowanych. Sposób jest niezwykle prosty i zgodny z konwencją języka SQL:

using(var context = new ApplicationDbContext())
{
    // wartość parametru jest niezmienna (w tym przypadku)
	var customers = context.Customer.FromSqlRaw("EXEC GetClients 'Paweł'");
}

using(var context = new ApplicationDbContext())
{
	string name = "Paweł";
	var customers = context.Customer
		.FromSqlInterpolated($"EXEC GetClients {name}");
}

using(var context = new ApplicationDbContext())
{
	// Microsoft.Data.SqlClient
    // Tym razem własnoręcznie dokonujemy parametryzowania w celu
    // zabezpieczenia się przed atakami SQL Injection
	var param = new SqlParameter("@FullName", "Paweł");
	var customers = context.Customer
		.FromSqlRaw("EXEC GetClients @FullName", param).ToList();
}

using(var context = new ApplicationDbContext())
{
	var customers = context.Customer
		.FromSqlRaw("EXEC GetClients @p0", "Paweł").ToList();
}

Metoda ExecuteSqlInterpolated() pozwala również na wykonywanie poleceń w postaci parametryzowanego łańcucha znaków. Parametrem zwracanym jest liczba wierszy na które wpłynęło zdefiniowane polecenie:

using(var context = new ApplicationDbContext())
{
	string name = "UpdatedPaweł";

	var updatedRows = context.Database
		.ExecuteSqlInterpolated($"UPDATE Customer SET FullName = {name} where Id = 1");
}

W podobny sposób możemy wykonywać polecenia dodawania oraz usuwania danych.