19 Vb.Net baza danych SQLite CRUD + 2 dodatki

Baza danych SQLite

Baza danych SQLite może pomóc nam w róznych sytuacjach: wyobraźmy sobie, że mamy napisać aplikację bazodanową, która będzie gromadziła dla nas jakieś dane. Żeby skorzystać z możliwości jakie daje nam SQLite trzeba określić na jakiej platformie będzie pracował nasz program. Po pierwsze jaka jest w nim wersja Net.Framework, a po drugie jaką mamy wersję Windows x64 bitowy czy x86 (32 bit).
Nasz projekt poglądowy wyposażymy w bibliotekę kompatybilną z wersją x86 bit dla platformy Net. Framework 4.0
Link do dokumentacji oraz do pobrania binariów dostępny jest pod poniższym adresem:
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Paczka zawiera szereg plików, ale dla nas najważniejszy będzie ten:
System.Data.SQLite.dll
i jego dołączymy do naszego projektu: Jak to zrobić ? to proste wystarczy wejść w zakładkę PROJECT, następnie wybrać z listy Add Reference..., następnie przejść do zakładki Browse i nacisnąć przycisk Browse aby przejść do okna eksploratora, w którym znajdujemy naszą ściągniętą paczkę i wybieramy System.Data.SQLite.dll po czym dołączamy go do projektu.
Można również zainstalować paczkę NuGet bezpośrednio z VisualStudio, która również oferuje potrzebne nam biblioteki. Żeby ją zainstalować należy po prawej stronie ekranu wejść w zakładkę Solution Eksplorer, wybrać nasz projekt i nacisnąć prawy klawisz myszy a następnie wybrać opcje "Manage NuGet Packages...", następnie przejść w zakładkę Online i wpisać w wyszukiwarce SQLite, następnie instalujemy pierwszą pozycję z listy.
Następnym krokiem jest zaimportowanie naszej nowej przestrzeni nazw, która korzystała będzie z dołączonej do projektu biblioteki

Imports System.Data.SQLite
Public Class Form1

End Class

Teraz kolejna sprawa, aby skorzystać z bazy danych trzeba ją najpierw utworzyć! do tego polecam mały, zgrabny i co najważniejsze darmowy program do obsługi naszych tabelek:
http://sqliteadmin.software.informer.com/0.8b/
Do utworzenia bazy danych i sprawdzania jej zawartości nadaje się doskonale a w dodatku obsługuje nasz ojczysty język. Można utworzyć bazę danych wraz z tabelami w kodzie programu, ale my nie będziemy sobie tym głowy zawracać.
Stwórzmy sobie zatem jakąś nową bazę danych, na której będziemy pracować: Ja nazwałem ją "bazadanych" - dla ułatwienia wy zróbcie tak samo, żeby się później nie myliło.
Mamy już naszą bazę danych utworzoną, teraz należało by utworzyć w niej jakąś tabelę żebyśmy mogli w niej pracować. W tym celu klikamy prawym klawiszem myszy na drzewie naszej bazy jak na obrazku poniżej.

Tabele nazwijmy "Pracownicy"

Musimy wprowadzić klucz podstawowy, którym będziemy posługiwać się później w naszym programie do identyfikacji w bazie danych naszego pracownika. Wciskamy dodaj pole i robimy to jak na obrazku poniżej w nazwie pola wpisując "Nr", ustawiając typ pola Integer i zaznaczając, że to pole będzie kluczem.

Następnie tworzymy jeszcze 3 inne pola typu TEXT:

• Imie
• Nazwisko
• Stanowisko

Zadbajmy o to, ażeby w nazwach pól nie było polskich znaków diakrytycznych (pozwoli nam to później uniknąć problemów). Następnie klikamy "Utwórz" i mamy gotową tabelę w naszej bazie danych SQLite. Jeśli wszystko zrobiliście jak należy przed waszymi oczami powinno pojawić się coś takiego:

Żeby było sprawniej, przejdźmy na chwilę do Visual Studio i zróbmy bulid naszego programu, utworzy nam się folder z naszym wyeksportowanym programem exe. Standardowo Visual Studio umieszcza nasze projekty w folderze Moje dokumenty\Visual Studio 2013\Projects\. Tam odnajdujemy naszą nazwę projektu i wchodzimy w nią, następnie wchodzimy dalej w tą samą nazwę i odnajdujemy folder Bin, wchodzimy w niego i Debug, w nim znajduje się nasza aplikacja exe. Na koniec kopiujemy tam naszą bazę danych. Zazwyczaj ścieżka dostępu do nazej aplikacji wygląda tak: C:\Users\użytkownik\Documents\Visual Studio 2013\Projects\NazwaProjektu\NazwaProjektu\bin\Debug

Po co to zrobiliśmy? żeby mozna było dodać do folderu z aplikacją naszą gotową bazę danych. Dzięki temu będziemy mogli łatwo się do niej odnieść w kodzie poleceniem Environment.CurrentDirectory, które wskazuje miejsce docelowe naszej bazy danych na folder, z którego otworzymy aplikację.

Żeby móc coś tworzyć w tej bazie danych musimy naszą formę wyposażyć w TextBox'y i Button'y.

Spróbujmy zrobić coś podobnego do tego co widać poniżej:

Świetnie teraz możemy przejść do obsługi naszej bazy. Piszemy kod dla przycisku "Dodaj":

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim CON As New SQLite.SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db") ' połączenie do bazy danych
        CON.Open() ' otwarcie połączenia
        Dim CMD As New SQLiteCommand("INSERT INTO Pracownicy (Imie, Nazwisko, Stanowisko) VALUES (@imie, @Nazwisko, @Stanowisko)", CON) ' zdefiniowanie pól i parametrów
        CMD.Parameters.Add("@Imie", Data.DbType.String).Value = TextBox1.Text ' przypisanie parametrom danych pobranych z textboxa
        CMD.Parameters.Add("@Nazwisko", Data.DbType.String).Value = TextBox2.Text
        CMD.Parameters.Add("@Stanowisko", Data.DbType.String).Value = TextBox3.Text
        CMD.ExecuteNonQuery() ' wykonanie poleceń na bazie
        CMD.Dispose() ' zwolnienie zasobów
        CON.Close() ' zamknięcie połączenia
        TextBox1.Clear() ' wykasowanie danych z textboxa
        TextBox2.Clear()
        TextBox3.Clear()
    End Sub

Dobrze mam nadzieję, że do tej pory wszystko jest w porządku i program działa poprawnie. W takim razie teraz zamiast oglądać nasze wpisane dane w programie SQLiteAdmin, obejrzymy je w naszym programie. Żeby to było możliwe musimy dodać do naszej formy kontrolkę DataGridView i kolejny Button, którym będziemy wyświetlać w niej nasze dane.

Żeby wyświetlić w kontrolce DataGridView nasze dane z tabeli trzeba do Buttona dodać poniższy kod:

        Dim Con As New SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db")
        Con.Open()
        Dim Cmd As New SQLiteCommand("SELECT Nr, Imie, Nazwisko, Stanowisko FROM Pracownicy", Con)
        Dim table As New DataTable()
        Dim dataAdapter As New SQLiteDataAdapter(Cmd)
        dataAdapter.Fill(table)
        DataGridView1.DataSource = table
        DataGridView1.Columns(0).Width = 30 ' Tym poleceniem ustawiamy szerokość kolumn
        DataGridView1.Columns(1).Width = 100
        DataGridView1.Columns(2).Width = 100
        DataGridView1.Columns(3).Width = 100
        Cmd.Dispose()
        Con.Close()

Mamy już prawie wszystko, teraz można sprawdzić działanie naszego przycisku do aktualizacji. Żeby zaktualizować dane w naszej tabeli trzeba do Button'a "Aktualizuj" dodać poniższy kod:

        Dim Con As New SQLite.SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db")
        Dim strSQL As String
        strSQL = "update Pracownicy set Imie = @Imie, Nazwisko = @Nazwisko, Stanowisko = @Stanowisko Where Nr = @Nr"
        Dim Cmd As New SQLiteCommand(strSQL, Con)
        Cmd.Parameters.AddWithValue("@Nr", TextBox4.Text)
        Cmd.Parameters.AddWithValue("@Imie", TextBox5.Text)
        Cmd.Parameters.AddWithValue("@Nazwisko", TextBox6.Text)
        Cmd.Parameters.AddWithValue("@Stanowisko", TextBox7.Text)
        Con.Open()
        Cmd.ExecuteNonQuery()
        Cmd.Dispose()
        Con.Close()

Aby zaktualizować konkretną osobę z listy wprowadzonych musimy podać jej klucz w naszej tabeli (czyli w naszym przypadku Nr porządkowy), klucz jest dla każdego unikalny. Jeśli skasujemy jakiś rekord z naszej bazy, nie będzie możliwości dodać go ponownie. Jak w takim razie skasować rekord? Dodajmy do formy jeszcze jeden Button i nazwijmy go "Skasuj" Postawmy go w pobliżu TextBox'a gdzie wprowadzamy numer np tak:

Do przycisku "Skasuj" dodajmy kod:

        Dim Con As New SQLite.SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db")
        Con.Open()
        Dim Cmd As New SQLiteCommand("DELETE FROM Pracownicy where Nr = @Nr", Con)
        Cmd.Parameters.Add("@Nr", Data.DbType.String).Value = TextBox4.Text
        Cmd.ExecuteNonQuery()
        Cmd.Dispose()
        Con.Close()

Poznaliśmy zatem wszystkie komendy CRUD (create, read, update i delete) czyli 4 podstawowe funkcje w aplikacjach korzystających z pamięci trwałej.


Dodatek nr 1
Mam dla was dodatek do naszego poradnika. Wyszukiwarkę ! Mając świadomość, że baza danych lubi przyjmować dużo danych, przyjdzie więc kiedyś czas, aby te dane jakoś z niej wyciągnąć. Do tego celu posłuży nam TextBox i kontrolką ListView. Dodajemy je więc do naszej formy. kontrolkę ListView trzeba jednak najpierw skonfigurować. Dodajmy do niej kolumny, które będą odpowiadały nazwom kolejnym kolumn, które mamy w naszej bazie. Ja zrobiłem to w ten sposób:

Wstawiłem też 2 TextBox'y, które będą wyszukiwały pracownika według podanych kryteriów czyli w moim przypadku wg nazwiska lub wg stanowiska. Poniżej podam wam kod, który będzie nam potrzebny do obsługi procedury TextChanged naszych Textbox'ów:

Obsługa wyszukiwania wg nazwiska:

    Private Sub TextBox8_TextChanged(sender As Object, e As EventArgs) Handles TextBox8.TextChanged
        ListView1.Items.Clear()
        Dim Con As New SQLite.SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db")
        Con.Open()
        Dim Cmd As New SQLiteCommand("SELECT * FROM Pracownicy WHERE Nazwisko LIKE @Nazwisko", Con)
        Cmd.Parameters.AddWithValue("@Nazwisko", TextBox8.Text & "%")
        Dim myReader As SQLite.SQLiteDataReader = Cmd.ExecuteReader()
        While myReader.Read
            Dim newListViewItem As New ListViewItem
            newListViewItem.Text = myReader.GetInt32(0)
            newListViewItem.SubItems.Add(myReader.GetString(1))
            newListViewItem.SubItems.Add(myReader.GetString(2))
            newListViewItem.SubItems.Add(myReader.GetString(3))
            ListView1.Items.Add(newListViewItem)
        End While
        Con.Close()
    End Sub

Obsługa wyszukiwania wg stanowiska:

    Private Sub TextBox9_TextChanged(sender As Object, e As EventArgs) Handles TextBox9.TextChanged
        ListView1.Items.Clear()
        Dim Con As New SQLite.SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db")
        Con.Open()
        Dim Cmd As New SQLiteCommand("SELECT * FROM Pracownicy WHERE Stanowisko LIKE @Stanowisko", Con)
        Cmd.Parameters.AddWithValue("@Stanowisko", TextBox9.Text & "%")
        Dim myReader As SQLite.SQLiteDataReader = Cmd.ExecuteReader()
        While myReader.Read
            Dim newListViewItem As New ListViewItem
            newListViewItem.Text = myReader.GetInt32(0)
            newListViewItem.SubItems.Add(myReader.GetString(1))
            newListViewItem.SubItems.Add(myReader.GetString(2))
            newListViewItem.SubItems.Add(myReader.GetString(3))
            ListView1.Items.Add(newListViewItem)
        End While
        Con.Close()
    End Sub

Na zakończenie podaje pełny kod dla naszej klasy:

Imports System.Data.SQLite
Public Class Form1
    'Procedura obsługi buttona "Dodaj"
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim Con As New SQLite.SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db") ' połączenie do bazy danych
        Con.Open() ' otwarcie połączenia
        Dim Cmd As New SQLiteCommand("INSERT INTO Pracownicy (Imie, Nazwisko, Stanowisko) VALUES (@imie, @Nazwisko, @Stanowisko)", Con) ' zdefiniowanie pól i parametrów
        Cmd.Parameters.Add("@Imie", Data.DbType.String).Value = TextBox1.Text ' przypisanie parametrom danych pobranych z textboxa
        Cmd.Parameters.Add("@Nazwisko", Data.DbType.String).Value = TextBox2.Text
        Cmd.Parameters.Add("@Stanowisko", Data.DbType.String).Value = TextBox3.Text
        Cmd.ExecuteNonQuery() ' wykonanie poleceń na bazie
        Cmd.Dispose() ' zwolnienie zasobów
        Con.Close() ' zamknięcie połączenia
        TextBox1.Clear() ' wykasowanie danych z textboxa
        TextBox2.Clear()
        TextBox3.Clear()
    End Sub

    'Procedura obsługi buttona "Aktualizuj"
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim Con As New SQLite.SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db")
        Dim strSQL As String
        strSQL = "update Pracownicy set Imie = @Imie, Nazwisko = @Nazwisko, Stanowisko = @Stanowisko Where Nr = @Nr"
        Dim Cmd As New SQLiteCommand(strSQL, Con)
        Cmd.Parameters.AddWithValue("@Nr", TextBox4.Text)
        Cmd.Parameters.AddWithValue("@Imie", TextBox5.Text)
        Cmd.Parameters.AddWithValue("@Nazwisko", TextBox6.Text)
        Cmd.Parameters.AddWithValue("@Stanowisko", TextBox7.Text)
        Con.Open()
        Cmd.ExecuteNonQuery()
        Cmd.Dispose()
        Con.Close()
    End Sub

    'Procedura obsługi buttona "Pokaż"
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim Con As New SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db")
        Con.Open()
        Dim Cmd As New SQLiteCommand("SELECT Nr, Imie, Nazwisko, Stanowisko FROM Pracownicy", Con)
        Dim table As New DataTable()
        Dim dataAdapter As New SQLiteDataAdapter(Cmd)
        dataAdapter.Fill(table)
        DataGridView1.DataSource = table
        DataGridView1.Columns(0).Width = 30 ' Tym poleceniem ustawiamy szerokość kolumn
        DataGridView1.Columns(1).Width = 100
        DataGridView1.Columns(2).Width = 100
        DataGridView1.Columns(3).Width = 100
        Cmd.Dispose()
        Con.Close()
    End Sub

    'Procedura obsługi buttona "Skasuj"
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim Con As New SQLite.SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db")
        Con.Open()
        Dim Cmd As New SQLiteCommand("DELETE FROM Pracownicy where Nr = @Nr", Con)
        Cmd.Parameters.Add("@Nr", Data.DbType.String).Value = TextBox4.Text
        Cmd.ExecuteNonQuery()
        Cmd.Dispose()
        Con.Close()
    End Sub

    'Procedura obsługi zdarzenia TextChanged dla textboxa wyszukującego pracownika po nazwisku
    Private Sub TextBox8_TextChanged(sender As Object, e As EventArgs) Handles TextBox8.TextChanged
        ListView1.Items.Clear()
        Dim Con As New SQLite.SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db")
        Con.Open()
        Dim Cmd As New SQLiteCommand("SELECT * FROM Pracownicy WHERE Nazwisko LIKE @Nazwisko", Con)
        Cmd.Parameters.AddWithValue("@Nazwisko", TextBox8.Text & "%")
        Dim myReader As SQLite.SQLiteDataReader = Cmd.ExecuteReader()
        While myReader.Read
            Dim newListViewItem As New ListViewItem
            newListViewItem.Text = myReader.GetInt32(0)
            newListViewItem.SubItems.Add(myReader.GetString(1))
            newListViewItem.SubItems.Add(myReader.GetString(2))
            newListViewItem.SubItems.Add(myReader.GetString(3))
            ListView1.Items.Add(newListViewItem)
        End While
        Con.Close()
    End Sub

    'Procedura obsługi zdarzenia TextChanged dla textboxa wyszukującego pracownika po stanowisku
    Private Sub TextBox9_TextChanged(sender As Object, e As EventArgs) Handles TextBox9.TextChanged
        ListView1.Items.Clear()
        Dim Con As New SQLite.SQLiteConnection("Data Source= " & Environment.CurrentDirectory & "\bazadanych.s3db")
        Con.Open()
        Dim Cmd As New SQLiteCommand("SELECT * FROM Pracownicy WHERE Stanowisko LIKE @Stanowisko", Con)
        Cmd.Parameters.AddWithValue("@Stanowisko", TextBox9.Text & "%")
        Dim myReader As SQLite.SQLiteDataReader = Cmd.ExecuteReader()
        While myReader.Read
            Dim newListViewItem As New ListViewItem
            newListViewItem.Text = myReader.GetInt32(0)
            newListViewItem.SubItems.Add(myReader.GetString(1))
            newListViewItem.SubItems.Add(myReader.GetString(2))
            newListViewItem.SubItems.Add(myReader.GetString(3))
            ListView1.Items.Add(newListViewItem)
        End While
        Con.Close()
    End Sub
End Class

Dodatek Nr 2
Do tej lekcji przygotowałem gotowy projekt z tegoporadnika, który można pobrać i uruchomić w swoim Visual Studio. Projekt można pobrać pod adresem:
https://drive.google.com/file/d/0B3v6ko5LNaO9R2pSUzlnSVFnV0E/view?usp=sharing

W pierwszym założeniu chciałem też zrobić poradnik jak wykorzystać bazę danych MS Access, ale zrezygnuje z tego ponieważ prawdopodobnie powieliłbym ten poradnik zmieniając tylko niektóre nazwy komend i rodzaj połączenia. Dlatego poradnik o bazach danych MsAccess nie powstanie. Jak ktoś będzie chciał poczytać coś więcej o tej bazie to w sieci jest mnóstwo przykładów.



Utworzono 24.09.215