Najlepsza praktyka do wysyłania zapytań do danych z MS SQL Server w C #?

Jaki jest najlepszy sposób na zapytanie danych z MS SQL Server w C #?

Wiem, że nie jest dobrą praktyką mieć zapytanie SQL w kodzie.

Czy to najlepszy sposób na utworzenie procedury składowanej i wywołanie jej z C # z parametrami?

using (var conn = new SqlConnection(connStr))
using (var command = new SqlCommand("StoredProc", conn) { CommandType = CommandType.StoredProcedure }) {
   conn.Open();
   command.ExecuteNonQuery();
   conn.Close();
}

Korzystanie z procedur przechowywanych jest jednym ze sposobów i jest szeroko stosowane od wielu lat.

Bardziej nowoczesnym sposobem interakcji z bazami danych SQL Server z C # (lub dowolnego języka .NET) jest użycie Entity Framework. Zaletą Entity Framework jest to, że zapewnia wyższy poziom abstrakcji.

Cytat z Microsoft ( https://msdn.microsoft.com/en-us/data/jj590134 ):

ADO.NET Entity Framework umożliwia programistom tworzenie aplikacji dostępu do danych poprzez programowanie w oparciu o koncepcyjny model aplikacji zamiast programowania bezpośrednio w relacyjnym schemacie przechowywania. Celem jest zmniejszenie ilości kodu i konserwacji wymaganej dla aplikacji zorientowanych na dane. Aplikacje Entity Framework zapewniają następujące korzyści:

• Aplikacje mogą działać w oparciu o bardziej koncepcyjny model koncepcyjny, w tym typy z dziedziczeniem, złożone elementy i relacje.
• Aplikacje są wolne od zakodowanych na stałe zależności od konkretnego silnika danych lub schematu pamięci.
• Odwzorowania między modelem koncepcyjnym a schematem specyficznym dla pamięci masowej można zmieniać bez zmiany kodu aplikacji.
• Programiści mogą pracować ze spójnym modelem obiektowym aplikacji, który można odwzorować na różne schematy przechowywania, ewentualnie zaimplementowane w różnych systemach zarządzania bazami danych.
• Wiele modeli koncepcyjnych można zmapować na pojedynczy schemat pamięci.
• Obsługa zapytań zintegrowanych z językiem (LINQ) zapewnia sprawdzanie poprawności składni w czasie kompilacji dla zapytań w modelu koncepcyjnym.

Zastosowanie ORM kontra Procedury składowane wiąże się z kompromisami, szczególnie pod względem bezpieczeństwa i miejsca, w którym znajduje się logika.

„Klasyczne” podejście do programowania w SQL Server polega na tym, że logika aplikacji znajduje się w procedurach przechowywanych, a programy mają tylko uprawnienia bezpieczeństwa do wykonywania procedur przechowywanych, a nie bezpośrednio aktualizują tabele. Chodzi tutaj o to, że procedury przechowywane są warstwą logiki biznesowej dla aplikacji. Chociaż teoria jest słuszna, z różnych powodów zwykle popada w niełaskę, zastępując ją logiką biznesową w języku programowania, takim jak C # lub VB. Dobre aplikacje są nadal wdrażane przy użyciu wielopoziomowego podejścia, w tym rozdzielania problemów itp., Ale częściej są zgodne z wzorem takim jak MVC.

Wadą implementacji logiki w ORM zamiast w bazie danych jest łatwość debugowania i testowania reguł integralności danych przez osoby odpowiedzialne za bazę danych (DA lub DBA). Weźmy klasyczny przykład przelewu pieniędzy z czeku na konto oszczędnościowe, ważne jest, aby uczynić to jako atomową jednostkę pracy, innymi słowy umieszczoną w transakcji. Jeśli tego rodzaju przeniesienie jest dozwolone tylko poprzez procedurę przechowywaną, DA i audytorzy mogą stosunkowo łatwo ocenić jakość przechowywanej procedury.

Jeśli z drugiej strony odbywa się to za pomocą ORM, takiego jak Entity Framework, a podczas produkcji okazuje się, że w rzadkich przypadkach pieniądze są sprawdzane, ale nie wkładane w debugowanie oszczędności może być znacznie bardziej skomplikowane, szczególnie jeśli potencjalnie zaangażowanych jest wiele programów. Najprawdopodobniej byłby to przypadek skrajny, być może obejmujący szczególne problemy sprzętowe, które muszą wystąpić w określonej sekwencji itp. W jaki sposób można to sprawdzić?

W rzeczywistości podstawowe twierdzenie jest dyskusyjne - istnieją kompromisy między sposobem SQL w kodzie lub kodem w bazie danych (tam, gdzie zmierzasz z procedurami przechowywanymi).

Rezultatem jest to, że nie ma jednego „najlepszego”, nie jest to coś, co można uogólnić, ponieważ niezależnie od tego, którą drogą pójdziesz, pójdziesz na kompromis (zyskujesz korzyści, ale także wprowadzasz ograniczenia).

Jeśli istnieje zgodność między aplikacją a bazą danych, to tak naprawdę nie ma to znaczenia. Z drugiej strony, jeśli duża baza danych jest współużytkowana przez znaczną liczbę aplikacji, wymuszanie spójności w bazie danych między tymi aplikacjami staje się znacznie ważniejsze.

Co ważniejsze, musisz martwić się architekturą i warstwami aplikacji - biorąc pod uwagę odpowiednią warstwę dostępu do danych, niezależnie od tego, czy korzystasz z ORM, takich jak Entity Framework lub NHibernate, czy robisz coś bardziej bezpośredniego, powinien izolować większość aplikacji od tej decyzji, niezależnie od tego, czy budujesz zapytania lub użyj procedur przechowywanych.

Wolę pracować nad stosunkowo małymi projektami w małych zespołach (1-3 programistów) - korzystanie z procedur przechowywanych jest dla mnie większym problemem niż jest to warte, ponieważ biorąc pod uwagę naturę naszych aplikacji (i mojego zestawu umiejętności?) Wdrażanie nowych kod jest na ogół znacznie łatwiejszy niż aktualizacja schematu (nawet pozwalając na posiadanie kodu, który sprawia, że ​​aktualizacja schematu jest stosunkowo prosta) i jestem w stanie egzekwować reguły biznesowe za pomocą wspólnego kodu dostępu do danych. Jest to wyraźnie klasyczny przykład „Your Mileage May Vary”.

Dopóki sparametryzujesz swoje dane wejściowe, każde podejście jest poprawne. Wiele z braków zapytań w argumentach kodu pochodziło ze złych, dawnych czasów, kiedy wiele bibliotek zmusiło cię do ciągłego dołączania instrukcji razem i stąd pochodziły ataki SQL Injection.

Najlepsza praktyka jest tutaj naprawdę przesadzona - istnieje wiele dobrych sposobów, a wybór, który wybierzesz, powinien zależeć od tego, co jest twoja aplikacja i co musisz zrobić. To powiedziawszy, są tylko dwie rzeczy, które możesz zrobić naprawdę źle:

• Jak wskazuje @Bill, zawsze należy sparametryzować swoje zapytania. Tworzenie ciągów jest łatwym wektorem do wstrzykiwania SQL, a także wszelkiego rodzaju trudnych do wyśledzenia błędów. Znacznie mądrzejsi ludzie wymyślili, jak tupelize i uciec sql, więc nie musisz sam tego wymyślać.

• Zamknij połączenia. Najlepszym sposobem jest zawinięcie wszystkiego w instrukcję, ale spróbuj / catch / wreszcie jest też fajny, jeśli to unosi twoją łódź. Ale zawsze upewnij się, że korzystasz z połączenia takiego jak tani samochód - prowadź go mocno i szybko i szybko się go pozbądź.

Inną praktyką, o którą opowiadam się stanowczo, jest to, że powinieneś skoncentrować swój kod dostępu do danych w jak najmniejszej liczbie miejsc. Nie zezwalamy aplikacjom internetowym frontonu na bezpośrednie odniesienie do System.Data.SqlClient, aby pomóc w egzekwowaniu tego zastrzeżenia.