HI WELCOME TO KANSIRIS

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

Leave a Comment
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


Create Single Stored Procedure for Crud (Insert, Select, Update, Delete) Operations in SQL Server
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;
}

0 comments:

Post a Comment

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