Insert, Update, Delete with Gridview....simple way using Stored Procedure

Introduction
This is a simple way to display, Update, Delete and Insert through a single page.

Using the code
Many of us may be encountered with Update and Delete operations with GridView. I’m explaining this with more functionality that is Inserting record through the GridView. There could me more way to achieve this functionality but I think it’s a very simple way to achieve this. I’m taking a very simple table named “quest_categories”

CREATE TABLE [dbo].[quest_categories](
[cat_id] [int] IDENTITY(1,1) NOT NULL,
[cat_name] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_quest_categories] PRIMARY KEY CLUSTERED
(
[cat_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Here is the Code for “Add Category”

protected void btnAdd_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings\["exam_moduleConnectionString"].ConnectionString);

SqlDataAdapter da = new SqlDataAdapter("SELECT cat_id, cat_nameFROMquest_categories",con);

DataTable dt = new DataTable();

da.Fill(dt);

// Here we'll add a blank row to the returned DataTableDataRow dr = dt.NewRow();dt.Rows.InsertAt(dr, 0);//Creating the first row of GridView to be EditableGridView1.EditIndex = 0;

GridView1.DataSource = dt;

GridView1.DataBind();//Changing the Text for Inserting a New Record((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text = "Insert";
}

And according to Text Diplays we’ll do further processing as “Update” or “Insert” record.
Here is the code for “RowUpdating” event.

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
if (((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text == "Insert")
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO quest_categories(cat_name) VALUES(@cat_name)";
cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[2].Controls[0]).Text;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();

Response.Redirect("quest_categories.aspx");
}
else
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UPDATE quest_categories SET cat_name=@cat_name WHERE cat_id=@cat_id";
cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
GridView1.EditIndex = -1;
BindData();
}

Rest operations are same as you might have done before so I’m not explaining it further.