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