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; } } 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:
// 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
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)
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; } 
Top comments (3)
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!
It is more than sufficient for my purposes. Thanks for the remark!
Thank you, I appreciate!