CRUD Operation in Gridview using Stored Procedure

Here we will learn crud operation in GridView using stored procedure in ASP.NET.

Before implementing this application first we will design the database table name is 'CollageInfo' and then stored Procedure name is CurdOperationInGridView.

Create Database Table and Stored procedure like following if you need,

CREATE TABLE CollageInfo
(
Collageid int identity PRIMARY KEY ,
Collagename nvarchar(50),
CollageRank double
)
GO
CREATE PROCEDURE CrudOperationsInGridView
@Collageid int = 0,
@Collagename varchar(50)=null,
@CollageRank int=0,
@status varchar(50)
AS
BEGIN
SET NOCOUNT ON;
--- Insert New Records For GridView
IF @status='INSERT'
BEGIN
INSERT INTO CollageInfo(Collagename,CollageRank) VALUES(@Collagename,@CollageRank)
END
--- Select Records in Table For GridView
IF @status='SELECT'
BEGIN
SELECT Collageid,Collagename,CollageRank FROM CollageInfo
END
--- Update Records in Table For GridView
IF @status='UPDATE'
BEGIN
UPDATE CollageInfo SET @Collagename=@Collagename,CollageRank=@CollageRank WHERE Collageid=@Collageid
END
--- Delete Records from Table For GridView
IF @status='DELETE'
BEGIN
DELETE FROM CollageInfo where Collageid=@Collageid
END
SET NOCOUNT OFF
END

Once we Create stored procedure in database then add an aspx page and write the following code on .aspx Page.

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>GridView Crud Operations using Stored Procedure in ASP.Net</title>
<style type="text/css">
.GridviewDesign {
font-size: 100%;
font-family: 'Trebuchet MS', 'Lucida Sans Unicode', 'Lucida Grande', 'Lucida Sans', Arial, sans-serif;
color: #303933;
}
.headerstyleForGrid {
color: #FFFFFF;
border-right-color: #abb079;
border-bottom-color: #abb079;
background-color: #df5015;
padding: 0.5em 0.5em 0.5em 0.5em;
text-align: center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="GridviewDesign">
<asp:GridView runat="server" ID="gvDetailsForCurdOperation" ShowFooter="true" AllowPaging="true" PageSize="10" AutoGenerateColumns="false" DataKeyNames="Collageid,Collagename" OnPageIndexChanging="gvDetailsForCurdOperation_PageIndexChanging" OnRowCancelingEdit="gvDetailsForCurdOperation_RowCancelingEdit" OnRowEditing="gvDetailsForCurdOperation_RowEditing" OnRowUpdating="gvDetailsForCurdOperation_RowUpdating" OnRowDeleting="gvDetailsForCurdOperation_RowDeleting" OnRowCommand="gvDetailsForCurdOperation_RowCommand">
<HeaderStyle CssClass="headerstyleForGrid" />
<Columns>
<asp:BoundField DataField="Collageid" HeaderText="Collage Id" ReadOnly="true" />
<asp:TemplateField HeaderText="Collage Name">
<ItemTemplate>
<asp:Label ID="lblCollagename" runat="server" Text='<%# Eval("Collagename")%>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCollagename" runat="server" Text='<%# Eval("Collagename")%>' />
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtpname" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Collage Rank">
<ItemTemplate>
<asp:Label ID="lblCollageRank" runat="server" Text='<%# Eval("CollageRank")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCollageRank" runat="server" Text='<%# Eval("CollageRank")%>' />
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCollageRank" runat="server" />
<asp:Button ID="btnAddNewItem" CommandName="AddNew" runat="server" Text="Add" />
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" ShowDeleteButton="true" />
</Columns>
</asp:GridView>
<asp:Label ID="lblresult" runat="server"></asp:Label>
</div>
</form>
</body>
</html>

After completion code for .aspx page write the following code on .aspx.cs page,

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) {
BindGridview();
}
}
protected void BindGridview()
{
DataSet ds = new DataSet();
using(SqlConnection con = new SqlConnection("Data Source=Munesh;Integrated Security=true;Initial Catalog=GridCurdOperation"))
{
con.Open();
SqlCommand cmd = new SqlCommand("CrudOperationsInGridView", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@status", "SELECT");
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0) {
gvDetailsForCurdOperation.DataSource = ds;
gvDetailsForCurdOperation.DataBind();
} else {
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetailsForCurdOperation.DataSource = ds;
gvDetailsForCurdOperation.DataBind();
int columncount = gvDetailsForCurdOperation.Rows[0].Cells.Count;
gvDetailsForCurdOperation.Rows[0].Cells.Clear();
gvDetailsForCurdOperation.Rows[0].Cells.Add(new TableCell());
gvDetailsForCurdOperation.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetailsForCurdOperation.Rows[0].Cells[0].Text = "No Records Found";
}
}
}
protected void gvDetailsForCurdOperation_RowCommand(object sender, GridViewCommandEventArgs e) {
if (e.CommandName.Equals("AddNew")) {
TextBox txtname = (TextBox) gvDetailsForCurdOperation.FooterRow.FindControl("txtpname");
TextBox txtprice = (TextBox) gvDetailsForCurdOperation.FooterRow.FindControl("txtprice");
crudoperationsForGrid("INSERT", txtname.Text, txtprice.Text, 0);
}
}
protected void gvDetailsForCurdOperation_RowEditing(object sender, GridViewEditEventArgs e) {
gvDetailsForCurdOperation.EditIndex = e.NewEditIndex;
BindGridview();
}
protected void gvDetailsForCurdOperation_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) {
gvDetailsForCurdOperation.EditIndex = -1;
BindGridview();
}
protected void gvDetailsForCurdOperation_PageIndexChanging(object sender, GridViewPageEventArgs e) {
gvDetailsForCurdOperation.PageIndex = e.NewPageIndex;
BindGridview();
}
protected void gvDetailsForCurdOperation_RowUpdating(object sender, GridViewUpdateEventArgs e) {
int Collageid = Convert.ToInt32(gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collageid"].ToString());
TextBox txtname = (TextBox) gvDetailsForCurdOperation.Rows[e.RowIndex].FindControl("txtCollagename");
TextBox txtCollageRank = (TextBox) gvDetailsForCurdOperation.Rows[e.RowIndex].FindControl("txtCollageRank");
crudoperationsForGrid("UPDATE", txtname.Text, txtCollageRank.Text, Collageid);
}
protected void gvDetailsForCurdOperation_RowDeleting(object sender, GridViewDeleteEventArgs e) {
int Collageid = Convert.ToInt32(gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collageid"].ToString());
string Collagename = gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collagename"].ToString();
crudoperationsForGrid("DELETE", Collagename, "", Collageid);
}
protected void crudoperationsForGrid(string status, string Collagename, string price, int Collageid) {
using(SqlConnection con = new SqlConnection("Data Source=Munesh;Integrated Security=true;Initial Catalog=GridCurdOperation")) {
con.Open();
SqlCommand cmd = new SqlCommand("CrudOperationsInGridView", con);
cmd.CommandType = CommandType.StoredProcedure;
if (status == "INSERT") {
cmd.Parameters.AddWithValue("@status", status);
cmd.Parameters.AddWithValue("@Collagename", Collagename);
cmd.Parameters.AddWithValue("@CollageRank", price);
} else if (status == "UPDATE") {
cmd.Parameters.AddWithValue("@status", status);
cmd.Parameters.AddWithValue("@Collagename", Collagename);
cmd.Parameters.AddWithValue("@CollageRank", price);
cmd.Parameters.AddWithValue("@Collageid", Collageid);
} else if (status == "DELETE") {
cmd.Parameters.AddWithValue("@status", status);
cmd.Parameters.AddWithValue("@Collageid", Collageid);
}
cmd.ExecuteNonQuery();
lblresult.ForeColor = Color.Green;
lblresult.Text = Collagename + " details " + status.ToLower() + "d successfully";
gvDetailsForCurdOperation.EditIndex = -1;
BindGridview();
}
}

Now run your application and see the output and perform CRUD operation for GridView.