In this post i will explain you GridView CRUD: Select Insert Edit Update Delete using Single Stored procedure with Alert Confirmations on Delete from GridView.
I have a gridview I want perform insert ,update ,delete,edit operation along with that i also want to delete the record from gridview by click on delete button with a alert message “Are you sure want to delete?”
To achieve all these functionalities i have used gridview event.
Create a table in sql sever.
CREATE TABLE [dbo].[tbluser](
[Userid] [int] IDENTITY(1,1) NOT NULL,
[Username] [varchar](100) NULL,
[password] [varchar](100) NULL,
[Address] [varchar](100) NULL
)
I have a created storeprocedure for performing insert ,update,delete,edit. I have declared a @status parameter and accordingly it we will perform database operation.
As we can see in the below query I have declared nullabe parameter in the store procedure .ie optional parameter. If don’t about the store procedure please read this post first-what is Stored Procedure?
create PROCEDURE Crudoperation
@userid int=null,
@username varchar(100)=null,
@password varchar(100)=null,
@address varchar(100)=null,
@status varchar(50)=null
as
begin
if(@status='select')
begin
select * from tbluser
end
if(@status='update')
begin
update tbluser set password=@password,Address=@address where UserId=@userid
end
if(@status='delete')
begin
delete from tbluser where UserId=@userid
end
if(@status='insert')
begin
insert into tbluser(UserName,password,Address) values(@username,@password,@address)
end
end1.Now open visual studio and add empty website.
2.Now add a gridview to the page
3.Right click on gridview and then click on gridview property ,double click on the following event of gridview.
3.Right click on gridview and then click on gridview property ,double click on the following event of gridview.
i have explain some gridview view event which are very useful.
1) Onrowcancelingedit
The RowCancelingEdit event is occur when the Cancel button of a row in edit mode is clicked2) Onrowediting
The RowEditing event is fire when a row’s Edit button is clicked, but before the GridView control enters edit mode.
3) Onrowupdating
The RowUpdating event is fire when a row’s Update button is clicked, but before the GridView control updates the row
5) Onrowdeleting
The RowDeleting event is fire when a row’s Delete button is clicked, but before the GridView control deletes the row.
6)OnRowCommand
The RowCommand event is fire when a button is clicked in the GridView control.
The RowCancelingEdit event is occur when the Cancel button of a row in edit mode is clicked2) Onrowediting
The RowEditing event is fire when a row’s Edit button is clicked, but before the GridView control enters edit mode.
3) Onrowupdating
The RowUpdating event is fire when a row’s Update button is clicked, but before the GridView control updates the row
5) Onrowdeleting
The RowDeleting event is fire when a row’s Delete button is clicked, but before the GridView control deletes the row.
6)OnRowCommand
The RowCommand event is fire when a button is clicked in the GridView control.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowCommand="GridView1_RowCommand" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating"> </asp:GridView> </div> </form> </body> </html>
Event are created on codebehid of page.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
}
}now paste the following code between gridview view tag.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" DataKeyNames="UserId,UserName" AutoGenerateColumns="False" Showfooter="True" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowCommand="GridView1_RowCommand" OnRowUpdating="GridView1_RowUpdating" CellPadding="4" ForeColor="#333333" GridLines="None" > <AlternatingRowStyle BackColor="White" /> <Columns> <asp:TemplateField HeaderText="Action"> <EditItemTemplate> <asp:Button ID="btnUpdate" CommandName="Update" runat="server" ToolTip="Update" Height="30px" Width="60px" Text="Update" /> <asp:Button ID="btnCancel" CommandName="Cancel" runat="server" Text="Cancel" ToolTip="Cancel" Height="30px" Width="60px" /> </EditItemTemplate> <ItemTemplate> <asp:Button ID="btnEdit" runat="server" CommandName="Edit" Text="Edit" ToolTip="Edit" Height="30px" Width="60px"/> <asp:Button ID="btnDelete" runat="server" Text="Delete" CommandName="Delete" ToolTip="Delete" Height="30px" Width="60px" OnClientClick="return confirm('Are you sure you want to delete this ?');" /> </ItemTemplate> <FooterTemplate> <asp:Button ID="btnAdd" runat="server" Text="AddNew" CommandName="AddNew" Width="120px" Height="30px" ToolTip="Add new User"/> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Username"> <EditItemTemplate> <asp:Label ID="lbleditusr" runat="server" Text='<%#Eval("Username") %>'/> </EditItemTemplate> <ItemTemplate> <asp:Label ID="lblitemUsr" runat="server" Text='<%#Eval("Username") %>'/> </ItemTemplate> <FooterTemplate> <asp:TextBox ID="txtftrusrname" runat="server"/> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="password"> <EditItemTemplate> <asp:TextBox ID="txtpassword" runat="server" Text='<%#Eval("password") %>'/> </EditItemTemplate> <ItemTemplate> <asp:Label ID="lblpassword" runat="server" Text='<%#Eval("password") %>'/> </ItemTemplate> <FooterTemplate> <asp:TextBox ID="txtftrpassword" runat="server"/> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Address"> <EditItemTemplate> <asp:TextBox ID="txtAddress" runat="server" Text='<%#Eval("Address") %>'/> </EditItemTemplate> <ItemTemplate> <asp:Label ID="lblAddress" runat="server" Text='<%#Eval("Address") %>'/> </ItemTemplate> <FooterTemplate> <asp:TextBox ID="txtftrAddress" runat="server"/> </FooterTemplate> </asp:TemplateField> </Columns> <EditRowStyle BackColor="#2461BF" /> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <RowStyle BackColor="#EFF3FB" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <SortedAscendingCellStyle BackColor="#F5F7FB" /> <SortedAscendingHeaderStyle BackColor="#6D95E1" /> <SortedDescendingCellStyle BackColor="#E9EBEF" /> <SortedDescendingHeaderStyle BackColor="#4870BE" /> </asp:GridView> </div> </form> </body> </html>
Eval function is used to bind data from database to controls inside DataBound controls like GridView, Repeater, etc.
now your gridview should look like this
Use of Multiple DataKeyNames (DataKeys) in ASP.Net GridView, How to find datakey value of gridview
DataKeyNames property allows us to set the names of the Column Fields in a gridview that
you want to use in your code but you do not want to display it for Example Primary Keys,ID,Username.
In my example i don’t want to display userid but i want to use it in code behind for deleting and updating record.
IsPostBack is a property of the Asp.Net page that checks whether the page is loaded first time or it is loaded due to button on page.
Now i have written code for operation on the event of gridview.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString());
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
userDetails(); //check page is loaded first time or it's loaded due to button on page
}
}
public void userDetails()
{
con.Open();
SqlCommand cmd = new SqlCommand("Crudoperation", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@status", "select");
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();
}
else
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow()); // if record not found then returning a blank table structure
GridView1.DataSource = ds;
GridView1.DataBind();
int columncount = GridView1.Rows[0].Cells.Count;
GridView1.Rows[0].Cells.Clear();
GridView1.Rows[0].Cells.Add(new TableCell());
GridView1.Rows[0].Cells[0].ColumnSpan = columncount;
GridView1.Rows[0].Cells[0].Text = "No Records Found";
}
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex; //this open new index that is edit mode
userDetails();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int userid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["UserId"].ToString()); //finding Datakeynames from gridview
string username = GridView1.DataKeys[e.RowIndex].Values["UserName"].ToString();
con.Open();
SqlCommand cmd = new SqlCommand("Crudoperation", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userid", userid);
cmd.Parameters.AddWithValue("@status", "delete");
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
userDetails();
Response.Write("<script language=javascript>alert('" + username + "'+'details deleted successfully');</script>");
}
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1; //after cancel button want go to one index back that's y -1
userDetails();
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtUsrname = (TextBox)GridView1.FooterRow.FindControl("txtftrusrname");
TextBox txtpwd = (TextBox)GridView1.FooterRow.FindControl("txtftrpassword"); //find textbox control on footer with crrosponding id
TextBox txtadress = (TextBox)GridView1.FooterRow.FindControl("txtftrAddress");
con.Open();
SqlCommand cmd = new SqlCommand("Crudoperation", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", txtUsrname.Text);
cmd.Parameters.AddWithValue("@password", txtpwd.Text);
cmd.Parameters.AddWithValue("@address", txtadress.Text);
cmd.Parameters.AddWithValue("@status","insert");
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
userDetails();
Response.Write("<script language=javascript>alert('" + txtUsrname.Text + "'+'Details inserted successfully');</script>");
}
else
{
Response.Write("<script language=javascript>alert('" + txtUsrname.Text + "'+' Details not inserted');</script>");
}
}
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int userid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
string username = GridView1.DataKeys[e.RowIndex].Values["UserName"].ToString();
TextBox txtpassword = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtpassword");
TextBox txtAddress = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtAddress");
con.Open();
SqlCommand cmd = new SqlCommand("Crudoperation", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@password", txtpassword.Text);
cmd.Parameters.AddWithValue("@address", txtpassword.Text);
cmd.Parameters.AddWithValue("@userid", userid);
cmd.Parameters.AddWithValue("@status", "update");
cmd.ExecuteNonQuery();
con.Close();
Response.Write("<script language=javascript>alert('" + username + "'+' Details Updated successfully!');</script>");
GridView1.EditIndex = -1;
userDetails();
}
}Now run code and perform gridview operation using storeprocedure
The post Insert, Update, Delete in Gridview using Single Stored Procedure appeared first on Software Development | Programming Tutorials.
Read More Articles
- Write a value which contain comma to a CSV file in c#?
- Reading CSV File with cells containing commas c#
- Split CSV with columns may contain ‘,’ Comma C#
- [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax
- [Simple Way]-How to get data from database using JQuery Ajax in asp net MVC
- [Simple Way]-ASP.NET Core Upload Multiple File Web API model
- [Simple Way]- Image Upload in .NET Core Web API
- [Easy Way]-Receive File and other form data together in ASP.NET Core Web API
- Replace image in word document using C#
- How to add new rows to an existing word document table in C#
- INSERT using LIST into Stored Procedure
- How to insert nested object using stored procedure in sql
- data insert using input output stored procedure
- Sql Server run update stored procedure after insert stored procedure
- Delete and update with stored procedure in ormlite (SQL Server) & C#
- Paging implementation in C# using Stored Procedure (SQL 2000)
- MySql: Will using Prepared statements to call a stored procedure be any faster with .NET/Connector?
- How to call a stored procedure that returns a list<int> using .net core entity framework
- Return id of record when insert or update or delete record
- Modify SqlDataSource.SelectCommand using Stored Procedure with parameters
- how to pass ref variable to a SQL stored Procedure using ADO.NET?
- Call stored procedure from EF asynchronously using C# inside async method
- Insert and Delete statement using TOP 1
- Call SQL Server stored procedure with parameters using C#
- Reading Huge Data Using SQL Stored Procedure and C# (SQL Server 2005)
- One stored procedure to update whole table or individual columns too
- C# sql server 2008 r2 insert stored procedure won't work
- Gridview insert delete with custom objects in viewstate
- With ADO, using SqlDataAdapter.FillSchema clears @ReturnValues and other stored procedure parameters marked for output
- Trouble using Dapper to call stored procedure from C#
- How to use single LinkButton in a Gridview to update TextBox elsewhere on aspx page
- Inserting PDF as byte array to SQL Server stored procedure using C#
- Dapper with multimapping using stored procedure
- How to Populate the Child Collections / Associations of an Entity from a Stored Procedure using NHibernate
- Display error message using stored procedure
- How to insert xml data from URL into SQL Server 2008 database by call stored procedure in C#
- Copy a stored procedure from one SQL Server 2008 to another using SQL Server Management Objects
- Execute Stored Procedure multiple times using Dapper?
- Getting individual count of insert and update using merge statement - C# and SQL Server
- SQL INSERT and SELECT into Oracle using a single command with .NET
- Autofac InstancePerHttpRequest using Ninject
- saving an image into directory c#
- Redirect to MVC view with model from $http post service
- Catch unhandled SocketException during asynchronous HttpWebResponse read
- Extension Methods on Generic Classes
- Array.Sort with Custom IComparer Interface
- Testing alexa skill returns "Error converting the Lambda event JSON payload to a string"
- How to delete certain characters of a word in c#
- Helper method for testing JsonResult in Nunit
- Remove My ASP.NET Application master page
- Passing in Tab name for CSV excel sheet
- Azure Graph with B2C AD
- Methods of programatically altering ipsec rules with C#?
- How to merge excel cells based on the cell value using c#
- C# Do While looping problems
- XAML- how to set the size of a ScrollViewer dynamically?
- external jar file include in java binding library in monodroid?
- IOT Hub - QueryDevice functions missing from RegistryManager?
- dotMemory and tracking memory leaks
- How to prevent decimal.TryParse convert "," to "."?
