Eliminazione di dati in un database SQL

Nell'esempio seguente viene mostrata una pagina con una colonna sul lato sinistro di DataGrid contenente un collegamento denominato Delete Author in ogni riga. Facendo clic sul collegamento, tutta la riga di dati verrà eliminata dal database.

Per visualizzare l'esecuzione di un esempio simile, eseguire l'esempio DataGrid10.aspx in Guide rapide di ASP.NET.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="VB" runat="server">
   Dim myConnection As SqlConnection
   Sub Page_Load(Src As Object, E As EventArgs) 
      ' Create a connection to the "pubs" SQL database located on 
      ' the local computer.
      myConnection = New SqlConnection ("server=localhost;" _
         & "database=pubs;Trusted_Connection=Yes")
      ' Determine  whether the page is a postback. If it is not a
      ' postback, call BindGrid.
      if Not IsPostBack Then 
         BindGrid()
      End If
   End Sub
   ' When the Delete Author link is clicked, set up a SQL DELETE
   ' statement, connect to the database, delete the indicated row, and 
   ' rebind the DataGrid to display the updated database.
   Sub MyDataGrid_Delete(sender As Object, E As DataGridCommandEventArgs)
      Dim deleteCmd As String = "DELETE FROM Authors WHERE au_id = @Id;"
      Dim myCommand As SqlCommand = New SqlCommand(deleteCmd, _
         myConnection)
      myCommand.Parameters.Add(New SqlParameter("@Id", _
         SqlDbType.VarChar, 11))
      ' Initialize the SqlCommand "@Id" parameter to the ID of the row
      ' that was clicked.
      myCommand.Parameters("@Id").Value = _
         MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
      ' Connect to the database and delete the specified row.
      myCommand.Connection.Open()
      ' Test whether the delete was accomplished, and display the 
      ' appropriate message to the user.
      Try 
         myCommand.ExecuteNonQuery()
         Message.InnerHtml = "<b>Record Deleted</b><br>"
      Catch ex As SqlException
         Message.InnerHtml = "ERROR: Could not delete record"
         Message.Style("color") = "red"
      End Try

      ' Close the connection.
      myCommand.Connection.Close()
      ' Rebind the DataGrid to show the updated information.
      BindGrid()
   End Sub
   ' The BindGrid procedure connects to the database and implements
   ' a SQL SELECT query to get all the data in "Authors" table.
   public Sub BindGrid() 
      Dim  myCommand As SqlDataAdapter = New SqlDataAdapter("SELECT *" _
        & " FROM authors", myConnection)
      Dim ds As DataSet = New DataSet()
      myCommand.Fill(ds)
      MyDataGrid.DataSource=ds
      MyDataGrid.DataBind()
   End Sub
</script>

<%-- Display the data in a DataGrid control. --%>
<body style="font: 10pt verdana">
   <form runat="server">
      <h3><font face="Verdana">Deleting a Row of Data</font></h3>
      <span id="Message" EnableViewState="false" 
         style="font: arial 11pt;" runat="server"/><p>
      <ASP:DataGrid id="MyDataGrid" runat="server"
         Width="800"
         BackColor="#ccccff" 
         BorderColor="black"
         ShowFooter="false" 
         CellPadding=3 
         CellSpacing="0"
         Font-Name="Verdana"
         Font-Size="8pt"
         HeaderStyle-BackColor="#aaaadd"
         DataKeyField="au_id"
         OnDeleteCommand="MyDataGrid_Delete"
      >
         <Columns>
            <ASP:ButtonColumn Text="Delete Author"
               CommandName="Delete"/>
         </Columns>
      </ASP:DataGrid>
   </form>
</body>
</html>

[C#]
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
   SqlConnection myConnection;
   protected void Page_Load(Object Src, EventArgs E) 
   {
      // Create a connection to the "pubs" SQL database located on the 
      // local computer.
      myConnection = new SqlConnection(   
         "server=localhost;database=pubs;Trusted_Connection=Yes");
      // Determine whether this page is a postback.  If it is not a
      // postback, call BindGrid.
      if (!IsPostBack) BindGrid();
   }
   // When the Delete link is clicked, create a SQL DELETE statement, 
   // connect to the database, delete the indicated row, and rebind the 
   // DataGrid to display the updated database.
   public void MyDataGrid_Delete(Object sender, 
      DataGridCommandEventArgs E) 
   {
      String deleteCmd = "DELETE FROM Authors WHERE au_id = @Id";
      SqlCommand myCommand = new SqlCommand(deleteCmd, myConnection);
      myCommand.Parameters.Add(new SqlParameter("@Id", 
         SqlDbType.VarChar, 11));
      // Initialize the SqlCommand "@Id" parameter to the ID of the row
      // that was clicked.
      myCommand.Parameters["@Id"].Value = 
         MyDataGrid.DataKeys[(int)E.Item.ItemIndex];
      // Connect to the database and delete the specified row.
      myCommand.Connection.Open();
      // Test whether the delete was accomplished, and display the 
      // appropriate message to the user.
      try 
      {
         myCommand.ExecuteNonQuery();
         Message.InnerHtml = "<b>Record Deleted</b><br>";
      }
      catch (SqlException) 
      {
         Message.InnerHtml = "ERROR: Could not delete record";
         Message.Style["color"] = "red";
      }
      // Close the connection.
      myCommand.Connection.Close();
      // Rebind the DataGrid to show the updated information.
      BindGrid();
   }
   // The BindGrid procedure connects to the database and implements
   // a SQL SELECT query to get all the data in the "Authors" database.
   public void BindGrid() 
   {
      SqlConnection myConnection = new SqlConnection(
         "server=localhost;database=pubs;Trusted_Connection=Yes");
      SqlDataAdapter myCommand = new SqlDataAdapter("SELECT *" +
         " FROM authors", myConnection);
      DataSet ds = new DataSet();
      myCommand.Fill(ds);
      MyDataGrid.DataSource=ds;
      MyDataGrid.DataBind();
   }
</script>

<%-- Display the data in a DataGrid control. --%>
<body style="font: 10pt verdana">
   <form runat="server">
      <h3><font face="Verdana">Deleting a Row of Data</font></h3>
      <span id="Message" EnableViewState="false" 
         style="font: arial 11pt;" runat="server"/><p>
      <ASP:DataGrid id="MyDataGrid" runat="server"
         Width="800"
         BackColor="#ccccff" 
         BorderColor="black"
         ShowFooter="false" 
         CellPadding=3 
         CellSpacing="0"
         Font-Name="Verdana"
         Font-Size="8pt"
         HeaderStyle-BackColor="#aaaadd"
         DataKeyField="au_id"
         OnDeleteCommand="MyDataGrid_Delete"
      >
         <Columns>
            <ASP:ButtonColumn Text="Delete Author"
               CommandName="Delete"/>
         </Columns>
      </ASP:DataGrid>
   </form>
</body>
</html>

Vedere anche

Accesso ai dati tramite ASP.NET | Accesso ai dati tramite ADO.NET | Spazio dei nomi System.Web.UI.WebControls | Classe DataGrid