When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, June 20, 2007

Accessing and Updating Data in ASP.NET: Deleting Data

By Scott Mitchell


A Multipart Series on ASP.NET's Data Source Controls
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data. These controls allow page developers to declaratively access and modify data without writing any code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.

  • Data Source Control Basics - explores the concepts and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
  • Accessing Database Data - shows how to use the SqlDataSource and AccessDataSource controls to query data from a relational database.
  • Filtering Database Data with Parameters - learn how to retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page, session variables, and so on.
  • Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.
  • Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
  • Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.
  • Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.
  • Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.
  • Inserting Data - learn how to insert data using a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
  • Deleting Data - see how to delete data using a SqlDataSource control. Also looks at how to programmatically cancel a delete.
  • Updating Basics - learn the basics of updating database data using a SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
  • Customizing the Editing Interface - see how to customize the GridView's columns to provide a customized editing interface that includes input validation and alternative user interface elements.
  • Handling Database NULL Valuese - explore how to extend the GridView's customized editing interface to handle database NULL values.
  • Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.
  • Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.
  • (Subscribe to this Article Series! )

    Introduction


    ASP.NET 2.0's data source and data Web controls make working with data much easier than in previous versions by allowing for a declarative approach to working with data. In short, the data source controls make it possible to retrieve, insert, update, and delete data without having to write the tedious dedata access code that was so commonplace in ASP.NET version 1.x. (A previous installment of this article series examined using the SqlDataSource to insert data into a database.)

    In this article we will focus on using the SqlDataSource control to delete records from a database. Specifically, we will look at three examples: using a SqlDataSource and a bit of server-side code to delete a record selected from a drop-down list; and using the GridView Web control's built-in deleting support; and how to programmatically cancel a delete operation. Read on to learn more!

    - continued -

    An Overview of Deleting Data Using the Data Source Controls


    The AccessDataSource, SqlDataSource, and ObjectDataSource controls all support inserting, updating, and deleting capabilities. In a nutshell, all three have a Delete method that, when invoked, kicks off the following deleting workflow:
    1. The data source's Deleting event is fired
    2. The deleting "action" occurs
    3. The data source's Deleting event is fired
    The deleting "action" differs between the data source controls. For the AccessDataSource and SqlDataSource, the action involves connecting to the specified database and executing the DELETE statement specified by the control's DeleteCommand property. For the ObjectDataSource, the deleting "action" involves creating an instance of the data source's underlying object and invoking the specified DeleteMethod. This article focuses on the SqlDataSource control; for a detailed look at deleting with the ObjectDataSource, refer to the "Editing, Inserting, and Deleting" tutorials in my Working with Data in ASP.NET 2.0 tutorial series.

    Let's explore the deleting "action" for the AccessDataSource and SqlDataSource controls in more detail. The DELETE statement or stored procedure specified by the control's DeleteCommand uses a parameterized query. That is, if you use an ad-hoc SQL statement for the DeleteCommand, that DELETE statement will use parameters like so:

    DELETE FROM TableName
    WHERE ColumnName1 = @Parameter1

    Most ad-hoc DELETE statements look like the one above: they specify a table name and have a single condition in their WHERE clause. The WHERE clause specifies a particular primary key value to delete. In the case where a table has a composite primary key (i.e., a primary key made up of multiple columns), then the WHERE clause would compare each key column, like so:

    DELETE FROM TableName
    WHERE ColumnName1 = @Parameter1 AND ColumnName2 = @Parameter2 AND ... AND ColumnNameN = @ParameterN

    In the Filtering Database Data with Parameters article, we looked at using parameters in the SelectCommand to filter the results, such as SELECT * FROM Products WHERE Price < @UnitPrice. The parameter - @UnitPrice, in this instance - has its value specified via the data source control's SelectParameters, which can specify a source for the parameter value. The source may be: a hard-coded value like "3.95", which would return all products less than $3.95; the value of a Web control on the page, allowing the user to enter a price bound in a TextBox; from the querystring; from session state; and so on.

    Likewise, the parameter values in the DELETE statement are assigned based on the parameters in the data source control's DeleteParameters collection, and these parameters can use the same sources as the SelectParameters.

    The AccessDataSource and SqlDataSource controls, behind the scenes, use the standard ADO.NET classes to perform their data access. That is, they connect to the database using a SqlConnection or OleDbConnection object and specify the command text and parameters via a SqlCommand or OleDbCommand object.

    Given this information, the deleting workflow for the AccessDataSource and SqlDataSource can be more specifically expressed as:

    1. The data source's Deleting event is fired
    2. SqlConnection and SqlCommand (or OleDbConnection and OleDbCommand) objects are created
    3. The command object's CommandText property is assigned the data source control's DeleteCommand property
    4. The parameters in the data source control's DeleteParameters collection are added the command object's Parameters collection
    5. A connection to the database is established and the command is executed, thereby deleting the data
    6. The data source's Deleted event is fired
    The remainder of this article examines three deleting scenarios using the SqlDataSource control: manually deleting data by programmatically calling the SqlDataSource control's Delete method; deleting data using the GridView control; and programmatically short-circuiting the deletion process in the SqlDataSource's Deleting event handler. The full code for these demos is available in the download at the end of this article.

    Manually Deleting Data


    The demos available for download at the end of this article illustrate different techniques for deleting data into the Products table of the Northwind database. Imagine that we were asked to create a page that allowed users to choose a product from a drop-down list and then delete the selected product by clicking a Delete button. We could create such a Web Form by adding a DropDownList and a Button Web control.

    In addition to these two user input controls we could also add a SqlDataSource control to populate the DropDownList control with the set of products and to execute the actual delete. This could be done using two SqlDataSource controls - one to do the select and one for the delete - or just one control that does both. For this demo, I used just one control.

    Previous articles in this series have examined using SqlDataSource control's to retrieve data and bind it to a Web control, so I won't step through the tasks for binding the list of products to the DropDownList. Once you have configured the SqlDataSource to retrieve the records from the Products table and have bound them to the DropDownList, you can configure the SqlDataSource's delete-related properties by selecting the SqlDataSource control in the Designer and, from the Properties window, selecting the DeleteQuery property. Doing so will bring up a dialog box where you can enter the SqlDataSource's DeleteCommand property and specify values for the DeleteParameters.

    Since we want to delete a particular record from the Products table, set the DeleteCommand property to the following DELETE statement:

    DELETE FROM [Products] WHERE [ProductID] = @ProductID

    Note the use of the parameter in the WHERE clause. The source for this parameter value will be the selected ProductID value in the DropDownList control on the page. Map this parameter value to the DropDownList's SelectedValue property value via the DeleteParameters collection using ControlParameter. The easiest way to accomplish this is from the dialog box brought up by clicking on the DeleteQuery property for the SqlDataSource control (see the screen shot below).

    Alternatively, these parameters can be specified via the SqlDataSource control's declarative syntax:

    <asp:SqlDataSource ID="ProductsDataSourceForDropDownList" runat="server" ConnectionString="..."
       DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID"
       SelectCommand="SELECT [ProductID], [ProductName] FROM [Products] ORDER BY [ProductName]">
       <DeleteParameters>
          <asp:ControlParameter ControlID="ProductList" Name="ProductID" PropertyName="SelectedValue"
             Type="Int32" />
       </DeleteParameters>
    </asp:SqlDataSource>

    Note that the above declarative markup contains both the delete-specific property settings (DeleteCommand and DeleteParameters) as well as the SelectCommand property.

    Once the Web controls have been added to the page and the SqlDataSource's properties have been correctly configured, deleting a new record is as simple as calling the data source control's Delete() method. In addition to deleting the product, we also need to rebind the data to the DropDownList (so that it is updated to no longer include the just-deleted product). Also, it would be nice to display some sort of confirmation message to the user upon deletion. The following "Delete Product" Button's Click event handler performs these three tasks:

    Protected Sub btnDeleteProduct_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDeleteProduct.Click
       'Programmatically call the Delete method
       

    ProductsDataSourceForDropDownList.Delete()



       'Display a confirmation message
       Dim productJustDeleted As String = ProductList.SelectedItem.Text
       ClientScript.RegisterStartupScript(Me.GetType(), "Message", String.Format("alert('The product {0} has just been deleted...');", productJustDeleted.Replace("'", "\'")), True)

       'Rebind the data to the DropDownList so that the just-deleted product no longer appears
       ProductList.DataBind()
    End Sub

    Deleting Data Using a GridView Control


    A number of new data Web controls were introduced with ASP.NET 2.0. These include the GridView, DetailsView, and FormView, among others. The GridView is designed to display a set of records in a grid. It also boasts built-in delete functionality that can be turned on with the tick of a checkbox. To demonstrate this functionality, let's look at using a GridView to list all of the products in a pageable GridView and then add a column of Delete buttons to the grid.

    Start by adding a SqlDataSource control to the page and use the same DeleteCommand as in the previous example:

    DELETE FROM [Products] WHERE [ProductID] = @ProductID

    Next, add the @ProductID parameter to the control's DeleteParameters collection. Rather than using ControlParameter as we did in the previous example, use the default Parameter object.

    <asp:SqlDataSource ID="ProductsDataSource" runat="server" ConnectionString="..."
       DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID"
       SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice], [Discontinued] FROM [Products] ORDER BY [ProductName]">
       <DeleteParameters>
          

    <asp:Parameter Name="ProductID" Type="Int32" />


       </DeleteParameters>
    </asp:SqlDataSource>

    Note: The SelectCommand used in the above SqlDataSource differs slightly from the previous example in that it returns two additional fields from the Products table: UnitPrice and Discontinued.

    Next, add a GridView to the page and, from its smart tag, bind it to the SqlDataSource control we just added (ProductsDataSource). Doing so automatically adds BoundFields for the columns returned in the SelectCommand. It is also sets the GridView's DataKeys property to the primary key column(s) of the data being returned - ">ProductID", in this case.

    To add a column of Delete buttons, go to the GridView's smart tag and check the "Enable Deleting" checkbox. This adds a CommandField to the DetailsView and sets its ShowDeleteButton property to True. A CommandField with this configuration displays a column with a Delete button in each row. Also check the "Enable Paging" checkbox, which is also located in the GridView's smart tag.

    After completing the above steps, your GridView's declarative markup should look similar to the following:

    <asp:GridView ID="gvProducts" runat="server"

    AllowPaging="True"


          AutoGenerateColumns="False" DataKeyNames="ProductID" DataSourceID="ProductsDataSource">
       <Columns>
          

    <asp:CommandField ShowDeleteButton="True" />


          <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
          <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" />
          <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" SortExpression="Discontinued" />
       </Columns>
    </asp:GridView>

    That's all there is to it! When the page is visited and a Delete button clicked, the following workflow transpires:

    1. A postback
    2. The GridView's RowDeleting event fires
    3. The GridView populates the @ProductID using the DataKeys value for the row whose Delete button was clicked. (It is essential that the DataKeys property be set to "ProductID". If you clear this property value out or it gets reset or lost somehow, the @ProductID parameter value won't be assigned when the Delete button is clicked. Consequently, the DELETE statement won't delete any records! Therefore, if you are testing the GridView and find that clicking the Delete button doesn't do anything, the first thing to check is that the GridView's DataKeys property is properly set.)
    4. The GridView invokes its SqlDataSource control's Delete() method
    5. The GridView's RowDeleted event fires

    This example illustrates only the simplest facilities of the GridView and omits commonly requested features, such as providing a messagebox confirming the delete. For information on adding a client-side confirmation messagebox to a GridView's Delete buttons, see Adding Client-Side Confirmation When Deleting (VB Version) (C# version).

    Cancelling a Delete


    As discussed earlier, when a data source control's Delete() method is invoked, it goes through the following sequence of steps:
    1. The data source's Deleting event is fired
    2. The deleting "action" occurs
    3. The data source's Deleting event is fired
    Note that before the actual delete "action" occurs, the Deleting event is fired. In certain scenarios, we may wish to proactively cancel a delete after the data source control's Delete() method has been invoked. For example, we might include a GridView that includes a Delete button for each row, although there may be certain business rules that prohibit certain records from being deleted. This business logic could be enforced in one of two ways: by programmatically hiding or disabling the Delete buttons for those records that cannot be deleted; or by programmatically checking the business rules when the Delete button is clicked and cancelling the delete if necessary. Ideally, both techniques would be used.

    Let's look at how to implement cancelling a delete from the SqlDataSource control's Deleting event. Building upon the previous example that used a GridView, let's update our page logic so that products that have a UnitPrice greater than $50.00 cannot be deleted. To accomplish this, we need to add create an event handler for the Deleting event and determine the price of the record that is about to be deleted. All that we have in this event handler are the parameter values - in this case, just the ProductID value. Consequently, we need to requery the database to determine this product's UnitPrice value. As the following code illustrates, to cancel a delete simply set the e.Cancel property to True.

    Protected Sub ProductsDataSource_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles ProductsDataSource.Deleting
       'Determine if the product being deleted has a unit price > $50
       Dim productID As Integer = Convert.ToInt32(e.Command.Parameters("@ProductID").Value)

       'Determine the unit price of the product that the user wants to delete...
       'There are many ways you can accomplish this - use a SqlDataSource control, write code to query the database...
       'Let's use data access code
       Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)
       Const strSql As String = "SELECT UnitPrice FROM Products WHERE ProductID = @ProductID"
       Dim myCommand As New SqlCommand(strSql, myConnection)
       myCommand.Parameters.AddWithValue("@ProductID", productID)

       myConnection.Open()
       Dim price As Object = myCommand.ExecuteScalar
       myConnection.Close()

       'Prohibit the delete if price is not a database NULL AND it is greater than $50
       If Not Convert.IsDBNull(price) AndAlso Convert.ToDecimal(price) > 50 Then
         e.Cancel = True   'Cancel the delete
         CannotDeleteMessage.Visible = True 'Show a message explaining why
       End If
    End Sub

    Note that this examples uses ADO.NET data access code to the database and execute a query to determine the UnitPrice of the product about to be deleted. This product's ProductID value is retrieved from the e.Command object's Parameters collection. (This parameter retrieval code is specific to the type of data source control being used; in other words, the code seen here would differ if using an ObjectDataSource control.) If the UnitPrice value is not a database NULL and exceeds $50.00, then the deletion is cancelled and a Label control (CannotDeleteMessage) is displayed, explaining to the user that they cannot delete the specified product due to its price.

    The following screen shot shows what happens when attempting to delete "Carnarvon Tigers," a product whose price exceeds $50.00.

    Carnarvon Tigers canont be deleted because its price exceeds $50.00.

    Conclusion


    In this article we looked at how to use the SqlDataSource to delete data from a database. We worked through three examples: manually deleting data by programmatically calling the data source control's Delete() method; deleting data using the built-in deleting capabilities of a GridView; and cancelling a delete via the SqlDataSource's Deleting event handler. In all three cases, the SqlDataSource control encapsulates many of the data access tasks, such as connecting to the database, creating the command object, and executing the parameterized query. In future installments of this article series we will take a similar in-depth look at updating.

    Until then... Happy Programming!

  • By Scott Mitchell


    Attachments:


  • Download the code used in this article

    Further Readings:


  • Tutorials on Editing, Inserting, and Deleting Data (using the ObjectDataSource)
  • Inserting, Updating, and Deleteing Data with the SqlDataSource (VB Version) (C# version)
  • Adding Client-Side Confirmation When Deleting (VB Version) (C# version)
  • A Multipart Series on ASP.NET's Data Source Controls
    ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data. These controls allow page developers to declaratively access and modify data without writing any code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.

  • Data Source Control Basics - explores the concepts and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
  • Accessing Database Data - shows how to use the SqlDataSource and AccessDataSource controls to query data from a relational database.
  • Filtering Database Data with Parameters - learn how to retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page, session variables, and so on.
  • Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.
  • Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
  • Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.
  • Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.
  • Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.
  • Inserting Data - learn how to insert data using a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
  • Deleting Data - see how to delete data using a SqlDataSource control. Also looks at how to programmatically cancel a delete.
  • Updating Basics - learn the basics of updating database data using a SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
  • Customizing the Editing Interface - see how to customize the GridView's columns to provide a customized editing interface that includes input validation and alternative user interface elements.
  • Handling Database NULL Valuese - explore how to extend the GridView's customized editing interface to handle database NULL values.
  • Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.
  • Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.
  • (Subscribe to this Article Series! )



    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article