Thursday 16 May 2013

Insert and Update and Delete Gridview Without using SqlDataSource


Insert and Update and Delete Gridview Without using SqlDataSource



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

<!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:label id="lblMessage" runat="server" enableviewstate="False" Font-Bold="True" />
 <asp:gridview id="gvShippers" runat="server" allowpaging="True" allowsorting="True"
  autogeneratecolumns="False" datakeynames="ShipperID" emptydatatext="There are no data records to display."
  onpageindexchanging="gvShippers_PageIndexChanging" onrowcancelingedit="gvShippers_RowCancelingEdit"
  onrowcommand="gvShippers_RowCommand" onrowdeleting="gvShippers_RowDeleting" onrowediting="gvShippers_RowEditing"
  onrowupdating="gvShippers_RowUpdating" onsorting="gvShippers_Sorting" showfooter="True"
  style="margin-top: 20px;" CellPadding="4" ForeColor="#333333" GridLines="None">
  <columns>
   <asp:templatefield>
    <itemtemplate>
     <asp:linkbutton id="btnSelect" runat="server" commandname="Select" text="Select" />
     <asp:linkbutton id="btnEdit" runat="server" commandname="Edit" text="Edit" />
     <asp:linkbutton id="btnDelete" runat="server" commandname="Delete" text="Delete" />
    </itemtemplate>
    <edititemtemplate>
     <asp:linkbutton id="btnUpdate" runat="server" commandname="Update" text="Update" />
     <asp:linkbutton id="btnCancel" runat="server" commandname="Cancel" text="Cancel" />
    </edititemtemplate>
    <footertemplate>
     <asp:linkbutton id="btnInsert" runat="server" commandname="Insert" text="Insert" />
    </footertemplate>
   </asp:templatefield>
   <asp:boundfield datafield="ShipperID" headertext="ShipperID" insertvisible="False"
    sortexpression="ShipperID" />
   <asp:templatefield headertext="CompanyName" sortexpression="CompanyName">
    <itemtemplate>
     <%# Eval("CompanyName") %>
    </itemtemplate>
    <edititemtemplate>
     <asp:textbox id="txtCompanyName" runat="server" text='<%# Eval("CompanyName") %>' />
    </edititemtemplate>
    <footertemplate>
     <asp:textbox id="txtCompanyName" runat="server" />
    </footertemplate>
   </asp:templatefield>
   <asp:templatefield headertext="Phone" sortexpression="Phone">
    <itemtemplate>
     <%# Eval("Phone") %>
    </itemtemplate>
    <edititemtemplate>
     <asp:textbox id="txtPhone" runat="server" text='<%# Eval("Phone") %>' />
    </edititemtemplate>
    <footertemplate>
     <asp:textbox id="txtPhone" runat="server" />
    </footertemplate>
   </asp:templatefield>
  </columns>
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <RowStyle BackColor="#EFF3FB" />
        <EditRowStyle BackColor="#2461BF" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="White" />
 </asp:gridview>

    </div>
    </form>
</body>
</html>
 .aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page
{
    private string Sort
    {
        get
        {
            return String.Concat(
                    this.SortExpression,
                    (this.SortDirection == SortDirection.Ascending) ? " ASC" : " DESC");
        }
    }

    private SortDirection SortDirection
    {
        get
        {
            if (ViewState["SortDirection"] == null)
            {
                ViewState["SortDirection"] = SortDirection.Ascending;
            }

            return (SortDirection)ViewState["SortDirection"];
        }
        set { ViewState["SortDirection"] = value; }
    }

    private string SortExpression
    {
        get
        {
            if (ViewState["SortExpression"] == null)
            {
                ViewState["SortExpression"] = "ShipperID";
            }

            return ViewState["SortExpression"].ToString();
        }
        set { ViewState["SortExpression"] = value; }
    }

    private void SetData()
    {
        SqlConnection conn = new SqlConnection(
            ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand("SELECT * FROM [Shippers]", conn);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);

        try
        {
            conn.Open();
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            DataView dv = dt.DefaultView;
            dv.Sort = this.Sort;

            gvShippers.DataSource = dv;
            gvShippers.DataBind();
        }
        finally
        {
            conn.Close();
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            this.SetData();
        }
    }

    protected void gvShippers_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvShippers.PageIndex = e.NewPageIndex;

        this.SetData();
    }

    protected void gvShippers_Sorting(object sender, GridViewSortEventArgs e)
 {
        if (this.SortExpression.Equals(e.SortBLOCKED EXPRESSION)
        {
            this.SortDirection = (this.SortDirection == SortDirection.Ascending) ? SortDirection.Descending :SortDirection.Ascending;
        }
        else
        {
            this.SortDirection = SortDirection.Ascending;
        }

        this.SortExpression = e.SortExpression;

        this.SetData();
 }

    protected void gvShippers_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvShippers.EditIndex = e.NewEditIndex;

        this.SetData();
    }

    protected void gvShippers_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvShippers.EditIndex = -1;

        this.SetData();
    }

    protected void gvShippers_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        GridViewRow gvr = gvShippers.Rows[e.RowIndex];

        TextBox txtCompanyName = gvr.FindControl("txtCompanyName") as TextBox;
        TextBox txtPhone = gvr.FindControl("txtPhone") as TextBox;

        if (txtCompanyName == null) { return; }
        if (txtPhone == null) { return; }

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand(
            "UPDATE [Shippers] SET [CompanyName] = @CompanyName, [Phone] = @Phone WHERE (ShipperID = @ShipperID)",
            conn);
        cmd.Parameters.AddWithValue("ShipperID", gvShippers.DataKeys[gvr.RowIndex]["ShipperID"]);
        cmd.Parameters.AddWithValue("CompanyName", txtCompanyName.Text);
        cmd.Parameters.AddWithValue("Phone", txtPhone.Text);

        try
        {
            conn.Open();

            if (cmd.ExecuteNonQuery().Equals(1))
            {
                lblMessage.Text = String.Format(
                    "Shipper '{0}' successfully updated.",
                    cmd.Parameters["ShipperID"].Value);

                gvShippers.EditIndex = -1;

                this.SetData();
            }
        }
        finally
        {
            conn.Close();
        }
    }

    protected void gvShippers_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        SqlConnection conn = new SqlConnection(
            ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand(
            "DELETE FROM [Shippers] WHERE (ShipperID = @ShipperID)",
            conn);
        cmd.Parameters.AddWithValue("ShipperID", gvShippers.DataKeys[e.RowIndex]["ShipperID"]);

        try
        {
            conn.Open();

            if (cmd.ExecuteNonQuery().Equals(1))
            {
                lblMessage.Text = String.Format(
                    "Shipper '{0}' successfully deleted.",
                    cmd.Parameters["ShipperID"].Value);

                this.SetData();
            }
        }
        finally
        {
            conn.Close();
        }
    }

    protected void gvShippers_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("Insert"))
        {
            LinkButton btnInsert = e.CommandSource as LinkButton;

            if (btnInsert == null) { return; }

            GridViewRow gvr = btnInsert.NamingContainer as GridViewRow;

            if (gvr == null) { return; }

            TextBox txtCompanyName = gvr.FindControl("txtCompanyName") as TextBox;
            TextBox txtPhone = gvr.FindControl("txtPhone") as TextBox;

            if (txtCompanyName == null) { return; }
            if (txtPhone == null) { return; }

            SqlConnection conn = new SqlConnection(
                ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(
                "INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@CompanyName, @Phone); SELECT @ShipperID = SCOPE_IDENTITY()",
                conn);
            cmd.Parameters.AddWithValue("CompanyName", txtCompanyName.Text);
            cmd.Parameters.AddWithValue("Phone", txtPhone.Text);
            cmd.Parameters.Add("ShipperID", SqlDbType.Int);
            cmd.Parameters["ShipperID"].Direction = ParameterDirection.Output;

            try
            {
                conn.Open();

                if (cmd.ExecuteNonQuery().Equals(1))
                {
                    lblMessage.Text = String.Format(
                        "Shipper '{0}' successfully added.",
                        cmd.Parameters["ShipperID"].Value);

                    this.SetData();
                }
            }
            finally
            {
                conn.Close();
            }
        }
    }

}

No comments:

Post a Comment