Is It Possible To Cancel A Sql​bulk​copy.write​to​server (c#)?

I am using Sql​Bulk​Copy.Write​To​Server (C#) to copy billions of rows, and which takes hours to complete. Sometimes I need to stop the operation for some urgent tasks, but

Solution 1:

The MSDN documentation says:

If the user wants to cancel the operation from the event, the Abort property of the SqlRowsCopiedEventArgs can be used.

So the following code should do the trick:

SqlBulkCopy cp = newSqlBulkCopy("your connection string");
    cp.NotifyAfter = 1000; // use here whatever number floats your boat
    cp.SqlRowsCopied += (sender, eventArgs) => eventArgs.Abort = true; // add some more conditions here

Solution 2:

Can I do something on the arriving of the notification event

As a SQL administrator you can always find the session running the bulk load and KILL it, like this:

declare@spidint= (
select session_id
from sys.dm_exec_requests
where command ='BULK INSERT')

if @spidisnotnullbegindeclare@sqlvarchar(200) = concat('kill ',@spid)

    exec ( @sql )

end

If you want to do this in your code, use the Async version of WriteToServer which accepts a Cancelation Token. eg

staticvoidMain(string[] args)
    {
        using (var src = new SqlConnection("server=.;database=tempdb;integrated security=true"))
        using (var dest = new SqlConnection("server=.;database=tempdb;integrated security=true"))
        {
            src.Open();
            dest.Open();
            var cmd = new SqlCommand("create table #t(id int)", dest);
            cmd.ExecuteNonQuery();

            bool cancel = false;
            var cancelationTokenSource = new CancellationTokenSource();
            var srcCmd = new SqlCommand("select row_number() over (order by (select null)) id from sys.objects o, sys.columns c, sys.columns c2", src);
            using (var rdr = srcCmd.ExecuteReader())
            {
                var bc = new SqlBulkCopy(dest);
                bc.NotifyAfter = 10000;
                bc.SqlRowsCopied += (s, a) =>
                {
                    Console.WriteLine($"{a.RowsCopied} rows copied");
                    if (cancel)
                    {
                        dest.Close();
                    }
                };

                bc.DestinationTableName = "#t";
                bc.ColumnMappings.Add(new SqlBulkCopyColumnMapping("id", "id"));

                var task = bc.WriteToServerAsync(rdr, cancelationTokenSource.Token);

                Console.WriteLine("Hit any key to cancel the bulk load");
                while (!task.Wait(1000))
                {
                    if (Console.KeyAvailable)
                    {

                        cancelationTokenSource.Cancel();
                        try
                        {
                            task.Wait();
                        }
                        catch (AggregateException ex)
                        {
                            Console.WriteLine(ex.InnerException.Message);
                            Console.WriteLine("WriteToServer Canceled");
                            break;
                        }
                    }
                }

                Console.WriteLine("Hit any key to exit");
                return;
            }




        }
    }

Solution 3:

You can try to modify your sql query and process data in chunks, for example, 1 thousand per request. If you need to stop operation, remember chunk size and number, stop sql operation, do what you need, and continue sql operations. Also you can make your own PaueOperationToken like CancelToken. Here is example of CancelToken.