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.
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).
Step 3: Form
Drag and drop a button and provide the name as "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.
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.
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".
And at the search type "Sqlite" here you will see "Sqlite Manager 0.7.7 " then install this.
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.