Here we
will learn how to do crud operations select,insert, edit, update and delete in
grid view using AJAX stored procedure
in asp.net
, in asp.net gridview curd operation happen without postback using updatepanel
.
Here we
will learn curd operation in gridview using stored procedure in asp.net .
Before
implement this application first we will design the database table name is
'CollageInfo' and then stored Procedure name is CurdOperationInGridView.
Create Database
Table and Stored procedure like following if you needed
CREATE TABLE
CollageInfo
(
Collageid
int identity PRIMARY KEY ,
Collagename
nvarchar(50),
CollageRank
double
)
GO
CREATE PROCEDURE
CrudOperationsInGridView
@Collageid
int = 0,
@Collagename
varchar(50)=null,
@CollageRank
int=0,
@status
varchar(50)
AS
BEGIN
SET NOCOUNT ON;
--- Insert New Records For GridView
IF @status='INSERT'
BEGIN
INSERT INTO
CollageInfo(Collagename,CollageRank) VALUES(@Collagename,@CollageRank)
END
--- Select Records in Table For GridView
IF @status='SELECT'
BEGIN
SELECT Collageid,Collagename,CollageRank FROM
CollageInfo
END
--- Update Records in Table
For GridView
IF @status='UPDATE'
BEGIN
UPDATE CollageInfo SET
@Collagename=@Collagename,CollageRank=@CollageRank
WHERE Collageid=@Collageid
END
--- Delete Records from Table For GridView
IF @status='DELETE'
BEGIN
DELETE FROM
CollageInfo where Collageid=@Collageid
END
SET NOCOUNT OFF
END
|
Once we
Create stored procedure in database then add an aspx page and write the
following code on .aspx Page.
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>GridView Crud Operations using Stored Procedure in
ASP.Net</title>
<style type="text/css">
.GridviewDesign {
font-size: 100%;
font-family: 'Trebuchet MS', 'Lucida Sans Unicode', 'Lucida Grande', 'Lucida Sans', Arial, sans-serif;
color: #303933;
}
.headerstyleForGrid {
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">
<asp:ScriptManager ID="scriptmanager1" runat="server"></asp:ScriptManager>
<div class="GridviewDesign">
<asp:UpdatePanel ID="panel1" runat="server">
<ContentTemplate>
<asp:GridView runat="server" ID="gvDetailsForCurdOperation" ShowFooter="true" AllowPaging="true" PageSize="10" AutoGenerateColumns="false" DataKeyNames="Collageid,Collagename" OnPageIndexChanging="gvDetailsForCurdOperation_PageIndexChanging" OnRowCancelingEdit="gvDetailsForCurdOperation_RowCancelingEdit"
OnRowEditing="gvDetailsForCurdOperation_RowEditing" OnRowUpdating="gvDetailsForCurdOperation_RowUpdating" OnRowDeleting="gvDetailsForCurdOperation_RowDeleting" OnRowCommand="gvDetailsForCurdOperation_RowCommand">
<HeaderStyle CssClass="headerstyleForGrid" />
<Columns>
<asp:BoundField DataField="Collageid" HeaderText="Collage Id" ReadOnly="true" />
<asp:TemplateField HeaderText="Collage
Name">
<ItemTemplate>
<asp:Label ID="lblCollagename" runat="server" Text='<%# Eval("Collagename")%>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCollagename" runat="server" Text='<%# Eval("Collagename")%>' />
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtpname" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Collage
Rank">
<ItemTemplate>
<asp:Label ID="lblCollageRank" runat="server" Text='<%# Eval("CollageRank")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCollageRank" runat="server" Text='<%# Eval("CollageRank")%>' />
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCollageRank" runat="server" />
<asp:Button ID="btnAddNewItem" 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>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="gvDetailsForCurdOperation" />
</Triggers>
</asp:UpdatePanel>
</div>
</form>
</body>
</html>
|
After
completion code for .aspx page write the following code on .aspx.cs page
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=Munesh;Integrated Security=true;Initial
Catalog=GridCurdOperation"))
{
con.Open();
SqlCommand cmd = new SqlCommand("CrudOperationsInGridView", 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)
{
gvDetailsForCurdOperation.DataSource = ds;
gvDetailsForCurdOperation.DataBind();
}
else
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetailsForCurdOperation.DataSource = ds;
gvDetailsForCurdOperation.DataBind();
int columncount =
gvDetailsForCurdOperation.Rows[0].Cells.Count;
gvDetailsForCurdOperation.Rows[0].Cells.Clear();
gvDetailsForCurdOperation.Rows[0].Cells.Add(new TableCell());
gvDetailsForCurdOperation.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetailsForCurdOperation.Rows[0].Cells[0].Text
= "No Records
Found";
}
}
}
protected void
gvDetailsForCurdOperation_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtname = (TextBox)gvDetailsForCurdOperation.FooterRow.FindControl("txtpname");
TextBox txtprice = (TextBox)gvDetailsForCurdOperation.FooterRow.FindControl("txtprice");
crudoperationsForGrid("INSERT", txtname.Text,
txtprice.Text, 0);
}
}
protected void
gvDetailsForCurdOperation_RowEditing(object sender, GridViewEditEventArgs e)
{
gvDetailsForCurdOperation.EditIndex = e.NewEditIndex;
BindGridview();
}
protected void
gvDetailsForCurdOperation_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvDetailsForCurdOperation.EditIndex = -1;
BindGridview();
}
protected void
gvDetailsForCurdOperation_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvDetailsForCurdOperation.PageIndex = e.NewPageIndex;
BindGridview();
}
protected void gvDetailsForCurdOperation_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int Collageid = Convert.ToInt32(gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collageid"].ToString());
TextBox txtname = (TextBox)gvDetailsForCurdOperation.Rows[e.RowIndex].FindControl("txtCollagename");
TextBox txtCollageRank = (TextBox)gvDetailsForCurdOperation.Rows[e.RowIndex].FindControl("txtCollageRank");
crudoperationsForGrid("UPDATE", txtname.Text,
txtCollageRank.Text, Collageid);
}
protected void
gvDetailsForCurdOperation_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int Collageid = Convert.ToInt32(gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collageid"].ToString());
string Collagename = gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collagename"].ToString();
crudoperationsForGrid("DELETE", Collagename, "", Collageid);
}
protected void crudoperationsForGrid(string status, string Collagename, string price, int Collageid)
{
using (SqlConnection con = new SqlConnection("Data
Source=Munesh;Integrated Security=true;Initial
Catalog=GridCurdOperation"))
{
con.Open();
SqlCommand cmd = new SqlCommand("CrudOperationsInGridView", con);
cmd.CommandType = CommandType.StoredProcedure;
if (status == "INSERT")
{
cmd.Parameters.AddWithValue("@status", status);
cmd.Parameters.AddWithValue("@Collagename", Collagename);
cmd.Parameters.AddWithValue("@CollageRank", price);
}
else if (status == "UPDATE")
{
cmd.Parameters.AddWithValue("@status", status);
cmd.Parameters.AddWithValue("@Collagename", Collagename);
cmd.Parameters.AddWithValue("@CollageRank", price);
cmd.Parameters.AddWithValue("@Collageid", Collageid);
}
else if (status == "DELETE")
{
cmd.Parameters.AddWithValue("@status", status);
cmd.Parameters.AddWithValue("@Collageid", Collageid);
}
cmd.ExecuteNonQuery();
lblresult.ForeColor = Color.Green;
lblresult.Text = Collagename
+ " details
" +
status.ToLower() + "d successfully";
gvDetailsForCurdOperation.EditIndex = -1;
BindGridview();
}
}
|
Now run your application and see the output and
perform Curd operation For Gridview.
you can download code semple from here Download
EmoticonEmoticon