// @nuget: Z.BulkOperations

using System;

using System.Data;

using System.Data.SqlClient;

using System.Threading;

using System.Threading.Tasks;

using Z.BulkOperations;

public class Program

{

    public static void Main()

    {

        CreateTable();

        var dt = GetDataToInsert(); 

        using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))

        {

            connection.Open();

            var sqlBulk = new SqlBulkCopy(connection);

​

            CancellationTokenSource cancelationTokenSource = new CancellationTokenSource();

            CancellationToken cancellationToken = cancelationTokenSource.Token;

            sqlBulk.DestinationTableName = "Customers";

​

            // Write from the source to the destination asynchronously.

            var task = sqlBulk.WriteToServerAsync(dt, cancellationToken);

​

            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.Message);

                        Console.WriteLine("WriteToServer Canceled");

                        break;

                    }

                }

            }

            sqlBulk.Close();

        }

​

        var data = GetData();

        FiddleHelper.WriteTable(data);

    }

    public static DataTable GetData()

    {       

        var dt = new DataTable();

        using(var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))

        {

            connection.Open();

            var command = connection.CreateCommand();

            command.CommandText = "SELECT * FROM Customers";

            using(var adapter = new SqlDataAdapter(command))

            {

                adapter.Fill(dt);

            }

        }

        dt.TableName = "Total: " + dt.Rows.Count + " rows";

        return dt;

    }

    public static DataTable GetDataToInsert()

    {

        var dt = new DataTable();

        dt.Columns.Add("CustomerID", typeof(int));

        dt.Columns.Add("Name");

        dt.Columns.Add("City");

        for(int i = 0; i < 1000; i++)

        {

            dt.Rows.Add(i, "Name_" + i, "City_" + i);

        }

        return dt;

    }

​

    public static void CreateTable()

    {       

        using(var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))

        {

            connection.Open();

            var command = connection.CreateCommand();

            command.CommandText = @"

CREATE TABLE Customers

(

    CustomerID INT IDENTITY(1, 1),

    Name VARCHAR(255),

    City VARCHAR(255)

)

            ";

            command.ExecuteNonQuery();

        }

    }

}