Zrób to sam - prosty i wydajny OR/M dla ADO.NET
Potrzeba matką wynalazków
- Och, przepraszam pana!
- Nie, nie, to ja przepraszam. Zagapiłem się, bo wie pan, szukam tu mojej żony.
- Ach tak? Ja też szukam swojej żony. A jak pańska żona wygląda?
- Wysoka, włosy płomienny kasztan, ścięte na okrągło z końcówkami podwiniętymi do twarzy. Doskonałe nogi, jędrne pośladki, duży biust. Była w spódniczce mini i bluzeczce z pięknym dekoltem. A pańska?
- Nieważne! Szukajmy pańskiej!
Wiem, że na świecie istnieje mnóstwo różnych gotowych bibliotek zajmujących się mapowaniem obiektowo relacyjnym. Jedne bardziej znane, takie jak NHibernate, Entity Framework, LINQ To SQL, inne mniej znane. Trzeba przyznać, że potrafią one znacznie ułatwić pracę przeciętnego programisty. Czytając hasła reklamowe tych narzędzi można przeczytać coś, z czym zawsze ostro walczę: Nie ma potrzeby znajomości bazy danych - OR/M załatwi to za ciebie!
. Dla małych baz danych, bazuń i bazuniątek znajomość wewnętrznej struktury tabel, połączeń i indeksów nie ma znaczenia. Wszystko będzie działało dobrze. Gdy baza zaczyna się rozrastać, zrozumienie sposobu generowania zapytań przez narzędzia OR/M staje się kluczowe. Warto zajrzeć choćby do tego wpisu: Entity Framework i rzeczywiste zapytania SQL Server i przekonać się, że w niektórych przypadkach możemy się wpakować w niezłe tarapaty.
Z drugiej strony, nawet przy poprawnym mapowaniu, zachodzi czasem potrzeba wykonania takich zapytań, które nie są wspierane przez narzędzia OR/M, ich wsparcie jest mocno ograniczone lub wyjątkowo trudne w realizacji. Wśród takich kluczowych mechanizmów można wyróżnić:
- Zapytania na widokach systemowych,
- Przetwarzanie struktur drzewiastych i grafów,
- Niestandardowe typy (wariantowe, przestrzenne),
- Klucze na kolumnach wyliczanych,
- Obsługa zapytań zwracających wiele zbiorów wynikowych,
- Wartości domyślne na kolumnach, wyliczane po stronie bazy danych.
Lista mogłaby być znacznie dłuższa. Podejrzewam, że każdy, kto musiał dłużej pracować z narzędziami typu OR/M w większym systemie doszedł do ściany i powiedział - muszę to zrobić czystym zapytaniem SQL.
Po trzecie, taka sytuacja pojawiła się u mnie, zastałem stary system. System od początku budowany w ADO.NET, z procedurami SQL, obiektami SqlDataReader i całą tą otoczką. Postąpić można różnie: przepisać no nowszą technologię, wymieszać starą technologię z nową lub zostać przy starej, ułatwiając sobie nieco część zadań.
Po czwarte, są ludzie, dla których nie ma czytelniejszego sposobu tworzenia zapytań na relacyjnej bazie danych niż język SQL.
I w końcu po piąte, warto czasem, choćby dla treningu, napisać coś swojego. Poznać jakieś mechanizmy i praktyczne rozwiązania, które mogą być wykorzystywane przy zupełni innej klasie problemów. To znakomita szkoła programowania i myślenia, która, tak mi się wydaje, zanika.
Odczyt danych w ADO.NET w sposób tradycyjny
Wieloletnia praca z pewną technologią sprawia, że potrafimy dostrzec pewne często powtarzające się operacje. Są to między innymi: pobranie łańcucha połączeniowego, utworzenie połączenia z bazą danych i obiektu polecenia, otwarcie połączenia i pobieranie danych w jakiejś pętli. Do tego dochodzą konwersje pomiędzy danymi, bo ADO.NET zwraca obiekty, a my potrzebujemy konkretnych typów. Po jakimś czasie operacje te wykonuje się automatycznie, po jakimś czasie przestaje się zwracać na nie uwagę, ale lepiej je zautomatyzować. Popatrzmy najpierw na tradycyjny sposób odczytu danych:
using (var conn = new SqlConnection())
using (var cmd = new SqlCommand("SELECT object_id, Name FROM sys.tables", conn))
{
var result = new List<Table>();
conn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add(new Table()
{
Name = reader[1] as string,
ObjectID = (int)reader[0]
});
}
return result;
}
Starsze aplikacje ADO.NET są zawalone tego typu kodem. Czasem do odczytu danych wykorzystuje się obiekty typu DataSet, ale i one mają pewne wady. Przejdźmy zatem do upraszczania kolejnych operacji.
Konwencja nazewnicza i automatyczne pobieranie łańcuchów połączeniowych
Popatrzmy na pierwsze przeróbki. Zgodnie z zasadą programowania obiektowego stworzę klasę reprezentującą bazę danych:
{
public string ConnectionString { get; set; }
public Database()
{
//Konwencja - łańcuch połączeniowy pokrywa się z nazwą klasy
string contextName = this.GetType().Name;
this.ConnectionString =
ConfigurationManager.ConnectionStrings[contextName].ConnectionString;
}
public Database(string connectionName)
{
this.ConnectionString =
ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
}
Przyjąłem następujące założenie (konwencję): jeżeli w pliku konfiguracyjnym znajduje się połączenie odpowiadające nazwie klasy, zostanie ono użyte. Przyjrzyjmy się przykładowej sekcji connectionstrings w pliku web.config lub app.config:
<configuration>
<connectionStrings>
<add name="MyDatabase"
connectionString="data source=.;initial catalog=Test;integrated security=True;"/>
<add name="Database"
connectionString="data source=.;initial catalog=Test;integrated security=True;"/>
</connectionStrings>
</configuration>
Jeżeli teraz stworzymy obiekt klasy Database korzystając z konstruktora domyślnego, wykorzystany zostanie łańcuch połączeniowy zdefiniowany pod nazwą Database
. Gdybyśmy zechcieli skorzystać z drugiego łańcucha połączeniowego wywołując konstruktor domyślny, można to zrobić przez dziedziczenie:
Można naturalnie skorzystać z konstruktora z parametrem i przekazać nazwę połączenia jawnie.
Przekazanie konwertera w postaci parametru
Nie ukrywam, że tworząc proste rozwiązanie OR/M korzystałem z doświadczeń innych tego typu rozwiązań. Jeżeli coś jest dobre - czemu z tego nie skorzystać? W NHibernate istnieje coś takiego jak obiekty transformacji. Definiujemy sposób przetwarzania jednego z rekordów, a NHibernate zrobi resztę: zastosuje to do wszystkich elementów i zwróci gotową listę. Rozwiązanie jest bardzo proste i ekstremalnie elastyczne. Taka podstawowa wersja przetwarzania, w którym mamy dokładną kontrolę nad całym procesem:
public List<T> SqlQuery<T>(string query, Func<SqlDataReader, T> transformer)
{
using (var conn = new SqlConnection(this.ConnectionString))
using (var cmd = new SqlCommand(query, conn))
{
conn.Open();
var result = new List<T>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add(transformer(reader));
}
return result;
}
}
Korzystając z takiej metody można uprościć sobie pobieranie danych ADO.NET do dwóch kroków: definicji przekształcenia oraz wywołania zapytania:
Func<SqlDataReader, Table> transformer = q => new Table()
{
Name = q[1] as string,
ObjectID = (int)q[0]
};
var transformed = db.SqlQuery("SELECT object_id, Name FROM sys.tables", transformer);
W zmiennej transformed otrzymamy pełną listę rekordów. Metoda konwertująca przyjmuje obiekt typu SqlDataReader, a zwracać może cokolwiek. W praktyce będzie to silnie typowany obiekt wykorzystywany dalej w logice biznesowej. Samo przekształcenie może być podane wprost, w miejscu parametru, bez definiowania dodatkowej zmiennej. Klasa natomiast, co jeszcze bardziej poszerza zakres zastosowań - anonimowa:
foreach (var item in anonymous)
{
Console.WriteLine("X={0}, Y={1}", item.X, item.Y);
}
Jeżeli zamierzamy w jakiś sposób przetwarzać rekordy po ich pobraniu, można zrezygnować z definiowania klasy pośredniej i skorzystać właśnie z klasy anonimowej. To jednak nie koniec możliwości, pójdźmy nieco dalej.
Automatyczne dopasowywanie właściwości
Czy nie dałoby się jakoś tak zaprojektować automatu, aby sama przypisywała odpowiednie kolumny z bazy do odpowiednich właściwości klasy? Oczywiście! Potrzebna nam będzie klasa, która wiąże właściwość klasy z indeksem kolumny w zapytaniu SQL:
{
public PropertyInfo Property { get; set; }
public int Index { get; set; }
}
Teraz przyda nam się metoda, która uzupełni listę takich powiązań:
{
List<PropertySetter> properties = new List<PropertySetter>();
var columns = typeof(T).GetProperties()
.Select(a => new { LowerName = a.Name.ToLower(), Property = a })
.ToArray();
for (int i = 0; i < reader.FieldCount; i++)
{
string column = reader.GetName(i).ToLower();
var prop = columns.FirstOrDefault(a => a.LowerName == column);
if (prop != null)
{
properties.Add(new PropertySetter()
{
Property = prop.Property,
Index = i
});
}
}
return properties;
}
Algorytm jest bardzo prosty - jeżeli nazwa kolumny zwracanej z zapytania pokrywa się z nazwą właściwości (porównania ignorują wielkość liter), zapamiętywane jest powiązanie [właściwość-indeks]. Taka lista przekazywana jest do dalszej obróbki.
Kompilacja w kodzie sposobem na wydajność refleksji
Znając typ wynikowy i właściwości, które należy ustawić, można już w zasadzie dokończyć zadanie. Dynamicznie utworzyć obiekt, a następnie, znów dynamicznie, przypisać kolejno wszystkie dopasowane właściwości. Operację należy powtórzyć dla każdego rekordu zwracanego z SQL Server. Gdy tych rekordów jest dużo, koszt dynamicznych wywołań może być znaczący. To dlatego zastosowałem nieco inne rozwiązanie - kompilacja funkcji, która będzie wykorzystywana wielokrotnie.
Bufor wykonywanych zapytań
Pisane zapytania są najczęściej wykonywane wielokrotnie, a przygotowanie mapowań i kompilacja mogą być kosztowne. To między innymi dlatego zdecydowałem się na prosty bufor zapytań. Dopasowanie jest bajecznie proste - jeżeli tekst zapytania się zgadza, skorzystaj z przygotowanej, skompilowanej w locie funkcji. Nie jest to duże ograniczenie. Niemożliwe staje się przepisanie identycznego zapytania do dwóch różnych obiektów, co, według mnie, jest nawet słuszne.
Przejdźmy jednak do metody właściwej, chyba najtrudniejszej. Pokazana jest ona na poniższym listingu:
{
lock (CommandCache)
{
if (CommandCache.ContainsKey(commandKey))
return (Func<SqlDataReader, T>)CommandCache[commandKey];
List<PropertySetter> properties = ExtractMappings<T>(reader);
var ctor = Expression.New(typeof(T).GetConstructor(Type.EmptyTypes));
var param = Expression.Parameter(typeof(SqlDataReader), "p");
var indexer = typeof(SqlDataReader).GetProperty("Item", new Type[] { typeof(int) });
var bindingArray = new MemberBinding[properties.Count];
for (int i = 0; i < properties.Count; i++)
{
var property = properties[i].Property;
var indexed = Expression.MakeIndex(param, indexer,
new List<Expression>() { Expression.Constant(properties[i].Index) });
bindingArray[i] = Expression.Bind(property,
Expression.Convert(indexed, property.PropertyType));
}
var bind = Expression.MemberInit(ctor,
bindingArray);
var lambda = Expression.Lambda<Func<SqlDataReader, T>>(bind, param);
var compiledFunction = lambda.Compile();
CommandCache.Add(commandKey, compiledFunction);
return compiledFunction;
}
}
Parametrami metody są: obiekt SqlDataReader oraz klucz bufora. Jeżeli klucz bufora zostanie odnaleziony, zwracana jest gotowa metoda tworząca odpowiedni obiekt dla każdego ze zwracanych z bazy SQL rekordów. Całość umieszczona jest w sekcji synchronizowanej - to tak na wypadek wielowątkowego dostępu do bufora i równoległego tworzenia tego samego wpisu w słowniku. To te elementy, które można pominąć. Reszta to przygotowanie drzewa wyrażeń i poddanie go kompilacji. Warto przejść przez ten proces z udziałem debugera. Gdybyśmy zatrzymali się na zmiennej bind i podejrzeli co ona reprezentuje, otrzymalibyśmy następujący rezultat:
ObjectID = (System.Int32)$p.Item[0],
Name = (System.String)$p.Item[1]
}
Reprezentuje to wywołanie konstruktora bezparametrowego klasy Przestrzen.Table, a następnie ustawienie wszystkich dopasowanych właściwości. Gdybyśmy zatrzymali się linijkę dalej, na zmiennej lambda, otrzymalibyśmy taką oto konstrukcję:
{
.New ADO_Mapper.Table(){
ObjectID = (System.Int32)$p.Item[0],
Name = (System.String)$p.Item[1]
}
}
Taka wewnętrzna konstrukcja mogłaby być wyrażona w łatwiejszy sposób, za pomocą takiego wyrażenia lambda:
{
ObjectID = (int)reader[0],
Name = (string)reader[1]
}
Przygotowane wyrażenie jest, jak już wspomniałem, kompilowane, zapisywane do pamięci podręcznej i wykorzystywane w postaci gotowej funkcji. Funkcję tę można traktować jak odpowiednik pokazanego powyżej wyrażenia lambda. Przejdźmy jeszcze do ostatecznej metody, która wykorzystuje pokazane mechanizmy:
{
using (var conn = new SqlConnection(this.ConnectionString))
using (var cmd = new SqlCommand(query, conn))
{
conn.Open();
var result = new List<T>();
var reader = cmd.ExecuteReader();
if (reader.Read())
{
var creator = ExtractCommandMapping<T>(reader, cmd.CommandText);
do
{
result.Add(creator(reader));
} while (reader.Read());
}
return result;
}
}
Warto zwrócić uwagę na zaburzoną, w stosunku do tradycyjnej, wersję pętli. Jeżeli strumień danych nie zawiera rekordów, nie ma sensu tworzyć mapowań i nie ma potrzeby kompilować wyrażeń. Taka przeróbka pozwala też oddzielić jednorazowe pobranie funkcji mapującej od pętli przetwarzającej kolejne rekordy.
Wykorzystanie klasy mapującej
Nie po to się pisze takie klasy, aby leżały odłogiem. Popatrzmy na przykładowe wywołania:
var db = new MyDatabase();
//Automatyczna konwersja na obiekt
var result1 = db.SqlQuery<Table>("SELECT object_id ObjectID, Name FROM sys.tables");
//Automatyczna konwersja na obiekt z wykorzystaniem cache
var result2 = db.SqlQuery<Table>("SELECT object_id ObjectID, Name FROM sys.tables");
Obie wersje wyglądają identycznie, ale tylko pierwsza a nich będzie wymagała przygotowania drzewa wyrażeń i kompilacji. Drugie zapytanie skorzysta z bufora i gotowej funkcji konwertującej.
Co dalej?
Klasa wykonująca mapowanie jest wyjątkowo prosta. Można pokusić się o przekazywanie parametrów, które wydaje się równie proste. Cache jest na to przygotowany, a samo przekazanie tych parametrów do ADO.NET też nie powinno nastręczać problemów. Można się pokusić o uzupełnianie klas zagnieżdżonych. W pokazanym przykładzie wszystkie właściwości były typami prostymi. Gdy właściwość jest strukturą lub klasą, można przeszukiwać także i tam, dopasowując płaskie wyniki zapytań do już mniej płaskich struktur. Można spróbować wykonać proste stronicowanie wyników dokładając do zapytania słowa kluczowe OFFSET ... FETCH (więcej na ten temat tutaj: Stronicowanie w SQL Server). Idąc jeszcze dalej, można wprowadzić proste, silnie typowane filtry, również w postaci wyrażeń lambda. Ogranicza nas tylko głowa.
Kategoria:C#SQL Server
Brak komentarzy - bądź pierwszy