VB.NET DB Connectivity Coding Reference part - 2 | CRUD & Navigation Operation

Previous post we have learned 

VB.NET Database Connectivity Coding Reference part - 1 | Database Connection.

In this post, We are going to learn How to handle the CRUD (C- Create, R- Retrieve, U- Update, D- Delete) and Navigate the record of database. (Next, Previous, Move Next, Move Previous).

This tutorial have one file which is a form.

 Resource File Download 

This is form Design View :

This is Form Code View:

Imports System.Data.SqlClient
Public Class frmCustomer

    Public intex As Integer = 0

    Dim conn As New SqlConnection("server=Najathi\SQLEXPRESS;database=Customer;Integrated Security=SSPI")

    Dim cmd As New SqlCommand("SELECT * FROM cus", conn)
    Dim da As New SqlDataAdapter(cmd)
    Dim ds As New DataSet("cus")

    Sub ShowData(position As Integer)

        If ds.Tables(0).Rows.Count >= 0 Then

            txtCusName.Text = ds.Tables(0).Rows(position).Item("CusName")
            txtCusAge.Text = ds.Tables(0).Rows(position).Item("CusAge")
            txtCusEmp.Text = ds.Tables(0).Rows(position).Item("CusEmp")

        End If

    End Sub

    Sub Fill()

        conn.Open()
        da.Fill(ds)
        conn.Close()

    End Sub

    Sub Update()

        conn.Open()
        da.Update(ds)
        conn.Close()

    End Sub

    Sub Clear()
        txtCusName.Clear()
        txtCusAge.Clear()
        txtCusEmp.Clear()
    End Sub

    Private Sub btnFrist_Click(sender As Object, e As EventArgs) Handles btnFrist.Click

        intex = 0
        ShowData(intex)

    End Sub

    Private Sub frmCustomer_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Fill()
        ShowData(intex)
    End Sub

    Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
        Clear()
    End Sub

    Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click

        intex = ds.Tables(0).Rows.Count - 1
        ShowData(intex)

    End Sub

    Private Sub btnMoveNext_Click(sender As Object, e As EventArgs) Handles btnMoveNext.Click

        If Not intex = ds.Tables(0).Rows.Count - 1 Then
            intex += 1
            ShowData(intex)
        End If

    End Sub

    Private Sub btnMovePrevious_Click(sender As Object, e As EventArgs) Handles btnMovePrevious.Click

        If intex > 0 Then

            intex -= 1
            ShowData(intex)

        End If

    End Sub

    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub

    Private Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click

        Dim query As String = "INSERT INTO cus(CusName,CusAge,CusEmp) VALUES('" & txtCusName.Text & "'," & Val(txtCusAge.Text) & "," & Val(txtCusEmp.Text) & ")"

        conn.Open()
        cmd = New SqlCommand(query, conn)
        cmd.ExecuteNonQuery()
        conn.Close()

        Fill()
        Clear()

    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click

        Dim query As String = "DELETE FROM cus WHERE CusEmp=" & txtCusEmp.Text

        conn.Open()
        cmd = New SqlCommand(query, conn)
        cmd.ExecuteNonQuery()
        conn.Close()

        Fill()
        Clear()

    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

        Dim query As String = "UPDATE cus SET CusName='" & txtCusName.Text & "',CusAge=" & Val(txtCusAge.Text) & ",CusEmp=" & Val(txtCusEmp.Text) & "WHERE CusEmp=" & Val(txtCusEmp.Text)

        conn.Open()
        cmd = New SqlCommand(query, conn)
        cmd.ExecuteNonQuery()
        conn.Close()

        Fill()
        Update()

        intex = ds.Tables(0).Rows.Count - 1
        ShowData(intex)

    End Sub
End Class