SqlDataSource and Gridview

(ASP.NET)


Tools Used: Visual Studio 2013 Express, Sql Server 2008/2012 Express and .NET Framework 4.5

The gridview uses sql datasource control to execute the query against the database and get the resultset from the database. The gridview links the sqldatasource  using the datasourcedid property. The sql datasource control has four types of command properties those are 


InsertCommand ->  To perform insert operation

SelectCommand -> To perform select operation

UpdateCommand -> To perform update operation

DeleteCommand -> To perform delete operation

The important events in sql datasource controls are

  1.   Onselected & OnSelecting
  2.   OnInserted & OnInserting
  3.   OnUpdated & OnUpdating
  4.   OnDeleted & OnDeleting
I customize the delete operation using the sql datasource OnDeleting event, In that event i am just calling the javascript function which displays the confirm dialog, if the user pressed ok button, i am triggering the onclick event of the button using javascript. In that button click event i am deleting the record from db.

.aspx page
   <script type="text/javascript">
    
        function Confirm(id) {
            if (confirm('Do you want to delete?')) {
                var btn = document.getElementById('btn_delete');
                var hdn = document.getElementById('hdn_delid');
                hdn.value = id;
                btn.click();
            }
        }

        function Success() {

            var hdn = document.getElementById('hdn_delid');
          
            alert(hdn.value + ' deleted successfully.');
        }
    </script>

 <div>
        <asp:GridView ID="grdvw_customers" DataKeyNames="Id" AutoGenerateEditButton="True" AutoGenerateDeleteButton="True" AllowSorting="True" AllowPaging="True" DataSourceID="sqlds_customers" runat="server">
        </asp:GridView>

        <asp:SqlDataSource   OnDeleting="sqlds_customers_Deleting"  SelectCommand="SELECT Id,CustomerId,CompanyName,ContactName,ContactTitle,City,Country,PostalCode FROM Customers" 
            UpdateCommand="UPDATE Customers SET CustomerId = @CustomerId,CompanyName = @CompanyName ,ContactName = @ContactName,ContactTitle = @ContactTitle,City = @City,Country = @Country,PostalCode = @PostalCode WHERE Id = @Id"  
             DeleteCommand="DELETE FROM Customers WHERE Id = @Id"
            ConnectionString="<%$ ConnectionStrings:CustomersConn %>" ID="sqlds_customers" runat="server">

        </asp:SqlDataSource>

        <asp:HiddenField ClientIDMode="Static" ID="hdn_delid" runat="server" />
        <asp:Button ID="btn_delete" ClientIDMode="Static" runat="server" Text="id" style="display:none;" OnClick="btn_delete_Click" />
    </div>
Codebehind/.cs file       

protected void sqlds_customers_Deleting(object sender, SqlDataSourceCommandEventArgs e)
        {
            try
            {

                int id = (int)e.Command.Parameters["@Id"].Value;
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "confirmfun", "Confirm('"+ id +"')",true);
                
            }
            catch(Exception)
            { }

            e.Cancel = true;
        }

        protected void btn_delete_Click(object sender, EventArgs e)
        {
            try
            {
                int id = Convert.ToInt32(hdn_delid.Value);

                using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["CustomersConn"].ToString()))
                {
                    conn.Open();

                    string sqlQuery = "DELETE FROM Customers WHERE Id = @Id";

                    SqlCommand cmd = new SqlCommand(sqlQuery, conn);

                    cmd.Parameters.AddWithValue("@Id",id);

                    int rowsAffected = cmd.ExecuteNonQuery();

                    if (rowsAffected == 1)
                    {
                       
                        Page.ClientScript.RegisterStartupScript(Page.GetType(), "successfun", "Success()", true);

                        //rebind the gridview after deletion
                        grdvw_customers.DataBind();
                    }
                }

               
            }
            catch(Exception)
            { }
        }


Ratings


Average Rating: 0.00 by 0 users
Giri Prasad
5/19/2014
5/19/2014
Download PDF
Download Project

Comments