How to Use and Connect To Sqlite in a Windows Application

Here I will explain how to use and connect to Sqlite in a Windows application.

Why we use Sqlite in C#

We use Sqlite because all ther other databases that we use generally require a server and Sqlite is a database that we can embed within our system. So we don't require any server for a Sqlite database. Most of mobile and small databases don't require a server and if we want to make a standalone application for one computer that doesn't depend on another server then Sqlite is perfect for us.

For more information about Sqlite you can go to this link: Sqlite.

How to connect Sqlite with C#. Basically C# doesn't support Sqlite itself so we need a third-party DLL to connect to a Sqlite database.

There is a link,  from this link you can get a DLL file link and at this link you go to the download section and download the latest version. The download first link is a ZIP file so you extract this file to your application in the debug folder.

Step 1: Select a new project

Open your Visual Studio and select new project and in Visual C# select "Windows Forms Application" and provide the name as Sqlite and click on OK.

window form application

Step 2: Extract DLL file

Right-click on your application and select "Open folder in your window application" and then go to:

BIN -> Debug and extract your application here.

After this again right-click on your application and select "Add reference" and go to:

Browser -> BIN -> Debug and select DLL file (Sqlite Net.dll).

DLL file

Step 3: Form

Drag and drop a button and provide the name as "connect Sqlite".
 
connect Sqlite

Step 4: Code

Double-click on the button and write the following code.

Add a namespace

using Finisar.SQLite;

You use sample code from this link:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Text;

using System.IO;

 

namespace First_Csharp_app

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void Choose_Btn_click(object sender, EventArgs e)

        {

            // [snip] - As C# is purely object-oriented the following lines must be put into a class:

 

            // We use these three SQLite objects:

            SQLiteConnection sqlite_conn;

            SQLiteCommand sqlite_cmd;

            SQLiteDataReader sqlite_datareader;

 

            // create a new database connection:

            sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;");

 

            // open the connection:

            sqlite_conn.Open();

 

            // create a new SQL command:

            sqlite_cmd = sqlite_conn.CreateCommand();

 

            // Let the SQLiteCommand object know our SQL-Query:

            sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";

 

            // Now lets execute the SQL ;D

            sqlite_cmd.ExecuteNonQuery();

 

            // Lets insert something into our new table:

            sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (1, 'Test Text 1');";

 

            // And execute this again ;D

            sqlite_cmd.ExecuteNonQuery();

 

            // ...and inserting another line:

            sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (2, 'Test Text 2');";

 

            // And execute this again ;D

            sqlite_cmd.ExecuteNonQuery();

 

            // But how do we read something out of our table ?

            // First lets build a SQL-Query again:

            sqlite_cmd.CommandText = "SELECT * FROM test";

 

            // Now the SQLiteCommand object can give us a DataReader-Object:

            sqlite_datareader = sqlite_cmd.ExecuteReader();

 

            // The SQLiteDataReader allows us to run through the result lines:

            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read

            {

                // Print out the content of the text field:

                //System.Console.WriteLine( sqlite_datareader["text"] );

 

                string myreader = sqlite_datareader.GetString(0);

                messageBox.Show(myreader);

            }

            // We are ready, now lets cleanup and close our connection:

            sqlite_conn.Close();

        }

    }

 

Step 5: Output

Run your application and click on the button.

Output

Step 6: Database

Now if you again right-click on your application and select "Open folder in your window application" and then go to:

BIN -> Debug then here you will see the database so for Sqlite there is no need for a server.
 
Database

Step 7: Database Table

If you want to see this database then there is a simple way to see the table and everything in the database then go to Mozila and click at the above and go to "Add-ons".
 
Add-ons

And at the search type "Sqlite" here you will see "Sqlite Manager 0.7.7 " then install this.
 
Sqlite Manager

After installation go again to the top at Mozila and there click on "web developer -> Sqlite Manager".

Here you go to the button (open) and browse your database then you will see the table in your database.
 
table in your database

You can go to my blog_Munesh