Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

SqlCommand.Cancel Method

Tries to cancel the execution of a SqlCommand.

Namespace:  System.Data.SqlClient
Assembly:  System.Data (in System.Data.dll)

'Declaration
Public Overrides Sub Cancel

Implements

IDbCommand.Cancel

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

In some, rare, cases, if you call ExecuteReader then call Close (implicitily or explicitly) before calling Cancel, and then call Cancel, the cancel command will not be sent to SQL Server and the result set can continue to stream after you call Close. To avoid this, make sure that you call Cancel before closing the reader or connection.

The following example demonstrates the use of the Cancel method.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Threading

Module Module1
    Private m_rCommand As SqlCommand

    Public Property Command() As SqlCommand
        Get 
            Return m_rCommand
        End Get 
        Set(ByVal value As SqlCommand)
            m_rCommand = value
        End Set 
    End Property 

    Public Sub Thread_Cancel()
        Command.Cancel()
    End Sub 

    Sub Main()
        Dim connectionString As String = GetConnectionString()

        Try 
            Using connection As New SqlConnection(connectionString)

                connection.Open()

                Command = connection.CreateCommand()
                Command.CommandText = "DROP TABLE TestCancel" 
                Try
                    Command.ExecuteNonQuery()
                Catch 
                End Try

                Command.CommandText = "CREATE TABLE TestCancel(co1 int, co2 char(10))"
                Command.ExecuteNonQuery()
                Command.CommandText = "INSERT INTO TestCancel VALUES (1, '1')"
                Command.ExecuteNonQuery()

                Command.CommandText = "SELECT * FROM TestCancel" 
                Dim reader As SqlDataReader = Command.ExecuteReader()

                Dim rThread2 As Thread = New Thread( _
                    New ThreadStart(AddressOf Thread_Cancel))

                rThread2.Start()
                rThread2.Join()

                reader.Read()
                Console.WriteLine(reader.FieldCount)
                reader.Close()
            End Using 

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try 
    End Sub 

    Private Function GetConnectionString() As String 
        ' To avoid storing the connection string in your code,   
        ' you can retrieve it from a configuration file. 
        Return "Data Source=(local);Initial Catalog=AdventureWorks;" _
           & "Integrated Security=SSPI;" 
    End Function 
End Module

.NET Framework

Supported in: 4.6, 4.5, 4, 3.5, 3.0, 2.0, 1.1

.NET Framework Client Profile

Supported in: 4, 3.5 SP1
Show:
© 2015 Microsoft