Eliminar datos en una base de datos de SQL
En el siguiente ejemplo se presenta una página con una columna en el lado izquierdo del objeto DataGrid que contiene un vínculo con el título Eliminar autor en cada fila. Al hacer clic en el vínculo se elimina esa fila de datos de la base de datos.
Para ver un ejemplo similar, ejecute el ejemplo DataGrid10.aspx del Tutorial de 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>
Vea también
Acceso a datos con ASP.NET | Acceso a datos con ADO.NET | System.Web.UI.WebControls (Espacio de nombres) | DataGrid (Clase)