[Visual Studio 2015] Obsługa lokalnej bazy danych przy użyciu SQLite – językiem początkującego

Witam.

Pewnie każdy początkujący programista zastanawiał się jak i gdzie zapisać dużo danych i w łatwy sposób mieć do nich dostęp. Możliwe jest wykorzystanie plików tekstowych, lecz dostęp do danych jest dość utrudniony. Można wykorzystać również pliki XML (z ang. Extensible Markup Language) lub bazy danych. W tym wpisie zajmiemy się obsługą lokalnych baz danych z wykorzystaniem biblioteki SQLite.

Dlaczego lokalna baza danych – jest prosty powód, otóż chyba każdy chce, aby jego program uruchamiał się na każdym komputerze bez instalowania dodatkowych programów czy bibliotek. Jedynie użytkownikowi wraz z programem będziemy dostarczać pliki (z reguły są to tylko pliki dll), które będę kopiowane wraz z programem wykonywalnym (*.exe). W niedalekiej przyszłości pokażę, jak dodać i obsłużyć te pliki biblioteczne tak, aby dostępny był tylko jeden plik exe – ale to jest temat przyszłościowy.

We wstępie zaznaczę jeszcze, że podczas omawiania zagadnień związanych z obsługą SQLite zakładam, że znasz podstawy podstaw obsługi Visual Studio 2015 i programowania w C#. Obsługę lokalnej bazy danych przedstawię na prostym przykładzie, natomiast na końcu zamieszczam również program „Książka adresowa„, która przedstawia w obszerniejszy sposób działanie bazy danych, oraz przedstawia inne ciekawe mechanizmy, które pewnie wykorzystasz. Programu tego jednak omawiać nie będę, ale w kodzie źródłowym znajduje się dość dużo komentarzy opisujących poszczególne czynności. Również zagadnień dotyczących samego SQL nie będę omawiał – jedynie podam link, gdzie można znaleźć przydatne materiały.

Tak więc zaczynamy zabawę z lokalną bazą danych!!

---

Przed przystąpieniem do pisania programu, należy najpierw zaimportować i dodać potrzebne biblioteki. Można to zrobić na wiele sposobów. Jednym z nich jest ściągnięcie odpowiednich plików ze strony źródłowej SQLite. Tylko, którą wersję wybrać i jakie pliki? Jest też duże prawdopodobieństwo, że braknie biblioteki SQLite.Interop.dll. Ja natomiast pokażę inny sposób, który w łatwy sposób doda wszystkie potrzebne pliki – otóż poprzez sam program Visual dzięki pakietom NuGet. Aby tego dokonać należy w eksploratorze projektu kliknąć prawym przyciskiem myszy na odwołania (references) i z menu wybrać „Zarządzaj pakietami NuGet…” tak jak to pokazano na poniższym rysunku:

Otworzy nam się nowe okienko w którym mamy możliwość wyboru odpowiedniego pakietu. W tym celu przechodzimy do zakładki Przeglądaj i w polu wyszukiwania wpisujemy SQLite. Po wyszukaniu i wyświetlenie wyników wybieramy pakiet System.Data.SQLite (tak jak to pokazano na wcześniejszym zdjęciu). Po prawej stronie w informacjach dotyczących pakietu naciskami przycisk Zainstaluj, oraz potwierdzamy OK w kolejnym okienku. Wszystkie wymagane składniki pakietu zostaną automatycznie zainstalowane. Dzięki temu pakietowi, jesteśmy pewni, że mamy aktualne biblioteki i wszystkie pliki, które są wymagane do prawidłowego działania naszej aplikacji.

Aby sprawdzić czy pakiet dodał odpowiednie pliki do projektu należy rozwinąć kategorie odwołania w naszym projekcie. Powinny znajdować się tam takie odwołania jak System.Data.SQLite, System.Data.SQLite.EF6, System.Data.SQLite.Linq. Należy jeszcze sprawdzić czy kopia lokalna jest tworzona. Po zaznaczeniu odpowiedniego odwołania (oczywiście dotyczącego tylko SQLite) w okienku właściwości (Properties) przy opcji kopia lokalna (Copy Local) powinno być ustawione True, jak to pokazano na poniższym zdjęciu:

Dzięki temu ustawieniu w folderze w którym znajduje się nasz plik wykonywalny exe naszego programu zostaną utworzone pliki które są konieczne do prawidłowego działania naszego programu. W jednym z następnych wpisów pokażę jak dodać pliki biblioteczne, aby były one dodane bezpośrednio do pliku wykonywalnego bez tworzenia dodatkowych plików.

Po tych kilku zabiegach mamy już dostępną bibliotekę potrzebną do obsługi lokalnej bazy danych. Teraz zajmiemy się tworzeniem programu.

Omówię jedynie fragmenty kodu, które są potrzebne do obsługi bazy danych. Reszta kodu jest opisana w projekcie, który jest dołączony na samym końcu tego wpisu.

---

Pod tym adresem można znaleźć przydatne informacje dot. budowania składni zapytań SQL.

---

Nasz program będzie umożliwiał utworzenie nowej bazy danych, otwarcie istniejącej, oraz dodanie i usunięcie danych, a także wypisanie kolejnych danych, które są zawarte w bazie danych. W projekcie zostaną użyte następujące kontrolki:

1. Button;
2. TextBox;
3. Label

Wyglądać to będzie następująco:

Obsługę lokalnej bazy danych można podzielić na kilka czynności:

1. Utworzenie nowego pliku
2. Otwarcie/Zamknięcie bazy danych
3. Wysłanie zapytania, które nie zwraca żadnych danych (np. utworzenie tabeli, dodanie nowych danych)
4. Wysłanie zapytanie, które zwraca jakiś wynik (np, wyszukanie danych)

W pierwszej kolejności zarejestrujemy przestrzeń nazw naszej biblioteki, aby łatwiej nam się programowało:

using System.Data.SQLite;

Aby utworzyć nowy plik naszej bazy należy wywołać funkcję:

//tworzenie nowego pliku
SQLiteConnection.CreateFile("c:\\nowe_dane.db");

W programie, który przedstawia sposób wykorzystania bazy danych do funkcji przekazywana jest zmienna path, która zawiera lokalizację pliku. Dwa ukośniki są stosowane z tego powodu, że jeden traktowany jest jako znak specjalny, na który reaguje kompilator. Po utworzeniu pliku konieczne jest utworzenie nowej tabeli w bazie danych, ale o tym za chwilę.

Otwarcie pliku wymaga utworzenia najpierw zmiennej, do której przypisany będzie nasz obiekt, czyli plik naszej bazy danych.

//zmienna przechowująca obiekt bazy danych
SQLiteConnection db_connect;         

//utworzenie obiektu potrzebnego do połączenia się z bazą, składnia wymagana przez bibliotekę.
db_connect = new SQLiteConnection("Data Source =" + path + ";Version=3;");

//połączenie się z bazą danych - funkcja tworząca nowy plik nie powoduje otwarcia go
db_connect.Open();

Następnie tworzymy obiekt, który przypisany jest do wcześniej utworzonej zmiennej. Jako parametr przekazywana jest składnia zawierająca adres naszego pliku, oraz wersję bazy danych. Możliwe jest również przesłanie innych informacji, takich jak hasło, ale na chwilę obecną nie zajmowałem się tym. Informacje na temat tej składni można znaleźć pod tym adresem – i nie tylko. W ostatniej linijce poprzez wywołanie funkcji/metody Open() otwierane jest połączenie z bazą danych. Zamknięcie bazy danych wykonuje się w bardzo prosty sposób, poprzez wywołanie metody/funkcji Close().

//zamknięcie połączenia z bazą danych
db_connect.Close();

Warto tutaj wspomnieć, że w przypadku, gdy plik pod wskazanym adresem nie istnieje, a zostanie wywołana funkcja/metoda Open(), plik ten zostanie utworzony. Jednak nie zalecane jest tworzenie w ten sposób nowych plików, z tego względu, że mogą one zawierać błędy.

Po utworzeniu i otwarciu pliku, należy w bazie danych utworzyć nową tabelę. Przedstawię najpierw, jak wygląda składnia wysłania zapytania bez zwrotu danych.

//zmienna przechowująca obiekt do wysyłania zapytań
SQLiteCommand db_command;            

//utworzenie obiektu który odpowiada za wysyłanie zapytania
db_command = new SQLiteCommand(db_querry, db_connect);

//wykonanie/wysłanie zapytania
db_command.ExecuteNonQuery();

W pierwszej kolejności tworzona jest zmienna przechowująca obiekt odpowiedzialny za wysyłanie zapytania, a w kolejnej tworzony jest ten obiekt. Przekazywane jest do niego zapytanie (db_querry jest zmienną typu string, która w moim przypadku zawiera składnię zapytania), oraz zmienna przechowująca obiekt podłączonej bazy danych, który został utworzony przed jego otwarcie. Po utworzeniu obiektu, wykonywane jest jego zapytanie poprzez wywołanie funkcji odpowiedzialnej za przesłanie zapytania które nie zwraca odpowiedzi.

Przykład tworzenia nowego pliku wraz z utworzeniem nowej tabli przedstawia się następująco:

public string path = null;                  //zmienna przechowująca lokalizację bazy danych
public SQLiteConnection db_connect;         //zmienna przechowująca obiekt bazy danych
public string db_querry = null;             //zmienna przechowująca zapytanie do bazy danych
public SQLiteCommand db_command;            //zmienna przechowująca obiekt do wysyłania zapytań

//tworzenie nowego pliku i tutaj po tym należy utworzyć tabelę w nowej bazie
SQLiteConnection.CreateFile(path);

//utworzenie obiektu potrzebnego do połączenia się z bazą, składnia wymagana przez bibliotekę.
db_connect = new SQLiteConnection("Data Source =" + path + ";Version=3;");

//połączenie się z bazą danych - funkcja tworząca nowy plik nie powoduje otwarcia go
db_connect.Open();

//utworzenie nowego zapytania dotyczącego utworzenia nowej tabeli w bazie danych
db_querry = "CREATE TABLE 'Osoby' ( 'Imie' TEXT, 'Nazwisko' TEXT)";

//utworzenie obiektu który odpowiada za wysyłanie zapytania
db_command = new SQLiteCommand(db_querry, db_connect);

//wykonanie/wysłanie zapytania
db_command.ExecuteNonQuery();

//zamknięcie połączenia z bazą danych
db_connect.Close();

W tym przypadku tworzona jest nowa tabela która nosi nazwę „Osoby„. Będzie ona zawierała kolumny „Imie” , oraz „Nazwisko” , których zawartość będzie typu TEXT.

Natomiast dodanie nowych danych do tabeli wygląda następująco:

//utworzenie nowego zapytania dodającego nowe dane do bazy
db_querry = "INSERT INTO 'Osoby' ('Imie', 'Nazwisko') VALUES ('"+ textBox2.Text +"','" + textBox3.Text + "');";

//utworzenie obiektu który odpowiada za wysyłanie zapytania
db_command = new SQLiteCommand(db_querry, db_connect);

//wykonanie/wysłanie zapytania
db_command.ExecuteNonQuery();

Do tabeli „Osoby” do kolumny „Imie” , oraz „Nazwisko” dodane są wartości wpisane do textbox2, oraz textbox3.

Kolejna czynność jaka jest wykonywana przy korzystaniu z bazy danych, to odczyt danych z tabeli bazy danych. Aby tego dokonać, należy najpierw utworzyć nową zmienną, z której będziemy odczytywać dane z kolejnych kolumn.

//zmienna przechowująca odebrane informacje zwrócone dla zapytania doczytująceg
SQLiteDataReader db_read;

Następnie do tej zmiennej przypisuje się rezultat jaki zwraca funkcja wysyłająca zapytanie związane z odczytem danych.

//wykonanie zapytania odczytującego dane z bazy danych
db_read = db_command.ExecuteReader();

Tak odebrane dane z lokalnej bazy danych, są teraz gotowe aby je odczytać. Dokonuje się tego poprzez wywoływanie funkcji Read() – przed pierwszym odczytaniem danych należy również ją wywołać. Odczyt danych wykonuje się wiersz po wierszu, tzn. wywołanie funkcji Read powoduje przejście do następnego wierszu. Odczytywanie danych dokonuje się poprzez odczyt każdej komórki z osobna. Dokonuje się tego po kolei dla każdego wiersza wskazując odpowiednią kolumną:

db_read.Read();                                 //przed pierwszym użyciem też należy użyć funkcji odczytującej dane z bazy
label2.Text = db_read["Imie"].ToString();       //odczytywanie dokonuje się poprzez wskazanie nazyw kolumny 
label3.Text = db_read[1].ToString();            //bądź poprzez podanie numeru kolumny - liczenie od 0

Odczyt danych z odpowiedniej kolumny dokonuje się jak odczyt z tabeli 1 wymiarowej. Można wykorzystać do tego indeks kolumny (liczy się od 0) bądź nazwę kolumny w bazie danych.

Poniżej zamieszczam cały przykład umożliwiający odczytanie danych z bazy danych.

//zmienna przechowująca odebrane informacje zwrócone dla zapytania doczytująceg
public SQLiteDataReader db_read;            

//utworzenie nowego zapytania powodującego odczytanie/wyszukanie wszystkich danych z bazy
db_querry = "SELECT * FROM 'Osoby'";

//utworzenie obiektu który odpowiada za wysyłanie zapytania
db_command = new SQLiteCommand(db_querry, db_connect);

//wykonanie zapytania odczytującego dane z bazy danych
db_read = db_command.ExecuteReader();

//odczytywanie danych
db_read.Read();                                 //przed pierwszym użyciem też należy użyć funkcji odczytującej dane z bazy
label2.Text = db_read["Imie"].ToString();       //odczytywanie dokonuje się poprzez wskazanie nazyw kolumny 
label3.Text = db_read[1].ToString();            //bądź poprzez podanie numeru kolumny - liczenie od 0

Należy wspomnieć również o tym, że w przypadku, gdy zostanie osiągnięty ostatni indeks, dane przestaną być odczytywane. Warto w tej procedurze (jak i w pozostałych procedurach) wykorzystać składnię do przechwytywanie błędu try – catch, aby program nie zakończył swojego żywot. Aby po osiągnięciu ostatniego indeksu móc od początku odczytywać dane, należy wykonać procedurę odczytu od początku, tzn. odłączyć się od bazy danych, połączyć się z nią na nowo i wykonać zapytanie.

Po poprawnym skompilowaniu programu w folderze DEBUG naszego projektu znajduje się plik wykonywalny naszego projektu wraz z pozostałymi plikami. Poniżej na zdjęciach przedstawiam, jakie pliki muszą znaleźć się na innym komputerze aby program działał prawidłowo (wersja minimalistyczna i maksymalistyczna 😉 ).

• 

• 

Mam nadzieję, że w prosty i w przyjazny sposób udało mi się przedstawić obsługę biblioteki SQLite. W przypadku jakichś pytań, proszę o feedback w postaci komentarza bądź wiadomości wysłanej z formularza kontaktu.

Jeżeli natomiast macie jakieś krytyczne opinie, bądź jeżeli coś jest nie tak to też pisać – będę wiedział co zmienić w tym i w przyszłych wpisach.

Poniżej udostępniam wam te projekty. Przed uruchomieniem projektu w programie Visual Studio 2015 należy najpierw skompilować projekt. Wymagany jest też dostęp do internetu, gdyż zostały przeze mnie usunięte niektóre pliki biblioteczne powodujące zwiększenie objętości całego projektu. W razie problemów, to pisać – prześlę bezpośrednio na e-mail cały kompletny projekt. W niedalekiej przyszłości postaram się umieści te projekty na dysku google i udostępnić tutaj.

Pliki do pobrania:

Pobierz “Cs_03_baza_danych_sqlite1.zip”

Cs_03_baza_danych_sqlite1.zip – Pobrano 571 razy – 3,94 MB

Pobierz “Cs_N04_Baza_danych_SQLite-Książka_adr1.zip”

Cs_N04_Baza_danych_SQLite-Książka_adr1.zip – Pobrano 614 razy – 3,96 MB

---

P.S.
Podobny wpis został również umieszczony przeze mnie na forum microgeek – zachęcam do rejestracji i częstych wizyt.