W poprzednim wpisie na temat Dappera, który możecie przeczytać tutaj, opisałem jego podstawowe właściwości oraz przedstawiłem, w jaki sposób możecie z niego skorzystać, aby wykonać najprostsze operacje bazodanowe. Czyli w skrócie znajdziecie tam, jak zrobić całego CRUDa.
No i w zasadzie, jest to kwintesencja tego narzędzia, lecz nie wszystko, co warto o nim wiedzieć. W tym artykule wyjaśnię, w jaki sposób można połączyć dane z SQL-owymi zapytaniami korzystając z Dappera.
Statyczne zapisanie wartości
Gdy znamy wartości poszczególnych elementów możemy zapisać je bezpośrednio w zapytaniu. Przykładowo, gdy chcemy pobrać użytkowników, którzy nie są usunięci (a w zasadzie zarchiwizowani), to wskazujemy w warunku zapytania na to przez podanie odpowiedniej wartości. To zapytanie mogłoby wyglądać tak:
SELECT Email FROM User WHERE IsDeleted = 0
Sposób korzystania z Dappera się nie zmienia, wsadzamy sql’owego stringa jako parametr do metody Query, o tak:
string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DapperParameterBlog;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
string sql = @"SELECT Email
FROM [User]
WHERE IsDeleted = 0";
using (var connection = new SqlConnection(connectionString))
{
var users = connection.Query<User>(sql);
foreach (var user in users)
{
Console.WriteLine(user.Email);
}
}
Console.ReadLine();
W zasadzie w tym przykładzie nic nie przekazujemy. Wiemy już na etapie implementacji, że będziemy pobierali właśnie takie dane, więc podajemy na sztywno wartość w warunku zapytania. Szkoda bardzo, by ktoś widział naszych zarchiwizowanych użytkowników w wyszukiwarce. Strach pomyśleć, co on by jeszcze z nimi zrobił!
Podstawowy sposób “Na Jana”
Problemem jednak jest coś pochodzącego z zewnątrz. Mając wspomnianą wcześniej szukajkę, nie zaimplementujemy przecież na sztywno wszystkich adresów e-mail. To by trzeba było studentów zatrudnić, żeby wpisywali. Co to by była za mordęga.
Można pomyśleć, że skoro sam SQL jest zapisany jako string, to dozwolone będą operacje na tekście. Mamy więc dostępną zarówno konkatenację, jak i interpolację.
Sposób ten okazuję się bardzo dynamiczny – dostajemy cały arsenał programistycznych możliwości, tutaj pyknąć ifka, tutaj coś w pętli przetworzyć, a dane z zewnątrz dołączyć do stringa, jak gdyby nigdy nic.
Dla przykładu, aby pobrać wszystkich userów, których e-mail zaczyna się od początkowej frazy, napiszemy taki kawałek kodu:
string userPhrase = "us";
string sql = $@"SELECT Email
FROM [User]
WHERE Name LIKE '{userPhrase}%'";
Byłoby wszystko fajnie, ale tak nie jest. Możemy tak pisać, jeżeli jesteśmy pewni, że nikt z zewnątrz nie ma możliwości wprowadzania tych danych, lub mamy bardzo silne walidatory, które uchronią nas od Dependency Injection. Bo ktoś może sobie zaśmieszkować i wrzucić wartość, tutaj do zmiennej userPhrase, która zmieni sposób interpretowania zapytania SQL przez silnik bazodanowy, a to jest już bardzo niebezpieczne. Więcej o SQL Injection w Dapperze napiszę w innym artykule.
Na szczęście nie trzeba ręcznie pisać funkcji, czy to walidatorów, które uchronią użytkowników Dappera przed tym cholerstwem. Bo z odsieczą przychodzą parametry w Dapperze.
Użycie parametrów
Parametry można użyć w Dapperze na kilka rożnych sposobów. Pozwalają one na bezpieczne dodawanie danych, czy wyrażeń do zapytań SQLowych, ale również powodują większą czytelność zapytania. Jest ono wtedy bardziej zrozumiałe i łatwe w analizie, ponieważ każdemu parametrowi nadaje się odpowiednią nazwę. Można wyróżnić następujące parametry:
- Anonymous – parametry anonimowe
Jako drugi argument w metodach Dappera (czy to Query, czy Execute) można stworzyć obiekt anonimowy i w nim utworzyć parametry oraz przypisać im odpowiednią wartość. Sam parametr to nazwa poprzedzona znakiem @ i jeżeli taki zapis znajdzie się w SQL-owym zapytaniu, to Dapper będzie wiedział z czym na do czynienia.
Dla powyższego przykładu byłoby to:
string userPhrase = "us";
string sql = @"SELECT Name
FROM [User]
WHERE Email LIKE @UserPhrase";
using (var connection = new SqlConnection(connectionString))
{
var users = connection.Query<User>(sql, new { UserPhrase = $"{userPhrase}%" });
foreach (var user in users)
{
Console.WriteLine(user.Email);
}
}
Console.ReadLine();
Jeżeli nazewnictwo przekazywanej zmiennej jest takie samo jak parametru, to nie ma potrzeby zapisywania podstawienia, a wystarczy wewnątrz new {} wstawić tylko zmienną. Np. gdy chcemy pobrać użytkownika o dokładnie takim samym e-mailu wystarczy:
string email= "user1@gmail.com";
string sql = @"SELECT Name
FROM [User]
WHERE Email = @Email";
var users = connection.Query<User>(sql, new { email });
- Dynamic- parametry dynamiczne
Elastyczniejszy sposób, który używam przy bardziej złożonych zapytaniach. Jeżeli nie wiadomo właściwie ile tych parametrów będzie, bo w zależności od jakichś warunków mogą być inne, to warto z niego skorzystać. Polega on na dodawaniu parametrów do obiektu o typie DynamicParameters, który następnie jest przekazywany do metod Dappera. Jest to w zasadzie kolekcja parametrów, które dodaje się przez metodę Add podając jako pierwsze nazwę parametru, a potem jego wartość.
Dla powyższego przykładu będzie to:
string email = "user1@gmail.com";
string sql = $@"SELECT Email
FROM [User]
WHERE Email = @Email";
DynamicParameters parameters = new DynamicParameters();
parameters.Add("Email", email);
var users = connection.Query<User>(sql, parameters);
Jednak ten przykład nie demonstruje korzyści wynikających z korzystania z dynamicznych parametrów. Załóżmy, że w aplikacji mamy dwa tryby: dla użytkownika oraz dla administratora. Gdy z żądania przychodzi informacja, że ma zostać pobrany administrator to musimy sprawdzić jeszcze dodatkowy warunek, który nie jest wykorzystywany przy zwykłej roli użytkownika. Dla przykładu:
string sql = $@"SELECT Name
FROM [User]
WHERE Name = @Name";
DynamicParameters parameters = new DynamicParameters();
parameters.Add("Name", name);
if(role == UserRole.Admin)
{
sql += @"AND OtherColumn = @NextParameter";
parameters.Add("OtherColumn", "some_value");
}
Oczywiście dużo tu uproszczeń, ale jak widzicie, dzięki temu możliwe jest przekazywanie odpowiedniej liczby parametrów przy złożonych zapytaniach SQL. Nie trzeba przekazywać żadnych w nadmiarze. Bardziej praktyczny przykład, to gdy wasza wyszukiwarka w zależności od jakiejś logiki musi czasami z joinować się z inną tabelą i wtedy pojawiają się również inne warunki wymagające użycia parametrów.
- List – lista parametrów
Tutaj naszym parametrem jest lista, a nie pojedyncza wartość. Możemy ją używać wraz z dwoma powyższymi sposobami. Dla przykładu, gdy szukamy konkretnych ról użytkownika:
var roles = new[] { UserRole.Admin, UserRole.Leader };
string sql = $@"SELECT Email
FROM [User]
WHERE Role IN @Roles";
var users = connection.Query<User>(sql, new { Roles = roles });
Nie musimy budować pojedynczo parametrów, a wykorzystać wyłącznie jeden, a trzeba mieć na uwadze, że liczba parametrów jest ograniczona.
- String- string parametr, a cholera wie, jak to nazwać po polsku
W zasadzie nigdy jeszcze z tego parametru nie miałem okazji korzystać. Używa on z obiektu DbString. Został stworzony do łatwiejszej kontroli nad typami tekstowymi, bo jak wiecie w bazach danych jest wiele typów ich reprezentujących, czy to char, czy nvarchar. Dlatego temat ten poruszę w innym artykule, jeżeli znajdę ciekawy przypadek, w którym warto będzie go użyć.
Podsumowanie
Niemal non-stop przy pracy nad projektami używam parametrów Dappera. Nie dość, że ułatwiają późniejszą analizę zapytania, to jeszcze zabezpieczają nas przed problemami typu Dependency Injection, o którym dokładnie napiszę w innym wpisie. Powyżej wszystkie przykłady oparte były jedynie na metodzie Query, ale dla metod Execute oraz innych z rodziny Query, również można używać parametrów. Warto o nich wiedzieć, bo osoba zaczynająca z Dapperem może nie być świadoma, jakie zalety wynikają z ich stosowania.
