Create Update Delete using DataGrid in C#

Hi in this tutorial we are going to see how to perform Create,Update and delete operation in DataGrid using c#.Create a New Project and design a form like as show below.

Now Open you MS SQL Database and create a new database TestDB and paste the following query in the query editor window and execute it.

USE [TestDB]

GO

/****** Object:  Table [dbo].[tblCRUD]    Script Date: 09/29/2016 22:37:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblCRUD](

[id] [int] IDENTITY(1,1) NOT NULL,

[name] [nvarchar](25) NULL,

[department] [nvarchar](25) NULL,

[year] [int] NULL,

[place] [nvarchar](25) NULL,

 CONSTRAINT [PK_tblCRUD] PRIMARY KEY CLUSTERED 

(

[id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Now Switch to your Visual Studio copy and past the following code

Full Source Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace test
{
    public partial class CRUD : Form
    {
        int index = 0;
        public CRUD()
        {
            InitializeComponent();
        }

        private void CRUD_Load(object sender, EventArgs e)
        {
            UpdateGrid();
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            try
            {
                string insertquery = "insert into tblCRUD values (@name,@department,@year,@place)";
                SqlConnection constr = Constr();
                using (SqlCommand cmd = new SqlCommand(insertquery, constr))
                {
                    constr.Open();
                    cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
                    cmd.Parameters.AddWithValue("@department", txtDept.Text.ToString());
                    cmd.Parameters.AddWithValue("@year", txtYear.Text.ToString());
                    cmd.Parameters.AddWithValue("@place", txtPlace.Text.ToString());
                    cmd.ExecuteNonQuery();
                    constr.Close();
                    MessageBox.Show("Data Inserted Successfully...");
                    UpdateGrid();
                    clearData();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception:"+ex.ToString());
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            SqlConnection constr = Constr();
            string deleteQuery = "delete tblCrud where id=@id";
            using (SqlCommand cmd = new SqlCommand(deleteQuery,constr))
            {
                constr.Open();
                cmd.Parameters.AddWithValue("@id", index);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Record Deleted Successfully...");
                UpdateGrid();
                clearData();
            }
        }

        /*Create Connection String*/
        private SqlConnection Constr()
        {
            string sqlConnection = @"Data Source= DESKTOP-LR8BT4M\GAMER;Initial Catalog=TestDB;Integrated Security=True";
            SqlConnection constr = new SqlConnection(sqlConnection);
            return constr;
        }

        /*Update DataGrid*/
        private void UpdateGrid()
        {
            SqlConnection constr = Constr();
            constr.Open();
            string selectQuery = "Select *from tblCRUD";
            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter(selectQuery, constr);
            sda.Fill(dt);
            dataGridView1.DataSource = dt;
            constr.Close();
        }

        /*Clear date*/
        private void clearData()
        {
            txtName.Text = string.Empty;
            txtDept.Text = string.Empty;
            txtYear.Text = string.Empty;
            txtPlace.Text = string.Empty;
        }         

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            SqlConnection constr = Constr();
            string updatequery = "update tblCRUD set name=@name,department=@department,year=@year,place=@place where ID=@id";
            using (SqlCommand cmd = new SqlCommand(updatequery, constr))
            {
                constr.Open();
                cmd.Parameters.AddWithValue("@id", index);
                cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
                cmd.Parameters.AddWithValue("@department", txtDept.Text.ToString());
                cmd.Parameters.AddWithValue("@year", txtYear.Text.ToString());
                cmd.Parameters.AddWithValue("@place", txtPlace.Text.ToString());
                cmd.ExecuteNonQuery();
                constr.Close();
                MessageBox.Show("Data updated Successfully...");
                UpdateGrid();
                clearData();
            }
        }

        private void dataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            try
            {

                index = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString());
                txtName.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
                txtDept.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
                txtYear.Text = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();
                txtPlace.Text = dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString();
            }
            catch (Exception ex)
            {                                
            }
        }         
    }

}

Output:

Dowload Full Source Code with database from here.

Download Source Code