// Bulk Operations

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

​

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

        var invoices = GenerateInvoices(5, 3);

        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.AutoMapOutputIdentity = true;

​

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

            }

            // 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";

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

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

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

            }

        }

    }

    public static List<Invoice> GenerateInvoices(int countInvoice, int countInvoiceItem) 

    {

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

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

        {

            var invoice = new Invoice() { Number = "Number_" + i, Items = new List<InvoiceItem>() };

            list.Add(invoice);

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

            {

                invoice.Items.Add(new InvoiceItem() { ProductName = "ProductName_" + i + "_" + j });

            }

        }

        return list;

    }

    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; }

    }

}