Insert, Update, Delete and Select in Gridview using Single Stored Procedure

Introduction

In this article lets see how to Insert, Update, Delete and Select in Gridview using a Single Stored Procedure.

first create the data table like below

1. Create table in database.
2. Create Store Procedure.
3. Design .aspx page.
4. Write Code on .aspx.CS pag

Step 1. Create table in Database

Table Name:  emp

Step 2. Create Store Procedure.

Step 3. Create/design .aspx page

Step 4. C# Code

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


public partial class Employees : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ConnectionString);
    SqlCommand cmd = new SqlCommand("employees2");
    SqlDataAdapter da = new SqlDataAdapter();
    DataSet ds = new DataSet();
    

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGrid();
            
        }
    }

    protected void insert_Click(object sender, EventArgs e)
    {
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.Parameters.AddWithValue("@action", "INSERT");
        cmd.Parameters.AddWithValue("@name", txtname.Text);
        cmd.Parameters.AddWithValue("@empaddress", txtaddress.Text);
        cmd.Parameters.AddWithValue("@emailid", txtemailid.Text);
        cmd.Parameters.AddWithValue("@contactno", txtcontactno.Text);
        cmd.Parameters.AddWithValue("@city", txtcity.Text);
        cmd.Parameters.AddWithValue("@country", txtcountry.Text);
        Label7.Text = "inserted";
        cmd.ExecuteNonQuery();
        GridView1.DataSource = ds.Tables["emp"];
        GridView1.DataBind();
        con.Close();
        txtname.Text = "";
        txtaddress.Text = "";
        txtemailid.Text = "";
        txtcontactno.Text = "";
        txtcity.Text = "";
        txtcountry.Text = "";
        
       
    }
    protected void update_Click(object sender, EventArgs e)
    {
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.Parameters.AddWithValue("@action", "Update");
        cmd.Parameters.AddWithValue("@empid", ddid.SelectedItem.Text);
        cmd.Parameters.AddWithValue("@name", txtname.Text);
        cmd.Parameters.AddWithValue("@empaddress", txtaddress.Text);
        cmd.Parameters.AddWithValue("@emailid", txtemailid.Text);
        cmd.Parameters.AddWithValue("@contactno", txtcontactno.Text);
        cmd.Parameters.AddWithValue("@city", txtcity.Text);
        cmd.Parameters.AddWithValue("@country", txtcountry.Text);
        Label7.Text = "Updated";
        cmd.ExecuteNonQuery();
        GridView1.DataSource = ds.Tables["emp"];
        GridView1.DataBind();
        con.Close();
        txtname.Text = "";
        txtaddress.Text = "";
        txtemailid.Text = "";
        txtcontactno.Text = "";
        txtcity.Text = "";
        txtcountry.Text = "";
        ddid.Text = "";
       

    }
    protected void delete_Click(object sender, EventArgs e)
    {
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.Parameters.AddWithValue("@action", "DELETE");
        cmd.Parameters.AddWithValue("@empid", ddid.SelectedItem.Text);
        Label7.Text = "";
        cmd.ExecuteNonQuery();
        GridView1.DataSource = ds.Tables["emp"];
        GridView1.DataBind();
        con.Close();
        txtname.Text = "";
        txtaddress.Text = "";
        txtemailid.Text = "";
        txtcontactno.Text = "";
        txtcity.Text = "";
        txtcountry.Text = "";
        
    }
    protected void Select_Click(object sender, EventArgs e)
    {
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.Parameters.AddWithValue("@action", "SELECT");
        cmd.Parameters.AddWithValue("@empid", ddid.SelectedItem.Text);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);

       
        txtname.Text = ds.Tables[0].Rows[0]["name"].ToString();
        txtaddress.Text = ds.Tables[0].Rows[0]["empaddress"].ToString();
        txtemailid.Text = ds.Tables[0].Rows[0]["emailid"].ToString();
        txtcontactno.Text = ds.Tables[0].Rows[0]["contactno"].ToString();
        txtcity.Text = ds.Tables[0].Rows[0]["city"].ToString();
        txtcountry.Text = ds.Tables[0].Rows[0]["country"].ToString();
    }
    protected void BindGrid()
    {
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        cmd.Parameters.AddWithValue("@action", "SELECT2");
        da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        GridView1.DataSource = ds.Tables["emp"];
        GridView1.DataBind();
    }
}

Output:

Thank You...