Bulk Insert With Dapper - with Table Type Parameter
Tags: dotnet, database, sql, tutorial
Published: 2019-11-29 12:34:52

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** 
```csharp
    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; }
    }

```

**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
```
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
```
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;
```
e. Our original list in C# will contain the values we need to insert into the table:
```csharp
// myInvList is a list of InvoiceSummary items defined by the class above at a)
InvoiceSummaryInsertSet(myInvList);
```
f. Our list is getting inserted into SQL Server DB using the table valued parameter, using the below **bulk insert function**
```csharp
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);
            }
        }
```
g. **helper function to transform a list into datatable**, used above, is implemented as below: (source: internet)
```csharp
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;

        }
```