ado.net tworzenie elastycznych aplikacji cz. 1

Wiadomo powszechnie że programista jest stworzeniem leniwym. Visual studio udostępnia mnóstwo różnych kreatorów które pozwalają na wyklikanie wielu różnych rzeczy. Niestety minusem tego podejścia jest kompletna niewiedza na temat tego jak pewne rzeczy działają. Dodatkowo często natrafimy na różne ograniczenia, które w pewnym momencie sprawią że albo będzie trzeba mocno się nakombinować, albo wręcz trzeba będzie napisać własne rozwiązanie.

Przykład który prezentuje tym razem jest raczej banalny i jest przeznaczony dla początkujących programistów. Podobnie jak udostępniona klasa jodb, jest mocno spolszczony i dodatkowo jest opisana praktycznie każda linia kodu.

Wyobraźmy sobie sytuację że mamy napisać aplikację do wprowadzania danych (na początku załóżmy że te dane nie muszą być walidowane w jakiś sensowny sposób). By sprawę maksymalnie uprościć załóżmy że będziemy dodawać klientów do bazy danych. Ale jak wiadomo wymagania mogą z czasem się zmienić i chcielibyśmy by nasza aplikacja była dość elastyczna.

Stwórzmy na początek bazę danych i tabelę gdzie będziemy trzymać nasze dane:

create database elasticApp;
use elasticApp;
go

create table klienci (
kli_id int not null primary key identity(1,1),
kli_imie varchar(255),
kli_nazwisko varchar(255),
kli_mail varchar(255),
kli_dataDodania datetime default getdate(),
kli_aktywny int default 1
)

Jak widać mamy prostą tabelę. Możemy napisać teraz prostą aplikację która będzie korzystała z jodb,
napiszemy sql z parametrami i można by powiedzieć że jest ok. Jednak jeśli ktoś chciałby byśmy zbierali jeszcze jedno pole np miejsce urodzenia, to musimy dokonać zmiany w bazie danych oraz zmiany w aplikacji (zmiana sql, dodanie kolejnych kontrolek itd).

Możemy też napisać aplikację, która sama zareaguje na zmiany w bazie i nie będziemy musieli nic zmieniać.

Nasza aplikacja może pobierać definicje tabeli z bazy i na tej podstawie generować pola tekstowe i później dynamicznie tworzyć zapytanie SQL by te dane zapisać.


string q = "SELECT top 1 column_name,data_type, character_maximum_length FROM"
+ " information_schema.COLUMNS WHERE table_name= @nazwaTabeli"
+ " ORDER BY ordinal_position";

powyższe zapytanie wklejone w C# i wykonane pozwoli nam na pobranie listy kolumn.

Później na podstawie listy kolumn generujemy textboxy:


//iterowanie po wszystich wierszach w obiektu datatable
// kazdy wiersz odpowiadna jednej kolumnie
//nazwa kolumny jest w column_name
foreach (DataRow dr in opisTabel.AsEnumerable())
{
if (!dr["column_name"].ToString().ToLower().Contains("id") && !dr["column_name"].ToString().ToLower().Contains("datadodania"))
{
Label l = new Label(); // stworzenie nowej labelki (opis tekstowy);
TextBox t = new TextBox(); // stworzenie nowego textboxa (pole tekstowe).
l.Name = "label_" + dr["column_name"].ToString(); //nadanie nazwy labelki
l.Text = dr["column_name"].ToString(); // ustawienie wyswietlanego tekstu
l.Top = 10 + a; // polozenie od gory
l.Left = 10; // polezenie od lewego brzegu formy
l.Show(); // pokazanie labelki*

t.Name = dr["column_name"].ToString(); //nadanie nazwy dla textboxa
t.Top = 10 + a; // polozenie od gory
t.Left = 160; // polezenie od lewego brzegu formy
t.Show();// pokazanie textboxa*

this.Controls.Add(t); // dodanie textboxa do listy kontrolek na formie
this.Controls.Add(l); // dodanie labelki do kontrolki na formie

a += 25; //dodanie 12 do ostatniej wartosci a
}
}
// po petli stworzymy guzik ktory bedzie zapisywał dane do bazy
Button b = new Button(); // stworzenie obiektu guzika
b.Left = 160; // polozenie od lewej
b.Top = 10 + 25 + a; //polozenie od gory
b.Text = "zapisz"; // nadanie etykiety guzika
b.Click += b_Click; //przypisanie eventu klikniecia do metody (ponizej)
b.Show(); //pokazanie guzika*
this.Controls.Add(b); // dodanie go do formy

//* obiekt zostanie pokazany na formie dopiero po tym jak zostanie dodany do kontrolek
// jesli zostala uzyta metoda show, a obiekt nie znajduje sie w kolekcji, nic sie nie pojawi.
}

Mając przygotowaną formatkę musimy jeszcze przygotować event który obsłuży nam zapis do bazy:


//metoda do obslugi zapisu danych
void b_Click(object sender, EventArgs e)
{
//slownik w ktorym bedziemy zbierac dane do zapisania
Dictionary slownik_danych = new Dictionary();
// przeiterowanie sie po textboxach w celu stworzenia slownika z danymi
foreach (Control c in this.Controls)
{
//prawie 'refleksja' sprawdzenie typow do rzutowania
if (c.GetType() == typeof(TextBox))
{
//rzutowanie kontrolki c na textbox
TextBox t = (TextBox)c;

//stworzenie danych w slowniku nazwa kontrolki (nazwa kolumny) oraz wpisana wartosc
slownik_danych.Add(t.Name, t.Text);
}
}

//zapisanie wprowadzonych danych do bazy
int i = jdb.zapisz_dane(slownik_danych, "klienci");
// sorawdzenie czy się udało zapisać dane:
if (i != 1)
{
// jesli nie to pokazujemy ładny komunikat.
MessageBox.Show("Nie udało się zapisać danych, popraw je", "błąd", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}

Oczywiście do tej pory w jdb nie mieliśmy metody która by potrafiła wygenerować odpowiedni SQL do zapisu danych, poniżej metody które pozwolą to osiągnąć:


///

/// wykonuje sql command z zapytaniami insert/update
///

/// sql command /// ilość wierszy
public int zapisz_dane(SqlCommand sqlc)
{
sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
return sqlc.ExecuteNonQuery();
}

///

/// pobiera opis wybranej tabeli
///

/// nazwa tabeli /// informacje o kolumnach w kolejnosci w jakiej są wyświetlane w sql server
public DataTable pobierz_opis_tabeli(string nazwaTabeli)
{
//zapytanie do information_schema pobierajace opis tabeli
string q = "SELECT column_name,data_type, character_maximum_length FROM"
+ " information_schema.COLUMNS WHERE table_name=@nazwaTabeli"
+ " ORDER BY ordinal_position";

//zwraca obiekt zwrocony przez metode pobierz dane z parametrem
return this.pobierz_dane(q, "@nazwaTabeli", nazwaTabeli);
}

///

/// metoda do wyznaczenia prefixu w nazwach kolumn
///

/// nazwa tabeli /// pefix uzywany w kolumnach
public string getColPrefix(string nazwaTabeli)
{
//probuje wyznaczyc prefix
try
{
// zapytanie ktore zwroci 1 wiersz z info o kolumnach w tabeli
string q = "SELECT top 1 column_name,data_type, character_maximum_length FROM"
+ " information_schema.COLUMNS WHERE table_name= @nazwaTabeli"
+ " ORDER BY ordinal_position";
//pobieranie danych do datatable
DataTable dt = this.pobierz_dane(q, "@nazwaTabeli", nazwaTabeli);

// stworzenie zmiennej prefix ktora zawiera cala nazwe kolumny z 1 wiersza
string prefix = dt.Rows[0]["column_name"].ToString();
int position = prefix.IndexOf('_'); // wyznaczenie pozycji znaku _ ktory oddziela prefix
prefix = prefix.Substring(0, position); // wyznaczenie napisu przed znakiem _

return prefix; // zwrocenie prefixu
}
// jesli sie nie udalo to zwroci null,
// mozna ustawic breakpoint na e.toString by sprawdzic jaki dokladnie blad wystapil
catch (Exception e)
{
e.ToString();
return null;
}
}

///

/// metoda ktora zwraca liste kolumn w formacie listy stringow
///

/// data table z wynikiem zapytania o opis tabeli /// liste stringow z nazwami kolumn
public List gen_lista_kolumn(DataTable dt)
{
//stworzenie nowej listy
List lista = new List();
//iterowanie po liscie kolumn dostarczonej jako parametr
foreach (DataRow dr in dt.AsEnumerable() ){
//dodaj do listy kolumne column_name z wiersza
lista.Add(dr["column_name"].ToString());
}
//zwroc liste stringow
return lista;
}

///

/// metoda do budowania zapytan typu insert
///

/// slownik z danymi do zapisania w bazie /// nazwa tabeli gdzie dane maja byc zapisane /// obiekt sql command do zapisania
public SqlCommand zbuduj_zapytanie_zapisujace(Dictionary daneWej, string nazwaTabeli)
{
//stworzenie obiektu ktory zostanie zwrocony
SqlCommand komenda_sql = new SqlCommand();
//zmienna kontrolna (wykorzystana pozniej)
int inu = 0;
//wygenerowanie listy kolumn w tabeli
List lista_kolumn = this.gen_lista_kolumn(this.pobierz_opis_tabeli(nazwaTabeli));
//zapytanie sql
string sql = " INSERT INTO " + nazwaTabeli + " (";

//iterowanie po kazdym wystapieniu ze slownika w celu zbudowania listy kolumn
foreach (var c in daneWej)
{
// linq (tutaj troche zbedne poniewaz metoda zostala uproszczona)
var kolatr = from p in lista_kolumn
where p.ToLower() == c.Key.ToLower()
select p;
// powyzsze linq sprawdza czy kolumna (c.key) znajduje sie na liscie kolumn w tabeli
kolatr.ToArray().Length.ToString();

//sprawdzenie czy linq zwrocilo jeden element (po drodze cast na tablice).
if (kolatr.ToArray().Length ==1)
{
//sprawdzenie czy dane do zapisania sa 'sensowne'
if (c.Value != null && c.Value.ToString() != "")
{
//doklejenie odpowiedniego kawalka sql
sql += c.Key + ", ";
//inkrementacja zmiennej kontrolnej
inu++;
}
}
}
// po petli usuwam zbedny przecinek
sql = sql.Substring(0, sql.Length - 2);

//dodaje kolejny kawalek kodu sql
sql += ") VALUES (";

// kolejna iteracja po danych wejsciowych teraz by dodac wartosci do zapytania
foreach (var c in daneWej)
{
// to samo linq co wyzej
var kolatr = from p in lista_kolumn
where p.ToLower() == c.Key.ToLower()
select p;
//sprawdzenie czy linq jest ok | tak jak wyzej
if (kolatr.ToArray().Length == 1)
{
//proba stworzenia odpowiedniego parametru i dodanie go sqlcommand
try
{
//sprawdzenie czy dane do zapisu maja 'sens' (jak wyzej)
if (c.Value != null && c.Value.ToString() != "")
{
//stworzenie nazwy parametru
string paramName = "@" + c.Key;
//dodanie parametru do sql
sql += paramName + ", ";
//stworzenie obiektu parametru
SqlParameter param = new SqlParameter (paramName, c.Value);
//dodanie parametru do sql command
komenda_sql.Parameters.Add(param);
}
}
//jesli sie cos nie powiodlo to mozna ustawic sobie breakpoint by zbadac dlaczego nie dziala.
catch (Exception e)
{
e.ToString();
}
}
}

//usuwam zbedne przecinki
sql = sql.Substring(0, sql.Length - 2);
// zakonczenie zapytania
sql += ")";

//sprawdzenie czy udalo sie dodac chociaz jedna kolumne
//zmienna kontrolna :)
if (inu > 0)
{
// przypisanie sklejonego stringa do komendy
komenda_sql.CommandText = sql;
//przypisanie polaczenia do komendy (teraz komenda jest gotowa do wykonania)
komenda_sql.Connection = this.polaczenie;
return komenda_sql;
}
//jesli nie udalo sie dodac zadnej kolumny to zwracamy null
return null;
}

///

/// zapisuje w bazie korzystajac z dynamicznego tworzenia zapytania
///

/// dane do zapisania /// nazwa tabeli gdzie dane maja byc zapisane /// 1 w przypadku sukcesu, -1 gdy porażka
public int zapisz_dane(Dictionary daneWej, string nazwaTabeli)
{
//proba zapisu
try
{
return this.zapisz_dane(this.zbuduj_zapytanie_zapisujace(daneWej, nazwaTabeli));
}
// jesli sie nie udalo, mozna ustawic break point by zobaczyc co poszlo nie tak
catch (Exception e)
{
e.ToString();
return -1;
}
}

kompletny projekt można ściągnąć tu: ElasticApp1

md5 i t-sql

Aby wyliczyć hash md5 w sql server korzystając z t-sql, powinniśmy zdefiniować prostą funkcję która nam to ułatwi. Jako że w większości przypadków posługujemy się hashem zapisanym jako 32 znaki w zapisie 16stkowym.

create FUNCTION genmd5
(
@inputstring varchar(max)
)
RETURNS varchar(32)
AS
BEGIN

return LOWER(CONVERT(VARCHAR(32), HashBytes('MD5', CONVERT(varchar, @inputstring )), 2))

END
GO

To co jest istotne w tej funkcji to linia:
return LOWER(CONVERT(VARCHAR(32), HashBytes('MD5', CONVERT(varchar, @inputstring )), 2))

wygląda ona dość skomplikowanie ze względu na konwersje. Należy się upewnić że ciąg wejściowy to varchar inaczej funkcja HashBytes może zwrócić inne wyniki (hashowanie odbywa się na poziomie bajtów a różne typy danych mogą mieć różne zapisy).
Jako że podobnie jak w .NET wynik ejst zwracany jako zapis bajtów należy go również zamienić na napis w odpowiedni sposób. Przyjęło się że hashe są przekazywane małymi literami.

md5 i .net (C#)

Czasem zachodzi potrzeba wygenerowania hasha MD5, aby tego dokonać w środowisku .NET potrzebujemy napisać prostą metodę:


public static string genMD5(string input)
{
System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create();
byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(input);
byte[] hashBytes = md5.ComputeHash(inputBytes);

StringBuilder sb = new StringBuilder();
for (int i = 0; i < hashBytes.Length; i++) { sb.Append(hashBytes[i].ToString("X2")); } return sb.ToString().ToLower(); }

tłumacząc kod od góry jest to publiczna metoda statyczna (można jej użyć bez instancjonowania klasy). Jako argument przyjmuje ona string który chcemy zakodować.

System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create();
Tworzy nam obiekt md5, który odpowiada za generowanie hashy (skrótów).

System.Security.Cryptography
jest namespacem, którego użyłem by nie było problemu z usingami, można też to dodać na samej górze i wtedy stworzenie obiektu będzie wyglądało tak:

MD5 md5 = MD5.Create();

byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(input);
Zamienia nasz napis na tablicę bajtów, które zostaną użyte to skalkulowania hasha.

byte[] hashBytes = md5.ComputeHash(inputBytes);
Powyższa linia tworzy tablicę bajtów w której znajduje się 16 elementów (Md5 zwraca hashe o długości 128bitów).


StringBuilder sb = new StringBuilder();
for (int i = 0; i < hashBytes.Length; i++) { sb.Append(hashBytes[i].ToString("X2")); }

Na koniec możemy zamienić nasz hash na napis zawierający 32znaki (liczba w zapisie 16stkowym).

ADO.NET zapytania parametryzowane – C#

W poprzednim wpisie pokazałem, jak napisać prostą klasę w C#, dzięki której można pobierać wyniki prostych zapytań typu select. W tym wpisie pokażę, jak parametryzować zapytania i jak tego nie robić. Zakładam, że czytając ten post, masz drogi czytelniku jakąś klasę na wzór z poprzedniego posta.

W poprzednim poście aplikacja potrafiła wykonać zapytanie ‘select * from klienci’, a gdybyśmy chcieli pokazać konkretnego klienta? Normalnie w SSMS wystarczy napisać zapytanie

select * from klienci where klient_id = 1

Jeśli powyższe zapytanie jest niejasne, zapraszam do lektury o filtrowaniu danych (kurs sql). Oczywiście można w zapytaniu podstawić jakąkolwiek inną tabelę i kolumnę, przykłady opieram na poprzednich postach (zestawy szkoleniowe).

Powyższe zapytanie zwróci nam jeden wiersz.


Zanim przejdziemy do tak zwanego “kodzenia”, jedna uwaga, zmieniłem komputer, na którym prezentuję kod, więc nazwy aplikacji i niektórych kontrolek i obiektów mogły się zmienić.

Gotową klasę można pobrać tutaj
 

By wykonać przykład, który chcę zaprezentować, musimy mieć projekt, posiadający jedną formę, na której znajduje się guzik oraz datagridview.

Gdy nasze zapytanie wkleimy do kodu, gdzie wykonuje się kod po kliknięciu guzika
private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");
dataGridView1.DataSource = db.pobierz_dane("select * from klienci where klient_id=1");
}

Po skompilowaniu, uruchomieniu i kliknięciu guzika pojawi się jeden wiersz w DataGridView:

Oczywiście bez sensu jest zmieniać kod, by zobaczyć inny rekord. Dołóżmy na formę textboxa.

Teraz załóżmy, że użytkownik aplikacji zna numery identyfikacyjne klientów i chce oglądać pojedynczych klientów. Do tego celu będzie w textboxa (pole tekstowe) wpisywał numer klienta.

Najprostszą drogą do osiągnięcia tego celu jest konkatenacja w zapytaniu:
if (textBox1.Text.Length > 0)
{

dataGridView1.DataSource = db.pobierz_dane(“select * from klienci where klient_id=” + textBox1.Text);
}

Powyższy oczywiście zadziała, jeśli użytkownik wpisze liczbę do pola tekstowego. Więc, generalnie nie jest to najlepsze rozwiązanie. Dodatkowo, przy takim rozwiązaniu narażamy się na SQL Injection, użytkownik może dopisać część zapytania, która w skrajnych przypadkach może doprowadzić do katastrofalnych zmian w bazie danych.

Przykład na to, że działa:

Przykład na to, że można popsuć coś w ten sposób:

W między czasie, treść zapytania przeniosłem do zmiennej q, tak by móc zdebuggować program i zobaczyć, jak wygląda zapytanie dostarczane do funkcji. Od razu widać, że taki sposób parametryzacji jest bardzo niebezpieczny.

Aby zrobić to poprowanie, powinniśmy skorzystać z obiektu SqlParameter. Do tego celu najlepiej zmodyfikować naszą klasę do obsługi bazy danych.

Skopiujmy naszą funkcję służącą do pobierania danych i dodajmy argumenty wejściowe:

public DataTable pobierz_dane(string q,string nazwa_p,object wartosc_p)
{
..

}

Teraz musimy dodać Parametr do obiektu SqlCommand, tak aby nasza funkcja wyglądała, np. tak:
public DataTable pobierz_dane(string q,string nazwa_p,object wartosc_p)
{
DataTable dt = new DataTable(); // deklaracja i utworzenie instancji obiektu DataTable o nazwie dt
SqlDataReader dr; // deklaracja obiektu SqlDataReader o nazwie dr
SqlCommand sqlc; // Deklaracja obiektu SqlCOmmand

sqlc = new SqlCommand(q);
// utworzenie instancji SQLCommand ktora ma wykonac zapytanie podane jako parametr
// w zmiennej q

sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
SqlParameter par = new SqlParameter(nazwa_p, wartosc_p); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand

dr = sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

Dodaliśmy te dwie linie:
SqlParameter par = new SqlParameter(nazwa_p, wartosc_p); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand

Musimy je dodać w miejscu, gdzie już mamy utworzoną instancję obiektu SqlCommand. Kolejność dodawania nie ma znaczenia. Parametry należy dodać do obiektu przed wywołaniem zapytania (metoda ExecuteReader).

Gdy już mamy gotową metodę, możemy zmienić nieco kod w naszej metodzie wywoływanej po kliknięciu guzika:

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");

string q = “select * from klienci where klient_id=@id”;
dataGridView1.DataSource = db.pobierz_dane(q,”@id”,textBox1.Text);
}
}

Jak widać, w zapytaniu pojawił się @id. Jest to wskaźnik parametru i informacja o jego nazwie, w przypadku SQLServer parametry powinny się zaczynać się od @. Teraz pod @id, przy wykonywaniu zapytania zostanie podstawiona wartość z pola tekstowego, jednak środowisko .NET zadba za nas o niezbędne walidacje tak, by wszystko zadziałało jak należy:

Teraz próba sql injection skończy się wyjątkiem:

Nasz kod nie posiada bloków try i catch, więc aplikacja najzwyczajniej w świecie się wywali. Kwestii Obsługi błędów poświecę osobny wpis.

Oczywiście może zdarzyć się sytuacja, że będziemy potrzebowali większej ilości parametrów, do tego celu możemy skorzystać ze słowników zaimplementowanych w języku C#. Potrzebujemy znowu przeciążyć metodę od pobierania danych.

Słownik w C# jest listą par obiektów, Para jest kluczem i wartością (tak samo jak SQLParameter). Oczywiście możemy przekazać od razu listę SQLParameter do naszej funkcji lub gotowy obiekt SQLCommand. Ale pokazana ścieżka wymusza użycie obiektów Sql* w jednym miejscu. Tak więc nasza funkcja może wyglądać tak:

public DataTable pobierz_dane(string q, Dictionary < string,object > lista_parametrow )
{
DataTable dt = new DataTable(); // deklaracja i utworzenie instancji obiektu DataTable o nazwie dt
SqlDataReader dr; // deklaracja obiektu SqlDataReader o nazwie dr
SqlCommand sqlc; // Deklaracja obiektu SqlCOmmand

sqlc = new SqlCommand(q);
// utworzenie instancji SQLCommand ktora ma wykonac zapytanie podane jako parametr
// w zmiennej q

sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
SqlParameter par; // deklaracja zmiennej
foreach (KeyValuePair<string, object> parametr in lista_parametrow)
//iteracja po wszystkich elementach slownika
{
par = new SqlParameter(parametr.Key, parametr.Value); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand
}

dr = sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

Jak widać w pierwszej linii:
public DataTable pobierz_dane(string q, Dictionary< string,object > lista_parametrow )
mamy deklarację zmiennej typu słownikowego: Dictionary< string,object > lista_parametrow

Później dodaliśmy pętlę foreach (co oznacza dla każdego obiektu w liście/słowniku/tablicy etc. wykonaj…)

foreach (KeyValuePair< string, object > parametr in lista_parametrow)
//iteracja po wszystkich elementach slownika
{
par = new SqlParameter(parametr.Key, parametr.Value); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand
}

Dla każdej pary słownika tworzymy obiekt KeyValuePair, jest to obiekt, potrafiący przechowywać dwie wartości jednocześnie. Jest to po prostu struktura/obiekt, posiadający dwie właściwości (key, value). W pętli powstają nowe instancje obiektu i dodawane są do listy parametrów. Mimo, iż obiekt par za każdym razem – mogłoby się wydawać – będzie nadpisany, o tyle referencje do pamięci będą poprawne i lista parametrów w obiekcie SQLCommand tak naprawdę będzie zawierać pełną listę dostarczonych parametrów. Przeróbmy nasz przykład, by działał ze słownikiem (na razie dodamy tylko jeden parametr).

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");

Dictionary<string, object> lista_par = new Dictionary< string, object >();
lista_par.Add(“@id”, textBox1.Text);
string q = “select * from klienci where klient_id=@id”;
dataGridView1.DataSource = db.pobierz_dane(q,lista_par);
}

Tworzymy obiekt (instancję) słownika i dodajemy do niej jedną parę: klucz i wartość (key,value).
Value jest typem object, co oznacza, że możemy podstawiać tam dowolny typ obiektu, nieważne, czy to będzie data, napis, czy liczba, ten kod zadziała i dane zostaną odpowiednio zwalidowane.

Żeby sprawdzić, czy zadziała to dla większej ilości parametrów, dodajmy jeszcze jedno pole tekstowe. Wyobraźmy sobie, że będziemy porównywać dwóch klientów:

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb(“localhost\\sqlexpress”, “zajecia”);

Dictionary<string, object> lista_par = new Dictionary< string, object >();
lista_par.Add(“@id”, textBox1.Text);
lista_par.Add(“@id2”, textBox2.Text);
string q = “select * from klienci where klient_id=@id or klient_id=@id2”;
dataGridView1.DataSource = db.pobierz_dane(q,lista_par);
}
}

Dodajemy jeszcze jeden parametr do zapytania, oraz dodajemy go do słownika, efekt:

ADO.NET – podłączenie się do bazy SQL Server w C#

Tworzenie aplikacji w środowisku .NET, które wykorzystują bazy danych (a zwłaszcza SQL Server) jest stosunkowo proste. Na początek potrzebujemy Visual Studio (najlepiej darmowy express C#) oraz SQL Server (też najlepiej express) lub SQL Server Client (w przypadku gdy SQL Server nie jest zainstalowany na komputerze na którym prowadzimy prace developerskie).

Ten wpis zawiera dość długi wstęp dla osób o niskim obeznaniu ze środowiskiem VS, dlatego jeśli jesteś bardziej zaawansowanym użytkownikiem przewiń proszę tekst.

Gotową klasę można pobrać tutaj

Na początek musimy utworzyć projekt w VS, najlepiej wybrać Windows Form Application (jeśli korzystamy z VS innego niż Express proszę zaznaczyć język C#).

Poniższe Screenshoty pochodzą z wersji VS 2010 Professional.

Utworzenie nowego projektu:

Po utworzeniu projektu naszym oczom powinna ukazać się formatka (form1) nazywana skrótowo formą:

Z lewej strony znajduje się toolbox (tam znajdziemy różne kontrolki). Metodą przeciągnij upuść można na formę bezpośrednio z Toolboxa przeciągać wszystkie dostępne kontrolki. Proponuję zacząć od Button’a (guzika).

Button znajduje się nad zaznaczonym na screenshocie checkbox’em. Kontrolki są posortowane alfabetycznie.


Finalnie na formie powinien pojawić się button1 nasz guzik. Z prawej strony ekranu powinien być object(solution) explorer i properties.

Po jednokrotnym kliknięciu guzika na formie w properties zobaczymy jego właściwości. Wszystkie te właściwości są dostępne z poziomu kodu (do którego przejdziemy za chwilę). Proponuję zwrócić uwagę na właściwość (Name) jest to nazwa naszego obiektu. Możemy ją zmienić – co zalecam uczynić. W momencie gdy guzików pojawi się więcej będzie trudno nad tym zapanować. Ja mój guzik nazwę pobieracz. Jak widać po zmianie nazwy opis guzika w ogóle się nie zmienia, za napis na guziku odpowiada właściwość Text, można ją dowolnie zedytować. Zalecam także przejrzenie innych dostępnych właściwości.
Gdy już skonfigurujemy guzik tak by nam odpowiadał możemy w niego 2 razy kliknąć, otworzony nam się okno z kodem odpowiedzialnym za stworzenie formy, oraz pojawi się tam metoda obsługująca zdarzenie (event) kliknięcia myszą w guzik.
cały kod powinien wyglądać +/- tak:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void pobieracz_Click(object sender, EventArgs e)
{

}
}
}

Omawiając kod od samej góry:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

Najprościej rzecz ujmując jest poinformowanie jakich przestrzeni nazw chcemy używać, tworząc obiekt możemy napisać w kodzie System.Data.SqlClient.SqlCommand sqlc = new System.Data.SqlClient.SqlCommand(); albo możemy dodać przestrzeń nazw using System.Data.SqlClient i wtedy w kodzie napiszemy SqlCommand sqlc = new SqlCommand();. Tak więc using wykorzystujemy tylko dla skrócenia zapisów i sprawienia by były bardziej czytelne.
Kolejno mamy:

namespace WindowsFormsApplication2
{

jest to deklaracja naszej własnej przestrzeni nazw. Standardowo VS tworzy przestrzeń nazw tożsamą z nazwą aplikacji/projektu/solucji.

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

Teraz mamy deklarację publicznej klasy (public) o nazwie Form1 dziedziczącej (:) z ogólnej klasy Form. Pojawia się jeszcze słowo partial, które informuje kompilator że ta klasa może być deklarowana w wielu plikach. Ten zabieg pozwala zaprezentować nam kod który rzeczywiście jest dla nas istotny, podczas przeciągania kolejnych obiektów na formę VS tworzy wiele linii kodów które są przed nami ukrywane, dla zachowania czystości i pewnej czytelności.

public Form1() to konsturktor klasy Form1, konstruktor poznajemy zawsze po tym że nazywa się tak samo jak klasa oraz konstruktor nie ma definicji tego co zwraca (brak nazwy typu obiektu i słowa void przed nazwą metody). W konstruktorze mamy jedną funkcję która inicjuje nam kontrolki na formie.

Poniżej zostaje nam obsługa kliknięcia w guzik:

private void pobieracz_Click(object sender, EventArgs e)
{

}

moj guzik nazywa się pobierz więc VS automatycznie utworzyło funkcję według konwencji nazewnictwa ze środowiska .NET, pomiędzy klamrami nalezy dopisać kod który zostanie wykonany po kliknięciu guzika. Jako że poniższy post jest de facto o bazach danych a nie podstawach programowania w .NET przejdziemy do pisania kodu który umożliwi nam podłączenie się do bazy danych.

Dla zachowania czystości kodu proponuję utworzyć nową klasę, aby to zrobić należy z górnego menu wyrać project/add new item, albo add class.

VS utworzy nam klasę nazwaną tak samo jak plik w przestrzeni nazw naszej aplikacji:
namespace WindowsFormsApplication2
{
class jodb
{
}
}

Na samej górze w sekcji using warto (należy) dodać:

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

Teraz musimy utworzyć konstruktor, warto się zastanowić w tym miejscu w jaki sposób chcemy łączyć się z bazą danych. Mam na myśli tutaj autentykację windows(domenową) lub autentykację SQL Server. Standardowo SQL Server instalowany jest z włączoną autoryzacją windows. Oznacza to że otwierając połączenie z SQL Serverem zostanie przekazany nasz login do komputera i na tej podstawie zostaną nam przyznane uprawnienia. Autoryzacja SQL Server standardowo jest wyłączona, można ją włączyć w dość prosty sposób (opiszę to w osobnym poście).
Kod który stworzę pozwala na połączenie się jedną i drugą metodą.

W klasie jodb należy stworzyć konstruktory, które w ostatecznym wyglądzie powinny obsłużyć podłączenie się do bazy. Na początek proponuję by kod klasy wyglądał tak:

class jodb
{

public jodb(string user, string pass, string instance, string dbdir)
{

}

public jodb(string instance, string dbdir)
{

}
}

Mimo iż on nic nie robi sensownego, możemy powiedzieć że mamy 2 konstruktory i zależnie od wyboru możemy podłączyć się za pomocą jednej z dwóch metod autentykacji. Warto pamiętać także o komentarzach które pozwalają później w łatwy sposób operować na wytworzonym kodzie. Dodająć /// linię nad deklaracją metody VS wygeneruje nam komentarze które uzupełnione o nasze dopiski będą wyświetlały się w intelisense (podpowiedzi podczas pisania kodu).
Gdy nasz kod wygląda +\- tak możemy przejść do sedna sprawy, musimy utworzyć połączenie do SQL Server. W obu przypadkach posłużymy się obiektem SQLConnection.

Proponuję stworzyć właściwość połączenie, która będzie widoczna dla całej klasy odpowiedzialnej za łączenie się SQL Serverem.

class jodb
{
private SqlConnection polaczenie; //obiekt polaczenia widoczny w calej klasie

W tym celu po klamrze otwierającej definicję klasy dodajemy private (czyli obiekt/właściwość widoczna tylko wewnątrz klasy – SQLConnection -> typ opbiektu/właściwości klasy, oraz polaczenie czyli nazwa naszego obiektu.
W takim wypadku mamy tylko deklarację zmiennej/obiektu bez utworzenia instancji. Teraz w konstruktorach należy dodać utworzenie instancji obiektu SQLConnection oraz przypisać właściwość ConnectionString i otworzyć połączenie. Teraz nasz kod powinien zacząć wyglądać tak:

class jodb
{
private SqlConnection polaczenie; //obiekt polaczenia widoczny w calej klasie

///
/// Konstruktor do tworzenia polaczenia za pomoca autoryzacji SQL Server
///

///uzytkownik ///haslo ///nazwa instancji ///nazwa bazy danych public jodb(string user, string pass, string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = “”;
polaczenie.Open();
}
///
/// Konstruktor do tworzenia polaczenia za pomoca autoryzacji windows
///

///nazwa instancji ///nazwa bazy danych public jodb(string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = “”;
polaczenie.Open();
}
}

Do pełni szczęścia brakują nam jeszcze connectionStringi, czyli ciągi wskazujące na konkretną bazę danych, można je znaleść na connectionstrings.com – ja poniżej przedstawiam te z których korzystam (dla mnie są wystarczające, jednakże istnieje możliwość zdefiniowania większej ilości parametrów).

Po uzupełnieniu ConnectionString nasz kontruktor powinien wyglądać mniej więcej tak:

public jodb(string user, string pass, string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = "user id=" + user + ";" +
"password=" + pass + ";Data Source=" + instance + ";" +
"Trusted_Connection=no;" +
"database=" + dbdir + "; " +
"connection timeout=3";
polaczenie.Open();
}

łańcuch do łączenia z bazą wygląda tak:
“user id=” + user + ;password=” + pass + “;Data Source=” + instance+ “;Trusted_Connection=no;database=” + dbdir + “;connection timeout=3”;

znaki + są konkatenacją (łączeniem łańcuchów) i parametryzują nasz łańcuch tak by podstawić zmienne które podajemy jako argumenty. Powyższy ciąg służy do łączenia za pomocą autentykacji SQL Server.

W drugim kostruktorze stosujemy trochę krótszy ConnectionString:
polaczenie.ConnectionString = "Data Source=" + instance + ";" +
"Trusted_Connection=yes;" +
"database=" + dbdir + "; " +
"connection timeout=3";

Jak widać główna różnica polega na braku użytkownika i hasła oraz zmianie na trusted_connection=yes.
Powyższy kod warto uzupełnić o obsługę błędów Try i catch, ale zrobimy to później, na tym etapie nie jest nam to potrzebne. Na tym etapie warto skompilować projekt (wciskając F5) lub zieloną strzałkę. Jeśli wszystko dobrze pójdzie ujrzymy naszą formatkę jako osobną aplikację. W przeciwnym wypadku musimy usunąć wszystkie błędy. Proszę pamiętać że konstruktory muszą się nazywać tak samo jak klasa więc jeśli klasa nazywa się inaczej niż jodb to konstruktory także. Warto też przeczytać ewentualne komunikaty błędów i sprawdzić w google co one mogą znaczyć. Jeśli na tym etapie coś nie działa proszę o komentarz pod postem, postaram się pomóc.

Jeśli projekt się nam kompiluje trzeba przetestować połączenie.
Przejdźmy do kodu w pliku form1.cs

Do góry na belce mamy listę otwartych plików:

Możemy też w solution explorer kliknąc dwa razy na form1.cs i jak pokaże się forma kliknąc 2 razy guzik lub wcisnąć klawisz F7.

Do funkcji/zdarzenia/metody odpowiedzialnej za kliknięcie guzikiem dopiszmy kod który utworzy nam obiekt do obsługi bazy danych, dodatkowo także wraz z tym obiektem zostanie ustanowione połączenie z bazą.


Jak widać przy dobrym opisaniu własnego kodu, łatwe jest jego późniejsze wykorzystanie.
Teraz nasz kod form1.cs powinien wyglądać podobnie do:
namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void pobieracz_Click(object sender, EventArgs e)
{
jodb baza = new jodb(“test”, “test”, “localhost\\s2012n”, “zajecia”);
}
}
}

Kompilujemy projekt, jeśli wszystko poszło ok po kliknięciu guzika nic się nie stanie. Jeśli coś jest nie tak albo projekt się nie skompiluje albo po kliknięciu guzika pojawi się wyjątek (exception) który poinformuje nas o błędzie. Warto zwrócić uwagę na podwójny \\ w nazwie instancji, \ jest znakiem wyjścia i znakiem wyjścia dla niego jest on sam stąd podwojenie. Proszę także pamiętać że SQL Server ma standardowo wyłączoną obsługę TCIP co oznacza że nie podłączymy się do niego za pomocą adresu IP ( tylko po nazwie hosta Windows) ew localhost.

To jest moment w którym zaczynają się schody i większośc ludzi ma problem z podłączeniem do bazy, najczęstszymi powodami są literówki w ConnectionString dlatego proszę go dokładnie sprawdzić w razie problemów, lub napisać komentarz postaram się pomóc.

Aby cały projekt miał sens brakuje nam metody która pozwoli pobrać nam dane z bazy. W poprzednich moich postach stworzona została baza zajęcia do której były dołączone dwa zestawy szkoleniowe, w dalszym kodzie będziemy się opierać na tej bazie.

Wracamy do pliku klasy do łączenia się z bazą danych, klikamy jego nazwę do góry na belce lub też klikamy go podwójnie w solution explorer.

Dopisujemy kolejną metodę publiczną, ja ją nazwę pobierz_dane.

///
/// Metoda do pobierana danych z SQL Server
///

///zapytanie sqL /// zwraca dane w obiekcie DataTable
public DataTable pobierz_dane(string q)
{
DataTable dt= new DataTable();

return dt;

}


Jak widać powyższy kod opatrzyłem komentarzami. Po słowie public mamy nazwę obiektu (DataTable) który oznacza co metoda zwróci, przygotowałem też wnętrze funkcji (wydmuszka) by było można skompilować projekt. W tym momencie ta metoda nie robi niczego sensownego po prostu za każdym razem zwróci pusty obiekt DataTable.
Za chwilę dopiszemy kod który pozwoli nam pobierać dane.

W tym celupotrzebujemy obiekt SQLCommand, któremu musimy wskazać z jakiego połączenia ma korzystać oraz jakie zapytanie ma wykonać, potrzebujemy także obiekt SQLDataReader, który to odczyta dane z bazy i na koniec trzeba te dane umieścić w obiekcie DataTable.

public DataTable pobierz_dane(string q)
{
DataTable dt= new DataTable(); // deklaracja i utworzenie instancji obiektu DataTable o nazwie dt
SqlDataReader dr; // deklaracja obiektu SqlDataReader o nazwie dr
SqlCommand sqlc; // Deklaracja obiektu SqlCOmmand

sqlc = new SqlCommand(q);
// utworzenie instancji SQLCommand ktora ma wykonac zapytanie podane jako parametr
// w zmiennej q

sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
dr =sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

cała funkcja do pobierania danych powinna wyglądać jak powyżej.

Teraz należy utworzyć obiekt w którym możemy zaprezentować pobrane dane, do tego celu możemy skorzystać z obiektu DataGridView. Można go utworzyć z poziomu kodu, lub też przeciągnąć z toolbox’a. W tym celu klikamy w solution explorer form1.cs dwa razy i jak pojawi się forma klikamy toolbox i przeciągamy DataGridView.
Proszę zwrócić uwagę iż kontrolka DataGridView może nie znajdować się w zakładce common controls.

Gdy już mamy datagridview, (domyślnie VS utworzy nam obiekt o nazwie datagridview1), wchodzimy do kodu form1.cs (przycisk F7 lub podwójny klik na guzik). Przy tworzeniu datagridview VS chce wymusić na nas ustawienia DataSource, ale my z tego świadomie rezygnujemy(nie klikamy w nic na formie co się pojawia wokół Datagridview).

W kodzie form1.cs w funkcji która wykonuje się po kliknięciu guzikiem dopisujemy linijkę kodu:

dataGridView1.DataSource = baza_win.pobierz_dane("select * from klienci");

Z lewej strony mamy obiekt datagridview1 i jego właściwość Datasource, przypisujemy do niej wynik zwrócony przez metodę pobierz_dane obiektu baza_win (wcześniej był pokazywany obiekt baza, jednak dodałem sobie także obiekt baza_win który łączy się z SQLServer poprzez logowanie windows).
Metoda pobierz_dane przyjmuje jeden argument typu string i jest nim zapytanie SQL.
Cała metoda wygląda tak:

poniżej jeszcze kod:
private void pobieracz_Click(object sender, EventArgs e)
{
// jodb baza = new jodb("test", "test", "localhost\\s2012n", "zajecia");
jodb baza_win = new jodb("localhost\\s2012n", "zajecia");
dataGridView1.DataSource = baza_win.pobierz_dane("select * from klienci");
}

Teraz zostaje nam tylko skompilować i uruchomić projekt (F5).
Po uruchomieniu programu powinno się pokazać coś podobnego do:

Po kliknięciu guzika powinny w DatagridView pojawić się wyniki: