How to use GridView with Insert, Edit, Update, Delete the Ado.net way C#

As I have written before about how to bind data with GridView Control with database.

http://howtouseasp.net/?p=59

Now, I in this article I will use Itemtemplate and Edittemplate where we will do Edit, Update and Delete the records.

Before to do it first of all create a database.

Run the following script in your Sql Server 2005.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[tbuser]‘) AND type in (N‘U’))

BEGIN

CREATE TABLE [dbo].[tbuser](

[id] [int] IDENTITY(1,1) NOT NULL,

[uid] [varchar](50) NOT NULL,

[Name] [varchar](50) NULL,

[address] [varchar](50) NULL,

[salary] [int] NULL,

CONSTRAINT [PK_tbuser] PRIMARY KEY CLUSTERED

(

[uid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

END

Well place the GridView control on a form and set the following property.

1.AutoGenerateColumns=”False”

And set the Gridview Events name is

onpageindexchanging=”GridView1_PageIndexChanging”

onrowcancelingedit=”GridView1_RowCancelingEdit”

onrowdeleting=”GridView1_RowDeleting” onrowediting=”GridView1_RowEditing”

onrowupdating=”GridView1_RowUpdating”

*Snapshot how to do set Events of GridView select the GridView and press f4

Or copy paste the following Source Code

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”user.aspx.cs” Inherits=”user” %>

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>

<title>Untitled Page</title>

.style1

{

width: 100%;

}

.style2

{

}

</style>

</head>

<body>

<tr>

Uid</td>

<td>

<asp:TextBox ID=”Txt_uid” runat=”server”></asp:TextBox>

</td>

</tr>

<tr>

Name</td>

<td>

<asp:TextBox ID=”Txt_Name” runat=”server”></asp:TextBox>

</td>

</tr>

<tr>

Address</td>

<td>

<asp:TextBox ID=”Txt_Address” runat=”server” Height=”22px” >

</asp:TextBox>

</td>

</tr>

<tr>

Salary</td>

<td>

<asp:TextBox ID=”Txt_Salary” runat=”server”></asp:TextBox>

</td>

</tr>

<tr>

 </td>

<td>

<asp:Button ID=”Button1″ runat=”server” Height=”26px” onclick=”Button1_Click”

Text=”Insert” Width=”89px” />

</td>

</tr>

<tr>

 </td>

<td>

 </td>

</tr>

<tr>

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”

CellPadding=”1″ ForeColor=”#333333″ GridLines=”None”

onpageindexchanging=”GridView1_PageIndexChanging”

onrowcancelingedit=”GridView1_RowCancelingEdit”

onrowdeleting=”GridView1_RowDeleting” onrowediting=”GridView1_RowEditing”

onrowupdating=”GridView1_RowUpdating” CellSpacing=”1″>

<asp:TemplateField HeaderText=”S.No”>

<%#Container.DataItemIndex +1 %>

<asp:Label ID=”Label5″ runat=”server”></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”Uid”>

<asp:Label ID=”Label1″ runat=”server” Text=’<%# Eval(“uid”) %>‘></asp:Label>

<asp:Label ID=”lb_id” runat=”server” Text=’<%# Eval(“id”) %>‘></asp:Label>

</ItemTemplate>

<asp:Label ID=”lbl_edit” runat=”server” Text=’<%# Eval(“id”) %>‘></asp:Label>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”Name”>

<asp:Label ID=”Label2″ runat=”server” Text=’<%# Eval(“Name”) %>‘></asp:Label>

</ItemTemplate>

<asp:TextBox ID=”txt_ename” runat=”server” Text=’<%# Eval(“name”) %>‘></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”Salary”>

<asp:Label ID=”Label3″ runat=”server” Text=’<%# Eval(“Salary”) %>‘></asp:Label>

</ItemTemplate>

<asp:TextBox ID=”txt_esal” runat=”server” Text=’<%# Eval(“Salary”) %>‘></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”Address”>

<asp:Label ID=”Label4″ runat=”server” Text=’<%# Eval(“Address”) %>‘></asp:Label>

</ItemTemplate>

<asp:TextBox ID=”txt_eadd” runat=”server” Height=”81px”

Text=’<%# Eval(“address”) %>‘

TextMode=”MultiLine” Width=”246px”></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”Edit”>

<asp:LinkButton ID=”LinkButton1″ runat=”server” CommandName=”edit”>Edit</asp:LinkButton>

</ItemTemplate>

<asp:LinkButton ID=”LinkButton2″ runat=”server” CommandName=”update”>Update</asp:LinkButton>

 <asp:LinkButton ID=”LinkButton3″ runat=”server” CommandName=”cancel”>Cancel</asp:LinkButton>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”Delete”>

<asp:LinkButton ID=”LinkButton4″ runat=”server” CommandName=”delete”

onclientclick=”return confirm(‘Are you sure want to delete the current record ?’)”>Delete</asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</td>

</tr>

<tr>

 </td>

<td>

 </td>

</tr>

<tr>

 </td>

<td>

 </td>

</tr>

</table>

<div>

</div>

</form>

</body>

</html>

Snapshots

CODING

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient; // add the namespace

public partial class user : System.Web.UI.Page

{

SqlConnection con = new SqlConnection(); // for connection

SqlCommand cmd;

DataTable dt;

SqlDataAdapter adp;

protected void Page_Load(object sender, EventArgs e)

{

con.ConnectionString = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString; //accessing connection name from Web.config

con.Open();

if (con.State == ConnectionState.Closed)

{

con.Open();

}

con.Close();

if (IsPostBack == false)

{

grdview(); // calling the grdview function

}

private void grdview()

{

if (con.State == ConnectionState.Closed)

{

con.Open();

}

adp = new SqlDataAdapter(“select * from tbuser order by id desc”, con); //fetching the records from table

dt = new DataTable();

adp.Fill(dt);

if (dt.Rows.Count == 0)

{

Response.Write(“No record found”);

}

else

{

GridView1.DataSource = dt;

GridView1.DataBind();

}

con.Close();

}

protected void Button1_Click(object sender, EventArgs e)

{

if (con.State == ConnectionState.Closed)

{

con.Open();

}

adp = new SqlDataAdapter(“Select uid from tbuser where uid=@uid”, con); // will check the uid if exists then display else condition

adp.SelectCommand.Parameters.AddWithValue(“@uid”, Txt_uid.Text);

dt = new DataTable();

adp.Fill(dt);

adp.Dispose();

if (dt.Rows.Count == 0)

{

// will insert the unique record of uid

cmd = new SqlCommand(“insert into tbuser values(@uid,@Name,@Address,@Salary)”, con);

cmd.Parameters.AddWithValue(“@uid”, Txt_uid.Text);

cmd.Parameters.AddWithValue(“@Name”, Txt_Name.Text);

cmd.Parameters.AddWithValue(“@Address”, Txt_Address.Text);

cmd.Parameters.AddWithValue(“@Salary”, Txt_Salary.Text);

cmd.ExecuteNonQuery();

cmd.Dispose();

con.Close();

grdview();

}

else

{

Response.Write(“user name is already exits”);

}}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

GridView1.PageIndex = e.NewPageIndex; // for pageindexing

grdview();

}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

GridView1.EditIndex = -1; // will cancel the updating

grdview();

}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{ //will delete the Record

Label id = ((Label)(GridView1.Rows[e.RowIndex].FindControl(“lb_id”))); // here we will find the label name “lb_id” which is bound with

//field name id

if (con.State == ConnectionState.Closed)

{ con.Open(); }

cmd = new SqlCommand(“delete from tbuser where id=@id”, con);

cmd.Parameters.AddWithValue(“@id”, id.Text);

cmd.ExecuteNonQuery();

cmd.Dispose();

con.Close();

grdview();

}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

GridView1.EditIndex = e.NewEditIndex;

grdview();

}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

// The label which is in Edittemplate name “lbl_edit” bind with id field from the table

Label id = ((Label)(GridView1.Rows[e.RowIndex].FindControl(“lbl_edit”))) ;

if (con.State == ConnectionState.Closed)

{ con.Open(); }

//In edittemplate where we placed the textbox which were bind with the fields and here we will find the

// controls and will access the fields and update the records

cmd = new SqlCommand(“update tbuser set name=@name,salary=@salary,address=@address where id=@id”, con);

cmd.Parameters.AddWithValue(“@name”, ((TextBox)(GridView1.Rows[e.RowIndex].FindControl(“txt_ename”))).Text);//name

cmd.Parameters.AddWithValue(“@salary”, Convert.ToInt32(((TextBox)(GridView1.Rows[e.RowIndex].FindControl(“txt_esal”))).Text));//salary

cmd.Parameters.AddWithValue(“@address”, ((TextBox)(GridView1.Rows[e.RowIndex].FindControl(“txt_eadd”))).Text);//address

cmd.Parameters.AddWithValue(“@id”, id.Text);//id

cmd.ExecuteNonQuery();

cmd.Dispose();

GridView1.EditIndex = -1;

con.Close();

grdview();

}}

Following Snapshots

Select the GridView and click on the Columns and add the TemplateField.

Following the image will show the Edit Templates option click on it and it will open the Itemtemplate and Edit template fields.

Following image after click on the Edit Templates

Select the Edit Linkbutton and set the CommandName =”edit” and for Update is “update”, Cancel=”cancel” and for Delete =”delete”

Inserting the records and displaying the user name is already exists…

Editing the records after click the EDIT button and it will open the Edittemplate where we place the Textbox controls

Here the data will delete after confirmation of the record when we click on DELETE button.

*Will add more how to use databound with Gridview Control

Hope you will like it…

I like this add soo much it really gives up the good knowledge for the new learners and for exp persons to know about the complete process of data handling from Database using GridView

Thanks Again to the Author of this ADD

Reply ↓

Really Yaar very very nice ,it is proof very helpful for me
thax Dude ..

it looks very understandble

hi,very nice article.it looks very understandble

very nice to capture easily

good example

Nice article, easy to capture.

ThankYou..

Hi,
Very informative. I am now able to copy the sample into my website and it will work. What about if I do not want to edit inside the gridview. Would you be able to provide another example to edit in a popup form.
thanks
Bob

Vikram on June 8, 2012 at 11:05 am said:

you can refer below link …

Click here

with regards
vik

Reply ↓

Hi,
One Question regarding clicking edit link, I still see Delete button after I click edit link, how can I hide it in edit mode?
Bob

Vikram on June 8, 2012 at 11:08 am said:

well in edit try to search delete button and set property visible=”false”

Reply ↓

hi, thanks for the best tutorial i have gone through, exactly for my learning purpose.

Am looking for the similar database to be managed in session as temporary tables and later to be placed on main table. Say, when two login users perform the task of editing,deleting..each

willing to have guidance

Nice Post..helpfull for beginers..

How to use GridView with Insert, Edit, Update, Delete the Ado.net way C#

14 comments on “How to use GridView with Insert, Edit, Update, Delete the Ado.net way C#”

Leave a Reply Cancel reply

Joy of Helping

Facebook Link

Recent Posts

Recent Comments

Archives

Categories

Meta