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.
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
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
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;
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;
}
No comments:
Post a Comment