ASP NET in a Nutshell 2nd Edition-ASP NET in a Nutshell 2nd Edition

7.5 Deleting Data

Example 7-7 shows how you can use the DataSet and SqlDataAdapter classes to delete data from the Titles table. This example shows the implementation of an ASP.NET page that displays data from the Titles table and allows users to delete a row from the table by clicking a button.

Unless you don't care about the state of the Pubs sample database, it would probably be a good idea to back up the database before deleting any of the rows in the Titles table (just in case you want to restore the database to its original state later).

As with the previous example, we use a DataGrid control to display the items in the dataset. However, in this case, we set the AutoGenerateColumns property of the DataGrid to False and supply BoundColumn controls for each displayed column. This provides greater flexibility in displaying the data, including the ability to determine which columns are displayed, the header to use for each column, and in the case of the price data, the ability to specify a format string for the data. This example also adds an <alternatingitemstyle> tag to specify that every other row should have a background color of silver. To enable handling of the Delete button, we set the DataGrid's onDeleteCommand method to the name of the event handler for the Delete button.

As with the previous example, Example 7-7 declares both the DataSet and SqlDataAdapter instances at the page level to make them available to all procedures; in the Page_Load event handler, we call GetTitleData and BindGrid, which perform the same operations as in the previous example (although this version of GetTitleData does not allow a WHERE clause).

Once the data is displayed, the user can click the Delete button for a row, which invokes the Delete_Click event handler. In Delete_Click, we call GetTitleData to fill the dataset, and then call the Delete method of the selected row (using the Item.ItemIndex property of the DataGridCommandEventArgs parameter passed to the event handler to determine the correct row to delete). Once the row is deleted from the dataset, we call the Update method of the SqlDataAdapter, passing it the modified dataset, and then call Response.Redirect to redirect the user to the original page.

Example 7-7. DeleteTitles.aspx

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
   <title>Delete Example</title>
   <script runat="server">
      Dim Titles As New DataSet( )
      Dim TitlesAdpt As New SqlDataAdapter( )
      
      Sub Page_Load(Sender As Object, e As EventArgs)
         If Not IsPostBack Then
            GetTitleData( )
            BindGrid( )
         End If
      End Sub
      
      Sub BindGrid( )
         TitleGrid.DataSource = Titles.Tables(0).DefaultView
         TitleGrid.DataBind( )
      End Sub
      
      Sub GetTitleData( )
         Dim ConnStr As String = "Data Source=(local)\NetSDK;" & _
            "Initial Catalog=Pubs;Trusted_Connection=True;"
         Dim SQL As String = "SELECT * FROM titles"
         Dim PubsConn As New SqlConnection(ConnStr)
         Dim TitlesCmd As New SqlCommand(SQL, PubsConn)
         TitlesAdpt.SelectCommand = TitlesCmd
         Dim TitlesCB As New SqlCommandBuilder(TitlesAdpt)
         ' No need to open or close connection,
         '   since the SqlDataAdapter will do this automatically.
         TitlesAdpt.Fill(Titles)
      End Sub
  
      Sub Delete_Click(Sender As Object, e As DataGridCommandEventArgs)
         GetTitleData( )
         Titles.Tables(0).Rows(e.Item.ItemIndex).Delete
         TitlesAdpt.Update(Titles)
         Response.Redirect("DeleteTitles.aspx")
      End Sub
   </script>
</head>
<body>
   <h1>Delete Example</h1>
   <form runat="server">
      <asp:datagrid id="TitleGrid"
         ondeletecommand="Delete_Click"
         cellpadding="3"
         autogeneratecolumns="false"
         runat="server">
         <columns>
            <asp:buttoncolumn buttontype="PushButton" 
               text="Delete" commandname="Delete" />
            <asp:boundcolumn headertext="Title ID"
               datafield="title_id"/>
            <asp:boundcolumn headertext="Title"
               datafield="title"/>
            <asp:boundcolumn headertext="Type"
               datafield="type"/>
            <asp:boundcolumn headertext="Publisher ID"
               datafield="pub_id"/>
            <asp:boundcolumn headertext="Price"
               datafield="price" dataformatstring="{0:c}"/>
         </columns>
      </asp:datagrid>
   </form>
</body>
</html>

The output of Example 7-7 is shown in Figure 7-7.

Figure 7-7. Output of DeleteTitles.aspx

[ Team LiB ]