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.
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
 |
|
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 Email
|
|||
15 comments :
Thank you very much sir...
nice description...Thanq
Your Written code is very faimiliar very nice code for understand
not working
@aishwar... I hope that is the problem with your code check it clearly where you did mistake....
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?
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)
message should be show Inserted when we are inserting new record.
Great. It has worked for me. Thanks very much.
VERY HELPFUL POST...
HI,
nice article keep posting
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.
we
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
it works for me....
Note: Only a member of this blog may post a comment.