SqlBulkCopy - WriteToServerAsync 3

CSharp · Console

/Program.cs

// @nuget: Z.BulkOperations
using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Z.BulkOperations;
					
public class Program
{
	public static void Main()
	{
		InsertCustomersData();
		
		using(var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
		{
			connection.Open();
			
			// Get data from the source table as a SqlDataReader.
            SqlCommand commandSourceData = new SqlCommand(
                "SELECT * FROM Customers_Archive;", connection);
			
            SqlDataReader reader =
                commandSourceData.ExecuteReader();
			
			using (var dConnection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
            {
                dConnection.Open();
                var sqlBulk = new SqlBulkCopy(dConnection);
                sqlBulk.DestinationTableName = "Customers";
                try
                {
                    // Write from the source to the destination asynchronously.
            		Task.Run(async () => { await sqlBulk.WriteToServerAsync(reader); }).Wait();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
					sqlBulk.Close();
                    reader.Close();
                }
            }
		}
		
		var data = GetData();
		FiddleHelper.WriteTable(data);
	}
	
	public static void InsertCustomersData()
	{
		CreateTable();
		var dt = GetDataToInsert();	
		
		using(var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
		{
			connection.Open();
			using (var sqlBulk = new SqlBulkCopy(connection))
        	{		
            	sqlBulk.DestinationTableName = "Customers_Archive";
            	sqlBulk.WriteToServer(dt);
        	}
		}
	}
	
	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();
			
			command = connection.CreateCommand();
			
			command.CommandText = @"
CREATE TABLE Customers_Archive
(
	CustomerID INT IDENTITY(1, 1),
	Name VARCHAR(255),
	City VARCHAR(255)
)
			";
			command.ExecuteNonQuery();
		}
	}
}
;