// @nuget: Z.BulkOperations

using System;

using System.Data;

using System.Data.SqlClient;

using Z.BulkOperations;

public class Program

{

    public static void Main()

    {

        CreateTable();

        var dt = GetDataToInsert(); 

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

        {

            connection.Open();

            var transaction = connection.BeginTransaction();

            using (var sqlBulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))

            {

                // SET BatchSize value.

                sqlBulk.BatchSize = 4000;

                sqlBulk.DestinationTableName = "Customers";

                sqlBulk.WriteToServer(dt);

                transaction.Commit();

            }

        }

        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();

        }

    }

}