Crud operations with Grid View in Asp.net using Ado.net
Crud (Insert , Update, Delete ,Select) operations in Grid View in Asp.net using Ado.net
Step1: First of all we have to create a database. In database we will create a table .
CREATE TABLE [dbo].[tbl_data](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
[city] [varchar](50) NULL,
CONSTRAINT [PK_tbl_data] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GOStep2: Now we will add a Grid View in our Productinfo.aspx page .
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Productinfo.aspx.cs" Inherits="_3_tier.Productinfo" %>
<!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" AutoGenerateColumns="False" ShowFooter="true"
BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
CellPadding="3" DataKeyNames="id" onrowcancelingedit="GridView1_RowCancelingEdit"
onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing" OnRowCommand="gvDetails_RowCommand"
onrowupdating="GridView1_RowUpdating" CellSpacing="2">
<Columns>
<asp:TemplateField HeaderText="Operations">
<EditItemTemplate>
<asp:Button ID="Button3" runat="server" CommandName="Update" Text="Update" />
<asp:Button ID="Button4" runat="server" CommandName="Cancel" Text="Cancel" />
</EditItemTemplate>
<ItemTemplate>
<asp:Button ID="Button1" runat="server" CommandName="Edit" Text="Edit" />
<asp:Button ID="Button2" runat="server" CommandName="Delete" Text="Delete" />
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="addnew" runat="server" CommandName="Add New" Text="Add New" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("name") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("name") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="addname" runat="server" ></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("city") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("city") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="addcity" runat="server" ></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FFF1D4" />
<SortedAscendingHeaderStyle BackColor="#B95C30" />
<SortedDescendingCellStyle BackColor="#F1E5CE" />
<SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
</div>
</form>
</body>
</html>Step3: Write the following events in Productinfo.aspx.cs
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;
namespace _3_tier
{
public partial class Productinfo : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
refreshdata();
}
}
public void refreshdata()
{
SqlConnection con = new SqlConnection(@"Data Source=ROHIT-HP;Initial Catalog=aa; User ID=sa;Password=sa1234; ");
SqlCommand cmd = new SqlCommand("select * from tbl_data", con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=ROHIT-HP;Initial Catalog=aa; User ID=sa;Password=sa1234;");
int id = Convert.ToInt16(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("delete from tbl_data where id =@id", con);
cmd.Parameters.AddWithValue("id", id);
int i = cmd.ExecuteNonQuery();
con.Close();
refreshdata();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
refreshdata();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
refreshdata();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=ROHIT-HP;Initial Catalog=aa; User ID=sa;Password=sa1234;");
TextBox txtname = GridView1.Rows[e.RowIndex].FindControl("TextBox1") as TextBox;
TextBox txtcity = GridView1.Rows[e.RowIndex].FindControl("TextBox2") as TextBox;
int id = Convert.ToInt16(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("Update tbl_data set name='"+txtname.Text+"',city='"+txtcity.Text+"' where id='"+id+"' ", con);
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("name", txtname.Text);
//cmd.Parameters.AddWithValue("city", txtcity.Text);
//cmd.Parameters.AddWithValue("id", id);
int i = cmd.ExecuteNonQuery();
con.Close();
GridView1.EditIndex = -1;
refreshdata();
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("Add New"))
{
SqlConnection con = new SqlConnection(@"Data Source=ROHIT-HP;Initial Catalog=aa; User ID=sa;Password=sa1234;" );
con.Open();
TextBox name = (TextBox)GridView1.FooterRow.FindControl("addname");
TextBox city = (TextBox)GridView1.FooterRow.FindControl("addcity");
SqlCommand cmd = new SqlCommand("insert into tbl_data(name,city) values('" + name.Text + "','" + city.Text + "')", con);
cmd.ExecuteNonQuery();
con.Close();
refreshdata();
}
}
}
}