// Bulk Operations

// Doc: https://bulk-operations.net/bulk-merge

​

// @nuget: Z.BulkOperations

// @nuget: System.Data.SqlClient

​

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using Z.BulkOperations;

public class Program

{

    public static void Main()

    {

        GenerateDatabase();

        SeedInvoices(2, 3);

        var dsGeneratedInvoices = GenerateInvoices(5, 3);       

        var dtInvoices = dsGeneratedInvoices.Tables[0];

        var dtInvoiceItems = dsGeneratedInvoices.Tables[1];

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

        {

            connection.Open();

            // SHOW data

            ShowData(connection, "Before");

​

            // SAVE Invoices

            using (var bulk = new BulkOperation(connection))

            {

                // easy to customize

                bulk.DestinationTableName = "Invoices";

                bulk.AutoMapOutputIdentity = true;

​

                bulk.BulkMerge(dtInvoices);

            }

​

            // SAVE InvoiceItems

            using (var bulk = new BulkOperation(connection))

            {   

                // SET foreign key value

                dtInvoiceItems.AsEnumerable().ToList().ForEach(x => x["InvoiceID"] = ((DataRow)x["InvoiceReference"])["InvoiceID"]);

​

                bulk.DestinationTableName = "InvoiceItems";

​

                bulk.BulkMerge(dtInvoiceItems);

            }

            // SHOW data

            ShowData(connection, "After");

        }

    }

    public static void ShowData(SqlConnection connection, string text)

    {

        // SHOW data

        using (var command = connection.CreateCommand())

        {

            command.CommandText = @"

SELECT * FROM Invoices

SELECT * FROM InvoiceItems

";

            var ds = new DataSet();

            var sqlDataAdapter = new SqlDataAdapter(command);

            sqlDataAdapter.Fill(ds);

​

            ds.Tables[0].TableName = "2 - Invoices " + text;

            ds.Tables[1].TableName = "3 - InvoiceItems " + text;

​

            FiddleHelper.WriteTable(ds.Tables[0]);

            FiddleHelper.WriteTable(ds.Tables[1]);

        }

    }

    public static DataSet GenerateInvoices(int countInvoice, int countInvoiceItem, bool useOld = false) 

    {

        DataSet dataSet = new DataSet();

        // Invoices

        var dtInvoices = new DataTable("Invoice");  

        dtInvoices.Columns.Add("InvoiceID", typeof(int));

        dtInvoices.Columns.Add("Number", typeof(string)); 

        dataSet.Tables.Add(dtInvoices);

        // InvoiceItems

        var dtInvoiceItems = new DataTable("InvoiceItems");  

        dtInvoiceItems.Columns.Add("InvoiceItemID", typeof(int));

        dtInvoiceItems.Columns.Add("ProductName", typeof(string)); 

        dtInvoiceItems.Columns.Add("InvoiceID", typeof(int));       

        dtInvoiceItems.Columns.Add("InvoiceReference", typeof(object));         

        dataSet.Tables.Add(dtInvoiceItems);

        // SEED table

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

        {

            var drInvoice = dtInvoices.NewRow();  

            drInvoice["InvoiceID"] = (i + 1);  

            drInvoice["Number"] = (useOld ? "Old_" : "") + "Number_" + i;  

            dtInvoices.Rows.Add(drInvoice);     

            for(int j = 0; j < countInvoiceItem; j++)

            {   

                var drInvoiceItem = dtInvoiceItems.NewRow();  

                drInvoiceItem["InvoiceItemID"] = (i * countInvoiceItem) + (j + 1);

                drInvoiceItem["ProductName"] = (useOld ? "Old_" : "") + "ProductName_" + i + "_" + j;

                drInvoiceItem["InvoiceReference"] = drInvoice;

                dtInvoiceItems.Rows.Add(drInvoiceItem);     

            }

        }

        return dataSet;

    }

    public static void SeedInvoices(int countInvoice, int countInvoiceItem) 

    {

        var dsGeneratedInvoices = GenerateInvoices(countInvoice, countInvoiceItem, true);

        var dtInvoices = dsGeneratedInvoices.Tables[0];

        var dtInvoiceItems = dsGeneratedInvoices.Tables[1];

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

        {

            connection.Open();

            // SAVE Invoices

            using (var bulk = new BulkOperation(connection))

            {

                // easy to customize

                bulk.DestinationTableName = "Invoices";

                bulk.ColumnMappings.Add("InvoiceID", ColumnMappingDirectionType.Output);

                bulk.ColumnMappings.Add("Number", ColumnMappingDirectionType.Input);

​

                bulk.BulkInsert(dtInvoices);

            }

​

            // SAVE InvoiceItems

            using (var bulk = new BulkOperation(connection))

            {   

                // SET foreign key value

                dtInvoiceItems.AsEnumerable().ToList().ForEach(x => x["InvoiceID"] = ((DataRow)x["InvoiceReference"])["InvoiceID"]);

​

                bulk.DestinationTableName = "InvoiceItems";

​

                bulk.BulkInsert(dtInvoiceItems);

            }

        }

    }

    public static void GenerateDatabase()

    {

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

        {

            connection.Open();

            using (var command = connection.CreateCommand())

            {

                command.CommandText = @"   

CREATE TABLE [Invoices]

(

    [InvoiceID] [INT] IDENTITY(1,1) NOT NULL,

    [Number] [NVARCHAR](MAX) NULL,

    CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED 

    (

        [InvoiceID] ASC

    )

)

​

CREATE TABLE [InvoiceItems]

(

    [InvoiceItemID] [INT] IDENTITY(1,1) NOT NULL,

    [ProductName] [NVARCHAR](MAX) NULL,

    [InvoiceID] [INT] NULL,

    CONSTRAINT [PK_InvoiceItems] PRIMARY KEY CLUSTERED 

    (

        [InvoiceItemID] ASC

    )

)

​

ALTER TABLE [InvoiceItems] WITH CHECK ADD  CONSTRAINT [FK_InvoiceItems_InvoiceID] FOREIGN KEY([InvoiceID])

REFERENCES [Invoices] ([InvoiceID])

 ";

                command.ExecuteNonQuery();

            }

        }

    }

}