Podstawy ADO.NET
Witam w moim tutorialu odnośnie podstaw technologii ADO.NET. Poznamy co to jest w ogóle za system oraz jak go wykorzystywac do prostych celow. Będziemy używać języka C# i używać trybu konsolowego, a następnie w ramach ćwiczeń sprobujemy zastosować bibliotekę WinForms do stworzenia aplikacji okienkowej...
Zapraszam :)
Fen

Uwaga:
materiał przedstawiany był na zajęciach z Baz Danych, jeden z kolegów udostępnil mi dostęp do bazy danych, więc teraz wszystkie adresy są nie dostępne. Wktórce może uda mi się zaktualizować ten tutorial.

Spis Treści:
  1. Wprowadzenie do ADO.NET
  2. Przygotowyanie aplikacji w C#
  3. Łączenie z Bazą Danych
  4. Wykonywanie poleceń SQL'a i prezentacja wyników (tryb połączeniowy)
  5. Podstawy pracy w trybie bezpołączeniowym
  6. Cwiczenia
Wprowadzenie do ADO.NET

ADO.NET jest zbiorem klas i interfejsów do łączenia się i obsługiwania baz danych poziomu aplikacji napisanych w technologii .NET. Mogą o więc być programy napisane na platformę Windows (w C# na przykład) lub dynamiczne strony internetowe korzystajace z technologii ASP.NET. Nie musimy się ograniczać tylko do baz danych, za źródła danych możemy takze wykorzystaywać pliki XML lub nawet zwykłe pliki tekstowe!

Przed ADO.NET jest wersja ADO napisana w technologii COM ActiveX. Nazwa ADO oznacza ActiveX Data Objects, dała ona nazwę ADO.NET, ale w rzeczywistości ADO.NET bardzo różni się od swojego "poprzednika".

ADO.NET zawiera zbiór klas, za pomocą których w łatwy i intuicyjny sposob możemy operować na danych zapisanych w bazie danych. Obsługa przypomina pracę z plikami, ale w większości przypadkow jest ona nawet łątwiejsza.

Jedym z podstawowych pojęć związanych z ADO.NET jest tzw. ".Net Data Privider". Są to zbiory klas slużące do komunikacji z pewnymi konkretnymi bazami danych. Podstawowi "providerze" to:

Każdy provider musi dostarczać nastepujących obiektów: W tej prezentacji zajmiemi się bazami SQL. Klasy wykorzytujące tego providera mają przedrostek Sql.

Tryb polączeniowy:
Wszelkie polecenia wykonuje się bezpśredni na źródle danych. Połączenie musi cały czas być aktwne. W tym trybie korzystamy głównie z klas Command oraz DataReader.

Tryb bezpołączeniowy:
Idea jest bardzo prosta: polącz się z bazą, pobierz odpowiedni zbiór danych i zapisz go do pamieci operacyjnej. Rozlacz się z bazą i wykonuj wszystkie operacje na wczesniej pobranych danych. Następnie polącz się znów z bazą i dokonaj synchronizacji danych. Do obsługi pamięci stosuje się klasę DataSet, a do łaczenia się i synchronizacji stosuje się klasy DataAdapter. Trzeba dodać, że klasa DataSet jest niezależna od providera.

Przygotowyanie aplikacji w C#
Naszym celem będzie stworzenie aplikacji w trybie konsolowym, w której użytkownik będzie wpisywał polecenie SQL'a a następnie, po naciśnięciu klawisza ENTER, program połączy sie z bazą danych, wykona kwerendę i zwróci wyniki. Zwróone wartości będzie wypisywać metodami podobnymi do standardowego "printf" w C.

Stwórzmy sobie etraz szkielet naszej aplikacji. Oto co trzeba wykonać:
  1. Otworzyć Visual Studion .NET 2005, a nastęnie wybrać File->New->Project Wpisujemy nazwę projektu ConsoleApplication1 i dajemy OK
  2. Aby zkompilować i zlinkować nasz program naciskamy klawisz F7 (lub Build->Build Solution). Aby uruchmić program naciskamy Ctr+F5 (lub Debug->Run Without Debugging).
    Dołóżmy parę komend, które wypiszą nam coś na ekranie:
    using System;
    using System.Collections.Generic;
    using System.Text;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                String name = "Bartek";
                int age = 21;
                System.Console.WriteLine("My name is {0}, and I'm {1} years old", name, age);
            }
        }
    }
    
    Aby wypisywać zmienne możemy użyć metody jak podono wyżej, czyli w stringu wpisujemy {n} a nasptęnie, po przecinku podajemy odpowiadającą zmienną. System automatycznie dokonuje formatowania.
    Można też używać operacji na stringach i napisać
    System.Console.WriteLine("My name is " + name + ", and I'm " + age + " years old");
    
    I powinno zwrócić podobny wynik :)
    Tyle na razie nam wystarczy, resztę można doczytac w dokumentacji...
  3. Kolejną rzeczą jest napisanie których "namespaców" będziemy używać w naszym projekcie. Dla naszych celów potrzebne sa następujace: System.Data.SqlClient oraz System.Data, więc po dodaniu kod wyglada następująco:
    using System;
    using System.Collections.Generic;
    using System.Text;
    
    using System.Data;
    using System.Data.SqlClient;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                String name = "Bartek";
                int age = 21;
                System.Console.WriteLine("My name is " + name + ", and I'm " + age + " years old");
            }
        }
    }
    
Mając już podstawową apliację możemy iść dalej...
Łączenie z Bazą Danych
Do połączenia z bazą danych musimy użyć obiektu SqlConnection, musimy mieć także przygotowanego tzw Connestion String'a, w naszym przypadku jest nim: server=193.138.147.99,5000;database=Northwind;user id=jdbctest;password=jdbctest;Network Library=DBMSSOCN;
"Server" - nazwa servera SQL, mozna także podać "Data Source"
"Database" - startowa baza danych, inna nazwa to "Initial Catalog"
"User ID", "Password" - parametry klienta
"Network Library" - DBMCSOCN oznacza, że lączymy się przez protokoł TCP/IP

Zatem aby się polączyć z bazą używamy następujacego kodu (dołączyłem obsługę wyjątów):
static void Main(string[] args)
{
    SqlConnection sqlConn = new SqlConnection();
    sqlConn.ConnectionString = "server=193.138.147.99,5000;"+
                               "database=Northwind;"+
                               "user id=jdbctest;"+
                               "password=jdbctest;"+
                               "Network Library=DBMSSOCN;";
    try
    {
        // otwórz połączenie:
        sqlConn.Open();

        System.Console.WriteLine("Połączono z bazą danych!");

        // zamknij połaczenie:
        sqlConn.Close();
    }
    catch (System.Data.SqlClient.SqlException se)
    {
        System.Console.WriteLine("Nastąpil bląd połaczenia: " + se);
    }
}
Wykonywanie poleceń SQL'a i prezentacja wyników (tryb połączeniowy)
  1. Spróbujmy dodać kod, który z poziomu aplikacji podwyższy nam ceny produków z kategorii 3 o 10 jednostek:
    Aby to zrobić potrzebujemy obiektu SqlCommand. Należy ustawić mu CommandText - polecenie do wykonania oraz Connection - czyli aktualne połaczenie z bazą.

    Metoda ExecuteNonQuery() wykonuje wszystkie polecenia, które zmieniają bazę danych, zwraca liczbę wierszy które zostały zmenione.

    Oro cały kod który wykona postawione wcześniej zadanie:
    // otwórz połączenie:
    sqlConn.Open();
    
    System.Console.WriteLine("Połączono z bazą danych!");
    
    // zmieńmy zawartość tabel:
    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandText = "update Products set UnitPrice = UnitPrice+10 where CategoryID = 3";
    sqlCmd.Connection = sqlConn;
    
    int rowsAffected = sqlCmd.ExecuteNonQuery();
    System.Console.WriteLine("Zmieniono: " + rowsAffected + " wierszy!");
    
    // zamknij połaczenie:
    sqlConn.Close();
    
    Na mojej bazie zostało zmienionych 13 wierszy.
  2. Kolejnym etapem będzie wyświetlenie danych z tabeli Products.
    Niech nasza aplikacja wypisze ProductName, UnitPrice oraz QuanttyPerUnit z produktów z kategorii 3
    Tym razem zadanie jest troche trudniejsze, musimy bowiem stworzyć dodatkowy obiekt SqlDataReader za pomocą którego bedziemy w stanie obsługiwać cały zwrócony zbiór poprzez wykonanie kwerendy.
    Jak zwykle musimy mieć obiekt SqlCommand z ustawionymi parametrami, aby wykonać zapytanie używamy metody ExecuteReader() która zwraca nam zbiór danych.
    SqlDataReader używa się bardzo prosto: metoda Reaad() czyta nam kolejny wiersz wyniku, i zwraca false jeśli nie ma już więcej wierszy.
    Do danych w wierszu stosujemy bardzo intuicyjny zapis ["nazwa kolumny"] - tak naprawdę dany wiersz jest rodzajem Slownika i wlaśnie dlatego w ten sposób możemy to zrobić.
    Na końcu musimy zamknąć naszego SqlDataReader...
    Jak widać obsługa tego obiektu bardzo przypomina obsługę zwyklych plików... moim zdaniem jest ona nawet prostsza.

    Oto kod:
    // wypisz wiersze:
    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandText = "select ProductName, UnitPrice, QuantityPerUnit from Products where CategoryID = 3";
    sqlCmd.Connection = sqlConn;
    
    SqlDataReader dataReader = sqlCmd.ExecuteReader();
    while (dataReader.Read())
    {
        Console.Write("{0, 32}", dataReader["ProductName"]);
        Console.Write("{0, 16}", dataReader["UnitPrice"]);
        Console.Write("{0, 25}", dataReader["QuantityPerUnit"]);
        Console.WriteLine();
    }
    dataReader.Close();
    

    Spośród wielu metod obiektu SqlCommand czasem przydaje się takze ExecuteScalar() Zwraca Ona pierwszą kolumnę w pierwszym wierszu z wyniku kwerendy, ale dzięki temu idealnie nadaje się do odczytywania wyników zapytań agregacyjnych jak COUNT(*), czy AVG(*):

    // policz wiersze w wyniku:
    sqlCmd.CommandText = "SELECT COUNT(*) from Products";
    int count = (int)sqlCmd.ExecuteScalar();
    Console.WriteLine("Liczba produktów: " + count);
    
Podstawy pracy w trybie bezpołączeniowym

Tryb bezapołączeniowy jak wcześniej wspominałem opiera się na założeniu, że większość pracy na bazie danych wykonujemy w swojej pamieci operacyjnej a co jakiś czas łaczymy sie z baza danych i wykonujemy scalanie wersji. Tryb ten zwiększa efektywność serwera baz danych, który musi na przykłąd obsługiwać wielu klientów jednoczenie. Zamiast utrzymywać z nimi stałe połączenia, aplikacja kliencka łączy się tylko wtedy kiedy musi pobrać dane lub je schynchronizować.
Takie podejście często jest stosowane w różnego rodzaju stronach internetowych, czy innych aplikacjach internetowych.

Aby móc wykorzystywać opisany wczesniej tryb musimy zastosować kilka klas: Oto co trzeba zrobic aby pobrać dane z bazy, wykonać proste operacje oraz synchronizacja z bazą:
  1. Tworzymy obiekt SqlConnection i ustawiamy mu poprawnego Connection String'a. Tak jak to było ostatnio Ale tym razem nie otwieramy połączenia.
  2. Tworzymy obiekt DataSet
  3. Tworzymy obiekt SqlDataAdapter i ustawiamy mu parametry połączeniowe oraz tzw: SelectCommand, na tym etapie warto sworzyć sobie obiekt pomocniczy typu SqlCommandBuilder który wygeneruje nam automatycznie inne potrzebne komendy do synchronizacji z bazą danych.
  4. Wykonac metodę SqlDataAdapter.Fill za pomocą której wypełnimy podany DataSet Pierwszy parametr to obiekt DataSet a drugi to string TableName czyli nazwa tabeli opd która zostaną zapisane dane do obiektu DataSet.
Oto kod, który do tej pory powinien być napisany:
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=193.138.143.99,5000;database=Northwind;user id=jdbc;password=jdbc;Network Library=DBMSSOCN";

try
{
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Products", conn);

    DataSet dset = new DataSet();
    
    // wypełnij obiekt DataSet i zapisz wynik w tabeli "MyTable"
    adapter.Fill(dset, "MyTable");
}

catch (SqlException sqle)
{
    Console.WriteLine(sqle);
}
Dalsza zabawa:
  1. Wykonac operacje na obiekcie DataSet, w tym celu możemy użyć następujacych metod:
    • DataSet.[nazwa tabeli] - zwraca tabelę o podanej nazwie
    • DataTable.Rows[numer wiersza][nazwa kolumny] - dostajemy konkretną wartość w kolumnie i wierszu
    • DataTable.Columns - informacje o kolumnach w tej tabeli
Wypisz wszystkie wiersze z tabeli "MyTable":
DataTable dt = dset.Tables["MyTable"];

foreach (DataRow dr in dt.Rows) 
{
    Console.WriteLine("product: {0, 25}, {1, 10}", dr["ProductName"], dr["UnitPrice"]);
}
Ustaw nową nazwę produktu:
for (int i = 0; i < dset.Tables["MyTable"].Rows.Count; ++i)
{
    if (dset.Tables["MyTable"].Rows[i]["ProductName"].ToString() == "Konbu")
    {
        Console.WriteLine("found...");
        dset.Tables["MyTable"].Rows[i]["ProductName"] = "Konbu Z Krakowa";
    }
}
  1. Użyć metody SqlDataAdapter.Update która wykona połaćzenie z bazą i schynronizuje obiekt DataSet z odpowiadającym zbiorem w bazie.
adapter.Update(dset, "MyTable");

Powyzej zaprezentowałem jedynie podstawy pracy w trybie bezpołączeniowym, ale to nie jest koniec jego możliwosci! W trybie tym możemy łączyć tabele, tworzyć tabele, tworzyc relacje i wiele innych. Jednak wymaga to juz przede wszystkim większego doświadczenia w samym ADO.NET.
Zachecam do przeglądnięcia sobie Helpa oraz materiałów na sieci o tym temacie aby się dowiedzieć więcej.

Mała praktyczna uwaga:
Jeśli chcemy odczytac tylko dane z bazy danych (a później nie musimy ich synchronizować) wystarczy, że napiszemy nasteujący kawałek kodu:

SqlConnection sqlconn = new SqlConnection(...);

sqlconn.Open();

SqlCommand command = new SqlCommand();
command.Connection = sqlconn;
command.CommandText = "SELECT * FROM Products";
command.CommandType = CommandType.Text;

SqlDataReader reader = command.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);

reader.Close();
sqlconn.Close();

Po prostu otwieramy nowe połączenie z bazą danych, a później wypełniamy obiekt DataTable i zamykamy wszystkie połączenia.
Od teraz wszystkie dane mamy w obiekcie typu DataTable i wszelkie odczyty wykonywane sa już na klienckiej pamięci operacyjnej.

Cwiczenia
  1. Dodaj a nastepnie usuń nową kategorię do tabeli Categories.
  2. Podwyższ cenę każdego produktu należącego do kategorii numer 4 o 25%.
  3. Wypisz Nazwy produktów, nazwę kategori do której należą oraz ich cenę. Wypisz tylko 10 pierwszych wierszy, jesli jest ich więcej.
  4. Policz sumę wszystkich cen oraz śrenią cenę produktów... użyj do tego metod ExecuteScalar z obiektu SqlCommand.
  5. Napisz program, który zapyta użytkownika jaki produkt chce obejrzeć, a nastęnie stwórz odpowiednie polecenie i kwerendę i przedstaw wynik na ekranie.
  6. Dodatkowe: Napisz program w WinForms w którym znajdują się dwa pola dycji (EditBox'y) oraz przycisk "RunQuery". W pierwsze pole edycji użytownik będzie wpisywał komendę SQL, naciskał RunQuery i wtedy wyniki będa się pokazywać w drugim polu edycji.

    przykładowy wygląd aplikacji powyżej :)