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!