// Bulk Operations

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

​

// @nuget: Z.BulkOperations

​

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.DataSetExtensions;

using System.Data.SqlClient;

using System.Linq;

using Z.BulkOperations;

public class Program

{

    public static void Main()

    {

        GenerateDatabase();

        var dsGeneratedInvoices = GenerateInvoices(5, 3);       

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

​

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

            }

            // 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 DataSet GenerateInvoices(int countInvoice, int countInvoiceItem) 

    {

        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["Number"] = "Number_" + i;  

            dtInvoices.Rows.Add(drInvoice);     

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

            {   

                var drInvoiceItem = dtInvoiceItems.NewRow();  

                drInvoiceItem["ProductName"] = "ProductName_" + i + "_" + j;

                drInvoiceItem["InvoiceReference"] = drInvoice;

                dtInvoiceItems.Rows.Add(drInvoiceItem);     

            }

        }

        return dataSet;

    }

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

            }

        }

    }

}