Click here to Skip to main content
13,146,014 members (42,450 online)
Click here to Skip to main content
Add your own
alternative version

Stats

44.9K views
3.8K downloads
96 bookmarked
Posted 1 Sep 2017
MIT

WinForms | WPF: Using SQLite DataBase

, 1 Sep 2017
Rate this:
Please Sign up or sign in to vote.
C# . . . Create | Open DB . . . Get Tables Schema From DB . . . Create | Read | Modify Tables, Rows, Cells . . . Text | Int | Image . . . Programmatically Add | Edit | Delete Rows . . .

Contents

Getting Started With Winforms

Goal

Let's code a simple Winforms app, which can be used to:

  • Open a code-defined SQLite DataBase
  • Or create it if it does not exist
  • Create a simple test table in DataBase, if it does not exist
  • Show a list of tables in database, and allow us to select a table to display and edit
  • Display a selected table in table-like style (using DataGrid/DataGridView control):

  • Allow us to add, modify and delete table rows via DataGrid (it will be done without any SQL, just using SQLiteCommandBuilder)
  • Provide a fully "foolproof" (i.e., protection for any edits that can throw any error messages, which can worry us).

Necessary Libraries & Project Configuration

Download SQLite library here {Latest at Aug 2017} (or Managed-only and another alternative versions here).

Set project configuration to x86, from Any CPU.

Put System.Data.SQLite and SQLInterop libraries to /bin/x86/Debug/.

Add reference to System.Data.SQLite.

How To Create

0. Add neccessary import:

using System.Data.SQLite;

Also, declare some variables as fields of Form class. It need because we need access them from different methods (event handlers). 


I.e. we should do this:

public partial class Form1 : Form
{
    SQLiteConnection mConn;
    SQLiteDataAdapter mAdapter;
    DataTable mTable;

Now your project is prepared to use SQLite.

1. Start from Form_Load. At first, let's connect to DB;

string mDbPath = Application.StartupPath + "/getstarted.db";

// If DB Not Exists, it will be created.
mConn = new SQLiteConnection("Data Source=" + mDbPath);

2. Open the DB file:

mConn.Open();

3. Now, let's create a table (but do it only if not exists)

// id        - Unique Counter - Key Field (Required in any table)
// FirstName - Text
// Age       - Integer
using (SQLiteCommand mCmd = new SQLiteCommand("CREATE TABLE IF NOT EXISTS [Test Table] (id INTEGER PRIMARY KEY AUTOINCREMENT, 'FirstName' TEXT, 'Age' INTEGER);", mConn))
{
    mCmd.ExecuteNonQuery();
}

4. Next, let's get all tables from DB to combobox.

// There "Tables" is a system table which contains info
// about tables in DB.
// "TABLE_NAME" field in "Tables" contains table names.
using (DataTable mTables = mConn.GetSchema("Tables"))
{
    for (int i = 0; i < mTables.Rows.Count; i++)
    {
        CmbTables.Items.Add(mTables.Rows[i].ItemArray[mTables.Columns.IndexOf("TABLE_NAME")].ToString());
    }

    if (CmbTables.Items.Count > 0)
    {
        CmbTables.SelectedIndex = 0; // Default selected index.
    }
}

5. How to open specific table in DataGridView by select from ComboBox? Use it:

            // --- Putting All Data From Selected Table To DataTable ---
            // ---------------------------------------------------------
            // In simply put, DataTable is just matrix (2-dimensional array)
            // which stores data of the table.
            // ! THIS CODE IS REQUIRED.
            mAdapter = new SQLiteDataAdapter("SELECT * FROM [" + CmbTables.Text + "]", mConn);
            mTable = new DataTable(); // Don't forget initialize!
            mAdapter.Fill(mTable);

            // ---------- Disabling Counter Field For Edition ----------
            // ---------------------------------------------------------
            // ! THIS CODE IS OPTIONAL.
            // Because it can throw exception.
            if (mTable.Columns.Contains("id"))
            {
                mTable.Columns["id"].ReadOnly = true;
            }

            // ------------ Making DataBase Saving Changes -------------
            // ---------------------------------------------------------
            // SQLiteCommandBuilder authomatically generates
            // neccessary INSERT, UPDATE, DELETE SQL queries.
            // Next we just have to run the
            // mAdapter.Update(mTable);
            // and all changes in the table will be saved to DataBase.
            // ! THIS CODE IS OPTIONAL (only if we need saving changes)
            new SQLiteCommandBuilder(mAdapter);

            // ----------- Binding DataTable To DataGridView -----------
            // ---------------------------------------------------------
            // DataGridView visualizes DataTable's data in the window.
            // ! THIS CODE IS REQUIRED (if we want DataGridView)
            dataGridView1.DataSource = mTable;

6. How to implement saving changes (for example, in Form_Closed)? Use it:

if (mAdapter == null) // If No Table Was Opened.
    return;

mAdapter.Update(mTable);

7. And small UX improvement: create an DataError event handler for DataGridView and just keep it empty:

private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
    // - Preventing PEEESKY DataGridView Default Error Dialog --
    // ---- When we trying set a non-int value to int field ----
    // ---------------------------------------------------------
    // Just handle DataError event. It's enough.
    // But if you want, you can to show your own error message.
    // Not so peeeeesky, please!!! :)
}

Getting Started With WPF

Goal

Let's port our Winforms code to WPF, including WPF 3.5 (Visual Studio 2008).

Necessary Libraries & Project Configuration

Download SQLite library here {Latest at Aug 2017} (or alterntative versions i.e. Managed-only - here).

Set project configuration to x86, from any CPU.

Put System.Data.SQLite and SQLInterop libraries to /bin/x86/Debug/.

.NET 3.5: you should download and install WPF Toolkit, because WPF in .NET 3.5 didn't contains the DataGrid!

How To Create

Use WinForms tutorial above, but:

  • use Window_Loaded instead of Form_Load
  • use CmbTables.ItemsSource = dt.DefaultView; instead of manually adding items to ComboBox

  • use dataGrid1.ItemsSource = mTable.DefaultView; instead of dataGridView1.DataSource = mTable;

  • use Window_Closed instead of Form_Closed

Programmatically Adding Rows With Images In Winforms

Goal

Let's add an avatars support to our "Get Started" application. To do this, we should add new Image-typed column, named "Avatar". Image columns stores and displays images as well as string columns displays strings.

How To Create

  1. Update your CREATE TABLE query, add new 'Avatar' field of type BLOB. Before:
    CREATE TABLE IF NOT EXISTS [Test Table] (id INTEGER PRIMARY KEY AUTOINCREMENT, _
                'FirstName' TEXT, 'Age' INTEGER);
    After:
  2. CREATE TABLE IF NOT EXISTS [Test Table] (id INTEGER PRIMARY KEY AUTOINCREMENT, _
      'FirstName' TEXT, 'Age' INTEGER, 'Avatar' BLOB);
  3. Remove DB file if it exists, to recreate the table.
  4. Let's try to run it. We will view this:

    But how to set an image to cell? We can't do it via DataGrid.

    We should do it programmatically. We can programmatically add a row with an image.

  5. Let's add a new button named "Add...". You should name it "Add...", not "Add", because it will open an OpenFileDialog to select an image.

    Let's write a logic:

    if (mAdapter == null) // If No Table Selected.
        return;
    
    // -------------- Loading An Image From File ---------------
    // ------------------- As Bytes Array ----------------------
    
    var ofd = new OpenFileDialog();
    
    if (ofd.ShowDialog() != DialogResult.OK)
        return;
    
    Image img = Image.FromFile(ofd.FileName);
    
    // Converting An Image To Array Of Bytes
    ImageConverter converter = new ImageConverter();
    byte[] imgBytes = (byte[]) converter.ConvertTo(img, typeof(byte[]));
    
    // Removing Image From RAM
    // Also, you can use "uses" keyword for Auto Dispose
    img.Dispose();
    
    // -------------- Adding New Row With Image ----------------
    // ---------------------------------------------------------
    
    // Creating A Row (Without Adding To Table)
    var newRow = mTable.NewRow();
    
    // Putting Bytes Array To "Avatar" Cell
    newRow["Avatar"] = imgBytes;
    
    // Initializing Other Cells (Not Required)
    newRow["FirstName"] = "Alex";
    newRow["Age"] = 30;
    
    // Adding A Row To Table
    mTable.Rows.Add(newRow);
  6. Try run again:

Now all right.

Programmatically Editing & Deleting Rows In Winforms

How To Create

private void BtnEdit_Click(object sender, EventArgs e)
{
    // ----------------- Editing Current Row -------------------
    // ---------------------------------------------------------

    if (mAdapter == null) // If No Table Selected.
        return;

    // Obtaining Current Cursor Position (Selected Row) In Table.
    var curPos = dataGridView1.BindingContext[dataGridView1.DataSource].Position;

    // If Any Row Selected.
    if (curPos != -1)
    {
        mTable.Rows[curPos]["FirstName"] = "Paul";
    }
}

private void BtnDelete_Click(object sender, EventArgs e)
{
    // ---------------- Deleting Current Row -------------------
    // ---------------------------------------------------------

    if (mAdapter == null) // If No Table Selected.
        return;

    // Obtaining Current Cursor Position (Selected Row) In Table.
    var curPos = dataGridView1.BindingContext[dataGridView1.DataSource].Position;

    // If Any Row Selected.
    if (curPos != -1)
    {
        mTable.Rows.RemoveAt(curPos);
    }
}

How To Develop C# Applications With SQLite More Simpler & Faster. How To Support It Easier

Let's look back to our sample.

Firstly, in chapter 1, we wrote simplest SQLite application for WinForms and WPF. Next, we added new feature - support of image column with programmatically create rows, and, finally, another one feature - programmatically update and delete rows.

Was this work enough easy, and was resulting code enough clear?

No. Unfortunately, it was not enough easy. Adding an new feature in our case presents a problem by itself, which discomforts designing of application itself, designing of data flow in base.

For example, we want to select all records in our DB and next display in WinForms's listbox. In our current way, complete code looks as following:

using (var con = new SqliteConnection("Data Source=test.db"))
{
    con.Open();

    using (var cmd = new SqliteCommand("CREATE TABLE IF NOT EXISTS [Users] (id INTEGER PRIMARY KEY AUTOINCREMENT, 'FirstName' TEXT, 'Age' INTEGER, 'Avatar' BLOB);", con))
    {
        cmd.ExecuteNonQuery();
    }

    using (var cmd = new SqliteCommand("SELECT * FROM Users;", con))
    {
        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                listBox1.Items.Add(rdr.GetString("FirstName") + " [" + rdr.GetInt32("Age") + " years old]");
            }
        }
    }

    con.Close();
 }

Too heave and complicated, isn't it?

Look at another case (pseudo-code):

[__em__]using (var con = MySQLiteDB.Init())
{
   var books = con.GetBooks(); //possibly LINQ here
   foreach (var book in books)
   {
       listBox1.Items.Add(book);
   }
}

And that's all!...

How it works?

This is based on implementation of ORM and Active Record patterns.

In this case, every Book in Books table is represented by such class (pseudo-code):

[ThisORMsTableAttribute]
class Book
 : ThisORMsBaseActiveRecordClass
{
    public string _TableName = "Books"; //Const for ORM library

    // Fields

    [ThisORMsFieldAttribute]
    public string FirstName;

    [ThisORMsFieldAttribute]
    public int Age;

    // Methods

    public string ToString()
    {
        return FirstName + " [" + Age + "]";
    }
}

And ORM library, which used by we, automatically builds all SQL queries, and GetBooks() method returns objects of native C# "Book" class.

C# have "native" ORM library, i.e. Entity Framework. And also have two ORMs specify for SQLite. I will shortly explain them below.

SqliteORM Library

This is free & open-source ORM library with Ms-PL license. Repository for contributors available here.

Its last release DLLs are Oct 30, 2012, and can be downloaded here.

Sample code:

[Table]
public class SimpleTable 
{
    [PrimaryKey]    public int Id { get; set; }
    [Field]         public string Test { get; set; }
}
// SELECT

var results = adapter.Select().Where(tbl => tbl.Value > 100 && tbl.When <= DateTime.Now);

// UPDATE

var myClass = new MyClass() { Name = "Foo", Date = DateTime.Now };
myClass.Save();

Also, this ORM supports "anonymous" tables. It simplers our work even if we don't creating ActiveRecord/entity class definition:

using (AnonymousAdapter adapter = AnonymousAdapter.Open("SimpleTable")) {
   var row = adapater.Select().Where( Where.Equal( "Id", 10 ));
   Console.WriteLine("Id: {0} Test: {1}", row["Id"], row["Test"] );

TODO I'll write tutorial about this ORM.

SQLite-net Library

This is free & open-source ORM library, for .NET, Xamarin, UWP, Azure platforms, with MIT license. Repository for contributors available here.

Its last release DLLs are Jan 16, 2016, and can be downloaded here, or, alternatively, dev (un-stable) version can be installed with NuGet.

Sample code:

public class Stock
{
	[PrimaryKey, AutoIncrement]
	public int Id { get; set; }
	[MaxLength(8)]
	public string Symbol { get; set; }
}

public class Valuation
{
	[PrimaryKey, AutoIncrement]
	public int Id { get; set; }
	[Indexed]
	public int StockId { get; set; }
	public DateTime Time { get; set; }
	public decimal Price { get; set; }
}
var db = new SQLiteConnection("foofoo");
db.CreateTable<Stock>();
db.CreateTable<Valuation>();

Select:

var conn = new SQLiteConnection("foofoo");
var query = conn.Table<Stock>().Where(v => v.Symbol.StartsWith("A"));

foreach (var stock in query)
	Debug.WriteLine("Stock: " + stock.Symbol);

Insert:

public static void AddStock(SQLiteConnection db, string symbol) {
	var s = db.Insert(new Stock() {
		Symbol = symbol
	});
	Console.WriteLine("{0} == {1}", s.Symbol, s.Id);
}

Also, this ORM has asynchronous API.

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

Rou1997
Europe Europe
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
SuggestionORM Pin
Ilya Chudin15-Aug-17 21:48
memberIlya Chudin15-Aug-17 21:48 
GeneralRe: ORM Pin
Rou199716-Aug-17 3:40
memberRou199716-Aug-17 3:40 
SuggestionDecent article - demo & source wanted Pin
Member 1023631812-Jun-17 9:23
memberMember 1023631812-Jun-17 9:23 
QuestionDeleting rows Pin
Kevin Derrick Murphy25-Sep-16 0:34
memberKevin Derrick Murphy25-Sep-16 0:34 
AnswerRe: Deleting rows Pin
Rou199725-Sep-16 14:28
memberRou199725-Sep-16 14:28 
GeneralRe: Deleting rows Pin
Kevin Derrick Murphy25-Sep-16 22:20
memberKevin Derrick Murphy25-Sep-16 22:20 
Bugi don't download - url link addresss Pin
shint17-Jul-16 15:34
membershint17-Jul-16 15:34 
GeneralRe: i don't download - url link addresss Pin
Rou199718-Jul-16 0:06
memberRou199718-Jul-16 0:06 
GeneralMy vote of 4 Pin
Santhakumar Munuswamy @ Chennai9-May-15 9:31
memberSanthakumar Munuswamy @ Chennai9-May-15 9:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 1 Sep 2017
Article Copyright 2017 by Rou1997
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid