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…

Pages

Saturday 6 April 2013

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

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

No comments:

Post a Comment

Blog Archive