SqlCommand.Cancel Method

Attempts to cancel the execution of a SqlCommand.

[Visual Basic]
Public Overridable Sub Cancel() Implements IDbCommand.Cancel
[C#]
public virtual void Cancel();
[C++]
public: virtual void Cancel();
[JScript]
public function Cancel();

Implements

IDbCommand.Cancel

Remarks

If there is nothing to cancel, nothing happens. However, if there is a command in process, and the attempt to cancel fails, no exception is generated.

Example

[Visual Basic, C#, C++] The following example demonstrates the use of the Cancel method.

[Visual Basic] 
Private _cmd As SqlCommand
Private _e1 As AutoResetEvent = New AutoResetEvent(False)
Private _e2 As AutoResetEvent = New AutoResetEvent(False)


Public Sub IssueQuery()
    Dim r As SqlDataReader
    Dim rows As Integer = 0
    Try
        r = _cmd.ExecuteReader()
        Do
            Do While r.Read
                rows = rows + 1
            Loop
        Loop While r.NextResult()
        Console.WriteLine("FAILED: execution should not have finished, {0} rows read!", rows)
    Catch 
        Console.WriteLine("PASSED:  Got expected exception!")
    End Try
    _e1.Set()
End Sub

Public Sub CancelQuery()
    System.Threading.Thread.Sleep(500)
    _cmd.Cancel()
    _e2.Set()
End Sub

Public Sub CancelTest()
    Dim conn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;")
    conn.Open()
    Dim _cmd As SqlCommand = New SqlCommand("", conn)
    Dim evs(2) As AutoResetEvent
    evs(0) = _e1
    evs(1) = _e2

    Dim t1Start As ThreadStart = New ThreadStart(AddressOf IssueQuery)
    Dim t2Start As ThreadStart = New ThreadStart(AddressOf CancelQuery)
    Dim t1 As Thread = New Thread(t1Start)
    Dim t2 As Thread = New Thread(t2Start)

    ' cancel operation while results are coming back
    _cmd.CommandText = "select * from orders, products"
    t1.Start()
    t2.Start()
    WaitHandle.WaitAll(evs)
    conn.Close()

End Sub

[C#] 
private SqlCommand _cmd;
// threads signal these when done
private AutoResetEvent _e1 = new AutoResetEvent(false);
private AutoResetEvent _e2 = new AutoResetEvent(false);

    public void IssueQuery() {
            SqlDataReader r;
        int rows = 0;
        try {
            r = _cmd.ExecuteReader();
            do {
                while (r.Read()) {
                    rows++;
                }
            } while (r.NextResult());

            Console.WriteLine("FAILED: execution should not have finished, {0} rows read!", rows);
        }            
        catch {
            Console.WriteLine("PASSED:  Got expected exception!");
        }
    _e1.Set();
    }

    public void CancelQuery() {
        System.Threading.Thread.Sleep(500);
        _cmd.Cancel();
        _e2.Set();
    }
    
    public void CancelTest() {
        SqlConnection conn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;");      
        conn.Open();
        _cmd = new SqlCommand(null, conn);
        
    AutoResetEvent[] evs = new AutoResetEvent[2];
        evs[0] = _e1;
        evs[1] = _e2;

        Thread t1 = new Thread(new ThreadStart(this.IssueQuery));
        Thread t2 = new Thread(new ThreadStart(this.CancelQuery));
        
        t1 = new Thread(new ThreadStart(this.IssueQuery));
        t2 = new Thread(new ThreadStart(this.CancelQuery));
        
        // cancel operation while results are coming back
        _cmd.CommandText = "select * from orders, products";
        t1.Start();
        t2.Start();
        WaitHandle.WaitAll(evs);
        conn.Close();
      }

[C++] 
public:
    SqlCommand* _cmd;
    // threads signal these when done
    AutoResetEvent* _e1;
    AutoResetEvent* _e2;

    Sample()
    {
        // Initialize _e1 and _e2 events:
        _e1 = new AutoResetEvent(false);
        _e2 = new AutoResetEvent(false);
    };

    void IssueQuery()
    {
        SqlDataReader* r;
        int rows = 0;
        try
        {
            r = _cmd->ExecuteReader();
            do
            {
                while (r->Read())
                {
                    rows++;
                }
            }
            while (r->NextResult());

            Console::WriteLine(S"FAILED: execution should not have finished, {0} rows read!", __box(rows));
        }
        catch (Exception*)
        {
            Console::WriteLine(S"PASSED:  Got expected exception!");
        }
        _e1->Set();
    }

    void CancelQuery()
    {
        System::Threading::Thread::Sleep(500);
        _cmd->Cancel();
        _e2->Set();
    }

    void CancelTest()
    {
        SqlConnection* conn = new SqlConnection(S"Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;");
        conn->Open();
        _cmd = new SqlCommand(0, conn);

        AutoResetEvent* evs[] = new AutoResetEvent*[2];
        evs->Item[0] = _e1;
        evs->Item[1] = _e2;

        Thread* t1 = new Thread(new ThreadStart(this, &Sample::IssueQuery));
        Thread* t2 = new Thread(new ThreadStart(this, &Sample::CancelQuery));
        t1 = new Thread(new ThreadStart(this, &Sample::IssueQuery));
        t2 = new Thread(new ThreadStart(this, &Sample::CancelQuery));

        // cancel operation while results are coming back
        _cmd->CommandText = S"select * from orders, products";
        t1->Start();
        t2->Start();
        WaitHandle::WaitAll(evs);
        conn->Close();
    };

[JScript] No example is available for JScript. To view a Visual Basic, C#, or C++ example, click the Language Filter button in the upper-left corner of the page.

Requirements

Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family, .NET Compact Framework

See Also

SqlCommand Class | SqlCommand Members | System.Data.SqlClient Namespace