// Bulk Operations

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

​

// @nuget: Z.BulkOperations

// @nuget: System.Data.SqlClient

​

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 invoices = GenerateInvoices(5, 3);

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

        {

            connection.Open();

            // SHOW data

            ShowData(connection, "Before");

​

            // SAVE Invoices

            using (var bulk = new BulkOperation<Invoice>(connection))

            {

                // easy to customize

                bulk.DestinationTableName = "Invoices";

                bulk.AutoMapOutputIdentity = true;

​

                bulk.BulkMerge(invoices);

            }

​

            // SAVE InvoiceItems

            using (var bulk = new BulkOperation<InvoiceItem>(connection))

            {   

                // SET foreign key value            

                invoices.ForEach(x => x.Items.ForEach(y => y.InvoiceID = x.InvoiceID));

                bulk.DestinationTableName = "InvoiceItems";

​

                bulk.BulkMerge(invoices.SelectMany(x => x.Items).ToList());

            }

            // 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 = "1 - Invoices " + text;

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

​

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

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

        }

    }

    public static List<Invoice> GenerateInvoices(int countInvoice, int countInvoiceItem, bool useOld = false) 

    {

        List<Invoice>  list = new List<Invoice>();

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

        {

            var invoice = new Invoice() { InvoiceID = (i + 1), Number = (useOld ? "Old_" : "") + "Number_" + i, Items = new List<InvoiceItem>() };

            list.Add(invoice);

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

            {

                invoice.Items.Add(new InvoiceItem() { InvoiceItemID = (i * countInvoiceItem) + (j + 1), ProductName = (useOld ? "Old_" : "") + "ProductName_" + i + "_" + j });

            }

        }

        return list;

    }

    public static void SeedInvoices(int countInvoice, int countInvoiceItem) 

    {

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

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

        {

            connection.Open();

        // SAVE Invoices

            using (var bulk = new BulkOperation<Invoice>(connection))

            {

                // easy to customize

                bulk.DestinationTableName = "Invoices";

                bulk.ColumnOutputExpression = invoice => invoice.InvoiceID;

                bulk.ColumnInputExpression = invoice => new

                {

                    invoice.InvoiceID,

                    invoice.Number

                };

​

                bulk.BulkInsert(invoices);

            }

​

            // SAVE InvoiceItems

            using (var bulk = new BulkOperation<InvoiceItem>(connection))

            {   

                // SET foreign key value            

                invoices.ForEach(x => x.Items.ForEach(y => y.InvoiceID = x.InvoiceID));

                bulk.DestinationTableName = "InvoiceItems";

​

                bulk.BulkInsert(invoices.SelectMany(x => x.Items).ToList());

            }

        }

    }

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

            }

        }

    }

    public class Invoice

    {

        public int InvoiceID { get; set; }

        public string Number { get; set; }

        public List<InvoiceItem> Items { get; set; }

    }

    public class InvoiceItem

    {

        public int InvoiceItemID { get; set; }

        public string ProductName { get; set; }

        public int InvoiceID { get; set; }

    }

}