Artykuł ten ma na celu zrozumienie różnych pojęć związanych z dostępem do danych przy użyciu ADO.NET. Artykuł ten przeznaczony jest dla osób początkujących w którym zostaną omówione podstawowe techniki dostępu do danych przy użyciu wspomnianej wyżej technologii.

ADO.NET to zestaw klas na platformie .NET, które ułatwiają dostęp do danych. Technologia ta istnieje już od dłuższego czasu i zapewnia kompletny zestaw bibliotek pozwalający na dostęp do danych. Główną zaletą ADO.NET jest fakt, że pozwala aplikacją na dostęp do różnego typu danych przy użyciu tej samej metodologii. Jeżeli wiemy jak przy użyciu ADO.NET uzyskać dostęp do bazy danych SQL nie będziemy mieli problemu z dostępem do innego rodzaju bazy danych, tj. Oracle czy MS Access. Będziemy jednak musieli użyć innego zestawu klas.

Wielu programistów używa obecnie ORM'y, aby uzyskać dostęp do baz danych. ORM zapewnia wiele sposobów dostępu do danych oraz uwalnia programistów od ciągłego i żmudnego pisania kodu. Osobiście uważam, że znajomość i zrozumienie ADO.NET jest niezbędne dla programisty .NET ponieważ pozwala na lepsze zrozumienie metod dostępu do danych. Ponadto, wiele firm wciąż używa ADO.NET.

Poniżej wizualizacja dostępu do danych przy użyciu ADO.NET:



Powyższy diagram pokazuje, że ADO.NET może być używane z różnymi typami aplikacji, tzn. mogą to być aplikację okienkowe, webowe. Może to być również WPF czy Silverlight.

Postarajmy się rozumieć kilka klas ADO.NET oraz metodologii pisania małych aplikacji. Aplikacja używa przykładej bazy danych firmy Microsoft, tj. AdventureWorks - która była już używana w przykładzie LINQtoSQL. Wspomniana baza danych pozwoli nam zrozumieć różne klasy oraz metody ADO.NET. Będziemy używac klas dla SQL Server’a, jednakże, podstawowe użycie kodu pozostaje takie samo i może być użyte przy dostępie do innego rodzaju bazy danych.

Zanim jednak przeniesiemy się do kodu należy zrozumieć kilka ważnych obiektów ADO.NET. W przykładowym scenariuszu wymagającym dostępu do danych, należy wykonać poniższe kroki:

1. połączyć się z bazą danych
2. przekazać żądanie do bazy danych, tj. wykonać polecenie Select, Insert czy Update
3. pobrać rezultat takiej operacji, tj. wiersze lub/i liczbę wierszy dodanych/zmienionych
4. przechować dane i wyświetlić je użytkownikowi

Klasa Connection jest używana do nawiązania połączenia z bazą danych. Klasa ta używa ConnectionString aby wskazać lokalizcję serwera, parametry uwierzytelnienia oraz inne informacje niezbęde do połączenia się z bazą danych. ConnectionString jest zwykle przechowywany wewnątrz app.config:

<connectionStrings>
    <add name="AdventureWorks2012_DataEntities" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;
AttachDbFilename=|DataDirectory|\Common\Database\AdventureWorks2012_Data.mdf;Integrated Security=True" providerName="System.Data.EntityClient" />
</connectionStrings>

// Zczytujemy nasz 'connectionString' z pliku app.config
// Jeżeli brakuje Wam klasy ConfigurationManger dodajcie referencję do System.Configuration
string connectionString = ConfigurationManager.ConnectionStrings["AdventureWorks2012_DataEntities"].ConnectionString;
// Ustalamy połączenie z bazą danych - nie jest ono jeszcze otwarte
SqlConnection con = new SqlConnection(connectionString);

Zanim przejdziemy do sekcji poświęconej wykonywaniu poleceń na bazie danych musimy zrozumieć jak przechowywać te dane i jak te dane mogą zostać wyświetlone użytkownikowi. Aby zaznajomić się z powyższym pojęciem należy poznać kilka z obiektów ADO.NET:

• DataReader - jest obiektem, którego używamy, gdy chcemy uzyskać dostęp po kolei do wyników zwracanych z bazy danych. Obiekt DataReader jest używany, aby poruszać się do przodu w sposób sekwencyjny w trakcie wykonywania zapytania. Jest używany z obiektem Command;
• Dataset - może być traktowany jako reprezentacja w pamięci danych z bazy danych. Rezutalt zapytania do bazy danych może być przechowywany w tej kolekcji. Dataset zawiera DataTables. Z kolei DataTables zawiera w sobie DataRow oraz DataColumn. DataSet lub DataTable może być używany z Command i obiektem DataAdapter do przechowywania wyników zapytania;
• DataAdapter - jest obiektem używanym do wypełniania DataSet/DataTable wynikiem zapytania z bazy danych. Może być traktowany jako łącznik pomiędzy połączonym i odłączonym modelem danych. Obiekt Command jest używany do wywołania zapytania a DataAdapter użyje obiektu Command, aby wypełnić DataSet/DataTable danymi zwracanymi w wyniku odpytywania bazy danych.

1. Jest więcej obiektów, które mogą/są używane do przechowywania danych, jednakże w artykule będą głównie używane powyższe obiekty.
2. Użycie i implementacja tych obiektów jest przedstawiona w kolejnej sekcji ponieważ zrozumienie obiektu Command jest wymagane wcześniej.

Po przygotowaniu połączenia z bazą danych następnym krokiem jest poinformowanie bazy danych o operacji, którą chcemy przeprowadzić. Możemy tego dokonać za pomocą obiektu Command. Będziemy również używać SqlCommand aby powiedzieć bazie danych o operacji, którą chcemy zreazliować. Podstawowe komendy na bazie danych to:

1. Polecenie Select – zwraca zestaw wierszy do aplikacji.
2. Polecenie Insert – zwraca liczbę wierszy dodanych.
3. Polecenie Delete – zwraca liczbę wierszy usuniętych.
4. Polecenie Update – zwraca liczbę wierszy zaktualizowanych.

// tworzymy nowe polecenie
SqlCommand cmd = con.CreateCommand();
// określamy typ naszego polecenia
cmd.CommandType = CommandType.StoredProcedure;
// definiujemy nazwę procedury składowanej do wywołania
cmd.CommandText = NazwaProcedurySkladownej;

// tworzymy nowe polecenie
SqlCommand cmd = con.CreateCommand();
// określamy typ naszego polecenia
cmd.CommandType = CommandType.StoredProcedure;
// definiujemy nazwę procedury składowanej do wywołania
cmd.CommandText = NazwaProcedurySkladownej;
// definiujemy nowy parametr wraz z przypisaniem do niego wartości
SqlParameter param = new SqlParameter("@id", id);
// dodajmy tak zdefiniowany parametr do naszego polecenia
cmd.Parameters.Add(param);

// tworzymy nowe polecenie
SqlCommand cmd = new SqlCommand();
// określamy typ naszego polecenia
cmd.CommandType = CommandType.Text;
// definiujemy zapytanie do bazy danych
string query = "select * from Person";
// dodajemy zdefiniowane zapytanie sql do naszego polecenia
cmd.CommandText = query;

// tworzymy nowe polecenie
SqlCommand cmd = new SqlCommand();
// określamy typ naszego polecenia
cmd.CommandType = CommandType.Text;
// definiujemy zapytanie do bazy danych
string query = "select * from Person where BusinessEntityID = " + businessEntityId;
// dodajemy zdefiniowane zapytanie sql do naszego polecenia
cmd.CommandText = query;

int businessEntityId = 3;
// tworzymy nowe polecenie
SqlCommand cmd = new SqlCommand();
// określamy typ naszego polecenia
cmd.CommandType = CommandType.Text;
// definiujemy zapytanie do bazy danych
string query = "select * from Person where BusinessEntityID = @id";
// definiujemy nowy parametr wraz z przypisaniem do niego wartości
SqlParameter param = new SqlParameter("@id", businessEntityId);
// dodajemy zdefiniowane zapytanie sql do naszego polecenia
cmd.CommandText = query;

Przejdziemy teraz do kolejnego etapu, tj. przetwarzania danych będących rezultatem zapytania Select. Dane te będą przechowywane w DataTable. Poniżej obszerny przykład ze szczegółowym omówieniem:

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace ADO_NET
{
    class Program
    {
        static void Main(string[] args)
        {
            // Zczytujemy nasz 'connectionString' z pliku app.config
            // Jeżeli brakuje Wam klasy ConfigurationManger dodajcie referencję do System.Configuration
            string connectionString = ConfigurationManager.ConnectionStrings["AdventureWorks2012_DataEntities"].ConnectionString;
            // Ustalamy połączenie z bazą danych - nie jest ono jeszcze otwarte
            SqlConnection con = new SqlConnection(connectionString);
            // definiujemy typ polecenia oraz zapytanie SQL
            CommandType cmd = CommandType.Text;
            string commandName = "Select * from HumanResources.Employee";
            // Przy pomocy wcześniej przygotowanej metody wywołujemy nasze zapytanie
            DataTable table = ExecuteSelectCommand(con, cmd, commandName);
            // Oraz w konsoli wypisujemy dane - celem sprawdzenia poprawności wykonania zapytania
            foreach (DataRow item in table.Rows)
            {
                Console.WriteLine("Id: {0}, Job title: {1}, Płeć: {2}",
                    item["BusinessEntityID"],
                    item["JobTitle"].ToString(),
                    item["Gender"].ToString());
            }
            Console.ReadKey();
            Console.WriteLine("----------------------------------------------");
            // A teraz przejdziemy do wykonania zapytania Sql z podanymi parametrami
            // CommandType się nie zmienia: CommandType cmd = CommandType.Text;
            // Zmienia się jedynie commandName
            commandName = @"Select * from HumanResources.Employee where BusinessEntityID > @id and
                            JobTitle = @job";
            int id = 150;
            string jobTitle = "Buyer";
            SqlParameter[] paramList = new SqlParameter[2];
            SqlParameter param = new SqlParameter("@id", id);
            SqlParameter param2 = new SqlParameter("job", jobTitle);
            paramList[0] = param;
            paramList[1] = param2;
            table = ExecuteSelectCommandWithParameters(con, cmd, commandName, paramList);
            // oraz tradycyjne juz sprawdzimy poprawność danych
            foreach (DataRow item in table.Rows)
            {
                Console.WriteLine("Id: {0}, Job title: {1}, Płeć: {2}",
                    item["BusinessEntityID"],
                    item["JobTitle"].ToString(),
                    item["Gender"].ToString());
            }
            Console.ReadKey();
        }
        /// <summary>
        /// Metoda wykonuje zapytanie sql do bazy danych
        /// Dzięki tej metodzie wystarczy przekazać wymagane parametry
        /// Nie trzebą będzie powielać tego samego kodu
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="commandName"></param>
        /// <returns></returns>
        public static DataTable ExecuteSelectCommand(SqlConnection con, CommandType cmdType, string commandName)
        {
            SqlCommand cmd = null;
            DataTable table = new DataTable();
            // Sprawdzamy stan naszego połączenia, jeżeli zamknięty to otwieramy połączenie
            // Takie podejście jest rozwiązaniem problemu:
            // ServerVersion throws 'System.InvalidOperationException'
            if (con.State == ConnectionState.Closed)
                con.Open();
            // tworzymy nowe polecenie
            cmd = con.CreateCommand();
            // określamy typ polecenia(parametr metody - cmdType)
            cmd.CommandType = cmdType;
            // określamy zapytanie do wykonania(parametr metody - commandName)
            cmd.CommandText = commandName;
            try
            {
                // SqlDataAdapter wypełnia DataSet/DataTable danymi zwrotnymi zapytania
                SqlDataAdapter da = new SqlDataAdapter();
                using (da = new SqlDataAdapter(cmd))
                {
                    da.Fill(table);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                cmd = null;
                con.Close();
            }
            return table;
        }
        /// <summary>
        /// Metoda wykonuje zapytanie sql do bazy danych wraz z parametrami zapytania
        /// Dzięki tej metodzie wystarczy przekazać wymagane parametry
        /// Nie trzebą będzie powielać tego samego kodu
        /// </summary>
        /// <param name="con"></param>
        /// <param name="cmdType"></param>
        /// <param name="commandText"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable ExecuteSelectCommandWithParameters(SqlConnection con, CommandType cmdType, string commandText, SqlParameter[] param)
        {
            SqlCommand cmd = null;
            DataTable table = new DataTable();
            if (con.State == ConnectionState.Closed)
                con.Open();
            cmd = con.CreateCommand();
            cmd.CommandType = cmdType;
            cmd.CommandText = commandText;
            // do zapytania dodajemy zdefiniowane wcześniej parametry
            // ich liczba nie jest ograniczona, na wejściu przyjmujemy listę parametrów
            cmd.Parameters.AddRange(param);
            try
            {
                // SqlDataAdapter wypełnia DataSet/DataTable danymi zwrotnymi zapytania
                SqlDataAdapter da = new SqlDataAdapter();
                using (da = new SqlDataAdapter(cmd))
                {
                    da.Fill(table);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Dispose();
                cmd = null;
                con.Close();
            }
            return table;
        }
        class test
        {
            public test()
            {
                int businessEntityId = 3;
                // tworzymy nowe polecenie
                SqlCommand cmd = new SqlCommand();
                // określamy typ naszego polecenia
                cmd.CommandType = CommandType.Text;
                // definiujemy zapytanie do bazy danych
                string query = "select * from Person where BusinessEntityID = " + businessEntityId;
                // dodajemy zdefiniowane zapytanie sql do naszego polecenia
                cmd.CommandText = query;
            }
        }
        class test2
        {
            public test2()
            {
                int businessEntityId = 3;
                // tworzymy nowe polecenie
                SqlCommand cmd = new SqlCommand();
                // określamy typ naszego polecenia
                cmd.CommandType = CommandType.Text;
                // definiujemy zapytanie do bazy danych
                string query = "select * from Person where BusinessEntityID = @id";
                // definiujemy nowy parametr wraz z przypisaniem do niego wartości
                SqlParameter param = new SqlParameter("@id", businessEntityId);
                // dodajemy zdefiniowane zapytanie sql do naszego polecenia
                cmd.CommandText = query;
            }
        }
        class test3
        {
            public test3()
            {
                SqlConnection con = null;
                con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings.Get("AdventureWorks2012_DataEntities"));
                // tworzymy nowe polecenie
                SqlCommand cmd = con.CreateCommand();
                // określamy typ naszego polecenia
                cmd.CommandType = CommandType.StoredProcedure;
                // definiujemy nazwę procedury składowanej do wywołania
                //cmd.CommandText = NazwaProcedurySkladownej;
                // definiujemy nowy parametr wraz z przypisaniem do niego wartości
                SqlParameter param = new SqlParameter("@id", 2);
                // dodajmy tak zdefiniowany parametr do naszego polecenia
                cmd.Parameters.Add(param);
            }
        }
    }
}

Procedury składowane zostały wspomniane wcześniej w artykule. Cechują się innym typem wykonywanego polecenia. Tak jak w przypadku polecenia Select mogą być wywoływane z parametrami lub bez. Poniżej przykład użycia procedur składowanych z parametrem:

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace ADO_NET_Procedures
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["AdventureWorks2012_DataEntities"].ConnectionString;
            SqlConnection con = new SqlConnection(connectionString);
            // zmieniamy typ polecenia na StoredProcedure
            CommandType cmdType = CommandType.StoredProcedure;
            // podajemy nazwę procedury, którą chcemy wywołać
            string CommandText = "uspGetEmployeeManagers";
            // przekazujemy wymagane parametry
            SqlParameter param = new SqlParameter("@BusinessEntityID", 5);
            bool result = ExectuteProcedure(con, cmdType, CommandText, param);
            Console.WriteLine();
            Console.WriteLine("Rezultat wykonania procedury składowanej: {0}", result);
            Console.ReadKey();
        }
        static bool ExectuteProcedure(SqlConnection con, CommandType cmdType, string commandText, SqlParameter param)
        {
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = cmdType;
            cmd.CommandText = commandText;
            cmd.Parameters.Add(param);
            // Sprawdzamy stan naszego połączenia, jeżeli zamknięty to otwieramy połączenie
            // Takie podejście jest rozwiązaniem problemu:
            // ServerVersion throws 'System.InvalidOperationException'
            if (con.State == ConnectionState.Closed)
                con.Open();
            try
            {
                // Zgodnie z definicją klasy, zczytujemy rekordy sekwencyjnie i wyświetamy ich zawartość
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("Poziom: {0}, Id: {1}, Imię: {2}, Nazwisko: {3}, Manager: {4}",
                        reader.GetInt32(0).ToString(),
                        reader.GetInt32(1).ToString(),
                        reader.GetString(2),
                        reader.GetString(3),
                        reader.GetString(5) + " " + reader.GetString(6));
                }
                // mimo polecenia return true, blok finally zostanie wykonany
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                cmd = null;
                con.Close();
            }
            return false;
        }
    }
}

Polecenia takie jak: Insert, Update oraz Delete są wykonywane przez wywołanie metody ExecuteNonQuery(). Podobnie jak w powyższych przypadkach zostanie przygotowana metoda do której będziemy przekazywać wymagane parametry. Polecenia te mogą być przekazywane jako zapytania z aplikacji bądź przez wywołanie procedur składowanych.

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace ADO_NET_Insert_Update_Delete
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["AdventureWorks2012_DataEntities"].ConnectionString;
            SqlConnection con = new SqlConnection(connectionString);
            CommandType cmdType = CommandType.Text;
            string commandText = "Delete from HumanResources.Department where DepartmentId = @id";
            // przykładowe id to usunięcia
            SqlParameter param = new SqlParameter("@id", 17);
            bool result = ExecuteNonQuery(con, cmdType, commandText, param);
            if (result)
                Console.WriteLine("Wykonanie się powiodło");
            else
                Console.WriteLine("Wywołanie się nie powiodło");
            Console.ReadKey();
        }
        static bool ExecuteNonQuery(SqlConnection con, CommandType cmdType, string commandText, SqlParameter param)
        {
            int result = 0;
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = cmdType;
            cmd.CommandText = commandText;
            cmd.Parameters.Add(param);
            if (con.State == ConnectionState.Closed)
                con.Open();
            try
            {
                // wykonanie polecenia, w tym wypadku 'result' to liczba usuniętych rekordów
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                cmd = null;
                con.Close();
            }
            if (result >= 1)
                return true;
            return false;
        }
    }
}

ADO.NET jest obecne na rynku od wiele lat. Wiele osób może pomyśleć, że ten artykuł jest bezużytyczny i praktycznie nikt nie używa już ADO.NET. Jednakże, został on napisany z punktu widzenia początkującego programisty, który dopiero zaczyna swoją przygodę z programowaniem lub chce utrwalić sobie podstawową widzę o tej technologii. Ponadto, obecność ORM’ów sprawiła, że powyższe rozwiązanie stało się przestrzałe. Znajomość ADO.NET może być jednak pomocna w lepszym zrozumieniu dostępu do danych.