SqlCommand.Cancel Method

public:
 override void Cancel();

public:
 virtual void Cancel();

public override void Cancel();

public void Cancel();

override this.Cancel : unit -> unit

abstract member Cancel : unit -> unit
override this.Cancel : unit -> unit

Public Overrides Sub Cancel ()

Public Sub Cancel ()

Implements

Examples

The following example demonstrates the use of the Cancel method.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

class Program
{
 private static SqlCommand m_rCommand;

 public static SqlCommand Command
 {
 get { return m_rCommand; }
 set { m_rCommand = value; }
 }

 public static void Thread_Cancel()
 {
 Command.Cancel();
 }

 static void Main()
 {
 string connectionString = GetConnectionString();
 try
 {
 using (SqlConnection connection = new SqlConnection(connectionString))
 {
 connection.Open();

 Command = connection.CreateCommand();
 Command.CommandText = "DROP TABLE TestCancel";
 try
 {
 Command.ExecuteNonQuery();
 }
 catch { }

 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";
 SqlDataReader reader = Command.ExecuteReader();

 Thread rThread2 = new Thread(new ThreadStart(Thread_Cancel));
 rThread2.Start();
 rThread2.Join();

 reader.Read();
 System.Console.WriteLine(reader.FieldCount);
 reader.Close();
 }
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message);
 }
 }
 static private string GetConnectionString()
 {
 // 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";
 }
}

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 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

Remarks

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 (implicitly 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.

See also

• Connecting and Retrieving Data in ADO.NET
• Using the .NET Framework Data Provider for SQL Server
• ADO.NET Overview