Tuesday, October 4, 2011

Insert,Update,Delete in GridView

Introduction
This Artical About Insert ,Update,Delete With in Single Gridview Control




Code
.aspx page

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

<!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>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <asp:GridView ID="gvCourse" runat="server" AllowPaging="True"
            AutoGenerateColumns="false" DataKeyNames="id"
            onpageindexchanging="gvCourse_PageIndexChanging"
            onrowcancelingedit="gvCourse_RowCancelingEdit"
            onrowdeleting="gvCourse_RowDeleting" onrowediting="gvCourse_RowEditing"
            onrowupdating="gvCourse_RowUpdating" ShowFooter="True" style="font-size: large">
            <Columns>
                <asp:TemplateField HeaderText="ID">
                    <ItemTemplate>
                        <asp:Label ID="lblID" runat="server" Text='<%#Eval("id") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:Label ID="lblId" runat="server" Text='<%#Eval("id") %>'></asp:Label>
                    </EditItemTemplate>
                    <FooterTemplate>
                        sss
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="City">
                    <ItemTemplate>
                        <asp:Label ID="lblCity" runat="server" Text='<%#Eval("city") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtCity" runat="server" Text='<%#Eval("city") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtICity" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
               
                 <asp:TemplateField HeaderText="Latitude">
                    <ItemTemplate>
                        <asp:Label ID="lblLat" runat="server" Text='<%#Eval("lat") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtLat" runat="server" Text='<%#Eval("lat") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtILat" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
                 <asp:TemplateField HeaderText="Longitude">
                    <ItemTemplate>
                        <asp:Label ID="lblLon" runat="server" Text='<%#Eval("lon") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtLon" runat="server" Text='<%#Eval("lon") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtILon" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateField>
               
               
                <asp:TemplateField HeaderText="Command">
                    <ItemTemplate>
                        <asp:Button ID="btnEdit" runat="server" CommandName="Edit" Text="Edit" />
                        <asp:Button ID="btnDelete" runat="server" CommandName="Delete" Text="Delete" />
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:Button ID="btnUpdate" runat="server" CommandName="Update" Text="Update" />
                        <asp:Button ID="btnCancel" runat="server" CommandName="Cancel" Text="Cancel" />
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:Button ID="btnInsert" runat="server" onclick="btnInsert_Click"
                            Text="Insert" />
                    </FooterTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
   
    </div>
    </form>
</body>
</html>


.CS Page

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;

public partial class AddLocation : System.Web.UI.Page
{  
    SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bind();
        }
    }
    protected void gvCourse_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {

        GridViewRow row;
        row = gvCourse.Rows[e.RowIndex];

        Label id = row.FindControl("lblId") as Label;
        TextBox city = row.FindControl("txtCity") as TextBox;
        TextBox lat = row.FindControl("txtLat") as TextBox;
        TextBox lon = row.FindControl("txtLon") as TextBox;
        con.Open();
        SqlCommand cmd = new SqlCommand("update Location set city='" +city.Text + "',lat='"+lat.Text+"',lon='"+lon.Text+"' where id='" + id.Text + "' ", con);
        cmd.ExecuteNonQuery();
        gvCourse.EditIndex = -1;
        con.Close();
        bind();


    }
    protected void gvCourse_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvCourse.EditIndex = e.NewEditIndex;
        bind();
    }
    protected void gvCourse_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int id;
        id = Convert.ToInt32(gvCourse.DataKeys[e.RowIndex].Value);
        con.Open();
        SqlCommand cmd = new SqlCommand("Delete  from Location where id='" + id + "' ", con);
        cmd.ExecuteNonQuery();

        con.Close();

        bind();

    }
    protected void gvCourse_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvCourse.PageIndex = e.NewPageIndex;
        bind();
    }
    protected void gvCourse_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvCourse.EditIndex = -1;
        bind();
    }
    protected void bind()
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("Select * from Location", con);
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;

        da.Fill(ds);

        gvCourse.DataSource = ds;
        gvCourse.DataBind();

        con.Close();

    }
    protected void btnInsert_Click(object sender, EventArgs e)
    {

        TextBox city = gvCourse.FooterRow.FindControl("txtICity") as TextBox;
        TextBox lan = gvCourse.FooterRow.FindControl("txtILat") as TextBox;
        TextBox lon = gvCourse.FooterRow.FindControl("txtILon") as TextBox;


        con.Open();
        SqlCommand cmd = new SqlCommand("Insert into Location values('" + city.Text + "','"+lan.Text+"','"+lon.Text+"')", con);
        cmd.ExecuteNonQuery();


        con.Close();
        bind();

    }
}