VB.NET SQLite Tutorials : CRUD (add,update,delete) Operations ADO.NET

VB.NET SQLite Tutorials for beginner : how to create SQLite connections string and CRUD (Create, Read, Update, Delete) operations example using ADO.NET connections.

VB.NET Tutorials Create Connections to SQLite Connection String

GridView CRUD Operations in VB.NET using ADO.NET and MySQL Database

VB.Net & SQLite Tutorials : How to create connection using ADO.NET and SQLite Embedded database? and how to create CRUD Examples in SQLite database? SQLite Embedded database can handle CRUD operations with ADO.NET connection, OdBc Connection as a provider to connect with SQLite database.

Please Read

CRUD Example using SQLite

How it's work?

First, we will create new database using SQLite Administration Tools and save to our project (/bin/debug), then we will make an connection using ADO.NET as a provider an create CRUD (add, Insert,Update,Delete) operations to our SQLite database.

Create New Project (CRUD SQLite)

Open your visual studio and create new project "CRUDSqlite", and at the Form1.vb add one TextBoxt, 4 Button and one DataGridView then please design it look like this images :

Form1.Vb

VB.NET SQLite Tutorials : CRUD (add,update,delete) Operations

Add New form (FormADDnewData.Vb) and insert 5 TextBox and 2 Button, then design it look like this images :

FormADDNewData.Vb

ADD SQLite .DLL Library from NuGet PackAges

we have already create all UI for our project, now we will import the SQLite library to our project before create database and connection using SQLite. Right click on you project solutions > Manage NuGet Packages for Solutions > and search for the "SQLite", read full tutorial here http://www.hc-kr.com/2016/08/vbnet-tutorials-create-connections-to.html

Create database in SQLite Embedded Database

Create new database using this query :


CREATE TABLE [tbl_biodata] (
[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[name] VARCHAR(30)  NULL,
[nis] VARCHAR(20)  NULL,
[kelas] NVARCHAR(15)  NULL,
[alamat] VARCHAR(15)  NULL
)

Create new database like "sqlite_db.s3db" and save it on the our project (/bin/debug), To create SQLite database, you can see full tutorial on the video below leter.

FULL Source code CRUD Operations using SQLite Database

Module Connections & Data Update Functions

Create new module from our project, and create name "ModuleConnections.Vb", then write all source code below :


Imports System.Data.SQLite
Module Moduleconnections
    ' import SQLite Library using Nuget Packages
    Public connection As SQLiteConnection
    Sub connect()
        Try
            connection = New SQLiteConnection("Data Source=crud.s3db")
            If connection.State = ConnectionState.Closed Then
                connection.Open()
            End If
        Catch ex As Exception
            MsgBox("Connection Failed!")
        End Try
    End Sub
    ' function for save or update and delete
    Public Sub RunSQL(ByVal sql As String)
        Try
            connect()
            Dim cmd As New SQLiteCommand
            cmd.Connection = connection
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            connection.Close()
            MsgBox("Data Hasbeen Saved !")
        Catch ex As Exception
            MsgBox("Failed when saving data")
        End Try
    End Sub
End Module

Source Code CRUD Operations (Form1.Vb)


Imports System.Data.SQLite
Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        showData()
    End Sub

    Sub showData()
        connect()
        Dim da As New SQLiteDataAdapter("select * from tbl_biodata", connection)
        'Dim dt As New DataTable
        Dim ds As New DataSet
        da.Fill(ds, "tbl_biodata")

        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "tbl_biodata"
        connection.Clone()
        da.Dispose()
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ' we will create save functions in our module
        Dim row, id As Integer
        Dim nama, nis, kelas, alamat As String
        ' Declare the variable to get value event click on datagridview
        row = DataGridView1.CurrentRow.Index
        id = DataGridView1(0, row).Value
        nama = DataGridView1(1, row).Value
        nis = DataGridView1(2, row).Value
        kelas = DataGridView1(3, row).Value
        alamat = DataGridView1(4, row).Value
        ' query to Update data into biodata tables
        Dim UpdateData As String = "UPDATE tbl_biodata SET name='" & nama & "',nis='" & nis & "',kelas='" & kelas & "',alamat='" & alamat & "' WHERE id=" & id & ""
        ' call function to update data
        RunSQL(UpdateData)
        ' fill new data into datagridview1
        showData()
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim id, row As Integer
        row = DataGridView1.CurrentRow.Index
        id = DataGridView1(0, row).Value
        Dim message As String
        message = MsgBox("Are you sure to delete this data? ", vbYesNo + vbInformation, "Warning")
        If message = vbNo Then
            Exit Sub
        End If
        ' query to delete data from biodata tables
        Dim DeleteData As String = "DELETE FROM tbl_biodata where id=" & id & ""
        ' call function to update data
        RunSQL(DeleteData)
        ' fill new data into datagridview1
        showData()
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Me.Close()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        FormADDnewData.Show()
    End Sub
    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        ' search functions
        connect()
        Dim da As New SQLiteDataAdapter("SELECT * FROM tbl_biodata WHERE name like '" & TextBox1.Text & "%'", connection)
        Dim dt As New DataTable
        da.Fill(dt)
        DataGridView1.DataSource = dt
        connection.Clone()
        da.Dispose()
    End Sub
End Class

Source Code Form Add New Data (FormAddNewData.Vb)


Public Class FormADDnewData
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim saveBiodata As String = "INSERT INTO tbl_biodata(id,name,nis,kelas,alamat)VALUES(" & TextBox1.Text & ",'" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "')"
        RunSQL(saveBiodata)
        With Form1
            ' refresh the gridview
            ' load new data
            .DataGridView1.Refresh()
            .showData()
        End With
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Me.Close()
    End Sub
End Class

Press "F5" keys to start debugging your simple crud applications.

CRUD (add,update,delete) Operations ADO.NET

Watch video tutorials below :

Video tutorial How to CRUD example in SQLite Database

Download Full Source code CRUD SQLite https://www.dropbox.com/s/pc27zceta27yp81/hc-kr-com-SQLiteCrudOperation.zip?dl=0
See you Next Lessons.

KODE AJAIB