Thursday, 27 June 2013

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#


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.
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.

Or
Run the following script in your Sql Server 2005.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head runat=”server”>
<title>Untitled Page</title>
<style type=”text/css”>
.style1
{
width100%;
}
.style2
{
}
</style>
</head>
<body>
<form id=”form1″ runat=”server”>
<table class=”style1″>
<tr>
<td class=”style2″>
Uid</td>
<td>
<asp:TextBox ID=”Txt_uid” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td class=”style2″>
Name</td>
<td>
<asp:TextBox ID=”Txt_Name” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td class=”style2″>
Address</td>
<td>
<asp:TextBox ID=”Txt_Address” runat=”server” Height=”22px” >
</asp:TextBox>
</td>
</tr>
<tr>
<td class=”style2″>
Salary</td>
<td>
<asp:TextBox ID=”Txt_Salary” runat=”server”></asp:TextBox>
</td>
</tr>
<tr>
<td class=”style2″>
&nbsp;</td>
<td>
<asp:Button ID=”Button1″ runat=”server” Height=”26px”onclick=”Button1_Click”
Text=”Insert” Width=”89px” />
</td>
</tr>
<tr>
<td class=”style2″>
&nbsp;</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td class=”style2″ colspan=”2″>
<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″>
<RowStyle BackColor=”#EFF3FB” />
<Columns>
<asp:TemplateField HeaderText=”S.No”>
<ItemTemplate>
<%#Container.DataItemIndex +1 %>
<asp:Label ID=”Label5″ runat=”server”></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Uid”>
<ItemTemplate>
<asp:Label ID=”Label1″ runat=”server” Text=’<%# Eval(“uid”) %>‘></asp:Label>
<asp:Label ID=”lb_id” runat=”server” Text=’<%# Eval(“id”) %>‘></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID=”lbl_edit” runat=”server” Text=’<%# Eval(“id”) %>‘></asp:Label>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Name”>
<ItemTemplate>
<asp:Label ID=”Label2″ runat=”server” Text=’<%# Eval(“Name”) %>‘></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID=”txt_ename” runat=”server” Text=’<%# Eval(“name”)%>‘></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Salary”>
<ItemTemplate>
<asp:Label ID=”Label3″ runat=”server” Text=’<%# Eval(“Salary”) %>‘></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID=”txt_esal” runat=”server” Text=’<%# Eval(“Salary”)%>‘></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Address”>
<ItemTemplate>
<asp:Label ID=”Label4″ runat=”server” Text=’<%# Eval(“Address”) %>‘></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<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”>
<ItemTemplate>
<asp:LinkButton ID=”LinkButton1″ runat=”server”CommandName=”edit”>Edit</asp:LinkButton>
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID=”LinkButton2″ runat=”server”CommandName=”update”>Update</asp:LinkButton>
&nbsp;<asp:LinkButton ID=”LinkButton3″ runat=”server”CommandName=”cancel”>Cancel</asp:LinkButton>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Delete”>
<ItemTemplate>
<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>
<FooterStyle BackColor=”#507CD1″ Font-Bold=”True” ForeColor=”White”/>
<PagerStyle BackColor=”#2461BF” ForeColor=”White”HorizontalAlign=”Center” />
<SelectedRowStyle BackColor=”#D1DDF1″ Font-Bold=”True”ForeColor=”#333333″ />
<HeaderStyle BackColor=”#507CD1″ Font-Bold=”True” ForeColor=”White”/>
<EditRowStyle BackColor=”#FF9999″ />
<AlternatingRowStyle BackColor=”White” />
</asp:GridView>
</td>
</tr>
<tr>
<td class=”style2″>
&nbsp;</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td class=”style2″>
&nbsp;</td>
<td>
&nbsp;</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.

1 comment: