Artykuł pochodzi w serii przygotowań do egzaminu 70-562 ASP.NET.
Witam w dzisiejszym artykule, w którym mowa będzie o dostępie do danych za pomocą klas, które wymagają aktywnego połączenia.
Używanie wbudowanych klas do operowania na danych
Klasy, za pomocą których możemy dodawać, edytować, usuwać itd. dane dostarczone są razem z frameworkiem .NET.
W sklad .NET’u wchodzą dostawcy do danych tacy jak: OleDb, Odbc, SQLServer, Oracl.
Poniższa tabela pokazuję listę podstawowych klas w ADO .NET. W tabeli pokazane zostały klasy bazowe, klasy klienckie oraz interfejsy:
Podstawowe informacje o obiekcie DbConnection
Klasa DbConnection jest klasą abstrakcyjną, po której klasy połączenia, specyficzne dla dostawcy, dziedziczą. Poniżej hierarchia klas:
Oraz przykładowy kod, w którym operujemy na obiekcie DbConnection:
1: DbConnection connection = new SqlConnection();
2: connection.ConnectionString = "Server=.;Database=pubs;Trusted_Connection=true";
3: connection.Open();
4: //jakaś akcja
5: connection.Close();
Widzimy utworzenie obiektu DbConnection a następnie wykorzystanie metody ConnectionString, która trzyma ścieżkę do naszej bazy danych. Musimy pamiętać, aby otworzyć połączenie zanim wykonamy jakiekolwiek komendy na bazie. Następnie należy połączenie zamknąć :) Możemy to zrobić za pomocą metody Close lub Dispose.
Konfigurowanie Connection String dla ODBC
Odbc jest jedną ze starszych technologii, które wspiera .NET. Poniżej najczęstsze ustawienia connection stringa dla ODBC:
Driver – sterownik ODBC dla połączenia
DSN – nazwa źródła danych, które mogą być konfigurowane przez administratora
Server – nazwa serwera, do którego się łączymy
Trusted_Connection – opis zabezpieczeń
Database – baza danych, do której się łączymy
DBQ - Zazwyczaj fizyczna ścieżka do źródła danych
Konfigurowanie Connection String dla OleDB
Podobnie jak wyżej, najczęstsze ustawienia:
Data source - Nazwa bazy danych lub fizycznej lokalizacji pliku bazy danych
File Name - Fizyczna lokalizacja pliku, który zawiera prawdziwy connection string.
Provider – specyficzny sterownik używający connection stringa do łączenia się danymi
Konfigurowanie Connection String dla SQL Server
Training kit przy opisywaniu connection stringa dla SQL Server wymienił bardzo dużo opcji (2,5 strony A4 :D ). Przedstawię kilka z nich:
Data Source, addr, address, network address, server - Nazwę lub adres IP serwera bazy danych.
Initial Catalog, database – nazwa bazy danych, z której korzystamy
User ID, uid, user – nazwa użytkownika wykorzystywana do łączenia się z SQL Serverem.
Password, pwd – hasło potrzebne do połączenia się z serwerem.
Workstation ID, wsid - Nazwa komputera klienckiego, który łączy się SQL Server.
Poza powyższymi są jeszcze między innymi: Current Language, language, Application Name, app, Packet Size, Network Library, net, network, Load Balance Timeout, connection lifetime i wiele innych. Zachęcam do doczytania na MSDN.
Dołączanie lokalnego pliku SQL do SQL EXPRESS
Aby dołączyć lokalny plik SQL do SQL Express możemy użyć do tego następującego connection stringa:
1: Data Source=.\SQLEXPRESS;
2: AttachDbFilename=C:\MyApplication\PUBS.MDF;
3: Integrated Security=True;
4: User Instance=True
Ustawiliśmy Data Source na SQL EXPRESS czyli tak jak chcieliśmy oraz w AttachDbFilename wskazaliśmy plik, który chcemy dołączyć.
W praktyce używa się zazwyczaj konfiguratora wbudowanego w SQL Express, który za nas wygeneruje odpowiedni connection string :)
Używanie connection string w Web Configu
Możemy używać connection stringów w pliku konfiguracyjnym www. Dzięki temu w jednym miejscu mamy kontrolę nad połączeniami do bazy co zapewnia łatwiejsze wprowadzanie zmian. Connection stringi możemy umieścić w znaczniku <connectionStrings> tak jak poniżej:
1: <connectionStrings>
2: <add name="PubsData" providerName="System.Data.SqlClient"
3: connectionString="Data Source=.\SQLEXPRESS;
4: AttachDbFilename=|DataDirectory|PUBS.MDF;
5: Integrated Security=True; User Instance=True"/>
6: </connectionStrings>
Powyższy przykład dodaje nowy connection string oraz nadaje mu nazwę PubsData. Do powyższego connection stringa możemy się dostać za pomocą klasy ConfigurationManager, tak jak w poniższym przykładzie:
1: ConnectionStringSettings pubs =
2: ConfigurationManager.ConnectionStrings["PubsData"];
3: DbConnection connection = new SqlConnection(pubs.ConnectionString);
Korzystanie z Visual Studio w celu dodania nowego połączenia
Dzięki Visual Studio dodawanie nowego połączenia do danych staje się prostsze :) Możemy dodać nowe połączenie używając explorera. Otworzy nam się ładny kreator i będziemy mogli określić co chcemy zrobić. Poniżej server explorer po dodaniu nowej bazy danych. Wystarczy kliknąć na explorerze prawym przyciskiem i wybrać dodanie nowego połączenia:
Szyfrowanie connection stringa
Często nasze connection stringi zawierają użytkownika i hasło bazy danych. Na szęście z pomocą przychodzi nam narzędzie Aspnet_regiis.exe, dzięki któremu możemy zaszyfrować ważne dane :)
Możemy szyfrować i deszyfrować zawartość pliku Web.config przy użyciu System.Configuration. DPAPIProtectedConfigurationProvider, który korzysta z ochrony danych Windows API (DPAPI) lub System.Configuration.RSAProtectedConfiguration- Usługodawcy, który wykorzystuje Rivest-Shamir-Adleman (RSA).
Kiedy trzeba korzystać z tego samego zaszyfrowanego pliku konfiguracji na wielu komputerach w sieci Web, należy użyć System.Configuration.RSAProtectedConfigurationProvider, który pozwala na eksport kluczy do szyfrowania danych. Klucze szyfrujące mogą być importowane do innego serwera. Jest to ustawienie domyślne. Typowy plik Web.config może wyglądać tak:
1: <?xml version="1.0"?>
2: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
3: <appSettings/>
4: <connectionStrings>
5: <add name="ConnectionString"
6: connectionString="Data Source=.\SQLEXPRESS;
7: AttachDbFilename=|DataDirectory|\northwnd.mdf;
8: Integrated Security=True;User Instance=True"
9: providerName="System.Data.SqlClient" />
10: </connectionStrings>
11: <system.web>
12: ...
13: </system.web>
14: </configuration>
Element connectionStrings może być zaszyfrowany, jeśli uruchomimy polecenie (w konsoli Visual Studio)
z podaniem pełnej ścieżki do witryny sieci Web np:
1: aspnet_regiis -pef "connectionStrings" "C:\...\EncryptWebSite"
Po wykonaniu polecenia nasz web config może wyglądać tak:
1: <?xml version="1.0"?>
2: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
3: <protectedData>
4: <protectedDataSections>
5: <add name="connectionStrings"
6: provider="RsaProtectedConfigurationProvider"
7: inheritedByChildren="false" />
8: </protectedDataSections>
9: </protectedData>
10: <appSettings/>
11: <connectionStrings>
12: <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
13: xmlns="http://www.w3.org/2001/04/xmlenc#">
14: <EncryptionMethod
15: Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
16: <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
17: <EncryptedKey Recipient=""
18: xmlns="http://www.w3.org/2001/04/xmlenc#">
19: <EncryptionMethod
20: Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
21: <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
22: <KeyName>Rsa Key</KeyName>
23: </KeyInfo>
24: <CipherData>
25: <CipherValue>PPWA1TkWxs2i698Dj07iLUberpFYIj6wBhbmqfmNK/plarau4i1k+xq5bZzB4VJW8
26: OkhwzcIIdZIXff6INJ1wlZz76ZV1DIbRzbH71t6d/L/qJtuOexXxTi2LrepreK/q3svMLpsJycnDPa
27: t9xaGoaLq4Cg3P19Z1J6HquFILeo=</CipherValue>
28: </CipherData>
29: </EncryptedKey>
30: </KeyInfo>
31: <CipherData>
32: <CipherValue>Q1re8ntDDv7/dHsvWbnIKdZF6COA1y3S91hmnhUN3nxYfrjSc7FrjEVyJfJhl5EDX
33: 4kXd8ukAjrqwuBNnQbsh1PAXNFDflzB4FF+jyPKP/jm1Q9mDnmiq+NCuo3KpKj8F4vcHbcj+f3GYqq
34: B4pYbblAvYnjPyPrrPmxLNT9KDtDr8pDbtGnKqAfcMnQPvA8l5w3BzPM4a73Vtt2kL/z9QJRu3Svd9
35: 33taxOO/HufRJEnE2/hcBq30WcBmEuXx3LFNjV+xVmuebrInhhxQgM2froBKYxgjwWiWNjIIjIeTI2
36: FQ8nZ8V8kzAVohmDYkZpCj4NQGdrjD996h97phI6NnHZYZHJ7oPRz</CipherValue>
37: </CipherData>
38: </EncryptedData>
39: </connectionStrings>
40: <system.web>
41: ...
42: </system.web>
43: </configuration>
Do deszyfrowania możemy użyć poniższego polecenia:
1: aspnet_regiis -pdf "connectionStrings" "C:\...\EncryptWebSite"
Korzystanie z obiektu DbCommand
DbCommand używamy do przesyłania jednej lub więcej zapytań pisanych w SQL. Może on być jednym z poniższych typów:
Data Manipulation Language (DML) - Polecenia pobrania, wstawiania, aktualizowania lub usuwania danych
Data Definition Language (DDL) - Polecenia tworzenia tabel i innych obiektów bazy danych lub modyfikacji schematu bazy danych.
Data Control Language (DCL) - Polecenia, które nadają, blokują, lub cofają uprawnienia
Poniższy kod pokazuje jak utworzyć i zainicjować (ale nie wykonać) DbCommand:
1: //pobieramy connection stringa
2: ConnectionStringSettings pubs =
3: ConfigurationManager.ConnectionStrings["PubsData"];
4:
5: DbConnection connection =
6: new SqlConnection(pubs.ConnectionString);
7: //tworzymy obiekt DbCommand
8: DbCommand cmd = connection.CreateCommand();
9: cmd.CommandType = CommandType.StoredProcedure;
10: cmd.CommandText = "uspGetCustomerById";
Ten kod tworzy obiekt SqlConnection i przypisuje go do zmiennej połączenia, która ma typ danych DbConnection. Obiekt DbConnection jest następnie wykorzystywane do tworzenia SqlCommand, który jest przypisany do zmiennej cmd. DbConnection musi być otwarty przed wywołaniem komendy. CommandText zawiera nazwę procedury przechowywanej, a CommandType wskazuje, że jest to nawiązanie do procedury składowanej.
Używanie obiektu DbParameter do przekazywania danych
Gdy zachodzi konieczność przekazania danych do procedury składowanej, należy używać obiektów DbParameter. Na przykład zdefiniowana przez użytkownika procedura składowana o nazwie uspGetCustomerById może wymagać ID klienta w celu pobrania odpowiednich klientów. Możemy tworzyć obiekty DbParameter za pomocą Parameters.Add tak, jak poniżej:
1: ConnectionStringSettings pubs =
2: ConfigurationManager.ConnectionStrings["PubsData"];
3:
4: DbConnection connection =
5: new SqlConnection(pubs.ConnectionString);
6: DbCommand cmd = connection.CreateCommand();
7: cmd.CommandType = CommandType.StoredProcedure;
8: cmd.CommandText = "uspGetCustomerById";
9:
10: DbParameter parm = cmd.CreateParameter();
11: parm.ParameterName = "@Id";
12: parm.Value = "AROUT";
13: cmd.Parameters.Add(parm);
Używanie metody ExecuteScalar
Można wykonać zapytanie, które ma zwrócić wynik tabeli zawierającej jeden wiersz i kolumnę, takie jak kwerendy, która np. pobiera całkowitą sprzedaż z danego dzinia. W takich sytuacjach, wyniki mogą być traktowane jako jedna wartość. Na przykład, następująca instrukcja SQL zwraca wynik, który składa się z pojedynczego wiersza w jednej kolumnie:
1: SELECT COUNT(*) FROM Sales
Poniższy kod pokazuje, jak korzystać z metody ExecuteScalar i łatwo pobrać ilość wierszy w tabeli Sprzedaż w zmiennej o nazwie count:
1: ConnectionStringSettings pubs =
2: ConfigurationManager.ConnectionStrings["PubsData"];
3:
4: DbConnection connection = new SqlConnection(pubs.ConnectionString);
5:
6: DbCommand cmd = connection.CreateCommand();
7: cmd.CommandType = CommandType.Text;
8: cmd.CommandText = "SELECT COUNT(*) FROM Sales";
9:
10: connection.Open();
11: int count = (int)cmd.ExecuteScalar();
12: connection.Close();
Używanie obiektu DbDataReader
Obiekt DbDataReader dostarcza metody wysokiej wydajności do pobierania danych z magazynu danych. Dostarcza dane tylko do odczytu itp. co sprawia, że jest idealny do wypełniania kontrolek ListBox, DropDownList czy nawet GridView, które mają dane tylko do odczytu.
DbDataReader zawiera metodę Read, która pobiera dane do swojego bufora. Tylko jeden
wiersz danych jest zawsze dostępny w danym czasie. Poniższy kod wykorzystuje metodę Load obiektu DataTable w celu wypełnienia DataTable bezpośrednio z obiektu DataReader połączonego z bazą danych:
1: ConnectionStringSettings pubs =
2: ConfigurationManager.ConnectionStrings["PubsData"];
3: DbConnection connection = new SqlConnection(pubs.ConnectionString);
4:
5: DbCommand cmd = connection.CreateCommand();
6: cmd.CommandType = CommandType.Text;
7: cmd.CommandText = "SELECT pub_id, pub_name FROM Publishers";
8: connection.Open();
9: DbDataReader rdr = cmd.ExecuteReader();
10: DataTable publishers = new DataTable();
11: publishers.Load(rdr, LoadOption.Upsert);
12: connection.Close();
13:
14: GridView1.DataSource = publishers;
15: GridView1.DataBind();
Korzystanie z DbDataAdapter
DbDataAdapter wykorzystywany jest do przetwarzania i aktualizowania danych pomiędzy DataTable i magazynem danych. Poniżej hierarchia:
DbDataAdapter posiada właściwość SelectCommand używaną podczas pobierania danych. SelectCommand musi zawierać prawidłowy obiekt DbCommand, który musi mieć ważne połączenie.
Korzystanie z metody Fill
Metoda Fill przenosi dane z magazynu danych do obiektu DataTable, który przekazujesz do tej metody.
Poniższy kod pokazuje jak DataTable mogą być ładowane przy użyciu metody Fill:
1: ConnectionStringSettings pubs =
2: ConfigurationManager.ConnectionStrings["PubsData"];
3:
4: DbConnection connection = new SqlConnection(pubs.ConnectionString);
5: SqlCommand cmd = (SqlCommand)connection.CreateCommand();
6: cmd.CommandType = CommandType.Text;
7: cmd.CommandText = "SELECT pub_id, pub_name FROM Publishers";
8:
9: SqlDataAdapter da = new SqlDataAdapter(cmd);
10: DataSet pubsDataSet = new DataSet("Pubs");
11: da.Fill(pubsDataSet, "publishers");
12: GridView1.DataSource = pubsDataSet;
Korzystanie z klasy DbProviderFactory
Istnieje wiele powodów do pisania aplikacji, która nie wymaga kodu specyficznego dla dostawcy bazy. Firma może chcieć, np. przejść z programu Microsoft Access do SQL Server itp.
Tu z pomocą przychodzi nam tytułowa klasa. Poniżej hierarchia:
Klasy fabryki dostawcy są implementowane jako singletony, Na przykład poniższy kod pokazuje jak utworzyć nowe połączenie za pomocą SqlClientFactory:
1: //Pobieramy instancję singletonu
2: DbProviderFactory factory = SqlClientFactory.Instance;
3: public DbConnection GetProviderConnection()
4: {
5: DbConnection connection = factory.CreateConnection();
6: connection.ConnectionString = @"Data Source=.\SQLEXPRESS;"
7: + "AttachDbFilename=|DataDirectory|PUBS.MDF;"
8: + "Integrated Security=True;User Instance=True";
9: return connection;
10: }
Używanie klasy DbProviderFactories
Aby zapytać o listę dostępnych fabryk, możesz użyć klasy DbProviderFactories. Zawiera ona metodę o nazwie GetFactoryClasses, która zwraca DataTable. Poniżej przykład pokazujący pobranie listy fabryk oraz informacji o nich i przypisanie do grid viewa:
1: DataTable providersList = null;
2: providersList = System.Data.Common.DbProviderFactories.GetFactoryClasses();
3: GridView1.DataSource = providersList;
4: GridView1.DataBind();
Używanie ADO.NET Transaction Object
Transakcja jest to atomowa jednostka pracy, która musi być wypełniona w całości. Transakcja się powiedzie, jeśli została wykona cała operacja. Transakcji ma cztery podstawowe właściwości: Cząsteczkowość, spójność, izolację i trwałość (tzw. właściwości ACID).
Możemy użyć obiektu DbConnection z metodą BeginTransaction, która tworzy obiekt DbTransaction. Dzięki temu otrzymamy możliwość obsługiwania transakcji. Poniższy kod pokazuje jak to osiągnąć:
1: ConnectionStringSettings cnSetting = ConfigurationManager.ConnectionStrings["PubsData"];
2: using (SqlConnection cn = new SqlConnection())
3: {
4: cn.ConnectionString = cnSetting.ConnectionString;
5: cn.Open();
6: using (SqlTransaction tran = cn.BeginTransaction())
7: {
8: try
9: {
10: //jakis kod
11: using (SqlCommand cmd = cn.CreateCommand())
12: {
13: cmd.Transaction = tran;
14: cmd.CommandText = "UPDATE jobs SET min_lvl=min_lvl * 1.1";
15: cmd.ExecuteNonQuery();
16: }
17: tran.Commit();
18: }
19: catch (Exception xcp)
20: {
21: tran.Rollback();
22: }
23: }
24: }
Używanie LINQ do pracy z danymi
Linq jest to bardzo fajny mechanizm wprowadzony w .NET 3.0, który np. z naszych tabel w bazie danych tworzy obiekty :) Dzięki linq nie tylko możemy się odnosić do baz Sql Serwer ale również XML czy zwykłych obiektów.
Aby zacząć zabawę z linq musimy stworzyć jakąś bazę oraz tabelę w niej. Następnie musimy stworzyć mapowanie bazy. Aby to osiągnąć dodajemy do projektu nowy plik LinqToSql. Następnie z Serwer Explorera przeciągamy nasze tabele na forme. Wygląda to np. tak:
Załóżmy, że nasz plik z mapowaniem nazywał się Dane. Dzięki Linq stworzy nam się klasa która nazwana zostanie (w tym wypadku) DaneDataContext. Dzięki niej będziemy mogli odwoływać się do obiektów stworzonych przez LINQ.
Zapytania w linq, tworzenie nowych danych
Aby wyciągnąć dane z tabeli za pomocą linq tworzymy najpierw nowy obiekt DataContext, o którym wspominałem w poprzednim akapicie a następnie za pomocą zapytania podobnego do sql wyciągamy dane. Poniższy przykład pokazuje jak to osiągnąć:
1: using (var zalacznik = new DaneLinqDataContext())
2:
3: var zatwierdz = from zat in zalacznik.Zalaczniks where zat.NewsId == null select zat;
4: }
Przykład dodania danych prezentuje się następująco:
1: using (var DodajNews = new DaneDataContext())
2: {
3:
4: New news = DodajNews.News.Single(n => n.Id == id);
5: news.Kategoria = int.Parse(KategorieDrop.SelectedValue);
6: news.Tytul = TytulNewsBox.Text;
7: news.SkroconyOpis = SkroconyOpisBox.Text;
8: news.Tresc = PoleWpis.Content;
9: news.Piorytet = int.Parse(PriorytetBox.SelectedValue);
10: news.Waga = int.Parse(WagaBox.SelectedValue);
11: news.DataUtworzenia = DateTime.Now;
12: DodajNews.SubmitChanges();
13:
14:
15: }
Widzimy, że wystarczy stworzyć nowy obiekt linq a następnie ustawiać właściwości danej klasy i na końcu je zapisać.
To wszystko w dzisiejszym artykule. Jak widzicie zakres materiału jest bardzo duży. W samym training kicie było ok 50 stron. Warto poczytać dużo więcej o powyższych zagadnieniach ponieważ przedstawione przeze mnie są tylko podstawowe informację. Nie pisałem również o asynchronicznym dostępnie do danych.
Wybaczcie literówki ale jest już troszkę późno a nie wiem czy jutro zdążę sprawdzić :)