Insert and Update Table Record Using Single Procedure and CellDoubleClick Event of DataGridView

Introduction

In this article I explain how to perform insert and update operations using a single store procedure and the CellDoubleClick Event of a DataGridView Control. First, understand the scenario here. I have used only one button for doing both the insert and update and a single procedure also. So first of all create a table and a Stored Procedure to implement it.

Create Table

create table emp2(id int identity(1,1),Name varchar(max), salary int )

Create Procedure

create procedure insertupdate

(

@id int,

@name varchar(max),

@salary int,

@Flag char(1)

)

As

IF @Flag ='S'

begin

insert into emp2 values (@name, @salary )

end

Else

begin

Update emp2 set Name =@name ,salary =@salary where id =@id

end

Now implement this procedure that inserts and updates in Windows Forms applications.

Step 1

Create a Windows Forms application and insert three TextBoxes and one DataGridView control on the form1 with the visible property set to false,

Step 2

Now write the code to insert and update the record.

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 use_gridview

{

public partial class Form1 : Form

{

//-----------------------------------------------------------------------//

public Form1()

{

InitializeComponent();

}

//-----------------------------------------------------------------------//

SqlConnection con = new SqlConnection("Data Source=MCNDESKTOP03;Initial Catalog=pulkit;User ID=sa;Password=wintellect@123");

SqlCommand cmd;

SqlDataAdapter da;

DataSet ds;

int i;

int num = 0;

//-----------------------------------------------------------------------//

private void Form1_Load(object sender, EventArgs e)

{

showrecord();

}

//-----------------------------------------------------------------------//

private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)

{

if (e.RowIndex > -1 && e.ColumnIndex > -1)

{

label1.Visible = true;

textBox1.Visible = true;

textBox1.ReadOnly = true;

textBox1.Text = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();

textBox2.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();

textBox3.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();

}

}

//-----------------------------------------------------------------------//

public void saverecord()

{

if (textBox2.Text == "")

{

MessageBox.Show("Please enter Name");

return;

}

if (textBox3.Text == "")

{

MessageBox.Show("Please enter salary");

}

con.Open();

cmd = new SqlCommand("insertupdate", con);

cmd.CommandType = CommandType.StoredProcedure;

if (textBox1.Text == "")

{

num = 0;

}

else

{

num = int.Parse(textBox1.Text);

}

cmd.Parameters.AddWithValue("@id", num);

cmd.Parameters.AddWithValue("@name", textBox2.Text);

cmd.Parameters.AddWithValue("@salary", int.Parse(textBox3.Text));

if (btnsave.Text == "Save")

{

cmd.Parameters.AddWithValue("@Flag", 'S');

}

else

{

cmd.Parameters.AddWithValue("@Flag", 'U');

}

i=cmd.ExecuteNonQuery();

if (i > 0 && btnsave.Text == "Save")

{

MessageBox.Show("record inserted");

}

else

{

MessageBox.Show("record Update");

}

con.Close();

}

//-----------------------------------------------------------------------//

public void clearfilds()

{

textBox1.Text = "";

textBox2.Text = "";

textBox3.Text = "";

btnsave.Text = "Save";

label1.Visible = textBox1.Visible = false;

}

//-----------------------------------------------------------------------//

public void showrecord()

{

con.Open();

cmd = new SqlCommand("select * from emp2", con);

da = new SqlDataAdapter(cmd);

ds = new DataSet();

da.Fill(ds);

dataGridView1.DataSource = ds.Tables[0];

con.Close();

}

//---------------------------------------------------------------------//

private void btnsave_Click(object sender, EventArgs e)

{

saverecord();

showrecord();

clearfilds();

}

//-----------------------------------------------------------------------//

}

}

Step 3

Run your application and insert a record.

Step 4

Now double-click in a DataGridView cell, then the record value is filled in the TextBox and the Save button becomes an Update button after that the value of the name salary field is changed; click on the Update button to update the record.