C# - Simple CRUD Application With SQLite

Submitted by razormist on Thursday, April 19, 2018 - 21:19.
In this tutorial we will create a Simple CRUD Application With SQLite using C#. C# is a general-purpose, object-oriented programming language. C# is expected to make it faster and less expensive to develop new application. The potential of C# is great when it comes in developing desktop application. It has a friendly environment for all new developers. So let's do the coding...

Getting Started

First you will have to download & install the Visual Studio. Visual Studios is an open source development feel free to create any application that you want. Here's the link for the Visual Studio https://www.visualstudio.com/. Here's the link for the SQLite Browser http://sqlitebrowser.org/.

Setting up SQLite

SQLite is very carefully tested prior to every release and relevant to use in some way. SQLite is very usable in any environments especially in embedded devices. First all you need to do is to install the components of the SQLIte database, by right clicking in the Main project title in the solution explorer then selecting the Manage NuGet Packages. tut1 Then go to the browse and search sqlite, after that install it and wait until the process is completed. tut2 Next go to the Updates and update the needed framework to make sqlite work properly. tut3Note: Update only the framework if there is an available new update.

Application Design

We will now create the design for the application, first locate the designer file called form1.Designer.cs, this is the default name when you create a new windows form. Rename the form as Main.cs and then write these codes inside your designer file. 
  1. namespace Simple_CRUD
  2. {
  3.     partial class Main
  4.     {
  5.         /// <summary>
  6.         /// Required designer variable.
  7.         /// </summary>
  8.         private System.ComponentModel.IContainer components = null;
  9.  
  10.         /// <summary>
  11.         /// Clean up any resources being used.
  12.         /// </summary>
  13.         /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
  14.         protected override void Dispose(bool disposing)
  15.         {
  16.             if (disposing && (components != null))
  17.             {
  18.                 components.Dispose();
  19.             }
  20.             base.Dispose(disposing);
  21.         }
  22.  
  23.         #region Windows Form Designer generated code
  24.  
  25.         /// <summary>
  26.         /// Required method for Designer support - do not modify
  27.         /// the contents of this method with the code editor.
  28.         /// </summary>
  29.         private void InitializeComponent()
  30.         {
  31.             this.btn_add = new System.Windows.Forms.Button();
  32.             this.dataGridView1 = new System.Windows.Forms.DataGridView();
  33.             this.label1 = new System.Windows.Forms.Label();
  34.             this.txt_firstname = new System.Windows.Forms.TextBox();
  35.             this.txt_lastname = new System.Windows.Forms.TextBox();
  36.             this.label2 = new System.Windows.Forms.Label();
  37.             this.txt_address = new System.Windows.Forms.TextBox();
  38.             this.label3 = new System.Windows.Forms.Label();
  39.             this.btn_update = new System.Windows.Forms.Button();
  40.             this.btn_delete = new System.Windows.Forms.Button();
  41.             this.btn_clear = new System.Windows.Forms.Button();
  42.             ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
  43.             this.SuspendLayout();
  44.             // 
  45.             // btn_add
  46.             // 
  47.             this.btn_add.Location = new System.Drawing.Point(73, 577);
  48.             this.btn_add.Name = "btn_add";
  49.             this.btn_add.Size = new System.Drawing.Size(129, 55);
  50.             this.btn_add.TabIndex = 0;
  51.             this.btn_add.Text = "ADD";
  52.             this.btn_add.UseVisualStyleBackColor = true;
  53.             this.btn_add.Click += new System.EventHandler(this.Add);
  54.             // 
  55.             // dataGridView1
  56.             // 
  57.             this.dataGridView1.AllowUserToAddRows = false;
  58.             this.dataGridView1.AllowUserToDeleteRows = false;
  59.             this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
  60.             this.dataGridView1.Location = new System.Drawing.Point(38, 181);
  61.             this.dataGridView1.Name = "dataGridView1";
  62.             this.dataGridView1.ReadOnly = true;
  63.             this.dataGridView1.RowTemplate.Height = 28;
  64.             this.dataGridView1.Size = new System.Drawing.Size(699, 369);
  65.             this.dataGridView1.TabIndex = 7;
  66.             this.dataGridView1.CellClick += new System.Windows.Forms.DataGridViewCellEventHandler(this.GetIdToDelete);
  67.             this.dataGridView1.CellDoubleClick += new System.Windows.Forms.DataGridViewCellEventHandler(this.Edit);
  68.             // 
  69.             // label1
  70.             // 
  71.             this.label1.AutoSize = true;
  72.             this.label1.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
  73.             this.label1.Location = new System.Drawing.Point(34, 66);
  74.             this.label1.Name = "label1";
  75.             this.label1.Size = new System.Drawing.Size(119, 27);
  76.             this.label1.TabIndex = 8;
  77.             this.label1.Text = "Firstname";
  78.             // 
  79.             // txt_firstname
  80.             // 
  81.             this.txt_firstname.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
  82.             this.txt_firstname.Location = new System.Drawing.Point(159, 63);
  83.             this.txt_firstname.Name = "txt_firstname";
  84.             this.txt_firstname.Size = new System.Drawing.Size(224, 35);
  85.             this.txt_firstname.TabIndex = 9;
  86.             // 
  87.             // txt_lastname
  88.             // 
  89.             this.txt_lastname.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
  90.             this.txt_lastname.Location = new System.Drawing.Point(530, 66);
  91.             this.txt_lastname.Name = "txt_lastname";
  92.             this.txt_lastname.Size = new System.Drawing.Size(224, 35);
  93.             this.txt_lastname.TabIndex = 11;
  94.             // 
  95.             // label2
  96.             // 
  97.             this.label2.AutoSize = true;
  98.             this.label2.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
  99.             this.label2.Location = new System.Drawing.Point(405, 69);
  100.             this.label2.Name = "label2";
  101.             this.label2.Size = new System.Drawing.Size(117, 27);
  102.             this.label2.TabIndex = 10;
  103.             this.label2.Text = "Lastname";
  104.             // 
  105.             // txt_address
  106.             // 
  107.             this.txt_address.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
  108.             this.txt_address.Location = new System.Drawing.Point(372, 117);
  109.             this.txt_address.Name = "txt_address";
  110.             this.txt_address.Size = new System.Drawing.Size(224, 35);
  111.             this.txt_address.TabIndex = 13;
  112.             // 
  113.             // label3
  114.             // 
  115.             this.label3.AutoSize = true;
  116.             this.label3.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
  117.             this.label3.Location = new System.Drawing.Point(247, 120);
  118.             this.label3.Name = "label3";
  119.             this.label3.Size = new System.Drawing.Size(100, 27);
  120.             this.label3.TabIndex = 12;
  121.             this.label3.Text = "Address";
  122.             // 
  123.             // btn_update
  124.             // 
  125.             this.btn_update.Location = new System.Drawing.Point(252, 577);
  126.             this.btn_update.Name = "btn_update";
  127.             this.btn_update.Size = new System.Drawing.Size(129, 55);
  128.             this.btn_update.TabIndex = 14;
  129.             this.btn_update.Text = "UPDATE";
  130.             this.btn_update.UseVisualStyleBackColor = true;
  131.             this.btn_update.Click += new System.EventHandler(this.Update);
  132.             // 
  133.             // btn_delete
  134.             // 
  135.             this.btn_delete.Location = new System.Drawing.Point(410, 577);
  136.             this.btn_delete.Name = "btn_delete";
  137.             this.btn_delete.Size = new System.Drawing.Size(129, 55);
  138.             this.btn_delete.TabIndex = 15;
  139.             this.btn_delete.Text = "DELETE";
  140.             this.btn_delete.UseVisualStyleBackColor = true;
  141.             this.btn_delete.Click += new System.EventHandler(this.Delete);
  142.             // 
  143.             // btn_clear
  144.             // 
  145.             this.btn_clear.Location = new System.Drawing.Point(590, 577);
  146.             this.btn_clear.Name = "btn_clear";
  147.             this.btn_clear.Size = new System.Drawing.Size(129, 55);
  148.             this.btn_clear.TabIndex = 16;
  149.             this.btn_clear.Text = "CLEAR";
  150.             this.btn_clear.UseVisualStyleBackColor = true;
  151.             this.btn_clear.Click += new System.EventHandler(this.Clear);
  152.             // 
  153.             // Main
  154.             // 
  155.             this.AutoScaleDimensions = new System.Drawing.SizeF(9F, 20F);
  156.             this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
  157.             this.ClientSize = new System.Drawing.Size(819, 667);
  158.             this.Controls.Add(this.btn_clear);
  159.             this.Controls.Add(this.btn_delete);
  160.             this.Controls.Add(this.btn_update);
  161.             this.Controls.Add(this.txt_address);
  162.             this.Controls.Add(this.label3);
  163.             this.Controls.Add(this.txt_lastname);
  164.             this.Controls.Add(this.label2);
  165.             this.Controls.Add(this.txt_firstname);
  166.             this.Controls.Add(this.label1);
  167.             this.Controls.Add(this.dataGridView1);
  168.             this.Controls.Add(this.btn_add);
  169.             this.Name = "Main";
  170.             this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
  171.             this.Text = "Main";
  172.             this.Load += new System.EventHandler(this.Main_Load);
  173.             ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
  174.             this.ResumeLayout(false);
  175.             this.PerformLayout();
  176.  
  177.         }
  178.  
  179.         #endregion
  180.  
  181.         private System.Windows.Forms.Button btn_add;
  182.         private System.Windows.Forms.DataGridView dataGridView1;
  183.         private System.Windows.Forms.Label label1;
  184.         private System.Windows.Forms.TextBox txt_firstname;
  185.         private System.Windows.Forms.TextBox txt_lastname;
  186.         private System.Windows.Forms.Label label2;
  187.         private System.Windows.Forms.TextBox txt_address;
  188.         private System.Windows.Forms.Label label3;
  189.         private System.Windows.Forms.Button btn_update;
  190.         private System.Windows.Forms.Button btn_delete;
  191.         private System.Windows.Forms.Button btn_clear;
  192.     }
  193. }
or also you create the layout by dragging the proper tools to the forms.

Creating the Script

We will now create the script to make things work. To do that go to the csharp script called Main.cs then right click and select view code, this will force you to go to the text editor. Then write these block of codes inside the Class of the form. 
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data;
  5. using System.IO;
  6. using System.Drawing;
  7. using System.Linq;
  8. using System.Text;
  9. using System.Threading.Tasks;
  10. using System.Windows.Forms;
  11. using System.Data.SQLite;
  12.  
  13. namespace Simple_CRUD
  14. {
  15.     public partial class Main : Form
  16.     {
  17.         SQLiteConnection conn;
  18.         SQLiteCommand cmd;
  19.         SQLiteDataAdapter adapter;
  20.         DataSet ds = new DataSet();
  21.         DataTable dt = new DataTable();
  22.         int id;
  23.         bool isDoubleClick = false;
  24.         String connectString;
  25.  
  26.         public Main()
  27.         {
  28.             InitializeComponent();
  29.             connectString = @"Data Source=" + Application.StartupPath + @"\Database\crud.db;version=3";
  30.             GenerateDatabase();
  31.         }
  32.  
  33.         private void Add(object sender, EventArgs e) {
  34.  
  35.             if (txt_firstname.Text != "" || txt_lastname.Text != "" || txt_address.Text != "")
  36.             {
  37.                 try
  38.                 {
  39.                     conn = new SQLiteConnection(connectString);
  40.                     cmd = new SQLiteCommand();
  41.                     cmd.CommandText = @"INSERT INTO member (firstname, lastname, address) VALUES(@firstname, @lastname, @address)";
  42.                     cmd.Connection = conn;
  43.                     cmd.Parameters.Add(new SQLiteParameter("@firstname", txt_firstname.Text));
  44.                     cmd.Parameters.Add(new SQLiteParameter("@lastname", txt_lastname.Text));
  45.                     cmd.Parameters.Add(new SQLiteParameter("@address", txt_address.Text));
  46.                     conn.Open();
  47.  
  48.                     int i = cmd.ExecuteNonQuery();
  49.  
  50.                     if (i == 1)
  51.                     {
  52.                         MessageBox.Show("Successfully Created!");
  53.                         txt_firstname.Text = "";
  54.                         txt_lastname.Text = "";
  55.                         txt_address.Text = "";
  56.                         ReadData();
  57.                     }
  58.                 }
  59.                 catch (Exception ex)
  60.                 {
  61.                     MessageBox.Show(ex.Message);
  62.                 }
  63.             }
  64.             else {
  65.                 MessageBox.Show("Required Field!");
  66.             }
  67.  
  68.         }
  69.  
  70.         private void GenerateDatabase() {
  71.             String path = Application.StartupPath + @"\Database\crud.db";
  72.             if (!File.Exists(path))
  73.             {
  74.                 conn = new SQLiteConnection(connectString);
  75.                 conn.Open();
  76.                 string sql = "CREATE TABLE member (ID INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT, address TEXT)";
  77.                 cmd = new SQLiteCommand(sql, conn);
  78.                 cmd.ExecuteNonQuery();
  79.                 conn.Close();
  80.             }
  81.         }
  82.  
  83.         private void ReadData() {
  84.             try
  85.             {
  86.                 conn = new SQLiteConnection(connectString);
  87.                 conn.Open();
  88.                 cmd = new SQLiteCommand();
  89.                 String sql = "SELECT * FROM member";
  90.                 adapter = new SQLiteDataAdapter(sql, conn);
  91.                 ds.Reset();
  92.                 adapter.Fill(ds);
  93.                 dt = ds.Tables[0];
  94.                 dataGridView1.DataSource = dt;
  95.                 conn.Close();
  96.                 dataGridView1.Columns[1].HeaderText = "Firstname";
  97.                 dataGridView1.Columns[2].HeaderText = "Lastname";
  98.                 dataGridView1.Columns[3].HeaderText = "Address";
  99.                 dataGridView1.Columns[0].Visible = false;
  100.                 dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
  101.                 dataGridView1.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
  102.                 dataGridView1.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
  103.                 dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
  104.             }
  105.             catch (Exception ex) {
  106.                 MessageBox.Show(ex.Message);
  107.             }
  108.         }
  109.  
  110.         private void Main_Load(object sender, EventArgs e)
  111.         {
  112.             ReadData();
  113.         }
  114.  
  115.         private void Edit(object sender, DataGridViewCellEventArgs e) {
  116.             id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[0].Value);
  117.             txt_firstname.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
  118.             txt_lastname.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
  119.             txt_address.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
  120.             isDoubleClick = true;
  121.         }
  122.  
  123.         private void GetIdToDelete(object sender, DataGridViewCellEventArgs e) {
  124.             id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[0].Value);
  125.             isDoubleClick = false;
  126.             txt_firstname.Text = "";
  127.             txt_lastname.Text = "";
  128.             txt_address.Text = "";
  129.         }
  130.  
  131.  
  132.         private void Update(object sender, EventArgs e) {
  133.             if(isDoubleClick) { 
  134.                 try {
  135.                     conn.Open();
  136.                     cmd = new SQLiteCommand();
  137.                     cmd.CommandText = @"UPDATE member set firstname=@firstname, lastname=@lastname, address=@address WHERE ID='"+ id +"'";
  138.                     cmd.Connection = conn;
  139.                     cmd.Parameters.AddWithValue("@firstname", txt_firstname.Text);
  140.                     cmd.Parameters.AddWithValue("@lastname", txt_lastname.Text);
  141.                     cmd.Parameters.AddWithValue("@address", txt_address.Text);
  142.                     int i = cmd.ExecuteNonQuery();
  143.  
  144.                     if (i == 1)
  145.                     {
  146.                         MessageBox.Show("Successfully Updated!");
  147.                         txt_firstname.Text = "";
  148.                         txt_lastname.Text = "";
  149.                         txt_address.Text = "";
  150.                         ReadData();
  151.                         id = 0;
  152.                         dataGridView1.ClearSelection();
  153.                         dataGridView1.CurrentCell = null;
  154.                         isDoubleClick = false;
  155.                     }
  156.  
  157.                     conn.Close();
  158.                 }
  159.                 catch (Exception ex) {
  160.                     MessageBox.Show(ex.Message);
  161.                 }
  162.             }
  163.         }
  164.  
  165.         private void Delete(object sender, EventArgs e) {
  166.             DialogResult dialogResult = MessageBox.Show("Do you to delete this record?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
  167.  
  168.             if (dialogResult == DialogResult.Yes)
  169.             {
  170.                 try
  171.                 {
  172.                     conn = new SQLiteConnection(connectString);
  173.                     conn.Open();
  174.                     cmd = new SQLiteCommand();
  175.                     cmd.CommandText = @"DELETE FROM member WHERE ID='" + id + "'";
  176.                     cmd.Connection = conn;
  177.                     int i = cmd.ExecuteNonQuery();
  178.                     if (i == 1)
  179.                     {
  180.                         MessageBox.Show("Successfully Deleted!");
  181.                         id = 0;
  182.                         dataGridView1.ClearSelection();
  183.                         dataGridView1.CurrentCell = null;
  184.                         ReadData();
  185.                         dataGridView1.ClearSelection();
  186.                         dataGridView1.CurrentCell = null;
  187.                     }
  188.                 }
  189.                 catch (Exception ex)
  190.                 {
  191.                     MessageBox.Show(ex.Message);
  192.                 }
  193.             }
  194.             else if (dialogResult == DialogResult.No)
  195.             {
  196.  
  197.             }
  198.  
  199.         }
  200.  
  201.         private void Clear(object sender, EventArgs e)
  202.         {
  203.             id = 0;
  204.             txt_firstname.Text = "";
  205.             txt_lastname.Text = "";
  206.             txt_address.Text = "";
  207.             dataGridView1.ClearSelection();
  208.             dataGridView1.CurrentCell = null;
  209.             isDoubleClick = false;
  210.         }
  211.     }
  212. }
Try to run the application and see if it works. There you go we successfully created a Simple CRUD Application With SQLite using C#. I hope that this tutorial help you understand on how to develop an application using C#. For more updates and tutorials just kindly visit this site. Enjoy Coding!!!

Tags

Add new comment

About text formats
  • 3787 views