Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Asp.net GridView Crud Operations (Insert Select Edit Update Delete) using Single Stored Procedure Example

Jul 20, 2015
Introduction

Here I will explain how to implement
gridview crud operations insert, select, edit, update and delete operations with single stored procedure in asp.net using c#, vb.net with example or insert, update, delete operations (crud) in asp.net gridview using single stored procedure in c#, vb.net with example.

Description:
  
In previous articles I explained
gridview examples in asp.net, display images from database using handler in asp.net, Bind Dropdownlist selected value in asp.net gridview, gridview rowdatabound event example in asp.net, Delete multiple rows in gridview using checkbox in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how to implement gridview crud operations (insert, select, edit, update) in asp.net with single stored procedure using c#, vb.net with example.

Before implement this example first design one table productinfo in your database as shown below

Column Name
Data Type
Allow Nulls
productid
Int(IDENTITY=TRUE)
Yes
productname
varchar(50)
Yes
price
varchar(50)
Yes
Now create one new stored procedure “Crudoperations” in your sql server database to perform insert, select, update and delete operations with single procedure for that follow below script


CREATE PROCEDURE CrudOperations 
@productid int = 0, 
@productname varchar(50)=null, 
@price int=0, 
@status varchar(50) 
AS 
BEGIN 
SET NOCOUNT ON; 
--- Insert New Records 
IF @status='INSERT' 
BEGIN 
INSERT INTO productinfo1(productname,price) VALUES(@productname,@price) 
END 
--- Select Records in Table 
IF @status='SELECT' 
BEGIN 
SELECT productid,productname,price FROM productinfo1 
END 
--- Update Records in Table  
IF @status='UPDATE' 
BEGIN 
UPDATE productinfo1 SET productname=@productname,price=@price WHERE productid=@productid 
END 
--- Delete Records from Table 
IF @status='DELETE' 
BEGIN 
DELETE FROM productinfo1 where productid=@productid 
END 
SET NOCOUNT OFF 
END

In case if you have any doubts to create procedure check below article



Once we finish stored procedure creation in database now open your aspx page and write the code like as shown below


<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>GridView Crud (Select Insert Edit Update Delete) Operations using Single Stored Procedure in ASP.Net</title>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
.headerstyle
{
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="GridviewDiv">
<asp:GridView runat="server" ID="gvDetails" ShowFooter="true" AllowPaging="true" PageSize="10" AutoGenerateColumns="false" DataKeyNames="productid,productname" OnPageIndexChanging="gvDetails_PageIndexChanging" OnRowCancelingEdit="gvDetails_RowCancelingEdit"
OnRowEditing="gvDetails_RowEditing" OnRowUpdating="gvDetails_RowUpdating" OnRowDeleting="gvDetails_RowDeleting" OnRowCommand ="gvDetails_RowCommand" >
<HeaderStyle CssClass="headerstyle" />
<Columns>
<asp:BoundField DataField="productid" HeaderText="Product Id" ReadOnly="true" />
<asp:TemplateField HeaderText="Product Name">
<ItemTemplate>
<asp:Label ID="lblProductname" runat="server" Text='<%# Eval("productname")%>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtProductname" runat="server" Text='<%# Eval("productname")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtpname" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText = "Price">
<ItemTemplate>
<asp:Label ID="lblPrice" runat="server" Text='<%# Eval("price")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtProductprice" runat="server" Text='<%# Eval("price")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtprice" runat="server" />
<asp:Button ID="btnAdd" 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 of aspx page add following namespaces in codebehind

C# Code


using System;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;

After completion of adding namespaces you need to write the code like as shown below


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=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("crudoperations", 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)
{
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
else {
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetails.DataSource = ds;
gvDetails.DataBind();
int columncount = gvDetails.Rows[0].Cells.Count;
gvDetails.Rows[0].Cells.Clear();
gvDetails.Rows[0].Cells.Add(new TableCell());
gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetails.Rows[0].Cells[0].Text = "No Records Found";
}
}
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtname = (TextBox)gvDetails.FooterRow.FindControl("txtpname");
TextBox txtprice = (TextBox)gvDetails.FooterRow.FindControl("txtprice");
crudoperations("INSERT", txtname.Text, txtprice.Text, 0);
}
}
protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
gvDetails.EditIndex = e.NewEditIndex;
BindGridview();
}
protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvDetails.EditIndex = -1;
BindGridview();
}
protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvDetails.PageIndex = e.NewPageIndex;
BindGridview();
}
protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int productid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["productid"].ToString());
TextBox txtname = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtProductname");
TextBox txtprice = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtProductprice");
crudoperations("UPDATE",txtname.Text,txtprice.Text,productid);
}
protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int productid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["productid"].ToString());
string productname = gvDetails.DataKeys[e.RowIndex].Values["productname"].ToString();
crudoperations("DELETE",productname,"",productid);
}
protected void crudoperations(string status, string productname, string price, int productid)
{
using (SqlConnection con = new SqlConnection("Data Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("crudoperations", con);
cmd.CommandType= CommandType.StoredProcedure;
if(status=="INSERT")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productname",productname);
cmd.Parameters.AddWithValue("@price",price);
}
else if(status=="UPDATE")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productname",productname);
cmd.Parameters.AddWithValue("@price",price);
cmd.Parameters.AddWithValue("@productid",productid);
}
else if(status=="DELETE")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productid",productid);
}
cmd.ExecuteNonQuery();
lblresult.ForeColor = Color.Green;
lblresult.Text = productname+" details "+status.ToLower()+"d successfully";
gvDetails.EditIndex = -1;
BindGridview();
}
}

VB.NET Code


Imports System.Web.UI.WebControls
Imports System.Data.SqlClient
Imports System.Data
Imports System.Drawing

Partial Class VBCode
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGridview()
End If
End Sub
Protected Sub BindGridview()
Dim ds As New DataSet()
Using con As New SqlConnection("Data Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("crudoperations", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@status", "SELECT")
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)
con.Close()
If ds.Tables(0).Rows.Count > 0 Then
gvDetails.DataSource = ds
gvDetails.DataBind()
Else
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow())
gvDetails.DataSource = ds
gvDetails.DataBind()
Dim columncount As Integer = gvDetails.Rows(0).Cells.Count
gvDetails.Rows(0).Cells.Clear()
gvDetails.Rows(0).Cells.Add(New TableCell())
gvDetails.Rows(0).Cells(0).ColumnSpan = columncount
gvDetails.Rows(0).Cells(0).Text = "No Records Found"
End If
End Using
End Sub
Protected Sub gvDetails_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
If e.CommandName.Equals("AddNew") Then
Dim txtname As TextBox = DirectCast(gvDetails.FooterRow.FindControl("txtpname"), TextBox)
Dim txtprice As TextBox = DirectCast(gvDetails.FooterRow.FindControl("txtprice"), TextBox)
crudoperations("INSERT", txtname.Text, txtprice.Text, 0)
End If
End Sub
Protected Sub gvDetails_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
gvDetails.EditIndex = e.NewEditIndex
BindGridview()
End Sub
Protected Sub gvDetails_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
gvDetails.EditIndex = -1
BindGridview()
End Sub
Protected Sub gvDetails_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
gvDetails.PageIndex = e.NewPageIndex
BindGridview()
End Sub
Protected Sub gvDetails_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
Dim productid As Integer = Convert.ToInt32(gvDetails.DataKeys(e.RowIndex).Values("productid").ToString())
Dim txtname As TextBox = DirectCast(gvDetails.Rows(e.RowIndex).FindControl("txtProductname"), TextBox)
Dim txtprice As TextBox = DirectCast(gvDetails.Rows(e.RowIndex).FindControl("txtProductprice"), TextBox)
crudoperations("UPDATE", txtname.Text, txtprice.Text, productid)
End Sub
Protected Sub gvDetails_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)
Dim productid As Integer = Convert.ToInt32(gvDetails.DataKeys(e.RowIndex).Values("productid").ToString())
Dim productname As String = gvDetails.DataKeys(e.RowIndex).Values("productname").ToString()
crudoperations("DELETE", productname, "", productid)
End Sub
Protected Sub crudoperations(ByVal status As String, ByVal productname As String, ByVal price As String, ByVal productid As Integer)
Using con As New SqlConnection("Data Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("crudoperations", con)
cmd.CommandType = CommandType.StoredProcedure
If status = "INSERT" Then
cmd.Parameters.AddWithValue("@status", status)
cmd.Parameters.AddWithValue("@productname", productname)
cmd.Parameters.AddWithValue("@price", price)
ElseIf status = "UPDATE" Then
cmd.Parameters.AddWithValue("@status", status)
cmd.Parameters.AddWithValue("@productname", productname)
cmd.Parameters.AddWithValue("@price", price)
cmd.Parameters.AddWithValue("@productid", productid)
ElseIf status = "DELETE" Then
cmd.Parameters.AddWithValue("@status", status)
cmd.Parameters.AddWithValue("@productid", productid)
End If
cmd.ExecuteNonQuery()
lblresult.ForeColor = Color.Green
lblresult.Text = (productname & Convert.ToString(" details ")) + status.ToLower() + "d successfully"
gvDetails.EditIndex = -1
BindGridview()
End Using
End Sub
End Class

Demo

Asp.net GridView Crud Operations (Insert Select Edit Update Delete) using Single Stored Procedure Example
Download Sample Code Attached

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

15 comments :

Unknown said...

Thank you very much sir...

Unknown said...

nice description...Thanq

Unknown said...

Your Written code is very faimiliar very nice code for understand

Unknown said...

not working

Suresh Dasari said...

@aishwar... I hope that is the problem with your code check it clearly where you did mistake....

Unknown said...

Sir before creating table you mentioned the table name as productinfo and later on in stored procedure you used it as productinfo1 in INSERT, SELECT, UPDATE,DELETE operations. How it is possible?

Unknown said...

Thankyou sir....
Small corretion(1)In Sql primary key (2)If i update go to edit table and give productid 1,2 then i check worked perfectly)

Your Past said...

message should be show Inserted when we are inserting new record.

Unknown said...

Great. It has worked for me. Thanks very much.

Unknown said...

VERY HELPFUL POST...

TTK said...

HI,
nice article keep posting

Unknown said...

Sir, when i am adding new value not inserted in database and show grid view. but message showing on page load time. please help me out.

Anonymous said...

we

Anonymous said...

Hi..I Doing CRUD Grid View Example In ASp.Net Using Storred Procedure..I Get Program From Your Site....
My Query Is

in the gvDetails_RowDeleting()

i am used in my table name, address, contact, email, dob, sid for student registration

as comapre to your method gvDetails_RowDeleting() my code is not work how i solve this

Anonymous said...

it works for me....

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.