Insert Update and Delete (CRUD) in Windows Form DataGridView using C# and VB.Net

Hi rani,

Refer below sample.

Namespaces

C#

using System.Data.SqlClient;

VB.Net

Imports System.Data.SqlClient

Code

C#

private void BindGrid()
{
    using (SqlConnection con = new SqlConnection(ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name, Country FROM Customers", con))
        {
            cmd.CommandType = CommandType.Text;
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    dataGridView1.DataSource = dt;
                }
            }
        }
    }

}

private void DataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
    //Check to ensure that the row CheckBox is clicked.
    if (e.RowIndex >= 0 && e.ColumnIndex == 0)
    {
        //Loop and uncheck all other CheckBoxes.
        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            if (row.Index == e.RowIndex)
            {
                row.Cells["checkBoxColumn"].Value = !Convert.ToBoolean(row.Cells["checkBoxColumn"].EditedFormattedValue);
                txtId.Text = row.Cells[1].Value.ToString();
                txtName.Text = row.Cells[2].Value.ToString();
                txtCountry.Text = row.Cells[3].Value.ToString();
            }
            else
            {
                row.Cells["checkBoxColumn"].Value = false;
            }
        }
    }
}

private void Form1_Load(object sender, EventArgs e)
{
    this.BindGrid();
    DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
    checkBoxColumn.HeaderText = "";
    checkBoxColumn.Width = 30;
    checkBoxColumn.Name = "checkBoxColumn";
    dataGridView1.Columns.Insert(0, checkBoxColumn);
    dataGridView1.CellContentClick += new DataGridViewCellEventHandler(DataGridView_CellClick);
}

private void Insert(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers(Name,Country) VALUES(@Name,@Country)", con))
        {
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            cmd.Parameters.AddWithValue("@Country", txtCountry.Text);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    this.BindGrid();
    txtId.Clear();
    txtName.Clear();
    txtCountry.Clear();
}

private void Update(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("UPDATE Customers SET Name = @Name , Country = @Country WHERE CustomerId = @CustomerId", con))
        {
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            cmd.Parameters.AddWithValue("@Country", txtCountry.Text);
            cmd.Parameters.AddWithValue("@CustomerId", txtId.Text);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    this.BindGrid();
    txtId.Clear();
    txtName.Clear();
    txtCountry.Clear();
}

private void Delete(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerId = @CustomerId", con))
        {
            cmd.Parameters.AddWithValue("@CustomerId", txtId.Text);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    this.BindGrid();
    txtId.Clear();
    txtName.Clear();
    txtCountry.Clear();
}

VB.Net

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Me.BindGrid()
    Dim checkBoxColumn As DataGridViewCheckBoxColumn = New DataGridViewCheckBoxColumn()
    checkBoxColumn.HeaderText = ""
    checkBoxColumn.Width = 30
    checkBoxColumn.Name = "checkBoxColumn"
    dataGridView1.Columns.Insert(0, checkBoxColumn)
    AddHandler dataGridView1.CellContentClick, AddressOf DataGridView_CellClick
End Sub

Private Sub Insert(sender As System.Object, e As System.EventArgs) Handles button1.Click
    Using con As SqlConnection = New SqlConnection(ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("INSERT INTO Customers(Name,Country) VALUES(@Name,@Country)", con)
            cmd.Parameters.AddWithValue("@Name", txtName.Text)
            cmd.Parameters.AddWithValue("@Country", txtCountry.Text)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    Me.BindGrid()
    txtId.Clear()
    txtName.Clear()
    txtCountry.Clear()
End Sub

Private Sub Update(sender As System.Object, e As System.EventArgs) Handles button2.Click
    Using con As SqlConnection = New SqlConnection(ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("UPDATE Customers SET Name = @Name , Country = @Country WHERE CustomerId = @CustomerId", con)
            cmd.Parameters.AddWithValue("@Name", txtName.Text)
            cmd.Parameters.AddWithValue("@Country", txtCountry.Text)
            cmd.Parameters.AddWithValue("@CustomerId", txtId.Text)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    Me.BindGrid()
    txtId.Clear()
    txtName.Clear()
    txtCountry.Clear()
End Sub

Private Sub Delete(sender As System.Object, e As System.EventArgs) Handles button3.Click
    Using con As SqlConnection = New SqlConnection(ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("DELETE FROM Customers WHERE CustomerId = @CustomerId", con)
            cmd.Parameters.AddWithValue("@CustomerId", txtId.Text)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    Me.BindGrid()
    txtId.Clear()
    txtName.Clear()
    txtCountry.Clear()
End Sub

Private Sub BindGrid()
    Using con As SqlConnection = New SqlConnection(ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId, Name, Country FROM Customers", con)
            cmd.CommandType = CommandType.Text
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    dataGridView1.DataSource = dt
                End Using
            End Using
        End Using
    End Using
End Sub

Private Sub DataGridView_CellClick(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs)
    If e.RowIndex >= 0 AndAlso e.ColumnIndex = 0 Then
        For Each row As DataGridViewRow In dataGridView1.Rows
            If row.Index = e.RowIndex Then
                row.Cells("checkBoxColumn").Value = Not Convert.ToBoolean(row.Cells("checkBoxColumn").EditedFormattedValue)
                txtId.Text = row.Cells(1).Value.ToString()
                txtName.Text = row.Cells(2).Value.ToString()
                txtCountry.Text = row.Cells(3).Value.ToString()
            Else
                row.Cells("checkBoxColumn").Value = False
            End If
        Next
    End If
End Sub

Screenshot