Sunday, 21 July 2013

insert update delete edit in datagridview using c#

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

We can see my Out Put looks like


In this example I am having Two Forms.



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.




10 comments:

  1. Very Nice and helpful too..

    but after updation my gridview is not updated..

    ReplyDelete
  2. dear sir/ma"am
    i have 2 button in gridview(edit and delete).edit button working properly but delete button not working properly.after deletion a row. the delete operation done by another cellindex not by delete button.

    ReplyDelete
  3. SIR..
    ,edit and delete link buttons,update,delete,binding data to grid view ,auto generate sno..is working..but how to insert data..

    ReplyDelete
  4. if i click edit it will show form2 ..then it will have auto generate key..and all field values will populated..and also i can update..by showing data updated..

    ReplyDelete
    Replies
    1. In your..example only how can i insert data..?? i came to know that updateid != empid....but how to insert i am getting ..every time i can update and delete

      Delete
    2. i mean i am not getting how to insert data..

      Delete
  5. Well I have a problem. Where you have named it empid I setted it as id and there's an error 'Example.Form1' does not contain a definition for 'id' '
    The other errors are "The name 'lblmessage' does not exist in the current context" , "The name 'con' does not exist in the current context"

    ReplyDelete
    Replies
    1. You have to change the name id on 'Example.Form1' also

      Delete