Here in this article, I will show you how to connect a C# Application with SQLite database.
Step 1: Right Click on Project Name Solution Explorer. Enter Manage NuGet Packages… Then find the SQLite DLL in browse tab as in the second picture.
Step 2: Designing Form
Form Design:
Step 3: We create SQLite database on runtime. While the program start, form load method will install SQLite database as in this code. And GetList method will auto fill the datagridView.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
SQLiteConnection con; SQLiteDataAdapter da; SQLiteCommand cmd; DataSet ds; void GetList() { con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"); da = new SQLiteDataAdapter("Select *From Student", con); ds = new DataSet(); con.Open(); da.Fill(ds, "Student"); dataGridView1.DataSource = ds.Tables["Student"]; con.Close(); } private void Form1_Load(object sender, EventArgs e) { if (!File.Exists("MyDatabase.sqlite")) { SQLiteConnection.CreateFile("MyDatabase.sqlite"); string sql = @"CREATE TABLE Student( ID INTEGER PRIMARY KEY AUTOINCREMENT , FirstName TEXT NOT NULL, LastName TEXT NOT NULL );"; con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"); con.Open(); cmd = new SQLiteCommand(sql, con); cmd.ExecuteNonQuery(); con.Close(); } //DataGridView Fill Method GetList(); } |
Step 4: Select, Insert and Update methods…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
private void button2_Click(object sender, EventArgs e) //update { cmd = new SQLiteCommand(); con.Open(); cmd.Connection = con; cmd.CommandText = "update Student set FirstName='" + textBox2.Text + "',LastName='" + textBox3.Text + "' where ID=" + textBox1.Text + ""; cmd.ExecuteNonQuery(); con.Close(); GetList(); } private void button3_Click(object sender, EventArgs e) //delete { cmd = new SQLiteCommand(); con.Open(); cmd.Connection = con; cmd.CommandText = "delete from Student where ID=" + textBox1.Text + ""; cmd.ExecuteNonQuery(); con.Close(); GetList(); } |
And All C# Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SQLite; //SQL Connector using System.Drawing; using System.IO; using System.Linq; using System.Text; using System.Windows.Forms; namespace WindowsFormsApplication6 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } SQLiteConnection con; SQLiteDataAdapter da; SQLiteCommand cmd; DataSet ds; void GetList() { con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"); da = new SQLiteDataAdapter("Select *From Student", con); ds = new DataSet(); con.Open(); da.Fill(ds, "Student"); dataGridView1.DataSource = ds.Tables["Student"]; con.Close(); } private void Form1_Load(object sender, EventArgs e) { if (!File.Exists("MyDatabase.sqlite")) { SQLiteConnection.CreateFile("MyDatabase.sqlite"); string sql = @"CREATE TABLE Student( ID INTEGER PRIMARY KEY AUTOINCREMENT , FirstName TEXT NOT NULL, LastName TEXT NOT NULL );"; con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"); con.Open(); cmd = new SQLiteCommand(sql, con); cmd.ExecuteNonQuery(); con.Close(); } //DataGridView Fill Method GetList(); } private void button1_Click_1(object sender, EventArgs e) //Insert { cmd = new SQLiteCommand(); con.Open(); cmd.Connection = con; cmd.CommandText = "insert into Student(FirstName,LastName) values ('" + textBox2.Text + "','" + textBox3.Text + "')"; cmd.ExecuteNonQuery(); con.Close(); GetList(); } private void button2_Click(object sender, EventArgs e) //update { cmd = new SQLiteCommand(); con.Open(); cmd.Connection = con; cmd.CommandText = "update Student set FirstName='" + textBox2.Text + "',LastName='" + textBox3.Text + "' where ID=" + textBox1.Text + ""; cmd.ExecuteNonQuery(); con.Close(); GetList(); } private void button3_Click(object sender, EventArgs e) //delete { cmd = new SQLiteCommand(); con.Open(); cmd.Connection = con; cmd.CommandText = "delete from Student where ID=" + textBox1.Text + ""; cmd.ExecuteNonQuery(); con.Close(); GetList(); } } } |
Output:
