CODEWITHSUNDEEP
c#sql-serversqlbulkcopy
Leo.W
Leo.W

Reputation: 569

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 I don't know how to do so. Can I do something on the arriving of the notification event (with Sql​Bulk​Copy.NotifyAfter and Sql​Bulk​Copy.SqlRowsCopied)?

Upvotes: 1

Views: 1352

Answers (3)

dnickless
dnickless

Reputation: 10918

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 = new SqlBulkCopy("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

Upvotes: 4

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89266

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 @spid int = (
select session_id
from sys.dm_exec_requests
where command = 'BULK INSERT')

if @spid is not null
begin
    declare @sql varchar(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

  static void Main(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;
            }




        }
    }

Upvotes: 1

Markeli
Markeli

Reputation: 558

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.

Upvotes: 0

Related Questions