How do I use Dapper to insert a list<myobject> as parameter to a stored procedure-CodePudding

I have a SQL Server table with 10 columns:

CREATE TABLE [bank].[CommonPostingsFromBankFiles]
(
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [BankRegistrationNumber] [INT] NOT NULL,
    [BankAccountNumber] [BIGINT] NOT NULL,
    [BankName] [NVARCHAR](50) NULL,
    [BankAccount] [NVARCHAR](50) NULL,
    [PostingAmount] [DECIMAL](18, 2) NOT NULL,
    [PostingDate] [DATE] NOT NULL,
    [Primo] [CHAR](1) NULL,
    [PostingText] [NVARCHAR](100) NULL,
    [HideThisRecord] [BIT] NULL,

    CONSTRAINT [PK_CommonPostingsFromBankFiles] 
        PRIMARY KEY CLUSTERED ([BankRegistrationNumber] ASC,
                               [BankAccountNumber] ASC,
                               [PostingAmount] ASC,
                               [PostingDate] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)

and a stored procedure that takes 7 parameters:

ALTER PROCEDURE [bank].[spInsertCommonPostings]
    (@BankRegistrationNumber INT,
     @BankAccountNumber BIGINT,
     @BankName NVARCHAR(50),
     @PostingAmount DECIMAL(18, 2),
     @PostingDate DATE,
     @Primo CHAR(1),
     @PostingText NVARCHAR(100))
AS
BEGIN
    IF NOT EXISTS (SELECT *
                   FROM bank.CommonPostingsFromBankFiles
                   WHERE BankRegistrationNumber = @BankRegistrationNumber
                     AND BankAccountNumber = @BankAccountNumber
                     AND BankName = @BankName
                     AND PostingAmount = @PostingAmount
                     AND PostingDate = @PostingDate)
        INSERT INTO bank.CommonPostingsFromBankFiles (BankRegistrationNumber, BankAccountNumber,
                                                      BankName, PostingAmount,
                                                      PostingDate, Primo, PostingText)
        VALUES (@BankRegistrationNumber, @BankAccountNumber,  
                @BankName, @PostingAmount, 
                @PostingDate, @Primo, @PostingText);
END;

What I want is to use Dapper to write a List<Postings> to the table using the stored procedure.

I have searched and searched but found no example that helped me.

If I do a

connection.Execute(sql: "spMyStoredProc", MyList, commandType: CommandType.StoredProcedure);

I get an error

Procedure or function spMyStoredProc has too many arguments specified

If I replace the name of the stored procedure with the sql from the stored procedure and set CommandType to Text it works as expected.

Could anybody please post me an example showing how to insert my list using my stored procedure.

Thanks, Steffen

CodePudding user response：

Simplistically, and assuming the names of the properties in your c# object are identical to the names of the parameters in your stored proc, you could:

MyList.ForEach(x => connection.Execute(sql: "spMyStoredProc", x, commandType: CommandType.StoredProcedure));

If the param/props aren't aligned it may be simplest to provide an anonymous type populated with values from x that covers them

MyList.ForEach(x => connection.Execute(
  sql: "spMyStoredProc",
  new { BankRegistrationNumber = x.BankRN, ... }, 
  commandType: CommandType.StoredProcedure
));

For a list of 10 objects it will invoke the proc 10 times; it's not the fastest way to do it, but I don't think you've stated any particular goals re performance etc.