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.
