Úvodná stránka -> C# -> SQLite v C# (05.07.2020)

SQLite v C#

V dnešnom článku si povieme niečo o SQLite databáze a vytvoríme jednoduchú aplikáciu v ktorej ju použijeme.

Takmer každá aplikácia, ktorá pracuje s nejakými dátami, musí okrem iného riešiť aj spôsob, akým tieto dáta, alebo dáta z nich odvodené uložiť. Môže na to využívať textové súbory, špeciálne navrhnuté binárne súbory, databázový server, úložisko poskytované operačným systémom a mnoho iných metód.

Čím komplikovanejšia funkčnosť aplikácie s dátami, tým sofistikovanejšie nástroje na to potrebujeme. Ak potrebujeme uložiť pár riadkov textu alebo číselných údajov, vystačíme si s primitívnym dátovým úložiskom typu textový súbor. Avšak, ak nad týmito dátami potrebujeme vykonávať zložitejšie operácie (typu vyhľadávanie záznamov podľa dynamických podmienok, spájanie dát podľa nejakého kľúčovania a podobne) nám takéto úložisko bude spôsobovať viac problémov ako úžitku. V tomto prípade môžeme ako úložisko použiť nejaký SQL server, kde okrem samotného uskladnenia dát získame možnosť využívať silu jazyka SQL (resp. jeho "dialektu" podľa konkrétneho servra) na prácu s dátami.

No ani klasický SQL server nie je vždy riešením, keďže v mnohých prípadoch je to drahá záležitosť, drahá aj finančne (ak sa nebavíme napr. o MySQL) aj na výkon stroja. Naštastie tu máme aj kompromisné riešenie a to sú miniatúrne databázové riešenia ako MS SQL Compact Edition (známy aj ako MS SQL CE) alebo SQLite o ktorom bude dnešný článok. Prvý menovaný sa už aktívne nevyvíja a jeho podpora definitívne končí v roku 2021.

SQLite v skratke

SQLite je knižnica poskytujúca relačný databázový systém (RDBMS), ktorú je možné jednoducho implementovať do takmer ľubovolnej aplikácie. Jej autorom je Dwayne Richard Hipp, ktorý ju vytvoril počas práce pre United States Navy (námorníctvo USA).

Samotná knižnica je napísala v jazyku C, no je prístupná pre "všetky relevantné" programovacie jazky (C, C++, C#, Java, Delphi, PHP, Ruby, Go a mnoho iných) a platformy. Je natívne prístupná v OS Android, využívajú ju aj aplikácie na iPhone-och, BlackBerry aj Symbian-e. Využívajú ju aplikácie rôzneho druhu od webových prehliadačov(Firefox, Chrome, Safari) po multimediálne systémy v automobiloch.SQLite podporuje väčšinu SQL štandardu a je jej dialekt bol údaje inšpirovaný PostgreSQL.

Testovacia aplikácia

SQLite NuGet

Prejdime teraz k tej zaujímavejšej časti tohto článku. Je čas si ukázať, ako s SQLite databázou vieme pracovať. Ukážeme si, ako vykonávať základné operácie nad databázou ako sú SELECT, INSERT, UPDATE a DELETE.

V našom príklade budeme používať nasledujúce typy objektov z namespace System.Data.SQLite:
SQLiteConnection - sprostredkováva pripojenie na SQLite databázu. (V prípade, ak sa pokúsime pripojiť na neexistujúci súbor, bude súbor vytvorený ako prázdna databáza.)
SQLiteCommand - poslúži nám na zadefinovanie SQL príkazu pre databázu, vyplnenie parametrov príkazu a jeho vykonanie
SQLiteDataAdapter - použijeme ho na prenos výsledku SELECT-u do DataTable

Otvoríme si Visual Studio a vytvoríme nový projekt typu Windows Forms App, ktorý pomenujeme napríklad SQLite. Do projektu pridáme NuGet balíček System.Data.SQLite (alternatívne môžeme knižnicu System.Data.SQLite sťiahnuť manuálne zo stránky sqlite.org pre našu cieľovú platformu (> link <)). Po inštalácii balíčka by sa nám mali do projektu pridať nové referencie, s ktorých budeme používať už spomenutú System.Data.SQLite.

Vytvoríme si súbor DBAdapter.cs, v ktorom budeme postupne implementovať rovnomennú triedu, ktorá bude slúžiť na komunikáciu s databázou:

Zdrojový kód:

using System;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;
namespace SQLite
{
    class DBAdapter
    {
        private static SQLiteConnection _con_sqlite = null;
        private const string DBPassword = ""; //<-- Heslo k DB ak sa ma pouzit

        public static bool IsConnected
        {
            get
            {
                if (_con_sqlite == null)
                    return false;

                return (_con_sqlite.State == System.Data.ConnectionState.Open);
            }
        }

}
}

Trieda zatiaľ obsahuje len privátnu premennú _con_sqlite typu SQLiteConnection , konštantu DBPassword, kde môžeme zadefinovať heslo k databáze ak je potrebné a property IsConnected, ktorá hovorí o tom, či sme aktuálne pripojený.

Pokračujeme pridaním metód Connect, Disconnect a GetSqlCommand:

Zdrojový kód:

...
public static bool Connect()
{
    string ProgramFldr = System.IO.Path.GetDirectoryName(Application.ExecutablePath);
    if (!ProgramFldr.EndsWith("\\"))
        ProgramFldr += "\\";

string DatabaseFile = ProgramFldr + "stock.db";
bool NewDB = !System.IO.File.Exists(DatabaseFile);

    string ConString = String.Format("Data Source={0};Persist Security Info=False;", DatabaseFile);
    if (!String.IsNullOrEmpty(DBPassword))
    {
        ConString += "Password=" + DBPassword;
    }

    try
    {
        _con_sqlite = new SQLiteConnection(ConString);
        _con_sqlite.Open();
    }
    catch
    {
        return false;
    }

if (NewDB && !CreateStockTable())
return false;

return true;
}

public static void Disconnect()
{
    if (_con_sqlite != null)
    {
        _con_sqlite.Close();
        _con_sqlite.Dispose();
        _con_sqlite = null;
        GC.Collect();
    }
}

private static SQLiteCommand GetSqlCommand(string SQL)
{
return new SQLiteCommand(SQL, _con_sqlite);
}

Metóda Connect zistí priečinok, v ktorom je umiestnená naša aplikácia a pokúsi sa v ňom nájsť súbor stock.db, ktorý bude slúžiť ako naša databáza. Následne vyskladáme connection string, pomocou neho vytvoríme nové pripojenie v objekte _con_sqlite typuSQLiteConnection. V prípade ak súbor z databázou pred tým ešte neexistoval, zavoláme metódu CreateStockTable (vytvoríme si ju za malý moment), ktorá pre nás vytvorí požadovanú tabuľku.

Ďalej nasleduje metóda Disconnect, ktorá v prípade potreby ukončí pripojenie k databáze a "vyčistí" náš objekt _con_sqlite.

Poslednou metódou, ktorú sme pridali je GetSqlCommand, ktorá na prvý pohľad vyzerá zbytočné (obsahuje len jeden riadok kódu), no poskytuje nám jednotný bod, v ktorom sa budú vytvárať objekty typu SQLiteCommand, čo sa nám môže v budúcnosti hodiť, napríklad ak budeme používať globálnu transakciu.

Pokračujeme pridaním ďalších metód, ktoré nám poslúžia na základné operácie nad databázou

Zdrojový kód:

private static bool Insert(SQLiteCommand SQLCmd)
{
    try
    {
        return (SQLCmd.ExecuteNonQuery() > 0);
    }
    catch { return false; }
}

private static bool Update(SQLiteCommand SQLCmd)
{
    try
    {
        SQLCmd.ExecuteNonQuery();
        return true;
    }
    catch { return false; }
}

private static bool Delete(SQLiteCommand SQLCmd)
{
return Update(SQLCmd);
}

private static int Select_Int(SQLiteCommand SQLCmd)
{
if (!IsConnected)
return -1;

    object Ret = SQLCmd.ExecuteScalar();
    if (Ret == null)
        return -1;

int Val;

if (!Int32.TryParse(Convert.ToString(Ret), out Val))
return -1;

return Val;
}

private static DataTable Select_DataTable(SQLiteCommand SQLCmd)
{
if (!IsConnected)
return null;

SQLiteDataAdapter adapter = new SQLiteDataAdapter(SQLCmd);

    try
    {
        DataTable Ret = new DataTable();
        adapter.Fill(Ret);
        adapter.Dispose();
        return Ret;
    }
    catch
    {
        return null;
    }
}

private static int GetLastKey()
{
SQLiteCommand SQLCmd = GetSqlCommand("select last_insert_rowid()");
return Select_Int(SQLCmd);
}

Prvé tri metódy Insert, Update a Delete prakticky fungujú na rovnakom princípe a to je volanie metódy ExecuteNonQuery objektu SQLCmd. Podstatným rozdielom je to, ako sa stavajú k jej návratovej hodnote. Metóda vracia počet ovplyvnených riadkov v databáze. Keď nad databázou vykonám INSERT, očakávam, že návratová hodnota bude väčšia ako 0, keďže vkladám nové riadky. Pri operáciach DELETE a UPDATE nie je vždy zaručené, že ovplyvnia nejaké riadky. Ak chcem vymazať všetky záznamy staršie ako 5 dní, môže sa stať, že týchto záznamov bude aj 100 alebo nemusí byť ani jedna. Samozrejme je možné na tieto akcie využívať jednu spoločnú metódu, no už sa mi viac krát osvedčilo, že takto je to jednoduchšie rozšíriteľné (napríklad, ak budem potrebovať pri každom INSERT-e vrátiť namiesto typu bool kľúč nového vloženého objektu).

Ďalšie dve metódy (Select_Int a Select_DataTable) pre zmenu poslúžia na získavanie dát z databázy. Ako už názov napovedá, prvá nám vráti int a druhá System.Data.DataTable. Metóda Select_Int pomocou volania ExecuteScalar získa prvú vrátenú hodnotu (z prvého riadka a prvého stĺpca), ktorú sa následne snaží pretypovať na int. V prípade úspechu vráti jej hodnotu, inak -1. Metóda Select_DataTable pre svoju funkčnosť využíva objekt typu SQLiteDataAdapter, ktorý dokáže dáta vrátené z databázy naplniť do objektu typu System.Data.DataTable. V prípade neúspechu vráti null.

Posledná metóda je GetLastKey, ktorá pomocou SQLite funkcie last_insert_rowid() vráti kľúč posledného vloženého záznamu v tabuľke s autoincrement kľúčom.

Týmto sme si postavili nejaký základ pre prácu s SQLite databázou, na ktorom budeme ďalej stavať. Povedzme, že chceme v našej aplikácii spravovať zoznam položiek na sklade a ich aktuálne množstvo. Budeme teda potrebovať databázu s jednou tabuľkou, kde budú jednotlivé položky uložené. Na vytvorenie tejto tabuľky si vytvoríme (už vyššie spomenutú metódu) CreateStockTable.

Zdrojový kód:

private static bool CreateStockTable()
{
    string SQL = "CREATE TABLE IF NOT EXISTS [Stock] ("
    + "[ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
    + "[Name] text NOT NULL,"
    + "[Quantity] float NOT NULL)";

    try
    {
        SQLiteCommand SQLCmd = GetSqlCommand(SQL);
        SQLCmd.ExecuteNonQuery();
        return true;
    }
    catch
    {
        return false;
    }
}

Tabuľka sa bude volať Stock, bude obsahovať stĺpec ID, ktorý bude aicky generovaný, stĺpec Name ktorý bude obsahovať názov položky a stĺpec Quantity s aktuálnym množstvom.

Keď máme tabuľku vytvorenú, môžeme s ňou naplno pracovať

Zdrojový kód:

public static DataTable GetItems()
{
    string SQL = "SELECT [ID], [Name], [Quantity] FROM [Stock] ORDER BY [Name]";
    SQLiteCommand SQLCmd = GetSqlCommand(SQL);
    return Select_DataTable(SQLCmd);
}

public static int InsertItem(string Name, int Quantity)
{
    string SQL = "INSERT INTO [Stock] ([Name], [Quantity]) VALUES (@Name, @Quantity)";
    SQLiteCommand SQLCmd = GetSqlCommand(SQL);
    SQLCmd.Parameters.Add("@Name", DbType.String).Value = Name;
    SQLCmd.Parameters.Add("@Quantity", DbType.String).Value = Quantity;
    if (Insert(SQLCmd))
    {
        return GetLastKey();
    }
    else
    {
        return -1;
    }
}

public static bool UpdateItem(int ID, string Name, int Quantity)
{
    string SQL = "UPDATE [Stock] SET [Name] = @Name, [Quantity] = @Quantity WHERE [ID] = @ID";
    SQLiteCommand SQLCmd = GetSqlCommand(SQL);
    SQLCmd.Parameters.Add("@ID", DbType.Int32).Value = ID;
    SQLCmd.Parameters.Add("@Name", DbType.String).Value = Name;
    SQLCmd.Parameters.Add("@Quantity", DbType.String).Value = Quantity;
    return Update(SQLCmd);
}

public static bool DeleteItem(int ID)
{
    string SQL = "DELETE FROM [Stock] WHERE [ID] = @ID";
    SQLiteCommand SQLCmd = GetSqlCommand(SQL);
    SQLCmd.Parameters.Add("@ID", DbType.Int32).Value = ID;
    return Delete(SQLCmd);
}

V metóde GetItems pomocou Select_DataTable získame zoznam všetkých položiek na sklade. Metódy InsertItem, UpdateItem a DeleteItem nám ukazujú ako pracovať s GetSqlCommand a parametrami v SQL reťazcoch.

V aplikácii budeme chcieť aj informáciu o množstve položiek a ich celkovom množstve. Na tento účel vytvoríme metódy GetTotalItems a GetTotalQuantity

Zdrojový kód:

public static int GetTotalItems()
{
    string SQL = "SELECT COUNT(*) FROM [Stock]";
    SQLiteCommand SQLCmd = GetSqlCommand(SQL);
    return Select_Int(SQLCmd);
}

public static int GetTotalQuantity()
{
    string SQL = "SELECT SUM([Quantity]) FROM [Stock]";
    SQLiteCommand SQLCmd = GetSqlCommand(SQL);
    return Select_Int(SQLCmd);
}

Týmto sme skompletizovali našu komunikačnú triedu a môžeme ju použiť v našej aplikácii. Otvoríme si hlavný formulár našej aplikácie a pridáme naň DataGridView (s menom dgv_main), tri Buttony (btn_new, btn_update, btn_del) a päť TextBoxov (tb_id, tb_name, tb_qty, tb_totitems, tb_totqty), ku ktorým môžeme pridať aj popisné Labely.

Formulár našej aplikácie

Funkcia tlačidiel je jasná z ich názvov, prvé tri TextBoxy budú služiť na zadávanie / editovanie údajov konkrétneho záznamu v databáze a zvyšné TextBoxy budú služiť ako sumárne polia na zobrazenie celkového počtu položiek (tb_totitems) a ich celkového množstva (tb_totqty).

Do dgv_main pridáme tri stĺpce (ColID, ColName, ColQty), ktorým nastavíme vlastnosť DataPropertyName podľa stĺpcov v tabuľke Stock, čiže ID, Name, Quantity). Zabránime priamemu pridávaniu riadkov nastavením vlastnosti AllowUserToAddRows na False a nastavíme vlastnost SelectionMode na FullRowSelect, čim zabezpečíme, aby sa po kliku na bunku v gride označil celý riadok.

Ako prvú metódu na našom formulari si vytvoríme LoadList, ktorá načíta aktúalny stav skladu do dgv_main a budeme ju volať z udalosti Load nášho formulára

Zdrojový kód:

private void LoadList()
{
    DataTable Items = DBAdapter.GetItems();
    if (Items == null)
    {
        MessageBox.Show("Pri načítavaní zoznamu došlo k chybe.");
        return;
    }

dgv_main.DataSource = Items;
dgv_main.ClearSelection();

tb_totitems.Text = Convert.ToString(DBAdapter.GetTotalItems());
tb_totqty.Text = Convert.ToString(DBAdapter.GetTotalQuantity());
}

private void MainForm_Load(object sender, EventArgs e)
{
LoadList();
}

Metóda LoadList zavolá GetItems našej komunikačnej triedy a v prípade, ak sa dáta podarilo načítať, naplní nimi dgv_main. Pokračuje naplnenie sumárnych TextBoxov tb_totitems a tb_totqty pomocou informačných metód GetTotalItems a GetTotalQuantity.

Pokračujeme pridaním pomocných metód CheckForm a ClearForm, ktoré nám budú overovať správnosť údajov v poliach na editáciu a v prípade potreby ich aj vyčistia.

Zdrojový kód:

private bool CheckForm()
{
    if (String.IsNullOrEmpty(tb_name.Text) || String.IsNullOrEmpty(tb_qty.Text))
    {
        MessageBox.Show("Vyplňte všetky polia formulára");
        return false;
    }

    if (!Int32.TryParse(tb_qty.Text, out int Quantity))
    {
        MessageBox.Show("Údaj v poli Množstvo nie je číslo.");
        return false;
    }

return true;
}

private void ClearForm()
{
    tb_id.Text = String.Empty;
    tb_name.Text = String.Empty;
    tb_qty.Text = String.Empty;
}

Ako posledné implementujeme handleri na udalosť Click na našich Buttonoch a udalosť CellClick na dgv_main.

Zdrojový kód:

private void btn_new_Click(object sender, EventArgs e)
{
if (!CheckForm())
return;

string Name = tb_name.Text;
int Quantity = Convert.ToInt32(tb_qty.Text);

    int NewID = DBAdapter.InsertItem(Name, Quantity);
    if (NewID < 0)
    {
        MessageBox.Show("Zápis sa nepodaril.");
                return;
    }

ClearForm();
LoadList();
}

private void btn_update_Click(object sender, EventArgs e)
{
    if (String.IsNullOrEmpty(tb_id.Text))
    {
        MessageBox.Show("Zvoľte záznam na úpravu");
        return;
    }

if (!CheckForm())
return;

    int ID = Convert.ToInt32(tb_id.Text);
    string Name = tb_name.Text;
    int Quantity = Convert.ToInt32(tb_qty.Text);

    if (!DBAdapter.UpdateItem(ID, Name, Quantity))
    {
        MessageBox.Show("Úprava sa nepodarila.");
                return;
    }

LoadList();
}

private void btn_del_Click(object sender, EventArgs e)
{
    if (String.IsNullOrEmpty(tb_id.Text))
    {
        MessageBox.Show("Zvoľte záznam na výmaz");
        return;
    }

    int ID = Convert.ToInt32(tb_id.Text);
    if (!DBAdapter.DeleteItem(ID))
    {
        MessageBox.Show("Výmaz sa nepodaril.");
        return;
    }

ClearForm();
LoadList();
}

private void dgv_main_CellClick(object sender, DataGridViewCellEventArgs e)
{
int iRow = e.RowIndex;

    if (iRow >= 0)
    {
        DataGridViewRow Row = dgv_main.Rows[iRow];
        tb_id.Text = Convert.ToString(Row.Cells["ColID"].Value);
        tb_name.Text = Convert.ToString(Row.Cells["ColName"].Value);
        tb_qty.Text = Convert.ToString(Row.Cells["ColQty"].Value);
    }
}

Myslím, že význam kódu pre tlačidlá netreba popisovať, metóda dgv_main_CellClick sa postará o to, aby sa po kliku na bunku dgv_main načítal obsah zvoleného riadka do našich editačných polí.

Záverom

Týmto sme si našu aplikáciu skompletizovali a môžeme si vyskúšať. Po vyplnení editačných políčok a kliku na btn_new sa pridá nový záznam do našej databázy. Po výbere riadka v dgv_main je možné záznam editovať alebo vymazať pomocou tlačidiel btn_update a btn_del.

Rád uvítam aj spätnú väzbu z vašej strany, čiže ak ste našli chybu v článku / kóde, alebo ste narazili na nejakú nejasnosť, budem rád ak zanecháte komentár pod člankom (váš príspevok môže byť užitočný aj pre ostatných).

Kompletný zdrojový kód aplikácie je možné sťahovať z: > link <

Použté zdroje

SQLite - Wikipedia > link <
Most Widely Deployed and Used Database Engine - SQLite.org > link <

Codeblog

Diskusia

Žiadne príspevky v diskusii.

Nový príspevok

Na prispievanie do diskusie musíte byť prihlásený.

Knowledge base

Najnovšie články

Najčítanejšie članky

Vyhľadávanie

Najčítanejšie seriály

Všetky seriály

Najčítanejšie kategórie

Všetky kategórie

Ostatné