Bulk Insert With Dapper - The Right One

One of my favorite online trainers, Tim Corey, introduced Dapper for me two years ago. It was only the last week when I stumbled upon his advanced Dapper video, and implemented the correct version for a bulk insert, using this micro-ORM.

Prerequisites:
- install Dapper nuget package;
- intermediate C#/with MS SQL database;

The task of our bulk insert will be to insert the following data into an MS SQL database:

a. Table structure in SQL, defined by the query:

CREATE TABLE [dbo].[InvoiceSummary](
[id] [int] IDENTITY(1,1) NOT NULL,
[Inv_Number] [int] NOT NULL,
[IssueDate] [datetime] NOT NULL,
[BillingCode] [nvarchar](100) NOT NULL,
[EntityName] [nvarchar](200) NOT NULL,
[BUName] [nvarchar](100) NOT NULL,
[Value] [float] NOT NULL,
[VAT] [float] NOT NULL,
[TotalValue] [float] NOT NULL,
[Currency] [nvarchar](50) NOT NULL,
[ExchangeRate] [float] NOT NULL,
[Entity_Id] [int] NOT NULL,
[BU_id] [int] NOT NULL,
[UpdatedBy] [nvarchar](100) NOT NULL,
[UpdatedAt] [datetime] NOT NULL,
[PeriodID] [nvarchar](50) NOT NULL,
[Comments] [nvarchar](200) NOT NULL,
[Comment] [nvarchar](200) NOT NULL,
[Status] [nvarchar](50) NOT NULL,
[AttentionOf] [nvarchar](300) NOT NULL,
[CC] [nvarchar](300) NULL,
[HFMCode] [nvarchar](100) NULL,
 CONSTRAINT [PK_InvoiceSummary] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[InvoiceSummary] ADD  CONSTRAINT [DF_InvoiceSummary_UpdatedAt]  DEFAULT (getdate()) FOR [UpdatedAt]
GO

b. There is a type defined, for the table-valued parameter, see SQL statement below:

CREATE TYPE [dbo].[BasicUDT] AS TABLE(

[id] [int] NOT NULL,

[Inv_Number] [int] NOT NULL,

[IssueDate] [datetime] NOT NULL,

[BillingCode] [nvarchar](100) NOT NULL,

[Entity_Id] [int] NOT NULL,

[BU_id] [int] NOT NULL,

[EntityName] [nvarchar](200) NOT NULL,

[BUName] [nvarchar](100) NOT NULL,

[Value] [float] NOT NULL,

[VAT] [float] NOT NULL,

[TotalValue] [float] NOT NULL,

[Currency] [nvarchar](50) NOT NULL,

[ExchangeRate] [float] NOT NULL,

[Comments] [nvarchar](200) NOT NULL,

[AttentionOf] [nvarchar](300) NOT NULL,

[CC] [nvarchar](300) NULL,

[HFMCode] [nvarchar](100) NULL,

[UpdatedBy] [nvarchar](100) NOT NULL,

[UpdatedAt] [datetime] NOT NULL,

[PeriodID] [nvarchar](50) NOT NULL,

[Comment] [nvarchar](200) NOT NULL,

[Status] [nvarchar](50) NOT NULL

)

GO

c. Stored procedure, to insert into our table, using table valued parameter:

CREATE procedure [dbo].[spInvoiceSummaryInsertSet]

@invsummary BasicUDT readonly

as

begin

set nocount on;

INSERT INTO [dbo].[InvoiceSummary]

           ([Inv_Number]

           ,[IssueDate]

           ,[BillingCode]

           ,[Value]

           ,[VAT]

           ,[TotalValue]

           ,[Currency]

           ,[ExchangeRate] 

           ,[Entity_Id]

           ,[BU_id]           

           ,[UpdatedBy], UpdatedAt

           ,[PeriodID]

           ,[Comment]

           ,[Status],

    [Comments]           

           ,[AttentionOf]

           ,[CC]

           ,[HFMCode],

   [EntityName],

   [BUName]

   )

SELECT [Inv_Number]

           ,[IssueDate]

           ,[BillingCode]

           ,[Value]

           ,[VAT]

           ,[TotalValue]

           ,[Currency]

           ,[ExchangeRate] 

           ,[Entity_Id]

           ,[BU_id]           

           ,[UpdatedBy], CURRENT_TIMESTAMP

           ,[PeriodID]

           ,[Comment]

           ,[Status],

    [Comments]           

           ,[AttentionOf]

           ,[CC]

           ,[HFMCode],

   [EntityName],

   [BUName]

from @invsummary

end;

d. Our original list in C# will contain the values we need to insert into the table:

// myInvList is a list of InvoiceSummary items defined by the class above at a)
InvoiceSummaryInsertSet(myInvList)

e. Our list is getting inserted into SQL Server DB using the table valued parameter

        public void InvoiceSummaryInsertSet(List<InvoiceSummary> myInvList)        {

            var dt = new ExcelServices().ConvertToDataTable(myInvList);

            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                var p = new
                {
                    invsummary = dt.AsTableValuedParameter("BasicUDT")
                };

                connection.Execute("dbo.spInvoiceSummaryInsertSet ", p, commandType: CommandType.StoredProcedure);
            }
        }

f. helper function to transform a list into datatable, used above, is implemented as below:

        public System.Data.DataTable ConvertToDataTable<T>(IList<T> data)

        {

            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

            System.Data.DataTable table = new System.Data.DataTable();

            foreach (PropertyDescriptor prop in properties)

            {

                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

            }

            foreach (T item in data)

            {

                DataRow row = table.NewRow();

                foreach (PropertyDescriptor prop in properties)

                {

                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;             

                }

                table.Rows.Add(row);

            }

            return table;

        }

That's all, it works ! Hopefully it helped some of you.