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

Leave a Reply

Your email address will not be published. Required fields are marked *

6 + twelve =