ADO.NET w praktyce


SqlCommand jest też bardzo wygodnym mechanizmem do parametryzowania zapytań SQL. Proszę sobie wyobrazić, że mamy polecenie, które wyszukuje klientów o danym numerze NIP. Załóżmy, że ten NIP byłby przekazany w zmiennej łańcuchowej strNip. Przykładowe polecenie mogłoby mieć postać:

cmd.CommandText = "select * from Customer where NIP='" + strNip + "'";

Jednak - jeżeli np. ten łańcuch byłby pobierany z pola, w które użytkownik coś wprowadza , to złośliwy osobnik mógłby wprowadzić np.:

';delete from Customer where ''='

W ten sposób do SQL Servera wysłany zostałby łańcuch:

select * from Customer where NIP='';delete from Customer where ''=''

co spowoduje, że zostaną skasowane dane z tabeli Customer - a co gorsza, programista nic o tym nie będzie wiedział. Taki błąd nazywa się SQL Injection i ma wiele podobnych odmian.

Jeżeli natomiast wyrażenie miałoby postać:

cmd.CommandText = "select * from Customer where NIP=@NIP";

to wystarczy odpowiednio zdefiniować kolekcję parametrów, by bezpiecznie przekazać parametr użytkownika. ADO.NET podstawi w miejsce @NIP wartość przypisaną odpowiedniemu parametrowi. Dostawca .NET odpowiednio to zapakuje i wyśle na serwer (Uwaga! W przypadku używania innych baz warto dokładnie sprawdzić jak pakowane są parametry - są dostawcy, którzy np. "sklejają" wyrażenia, tak że napisany kod i tak pozostaje podatny na SQL Injection - dotyczy to zwłaszcza tych baz, które nie mają mechanizmów procedur przechowywanych). W przypadku SQL 2000/2005 taki kod wykorzysta sp_executesql z parametrami, co w bardzo dużym stopniu zabezpiecza przed tym błędem. Jednak jeszcze lepiej, gdy parametryzowany SqlCommand wywołuje procedurę przechowywaną.

Do wykonywania poleceń na serwerze ADO.NET używa procedury przechowywanej sp_executesql.

Do wykonywania poleceń na serwerze ADO.NET używa procedury przechowywanej sp_executesql.

Przyjrzyjmy się przykładowemu kodowi do dopisywania 1000 nowych klientów:

  1. using (SqlConnection cnn = new SqlConnection(CustomerEntry.Properties.Settings.Default.CnnTEST1)) {
  2. SqlCommand cmd = cnn.CreateCommand();
  3. cmd.CommandText = "insert into Customer(Name,NIP,Address1) values (@Name,@NIP,@Address1)";
  4. SqlParameter[] param=new SqlParameter[]{

    new SqlParameter("@Name",SqlDbType.NVarChar,50)

    new SqlParameter("@NIP",SqlDbType.NVarChar,50)

    new SqlParameter("@Address1",SqlDbType.NVarChar,50)};

  5. cmd.Parameters.AddRange(param);
  6. cnn.Open();
  7. SqlTransaction tr = cnn.BeginTransaction();
  8. cmd.Transaction = tr;
  9. try {
  10. for (int i = 1; i < 1000; i++) {
  11. cmd.Parameters["@Name"].Value="Name"+i;
  12. cmd.Parameters["@NIP"].Value="NIP"+i;
  13. cmd.Parameters["@Address1"].Value = "Adr" + i;
  14. cmd.ExecuteNonQuery();
  15. }
  16. tr.Commit();
  17. } catch (SqlException exp) {
  18. tr.Rollback();
  19. }
  20. cnn.Close();
  21. }

Narzędzie do projektowania obiektów DataSet w VS.NET 2005 pozwala nie tylko stworzyć strukturę do przechowywania danych po stronie klienta, ale także umożliwia automatyczne zdefiniowanie kodu do wywoływania procedur przechowywanych.

Narzędzie do projektowania obiektów DataSet w VS.NET 2005 pozwala nie tylko stworzyć strukturę do przechowywania danych po stronie klienta, ale także umożliwia automatyczne zdefiniowanie kodu do wywoływania procedur przechowywanych.

Kod zakłada, że zdefiniowane zostało połączenie CnnTEST1 w ustawieniach aplikacji. Po utworzeniu połączenia tworzone jest polecenie i inicjowane jest polecenie SQL - w tym wypadku instrukcja insert z trzema parametrami (linia 3). W kolejnych liniach tworzona jest tablica zawierająca różne SqlParameter (linia 4), która następnie przekazywana jest do inicjacji kolekcji parametrów znajdujących się w poleceniu cmd.

Konstruktorów SqlParameter jest dużo - niektóre pozwalają określić dokładność zmiennej (przy przekazywaniu zmiennej typu decimal) albo też wskazać, która wersja wartości ma być brana w wypadku zasilania z DataSet - co wykorzystywane jest przy konstrukcji poleceń aktualizujących w DataAdapter. Warto dodać, że odwzorowanie typów nie musi być dokładne w 100% - np. tu można by użyć typu VarChar. Jednak zależy to od konkretnego przypadku (wyrażenia SQL czy procedury przechowywanej).

Ponieważ tych operacji dopisywania do tabeli Customer jest dużo, przyjęto założenie, że albo wszystkie się udadzą, albo wszystkie zostaną wycofane i baza danych będzie przywrócona do początkowej zawartości. Aby to osiągnąć, w linii 7 otwierana jest transakcja, którą przypisujemy danemu poleceniu (będzie ona dopisywać kolejnych klientów). Następnie w pętli inicjowane są parametry o danej nazwie (linie 11,12,13) i wykonywane jest polecenie. Jeżeli wszystkie 1000 iteracji zakończy się sukcesem, to wykonywane jest polecenie Commit (linia 16), co spowoduje, że transakcja zostanie zatwierdzona i wszystkie operacje wykonane przez nasze polecenie będą utrwalone w bazie. Jeżeli pojawi się jakiś błąd, to ADO.NET zgłosi wyjątek, który można przechwycić i ręcznie wycofać transakcję (linia 18). Oczywiście, jeżeli w ogóle nie byłoby bloku try-catch i pojawiłby się błąd, to zmiany automatycznie byłyby wycofane - bo aplikacja zakończy działanie, co spowoduje przerwanie połączenia i automatyczny rollback. Na koniec zamykamy połączenie z bazą.

Konstruktory SqlParameter pozwalają precyzjnie określić własności parametrów polecenia SQL.

Konstruktory SqlParameter pozwalają precyzjnie określić własności parametrów polecenia SQL.

Teraz przyjrzymy się bardziej skomplikowanemu przypadkowi - załóżmy, że potrzebujemy jakoś przeliczyć kwoty na fakturach w zależności od daty przypisanej do nagłówka (to jest tylko przykład). Operacja jest na tyle skomplikowana, że nie da się jej prosto wyrazić w TSQL i uruchomić jako procedurę przechowywaną. Trzeba do klienta pobrać potrzebne informacje i wykonać obliczenia. Chcemy też, żeby operacja spełniała postulat atomowości, co oznacza, że albo wszystkie faktury zostaną przeliczone, albo żadna nie ulegnie modyfikacji. Aby to osiągnąć, trzeba zamknąć proces w transakcji.

Schemat operacji można zapisać w następujący sposób:

  1. Rozpocznij transakcję
  2. Pobierz wszystkie rekordy z Invoice

    • Pobierz wszystkie rekordy z InvoiceItem
    • Sprawdź warunek
    • Jeżeli spełniony, wykonaj modyfikację
  3. Zamknij transakcję

Automatyczne tworzenie metod dostępowych

Podobny mechanizm można stosować przy wywoływaniu procedur przechowywanych - wtedy parametrami ADO.NET muszą być parametry danej procedury. Jednak generalnie jest to dosyć żmudne zajęcie - i stąd bardzo tu się przydają możliwości IDE. Jeżeli np. stworzymy DataSet, to w VS.NET 2005 można przeciągnąć procedurę przechowywaną z Server Explorer na obszar projektanta.

VS wygeneruje, w zależności od typu procedury, odpowiedni kod, który pozwoli wywołać daną funkcjonalność. W przypadku procedury, która nie zwraca zbioru rekordów, tworzona jest po prostu metoda:

dsSampleTableAdapters.QueriesTableAdapter dt = new CustomerEntry.dsSampleTableAdapters.

QueriesTableAdapter();

dt.SearchReturnCount(12);

Wykorzystane są tu także typy "nullable", dzięki czemu nie ma problemu w przypadku, gdy do/z SQL trzeba przekazać NULL.

Gdy dana procedura zwraca recordset, wtedy powstaje odpowiednia tabela w DataSet i równocześnie TableAdapter, w którym metoda Fill przyjmuje parametry potrzebne do wywołania danej procedury przechowywanej. Można wtedy w łatwy sposób wypełnić daną tabelę, jak w poniższym przykładzie:

dsSample ds = new dsSample ();

DataSet1TableAdapters.

SearchInCustomerTableAdapter dt1 = new CustomerEntry.DataSet1TableAdapters.

SearchInCustomerTableAdapter();

dt1.Fill(ds.SearchInCustomer,"numer NIP");

Warto przypomnieć, że w ADO.NET 1.1 i VS.NET 2003 na formularz (czy komponent) przeciągane były obiekty typu SqlCommand i SqlConnection. W nowszej wersji mechanizmy zostały elegancko opakowane w ramach narzędzia do projektowania obiektów DataSet.