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

jQuery Ajax Gridview Crud Operations (Insert, Edit, Update, Delete) in Asp.net using C#, VB.NET

Jul 28, 2015
Introduction

Here I will explain how to implement
jQuery ajax gridview crud operations insert, select, edit, update and delete example with single stored procedure in asp.net using c#, vb.net or jQuery 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 jQuery 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>jQuery Gridview Crud Operations Example</title>
<script type="text/javascript" src="http://code.jquery.com/jquery-1.8.2.js"></script>
<script type="text/javascript">
var prodid=0, opstatus='';
$(function () {
BindGridview();
});
function BindGridview() {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "jQueryGridviewCrudOperations.aspx/BindGridview",
data: "{}",
dataType: "json",
success: function (data) {
var result = data.d;
for (var i = 0; i < result.length; i++) {
$("#gvDetails").append('<tr><td>' + result[i].productid + '</td><td>' + result[i].productname + '</td><td>' + result[i].price + '</td><td><img src=edit.jpg width=20px height=20px onclick=updatedata(' + result[i].productid + ', "' + result[i].productname + '","' + result[i].price + '") > <img src=delete.png onclick=deleterecords(' + result[i].productid + ')> </td></tr>');
}
},
error: function (data) {
var r = data.responseText;
var errorMessage = r.Message;
alert(errorMessage);
}
});
}
function deleterecords(productid) {
insertupdatedata(productid, '', '', 'DELETE')
}
function insertupdatedata(productid, productname, price, status) {
if (prodid != 0 && opstatus == 'UPDATE')
productid = prodid;
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "jQueryGridviewCrudOperations.aspx/crudoperations",
data: "{'productid':'" + productid + "','productname':'" + productname + "','price':'" + price + "','status':'" + status + "'}",
dataType: "json",
success: function (data) {
if (data.d == 'true')
window.location.reload();
},
error: function (data) {
var r = data.responseText;
var errorMessage = r.Message;
alert(errorMessage);
}
});
}
function updatedata(productid, productname, price) {
prodid = productid;
$('#txtProduct').val(productname);
$('#txtPrice').val(price);
opstatus = 'UPDATE';
}
</script>
<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">
<table>
<tr><td>Product Name:</td><td><input type="text" id="txtProduct" /></td></tr>
<tr><td>Price:</td><td><input type="text" id="txtPrice" /></td></tr>
<tr><td></td><td><input type="button" id="btnInsert" value="Insert" onclick="insertupdatedata('0',$('#txtProduct').val(),$('#txtPrice').val(),'INSERT')" /></td></tr>
</table><br />
<div class="GridviewDiv">
<asp:GridView runat="server" ID="gvDetails">
<HeaderStyle CssClass="headerstyle" />
</asp:GridView>
</div>
</form>
</body>
</html>

After completion of aspx page add following namespaces in codebehind

C# Code


using System;
using System.Collections.Generic;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;

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)
{
BindColumnToGridview();
}
}
private void BindColumnToGridview()
{
DataTable dt = new DataTable();
dt.Columns.Add("ProductId");
dt.Columns.Add("ProductName");
dt.Columns.Add("Price");
dt.Columns.Add("Edit");
dt.Rows.Add();
gvDetails.DataSource = dt;
gvDetails.DataBind();
gvDetails.Rows[0].Visible = false;
}
[WebMethod]
public static ProductDetails[] BindGridview()
{
DataTable dt = new DataTable();
List<ProductDetails> details = new List<ProductDetails>();
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(dt);
con.Close();
foreach (DataRow dtrow in dt.Rows)
{
ProductDetails product = new ProductDetails();
product.productid = dtrow["productid"].ToString();
product.productname = dtrow["productname"].ToString();
product.price = dtrow["price"].ToString();
details.Add(product);
}
}
return details.ToArray();
}
public class ProductDetails
{
public string productid { get; set; }
public string productname { get; set; }
public string price { get; set; }
}
[WebMethod]
public static string crudoperations(string status, string productname, string price, int productid)
{
string msg = "false";
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();
msg = "true";
}
return msg;
}

VB.NET Code


Imports System.Collections.Generic
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.Services
Imports System.Data
Imports System.Data.SqlClient

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
BindColumnToGridview()
End If
End Sub
Private Sub BindColumnToGridview()
Dim dt As New DataTable()
dt.Columns.Add("ProductId")
dt.Columns.Add("ProductName")
dt.Columns.Add("Price")
dt.Columns.Add("Edit")
dt.Rows.Add()
gvDetails.DataSource = dt
gvDetails.DataBind()
gvDetails.Rows(0).Visible = False
End Sub
<WebMethod()> _
Public Shared Function BindGridview() As ProductDetails()
Dim dt As New DataTable()
Dim details As New List(Of ProductDetails)()
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(dt)
con.Close()
For Each dtrow As DataRow In dt.Rows
Dim product As New ProductDetails()
product.productid = dtrow("productid").ToString()
product.productname = dtrow("productname").ToString()
product.price = dtrow("price").ToString()
details.Add(product)
Next
End Using
Return details.ToArray()
End Function
Public Class ProductDetails
Public Property productid() As String
Get
Return m_productid
End Get
Set(ByVal value As String)
m_productid = Value
End Set
End Property
Private m_productid As String
Public Property productname() As String
Get
Return m_productname
End Get
Set(ByVal value As String)
m_productname = Value
End Set
End Property
Private m_productname As String
Public Property price() As String
Get
Return m_price
End Get
Set(ByVal value As String)
m_price = Value
End Set
End Property
Private m_price As String
End Class
<WebMethod()> _
Public Shared Function crudoperations(ByVal status As String, ByVal productname As String, ByVal price As String, ByVal productid As Integer) As String
Dim msg As String = "false"
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()
msg = "true"
End Using
Return msg
End Function
End Class

Demo

jQuery Ajax Gridview Crud Operations (Insert, Edit, Update, Delete) in Asp.net using C#, VB.NET



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

10 comments :

Anonymous said...
This comment has been removed by the author.
Anonymous said...

Hi!
Sir I read all your post kindly update is it using JSON too for data fetching.

Unknown said...

source code please

Raj said...

I get 'undifined' alert when i click on insert or delte
can you tell me sir why its comming

Swastik said...

Delete is not working.
Rest is done.
It will show an error of undefined when it goes to .js file.
What to do..??

Avi13 said...

I am getting undefined alert as I am returning simple string from web method

Er Mayur Patel said...

Edit Is Not Working Rest Is Fine...

Er Mayur Patel said...

I HAVE COPY PASTED Whole Same CODE And I Facing This

<img src=Images/edit.png width=20px height=20px onclick=updatedata(' + result[i].productid + ', "' + result[i].productname + '","' + result[i].price + '")

When I Pass Only ' + result[i].productid + ' It Invokes Updatedata()

But When I Pass Multiple Parameters Like This
(' + result[i].productid + ', "' + result[i].productname + '","' + result[i].price + '")

It Is Not Invoking Updatedata.I Think There Is Problem In Passing Multiple Parameters syntax Please Help Me..

Er Mayur Patel said...

I Resolved It And This Is The Solution...

1).Passing String Parameter...{
'in (img src=Images/edit.png width=20px height=20px onclick=updatedata(' + result[i].productid + ',\'' + result[i].productname + '\',' + result[i].price + ') (img src=Images/delete.png width=20px height=20px onclick=deleterecords(' + result[i].productid + ')(/td /tr');
}

2).For Update Bit Modified Code...
In InsertUpdatData(productid, productname, price, status)

if (prodid != 0 && opstatus == 'UPDATE') {
productid = prodid;
status = opstatus;
}

sat said...

bro !! its done but i could't find where the error was occured!!

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.