DEV Community

Zoltan Halasz
Zoltan Halasz

Posted on

Bulk Insert With Dapper - with Table Type Parameter

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.

Material for study: https://www.youtube.com/watch?v=eKkh5Xm0OlU&t=12s; this was my inspiration for the tutorial.

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. define the Invoice Summary class

    public class InvoiceSummary
    {
        public int id { get; set; }
        public int Inv_Number { get; set; }
        public DateTime IssueDate { get; set; }
        public string BillingCode { get; set; }
        public int Entity_Id { get; set; }
        public int BU_id { get; set; }
        public string EntityName { get; set; }
        public string BUName { get; set; }
        public float Value { get; set; }
        public float VAT { get; set; }
        public float TotalValue { get; set; }
        public string Currency { get; set; }
        public float ExchangeRate { get; set; }
        public string Comments { get; set; }
        public string AttentionOf { get; set; }
        public string CC { get; set; }
        public string HFMCode { get; set; }
        public string UpdatedBy { get; set; }
        public DateTime UpdatedAt { get; set; }
        public string PeriodID { get; set; }
        public string Comment { get; set; }
        public string Status { get; set; }
    }

Enter fullscreen mode Exit fullscreen mode

b. Table structure in SQL, defined by the query below. This will be the target of the bulk insert.

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
Enter fullscreen mode Exit fullscreen mode

c. There is a type defined in the database, 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
Enter fullscreen mode Exit fullscreen mode

d. Stored procedure, doing the bulk 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;
Enter fullscreen mode Exit fullscreen mode

e. 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);
Enter fullscreen mode Exit fullscreen mode

f. Our list is getting inserted into SQL Server DB using the table valued parameter, using the below bulk insert function

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);
            }
        }
Enter fullscreen mode Exit fullscreen mode

g. helper function to transform a list into datatable, used above, is implemented as below: (source: internet)

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;

        }
Enter fullscreen mode Exit fullscreen mode

Top comments (3)

Collapse
 
mikependon profile image
Mike Pendon (Zym)

Great post! Btw, you are not doing a real bulk-insert operation here. What you are doing is a batch-insert through UDT that accepts the list of the data entities. In which, there is a performance flaw of INSERT-SELECT when compared to using the BCP or SqlBulkCopy class. In anyway, this is a much better approach when compared to your first post related to this. Cheers!

Collapse
 
zoltanhalasz profile image
Zoltan Halasz

It is more than sufficient for my purposes. Thanks for the remark!

Collapse
 
zoltanhalasz profile image
Zoltan Halasz

Thank you, I appreciate!