Thursday, 6 February 2014

Working With gridview

How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure




 

Introduction


In general, gridview control is used to populate the data in tabular format from different datasources and basically it derived from webcontrol class. Actually gridview control is built with lot of features and this control is commonly used for all asp.net applications. And here I’m explaining some basic functions of this gridview control such as insert edit and delete a record.

1 .Data source


First, we need to prepare the database table and stored procedure for gridview operations such as insert, update & delete a records, here I’m using sql server 2008 for all database activities, for this sample I have created the customer table and stored procedure for all mentioned gridview operations. Create the customer table in your own test database using below table script and insert some records initially to load gridview with data.
/****** Object: Table [dbo].[tblCustomer] Script Date: 06/25/2013 00:44:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCustomer](
[CustID] [int] IDENTITY(1,1) NOT NULL,
[CustName] [varchar](100) NOT NULL,
[Custaddress1] [varchar](100) NOT NULL,
[Custaddress2] [varchar](100) NOT NULL,
[CustPhone] [varchar](100) NOT NULL,
[CustEmail] [varchar](100) NOT NULL,
[Createdby] [varchar](100) NOT NULL,
[CreatedDt] [datetime] NOT NULL,
[Updatedby] [varchar](100) NULL,
[UpdatedDt] [datetime] NULL,
[Active] [bit] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Insert script [dbo].[tblCustomer]    Script Date: 06/21/2013 14:48:36 ******/

INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])
VALUES('Muthu','Delhi','Maujpur, India','2345678912','muthu99@yahoo.com','admin','Jun 21 2013  2:44:06:420PM',NULL,NULL,1);
INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])
VALUES('Ramesh','Vijayapura','bangalore, India','245352454','rrramesh@gmil.com','admin','Jun 21 2013  2:46:12:750PM',NULL,NULL,1);
INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])
VALUES('Sridhar','Ashok nagar','Chennai, India','45656423422','rrramesh@gmil.com','admin','Jun 21 2013  2:46:49:807PM',NULL,NULL,1);
INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])
VALUES('Kumar','Thrissur','Kochi, India','23456432','rkkum@lmil.com','admin','Jun 21 2013  2:47:56:803PM',NULL,NULL,1);

 

Once you execute the above table and insert scripts, you will get the below records in the table initially to load data into gridview control.
Execute the below SP_Customer stored procedure in sql server database which is used to handle insert, update, select and delete a record into customer table. Here we are not deleting a record permanently but using active flag column, make it inactive the record while doing the delete operation then make it active and get it whenever you want. This is the easy way to handle all the gridview operations in data base side instead of using sql query from front end side. Also here we used @@ERROR for error handling in the stored procedure and returns the number to front end and confirm that the performed action is successful or not.
/****** Object: StoredProcedure [dbo].[SP_Customer] Script Date: 09/18/2013 21:56:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SP_Customer]
(
@pvchCustId int =0,
@pvchCustName varchar(100)=null,
@pvchustaddress1 varchar(100)=null,
@pvchustaddress2 varchar(100)=null,
@pvchCustPhone varchar(100)=null,
@pvchCustEmail varchar(100)=null,
@pvchCreatedBy varchar(100)=null,
@pvchAction varchar(50)=null,
@pIntErrDescOut int output
)
AS
BEGIN
if(@pvchAction='select')
begin
SELECT CUSTID,CUSTNAME,CUSTNAME,CUSTADDRESS1,CUSTADDRESS2,CUSTPHONE,
CUSTEMAIL,ACTIVE FROM tblCustomer WHERE Active=1;
end
else if(@pvchAction='insert')
begin
if not exists(select * from tblCustomer where CustName=@pvchCustName or CustEmail=@pvchCustEmail)
begin
INSERT INTO tblCustomer(CustName,Custaddress1,Custaddress2,CustPhone,
CustEmail,Createdby,CreatedDt,Active)VALUES(@pvchCustName,@pvchustaddress1,@pvchustaddress2,@pvchCustPhone,
@pvchCustEmail,@pvchCreatedBy,GETDATE(),1);
end
else
begin
set @pIntErrDescOut=2
end
end
else if(@pvchAction='update')
begin
UPDATE tblCustomer SET CustName=@pvchCustName,Custaddress1=@pvchustaddress1,Custaddress2=@pvchustaddress2,
CustPhone=@pvchCustPhone,CustEmail=@pvchCustEmail,Updatedby=@pvchCreatedBy,UpdatedDt=GETDATE()
WHERE CustID=@pvchCustId;
end
else if(@pvchAction='delete')
begin
UPDATE tblCustomer SET Active=0,Updatedby=@pvchCreatedBy,UpdatedDt=GETDATE()
WHERE CustID=@pvchCustId;
end
IF (@@ERROR <> 0)
BEGIN
SET @pIntErrDescOut = 1
END
END

 

2. Create ASP.NET Web application


Now we have prepared the all database related activities for this and moving to front end side to setup aspx page and codebehind file to handle gridview operations, so first create the asp.net web application and add GridviewControl.aspx page as shown below.

3. Connection string (web.config)

Now we have created the asp.net application and open the web.config file to add connection string as shown below. Connection string must be based on your database details.
<connectionStrings>
<add name="ConnectionString"
connectionString="Data Source=...........your connection string........."
providerName="System.Data.SqlClient"/>
</connectionStrings>

 

4. Design Gridview

Open the GridviewControl.aspx page to design the gridview. Drag the gridview control from tool box and placed into the page and make it Autogeneratecolumns as false, so manually we will create the gridview columns to display.  Copy the below code and paste into aspx page as shown below. In the below gridview I used templatefields to display data and perform gridview operations.
1. Templatefield:  Actually we use this templatefield to display any asp.net control such as check box, label, drop down etc in the gridview control to provide additional functionality and this templatefield supports many templates, here we are using Itemtemplate, EdititemTemplate and FooterTemplate.
2. ItemTemplate : This template used to display the content every row rendered by the gridview control. Here we using label in this template and displaying the data.
3. EditItemTemplate: When we select or edit a specific row, the contents of this templates are displayed in the gridview. Mainly used when we edit a particular row in the gridview control. Here we use textbox control to display the data, while editing a row in the gridview.
4. FooterTemplete: We use this template when wants to display the contents in the footer. Here we included textbox control in this templete to add new record in the footer area.
Also applied the required field validator wherever required and added JavaScript confirm message while performing the delete functionality. Delete confirmation message will be displayed when we click on the delete link on the girdview. So that we can get confirmation from user before delete a record (making in active) from table.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridviewControl.aspx.cs"
Inherits="GridviewSample.GridviewControl" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Demo Editable gridview</title>
</head>
<body>
<form id="form1" runat="server">
<div style="font-size:20px;font-family:Verdana">
Sample - Editable Gridview Control
<br />
</div>
<div>
<asp:GridView ID="GridViewSample" runat="server" AutoGenerateColumns="False" Font-Names="Verdana"
AllowPaging="true" ShowFooter="true" PageSize="5" Width="75%" OnPageIndexChanging="GridViewSample_PageIndexChanging"
OnRowCancelingEdit="GridViewSample_RowCancelingEdit" OnRowEditing="GridViewSample_RowEditing"
OnRowUpdating="GridViewSample_RowUpdating"
OnRowDeleting="GridViewSample_RowDeleting" OnRowCommand="GridViewSample_RowCommand"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px">
<AlternatingRowStyle BackColor="#FFD4BA" />
<FooterStyle BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
<PagerStyle BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
<HeaderStyle Height="30px" BackColor="#FF9E66" Font-Size="15px" BorderColor="#CCCCCC"
BorderStyle="Solid" BorderWidth="1px" />
<RowStyle Height="20px" Font-Size="13px" BorderColor="#CCCCCC" BorderStyle="Solid"
BorderWidth="1px" />
<Columns>
<asp:TemplateField HeaderText="CustID" Visible="false">
<ItemTemplate>
<asp:Label ID="lblCustID" runat="server" Text='<%#Eval("CustID") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lbladd" runat="server"></asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Customer Name" HeaderStyle-Width="10%">
<ItemTemplate>
<asp:Label ID="lblCustName" runat="server" Text='<%#Eval("CustName") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCustName" runat="server" Text='<%#Eval("CustName") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddCustName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="reqName" ValidationGroup="ValgrpCust" ControlToValidate="txtAddCustName" runat="server" ErrorMessage="*"></asp:RequiredFieldValidator>
</FooterTemplate>
<HeaderStyle Width="15%"></HeaderStyle>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address 1" HeaderStyle-Width="15%">
<ItemTemplate>
<asp:Label ID="lblAddress1" runat="server" Text='<%#Eval("Custaddress1") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCustAddres1" runat="server" Text='<%#Eval("Custaddress1") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddCustAdres1" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="reqAdd1" ValidationGroup="ValgrpCust" ControlToValidate="txtAddCustAdres1" runat="server" ErrorMessage="*"></asp:RequiredFieldValidator>
</FooterTemplate>
<HeaderStyle Width="15%"></HeaderStyle>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address 2" HeaderStyle-Width="15%">
<ItemTemplate>
<asp:Label ID="lblAddress2" runat="server" Text='<%#Eval("Custaddress2") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCustAddres2" runat="server" Text='<%#Eval("Custaddress2") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddCustAdres2" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="reqAdd2" ValidationGroup="ValgrpCust" ControlToValidate="txtAddCustAdres2" runat="server" ErrorMessage="*"></asp:RequiredFieldValidator>
</FooterTemplate>
<HeaderStyle Width="15%"></HeaderStyle>
</asp:TemplateField>
<asp:TemplateField HeaderText="Phone" HeaderStyle-Width="10%">
<ItemTemplate>
<asp:Label ID="lblPhone" runat="server" Text='<%#Eval("CustPhone") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCustPhone" runat="server" Text='<%#Eval("CustPhone") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddCustPhone" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="reqPhone" ValidationGroup="ValgrpCust" ControlToValidate="txtAddCustPhone" runat="server" ErrorMessage="*"></asp:RequiredFieldValidator>
</FooterTemplate>
<HeaderStyle Width="10%"></HeaderStyle>
</asp:TemplateField>
<asp:TemplateField HeaderText="Email" HeaderStyle-Width="15%">
<ItemTemplate>
<asp:Label ID="lblEmail" runat="server" Text='<%#Eval("CustEmail") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEmail" runat="server" Text='<%#Eval("CustEmail") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddEmail" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="reqEmail" ValidationGroup="ValgrpCust" ControlToValidate="txtAddEmail" runat="server" ErrorMessage="*"></asp:RequiredFieldValidator>
</FooterTemplate>
<HeaderStyle Width="15%"></HeaderStyle>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit/Delete" HeaderStyle-Width="15%">
<ItemTemplate>
<asp:LinkButton ID="btnEdit" Text="Edit" runat="server" CommandName="Edit" />
<span onclick="return confirm('Are you sure want to delete?')">
<asp:LinkButton ID="btnDelete" Text="Delete" runat="server" CommandName="Delete" />
</span>
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID="btnUpdate" Text="Update" runat="server" CommandName="Update" />
<asp:LinkButton ID="btnCancel" Text="Cancel" runat="server" CommandName="Cancel" />
</EditItemTemplate>
<FooterTemplate>
<asp:Button ID="btnInsertRecord" runat="server" Text="Add" ValidationGroup="ValgrpCust" CommandName="Insert" />
</FooterTemplate>
<HeaderStyle Width="15%"></HeaderStyle>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
<div><br />
<asp:Label ID="lblMessage" ForeColor="Green" Font-Bold="true" runat="server" Text=""></asp:Label>
</div>
</form>
</body>
</html>

 

5. Code behind part-Namespace details

 Add below namespaces in code behind file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using System.Configuration;

 

6. Page load

Display the all records in page load events using below LoadData() function, in this function we are using sql server stored procedure “SP_Customer “ to get the records from database and this function LoadData() will needs to be called after every user actions on gridview operations such as add, update and delete.
string strCon = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlDataAdapter SqlAda;
DataSet ds;

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadData();
}
}
private void LoadData()
{
using (SqlConnection Sqlcon = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
Sqlcon.Open();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_Customer";
cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
cmd.Parameters["@pvchAction"].Value = "select";
cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
SqlAda = new SqlDataAdapter(cmd);
ds = new DataSet();
SqlAda.Fill(ds);
GridViewSample.DataSource = ds;
GridViewSample.DataBind();
}
}
}
You can run the project with above code and page will display the gridview with loaded data from database as shown below

7. Add record

I used Rowcommand event to insert the records into database. Actually rowcommand is raised when a button or link button is clicked on the gridview control. The commandName property to decide which action needs to be performed. Get the footer row textbox values by using findcontrol method and insert into database using stored procedure as like below. After inserting the rows, load the data again from database by calling LoadData() function to refresh the gridview.
protected void GridViewSample_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("Insert"))
{
int retVal=0;
TextBox Name = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustName");
TextBox Address1 = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustAdres1");
TextBox Address2 = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustAdres2");
TextBox Phone = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustPhone");
TextBox Email = (TextBox)GridViewSample.FooterRow.FindControl("txtAddEmail");
using (SqlConnection Sqlcon = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
Sqlcon.Open();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_Customer";
cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@pvchCustName", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchustaddress1", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchustaddress2", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchCustPhone", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchCustEmail", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchCreatedBy", SqlDbType.VarChar, 100));
cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters["@pvchAction"].Value = "insert";
cmd.Parameters["@pvchCustName"].Value = Name.Text.Trim();
cmd.Parameters["@pvchustaddress1"].Value = Address1.Text.Trim();
cmd.Parameters["@pvchustaddress2"].Value = Address2.Text.Trim();
cmd.Parameters["@pvchCustPhone"].Value = Phone.Text.Trim();
cmd.Parameters["@pvchCustEmail"].Value = Email.Text.Trim();
cmd.Parameters["@pvchCreatedBy"].Value = "Admin";
cmd.ExecuteNonQuery();
retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;
}
}
if (retVal == 2)
{
lblMessage.Text = "Customer name or Email ID aready exist!";
}
else
{
GridViewSample.EditIndex = -1;
LoadData();
lblMessage.Text = "Record inserted successfully!";
}
}
}

 

8. Update Record

Here I’m using gridview rowediting and rowupdating events to edit and update the specific row in the gridview. Rowediting event will occur when specific row’s edit button is clicked and enable the control to edit the data. Get the updated values in gridview rowupdating event, actually this will occur when row’s update button is clicked. Place the below code in rowediting and rowupdating event as shown below.
protected void GridViewSample_RowEditing(object sender, GridViewEditEventArgs e)
{
GridViewSample.EditIndex = e.NewEditIndex;
LoadData();
}
protected void GridViewSample_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
Label Custid = (Label)GridViewSample.Rows[e.RowIndex].FindControl("lblCustID");
TextBox Name = (TextBox)GridViewSample.Rows[e.RowIndex].FindControl("txtCustName");
TextBox Address1 = (TextBox)GridViewSample.Rows[e.RowIndex].FindControl("txtCustAddres1");
TextBox Address2 = (TextBox)GridViewSample.Rows[e.RowIndex].FindControl("txtCustAddres2");
TextBox Phone = (TextBox)GridViewSample.Rows[e.RowIndex].FindControl("txtCustPhone");
TextBox Email = (TextBox)GridViewSample.Rows[e.RowIndex].FindControl("txtEmail");
using (SqlConnection Sqlcon = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
Sqlcon.Open();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_Customer";
cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@pvchCustId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@pvchCustName", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchustaddress1", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchustaddress2", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchCustPhone", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchCustEmail", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchCreatedBy", SqlDbType.VarChar, 100));
cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters["@pvchAction"].Value = "update";
cmd.Parameters["@pvchCustId"].Value = Convert.ToInt32(Custid.Text.ToString());
cmd.Parameters["@pvchCustName"].Value = Name.Text.Trim();
cmd.Parameters["@pvchustaddress1"].Value = Address1.Text.Trim();
cmd.Parameters["@pvchustaddress2"].Value = Address2.Text.Trim();
cmd.Parameters["@pvchCustPhone"].Value = Phone.Text.Trim();
cmd.Parameters["@pvchCustEmail"].Value = Email.Text.Trim();
cmd.Parameters["@pvchCreatedBy"].Value = "Admin";
cmd.ExecuteNonQuery();
int retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;
}
}
GridViewSample.EditIndex = -1;
LoadData();
lblMessage.Text = "Record updated successfully!";
}

 

 

9. Cancel Editing

if you want to cancel the edit while gridview in edit mode then use the below RowCancelEdit event to cancel the edit. This event occurs when cancel button of row in edit mode is clicked.
protected void GridViewSample_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridViewSample.EditIndex = -1;
LoadData();
}

 

10. Gridview Pagination

Pageindexchanging event is used to handle paging in gridview control. Set allowpaging property to true and also set the pagesize property to display the number of records in each page and this Pageindexchanging event occures when any paging link/button is clicked. Place the below code in Pageindexchanging as shown below.
protected void GridViewSample_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridViewSample.PageIndex = e.NewPageIndex;
LoadData();
}

 

11. Delete Record

Below code shows how to delete a record in gridview using  RowDeleting, this event will occurs when a row’s delete button is clicked. Get the custid value which is invisible column in the gridview and passing as a parameter for sql stored procudere and make it inactive. Also displayed the delete confirmation javascript message to the user before doing this opreation.
protected void GridViewSample_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Label CustID = (Label)GridViewSample.Rows[e.RowIndex].FindControl("lblCustID");
using (SqlConnection Sqlcon = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
Sqlcon.Open();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_Customer";
cmd.Parameters.Add(new SqlParameter("@pvchCustId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@pvchCreatedBy", SqlDbType.VarChar, 50));
cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters["@pvchAction"].Value = "delete";
cmd.Parameters["@pvchCustId"].Value = Convert.ToInt32(CustID.Text.ToString());
cmd.Parameters["@pvchCreatedBy"].Value = "Admin";
cmd.ExecuteNonQuery();
int retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;
}
}
GridViewSample.EditIndex = -1;
LoadData();
lblMessage.Text = "Record deleted successfully!";
}

 

See the below screenshot for displaying the delete confirmation before delete a record.

12. Attachments

I have attached sample project here, download and you can test it from your side. There is two file in the attachment and follow the below steps to see the above sample.
1. SQL script – First execute this script in your test database
2. Sample project - Open the project and change the connection string in web.config
3. Run the project.
I hope this article will help you to understand the basic functionalities of gridview control and you can download the attached sample project and see how it works.
Thanks for reading this article and please provide your feedback and suggestions.

 
.Parameters["@pvchCustName"].Value = Name.Text.Trim();
cmd.Parameters["@pvchustaddress1"].Value = Address1.Text.Trim();
cmd.Parameters["@pvchustaddress2"].Value = Address2.Text.Trim();
cmd.Parameters["@pvchCustPhone"].Value = Phone.Text.Trim();
cmd.Parameters["@pvchCustEmail"].Value = Email.Text.Trim();
cmd.Parameters["@pvchCreatedBy"].Value = "Admin";
cmd.ExecuteNonQuery();
int retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;
}
}
GridViewSample.EditIndex = -1;
LoadData();
lblMessage.Text = "Record updated successfully!";
}

 

 

9. Cancel Editing

if you want to cancel the edit while gridview in edit mode then use the below RowCancelEdit event to cancel the edit. This event occurs when cancel button of row in edit mode is clicked.
protected void GridViewSample_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridViewSample.EditIndex = -1;
LoadData();
}

 

10. Gridview Pagination

Pageindexchanging event is used to handle paging in gridview control. Set allowpaging property to true and also set the pagesize property to display the number of records in each page and this Pageindexchanging event occures when any paging link/button is clicked. Place the below code in Pageindexchanging as shown below.
protected void GridViewSample_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridViewSample.PageIndex = e.NewPageIndex;
LoadData();
}

 

11. Delete Record

Below code shows how to delete a record in gridview using  RowDeleting, this event will occurs when a row’s delete button is clicked. Get the custid value which is invisible column in the gridview and passing as a parameter for sql stored procudere and make it inactive. Also displayed the delete confirmation javascript message to the user before doing this opreation.
protected void GridViewSample_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Label CustID = (Label)GridViewSample.Rows[e.RowIndex].FindControl("lblCustID");
using (SqlConnection Sqlcon = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
Sqlcon.Open();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_Customer";
cmd.Parameters.Add(new SqlParameter("@pvchCustId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@pvchCreatedBy", SqlDbType.VarChar, 50));
cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters["@pvchAction"].Value = "delete";
cmd.Parameters["@pvchCustId"].Value = Convert.ToInt32(CustID.Text.ToString());
cmd.Parameters["@pvchCreatedBy"].Value = "Admin";
cmd.ExecuteNonQuery();
int retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;
}
}
GridViewSample.EditIndex = -1;
LoadData();
lblMessage.Text = "Record deleted successfully!";
}

 

See the below screenshot for displaying the delete confirmation before delete a record.

12. Attachments

I have attached sample project here, download and you can test it from your side. There is two file in the attachment and follow the below steps to see the above sample.
1. SQL script – First execute this script in your test database
2. Sample project - Open the project and change the connection string in web.config
3. Run the project.
I hope this article will help you to understand the basic functionalities of gridview control and you can download the attached sample project and see how it works.
Thanks for reading this article and please provide your feedback and suggestions.

 

No comments:

Post a Comment