Home > ASP.Net > Insert and Update and Delete Without SqlDataSource

Insert and Update and Delete Without SqlDataSource


Hi all,

Try this example to Insert and Update and Delete Without SqlDataSource

In .aspx Page add this code :-

<%@ 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>

and in Code Behind add the following Code :


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();
            }
        }
    }

}

Hope this helps

Good Luck

Advertisements
Categories: ASP.Net
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: