Sunday, 18 May 2014

How to use and connect Sqlite in a window application

Here i will explain How to use and connect Sqlite in a window application.

Why we use Sqlite in C# :-  We use Sqlite because all ther other DataBase which we use generally require server for that so Sqlite  is a database which we can embade with our system. So we don't require any server for Sqlite  database so mostof the mobile and small database where we don't require any server and want to make standalone application for one computer that doesn't depend on other server then Sqlite is a perfect for us.
For more information about Sqlite  you can go on this link.. Sqlite 
How to connect Sqlite  with C#. Basically C# doesn't support Sqlite  itself so we need some 3rd party DLL to connect Sqlite  database.
There is a link , from this link you can get some DLL file. link and at this link you go on download section and download latest version so download 1st link this is a ZIP file so you extract this file in your application in debug folder. 
Step(1) :select a new project
Open your visual stdio and select new project and in visual C# select " window form application" and give this 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 down a button and give it name as "connect Sqlite ".
Step(4) :Code
Double click on button and write this code.
add a namespace
using Finisar.SQLite;
you take 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 Finisar.SQLite;

using System.Text;
using System.IO;

namespace First_Csharp_app
    public partial class Form1 : Form
        public Form1()
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:

// 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

// 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

// ...and inserting another line:
sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (2, 'Test Text 2');";

// And execute this again ;D

// 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:

// 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:

Step(5) :Output
Run your application and click on button.
Step(4) :Database
Now if you again click on Right click on your application and select "Open folder in your window application" and then go to
BIN -> Debug - > here you will see database so for Sqlite there is no need of any server

Step(4) :DataBase Table
If you want to see this database there is a simple way to see table and everything in database then go on Mozila and click at above and go at " Add-ons".

 and at the search type " Sqlite " here you will see "Sqlite Manager 0.7.7 " and installed this.

After installed this again go at top at mozile and there click on "web developer -> Sqlite Manager"
Here you go at button ( open ) and browser your database then you will see table in your database.


No comments:

Post a Comment

C# program Selection Sorting

Selection sort is a straightforward sorting algorithm. This algorithm search for the smallest number in the elements array and then swap i...