insert update delete edit in datagridview using c#

Advertisement

In this Article I will explain How to insert, update, edit, delete, clear using datagridview in C# from one Form to another Form.

In my previous articles I explained How to display data in datagridview, How to add auto generate SNo in datagridview and also How to add Edit and Deletelink buttons in datagridview.

We can see my Out Put looks like

In this example I am having Two Forms.

I designed Form1 looks like this, Please check my previouspost if you having any doubts

Now I am adding another form, Form2.cs and I designed my Form2.cs as shown below

After completion of UI design.

My Solution explorer looks as shown below.

I design my table with name Employee looks like this

For this table i written two stored procedures for Insert and update.

USE [dotnetdb]
GO

/****** Object:  StoredProcedure [dbo].[SP_InsertEmpinfo]    Script Date: 07/21/2013 15:09:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo].[SP_InsertEmpinfo]
(
@EmpId nvarchar(50),
@EmpName nvarchar(50),
@EmpAddress nvarchar(50),
@EmpDesignation nvarchar(50)
)

As Begin

Insert into Employee (EmpId,EmpName,EmpAddress,EmpDesignation) values (@EmpId,@EmpName,@EmpAddress,@EmpDesignation)
end

GO


USE [dotnetdb]
GO

/****** Object:  StoredProcedure [dbo].[SP_UpdateEmpInfo]    Script Date: 07/21/2013 15:09:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[SP_UpdateEmpInfo]


(
@Empid nvarchar(50),
@EmpName nvarchar(50),

@EmpAddress nvarchar(50),
@EmpDesignation nvarchar(50)

)

As Begin


update Employee set EmpName = @EmpName,EmpAddress = @EmpAddress,EmpDesignation = @EmpDesignation where Empid = @Empid

End

GO

After Creating Stored Procedure, I Written the code for Insert,Update,Edit,Delete,Clear. In Form1.cs and Form2.cs.

Form2.cs Code

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

namespace DatagridviewExample
{
    public partial class Form2 : Form
    {
        public static string Employeeid;

        SqlConnection con = new SqlConnection("Data Source = CHINNU;Initial Catalog = dotnetdb;Uid = sa;Password = password123;");

        public Form2()
            
        {

            if (Form1.empid != null)
            {

                Employeeid = Form1.empid;
            }
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(Form1.empid))
            {
                
                con.Open();
                SqlCommand Command = new SqlCommand("Select * from employee where EmpId='" + Employeeid + "'", con);
                SqlDataReader Reader = Command.ExecuteReader();
                if (Reader.HasRows)
                {
                    if (Reader.Read())
                    {
                        txtempid.Text = Reader.GetValue(0).ToString();
                        txtempid.Enabled = false;
                        
                        txtempname.Text = Reader.GetValue(1).ToString();
                        txtempaddress.Text = Reader.GetValue(2).ToString();

                        txtdesignation.Text = Reader.GetValue(3).ToString();
                  
                    }
                    con.Close();
                }

            }
        }

        private void submit_Click(object sender, EventArgs e)
        {

            string empupdateid = txtempid.Text;
            if (empupdateid != Employeeid)
            {
                SqlCommand cmd = new SqlCommand("SP_InsertEmpinfo", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@EmpId", SqlDbType.NVarChar).Value = txtempid.Text;
                cmd.Parameters.Add("@EmpName", SqlDbType.NVarChar).Value = txtempname.Text;
                cmd.Parameters.Add("@EmpAddress", SqlDbType.NVarChar).Value = txtempaddress.Text;
                cmd.Parameters.Add("@EmpDesignation", SqlDbType.NVarChar).Value = txtdesignation.Text;
                con.Open();
                cmd.ExecuteNonQuery();
                lblmessage.Text = "Data inserted";
                con.Close();
            }

            else
            {
                SqlCommand cmd = new SqlCommand("SP_UpdateEmpInfo", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@EmpId", SqlDbType.NVarChar).Value = txtempid.Text;
                cmd.Parameters.Add("@EmpName", SqlDbType.NVarChar).Value = txtempname.Text;
                cmd.Parameters.Add("@EmpAddress", SqlDbType.NVarChar).Value = txtempaddress.Text;
                cmd.Parameters.Add("@EmpDesignation", SqlDbType.NVarChar).Value = txtdesignation.Text;
                con.Open();
                cmd.ExecuteNonQuery();
                lblmessage.Text = "Data Updated";
                con.Close();


            }

        }

        private void btnReset_Click(object sender, EventArgs e)
        {

            txtempname.Text = "";
            txtempaddress.Text = "";
            txtdesignation.Text = "";
           
                lblmessage.Text = "Data Cleared";
            

        }




    }
}

Form1.cs having datagridview with Edit and Delete LinkButtons.

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

namespace DatagridviewExample
{


    public partial class Form1 : Form
    {
         SqlConnection con = new SqlConnection("Data Source = CHINNU;Initial Catalog = dotnetdb;Uid = sa;Password = password123;");
        public static string empid;

        public string emp
        {
            get { return empid; }
            set { empid = value; }

        }
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            displayDataGridView();

            DataGridViewLinkColumn Editlink = new DataGridViewLinkColumn();
            Editlink.UseColumnTextForLinkValue = true;
            Editlink.HeaderText = "Edit";
            Editlink.DataPropertyName = "lnkColumn";
            Editlink.LinkBehavior = LinkBehavior.SystemDefault;
            Editlink.Text = "Edit";
            dataGridView1.Columns.Add(Editlink);

            DataGridViewLinkColumn Deletelink = new DataGridViewLinkColumn();
            Deletelink.UseColumnTextForLinkValue = true;
            Deletelink.HeaderText = "delete";
            Deletelink.DataPropertyName = "lnkColumn";
            Deletelink.LinkBehavior = LinkBehavior.SystemDefault;
            Deletelink.Text = "Delete";
            dataGridView1.Columns.Add(Deletelink);

        }


        public void displayDataGridView()
        {


           
            
                SqlCommand cmd;
                cmd = new SqlCommand("select * from Employee", con);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];

                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.AllowUserToAddRows = false;
                int i = 1;
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    row.Cells["SNO"].Value = i;
                    i++;
                }


            

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 5)
            {

            
                     empid = Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells["EmpId"].Value);
                Form2 fm2 = new Form2();
                fm2.Show();
                
            }

            if (e.ColumnIndex == 6)
            {
                empid = Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells["EmpId"].Value);
                
                    SqlDataAdapter da = new SqlDataAdapter("delete from employee where EmpId = '"+empid+"'",con);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                  displayDataGridView();
                    dataGridView1.Refresh();
                
            }
        }
                 
        }
    }

App.Config:

After Completing all the steps Press F5.

OutPut:

Inserting the data with Label message.

The Data we inserted in Form2 it was displayed in Form1 as shown below.

Now if you want to Edit the Information,when we click on edit it shows form2.cs

I updated the EmpAdress from Us to America.

Now after Updating Form2 it will display Form1 with updated information.

Now if we click on delete the row will be deleted from the datagridview as well as from database as shown below in Datagridview.

Advertisements

SHARE

Phani Kumar

• 
• 
• 
• 
•