SqlDataSource.DeleteCommand Property (System.Web.UI.WebControls)

DeleteCommand Property

Collapse All

Language Filter

This page is specific to

Microsoft Visual Studio 2008/.NET Framework 3.5

Other versions are also available for the following:

.NET Framework Class Library

SqlDataSource..::.DeleteCommand Property

Gets or sets the SQL string that the SqlDataSource control uses to delete data from the underlying database.

Namespace: System.Web.UI.WebControls
Assembly: System.Web (in System.Web.dll)

Syntax

Visual Basic (Declaration)

Public Property DeleteCommand As String

Visual Basic (Usage)

Dim instance As SqlDataSource
Dim value As String

value = instance.DeleteCommand

instance.DeleteCommand = value

public string DeleteCommand { get; set; }

Visual C++

public:
property String^ DeleteCommand {
    String^ get ();
    void set (String^ value);
}

JScript

public function get DeleteCommand () : String
public function set DeleteCommand (value : String)

ASP.NET

<asp:SqlDataSource DeleteCommand="String" />

Property Value

Type: System..::.String
An SQL string that the SqlDataSource uses to delete data.

Remarks

The DeleteCommand represents an SQL query or the name of a stored procedure, and is used by the Delete method.

Because different database products use different varieties of SQL, the syntax of the SQL string depends on the current ADO.NET provider being used, which is identified by the ProviderName property. If the SQL string is a parameterized query or command, the syntax of the parameter also depends on the ADO.NET provider being used. For example, if the provider is the System.Data.SqlClient, which is the default provider for the SqlDataSource class, the syntax of the parameter is '@parameterName'. However, if the provider is set to the System.Data.Odbc or System.Data.OleDb, the placeholder of the parameter is '?'. For more information about parameterized SQL queries and commands, see Using Parameters with the SqlDataSource Control.

The DeleteCommand property can be an SQL string or the name of a stored procedure, if the database supports stored procedures.

The DeleteCommand property delegates to the DeleteCommand property of the SqlDataSourceView object that is associated with the SqlDataSource control.

Security Note:
For security purposes, the DeleteCommand property is not stored in view state. Because it is possible to decode the contents of view state on the client, storing sensitive information about the database structure in view state could result in an information disclosure vulnerability.

Examples

The following code example demonstrates how to set the DeleteCommand text to delete an order from the Northwind database Orders table. Data is retrieved from the Orders table and displayed in a GridView control. The GridView renders a Delete button automatically when the AutoGenerateDeleteButton property is set to true. Additionally, when the Delete button is clicked, the GridView control automatically populates the DeleteParameters collection and calls the Delete method. Finally, because this code example deletes data, an event handler is added to attempt to back up the database to disk before the Delete operation is performed.

Visual Basic

<%@Page  Language="VB" %>
<%@Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
 Sub On_Record_Deleting(ByVal source As Object, ByVal e As SqlDataSourceCommandEventArgs)
     ' Cancel the delete operation if the checkbox is not checked.
     If Not CheckBox1.Checked 
            e.Cancel = True
            Label1.Text = "The command was cancelled because the CheckBox was not checked."
     End If

End Sub 'On_Record_Deleting

Sub On_Record_Deleted(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs)
    Label1.Text = e.AffectedRows & " row(s) were deleted"

End Sub

</script>

<html  >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">

        <asp:SqlDataSource
            id="SqlDataSource1"
            runat="server"
            DataSourceMode="DataSet"
            ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
            SelectCommand="SELECT * FROM Orders"
            DeleteCommand="DELETE FROM [Order Details] WHERE OrderID=@OrderID;DELETE FROM Orders WHERE OrderID=@OrderID;"
            OnDeleting="On_Record_Deleting"
            OnDeleted="On_Record_Deleted">
        </asp:SqlDataSource>
        <br />

       <asp:CheckBox 
         id="CheckBox1" 
         runat="server"
         autopostback="true"
         text="Check To Delete Data" />
        <br />
        <br />

        <asp:GridView
            id="GridView1"
            runat="server"
            AutoGenerateColumns="False"
            DataKeyNames="OrderID"
            AutoGenerateDeleteButton="True"
            AllowPaging="True"
            PageSize="20"
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField HeaderText="Order ID" DataField="OrderID" />
                <asp:BoundField HeaderText="Customer" DataField="CustomerID" />
                <asp:BoundField HeaderText="Order Placed" DataField="OrderDate" />
                <asp:BoundField HeaderText="Order Shipped" DataField="ShippedDate" />
            </Columns>
        </asp:GridView>

        <asp:Label
            id="Label1"
            runat="server">
        </asp:Label>

    </form>
  </body>
</html>

<%@Page  Language="C#" %>
<%@Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
private void OnRecordDeleting(Object source, SqlDataSourceCommandEventArgs e) {    
    // Cancel the delete operation if the checkbox is not checked.
    if (! CheckBox1.Checked) {
        e.Cancel = true;
        Label1.Text = "The command was cancelled because the CheckBox was not checked.";
    }
 }

private void OnRecordDeleted(object source, SqlDataSourceStatusEventArgs e) {
    Label1.Text = e.AffectedRows + " row(s) were deleted";
}
</script>

<html  >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:SqlDataSource
            id="SqlDataSource1"
            runat="server"
            DataSourceMode="DataSet"
            ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
            SelectCommand="SELECT * FROM Orders"            
            DeleteCommand="DELETE FROM [Order Details] WHERE OrderID=@OrderID;DELETE FROM Orders WHERE OrderID=@OrderID;"
            OnDeleting="OnRecordDeleting"
            OnDeleted="OnRecordDeleted">
        </asp:SqlDataSource>
        <br />
       <asp:CheckBox 
         id="CheckBox1" 
         runat="server"
         autopostback="true"
         text="Check To Delete Data" />
        <br />
        <br />

        <asp:GridView
            id="GridView1"
            runat="server"
            AutoGenerateColumns="False"
            DataKeyNames="OrderID"
            AutoGenerateDeleteButton="True"
            AllowPaging="True"
            PageSize="20"
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField HeaderText="Order ID" DataField="OrderID" />
                <asp:BoundField HeaderText="Customer" DataField="CustomerID" />
                <asp:BoundField HeaderText="Order Placed" DataField="OrderDate" />
                <asp:BoundField HeaderText="Order Shipped" DataField="ShippedDate" />
            </Columns>
        </asp:GridView>

        <asp:Label
            id="Label1"
            runat="server">
        </asp:Label>

    </form>
  </body>
</html>

<%@Page  Language="VJ#" %>
<%@Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
     private void OnRecordDeleting(Object source, 
        SqlDataSourceCommandEventArgs e)
    {
        // Because this example actually deletes data from the Northwind
        // database, provide a way for users to recover any deleted records.
        SqlConnection cxn = new SqlConnection(
            "Data Source=localhost;Integrated Security=SSPI;" 
            + "Initial Catalog=Northwind;Connect Timeout=15");

        try {
            cxn.Open();
            SqlCommand backup = new SqlCommand();
            backup.set_Connection (cxn);
            backup.set_CommandText(
                " USE master " + " EXEC sp_addumpdevice 'disk','Northwind_1'," 
                + "'c:\\temp\\Northwind_1.dat'" 
                + " BACKUP DATABASE Northwind TO Northwind_1");
            backup.ExecuteNonQuery();
        }
        catch (SqlException se) {
            // Handle an exception thrown if the device already exists.
            Label1.set_Text("An error occurred while backing up your " 
                + "database. Please check the SQL logs.");
        }
        finally {
            // Always release the connection.
            cxn.Dispose();
        }
        Label1.set_Text("A record has been deleted. To recover your data, " 
            + "restore the database from the " 
            + "database backup named Northwind_1.dat located on the database " 
            + "server in c:\\temp.");
    } //OnRecordDeleting
</script>

<html  >
    <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
        <form id="form1" runat="server">

            <asp:SqlDataSource
                id="SqlDataSource1"
                runat="server"
                DataSourceMode="DataSet"
                ConnectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;"
                SelectCommand="SELECT * FROM Orders"
                DeleteCommand="DELETE FROM [Order Details] WHERE OrderID=@OrderID;DELETE FROM Orders WHERE OrderID=@OrderID;"
                OnDeleting="OnRecordDeleting">
            </asp:SqlDataSource>

            <asp:GridView
                id="GridView1"
                runat="server"
                AutoGenerateColumns="False"
                DataKeyNames="OrderID"
                AutoGenerateDeleteButton="True"
                AllowPaging="True"
                PageSize="20"
                DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField HeaderText="Order ID" DataField="OrderID" />
                    <asp:BoundField HeaderText="Customer" DataField="CustomerID" />
                    <asp:BoundField HeaderText="Order Placed" DataField="OrderDate" />
                    <asp:BoundField HeaderText="Order Shipped" DataField="ShippedDate" />
                </Columns>
            </asp:GridView>

            <asp:Label
                id="Label1"
                runat="server">
            </asp:Label>

        </form>
    </body>
</html>

Platforms

Windows Vista, Windows XP SP2, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP Starter Edition, Windows Server 2003, Windows Server 2000 SP4, Windows Millennium Edition, Windows 98

The .NET Framework and .NET Compact Framework do not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.

Version Information