Execute Stored Procedure multiple times using Dapper?

Question

I want Dapper to execute a Stored Procedure once for every item's parameters.

I am looping the collection to build parameters. And I don't want to place the execute method inside the loop.

How can I achieve this? Thanks in advance.

public static int SaveData3() 
        {
            var list = new List();
            var p1 = new Person() { Id = 20, FirstName = "Michiel", LastName = "De Ruyter" };
            var p2 = new Person() { Id = 21, FirstName = "Jacob", LastName = "Jakobsson" };
            list.Add(p1);
            list.Add(p2);

            var queryParameters = new DynamicParameters();
            foreach (var item in list)
            {
                queryParameters.Add("@id", item.Id);
                Log.Information("added {0} as parameter", item.Id);
                queryParameters.Add("@first_name", item.FirstName);
                Log.Information("added {0} as parameter", item.FirstName);
                queryParameters.Add("@last_name", item.LastName);
                Log.Information("added {0} as parameter", item.LastName);
            }

            using (IDbConnection connection = new OracleConnection(GetConnectionString()))
            {
                try
                {
                    var affectedRows = connection.Execute("kwc_test_person_procedure",
                    queryParameters,
                    commandType: CommandType.StoredProcedure
                    );
                    Log.Information("Executed procedure to store data. Rows affected: {0}", affectedRows);
                    return affectedRows;
                }
                catch (Exception ex)
                {
                    Log.Error(ex.Message);
                    Log.Error(ex.StackTrace);
                    throw;
                }

            }
        }

When I ran this, only the last object (Jacob) is stored in the database.

2021-06-22 13:52:58.225 +02:00 [INF] added 20 as parameter 2021-06-22 13:52:58.287 +02:00 [INF] added Michiel as parameter 2021-06-22 13:52:58.291 +02:00 [INF] added De Ruyter as parameter 2021-06-22 13:52:58.293 +02:00 [INF] added 21 as parameter 2021-06-22 13:52:58.296 +02:00 [INF] added Jacob as parameter 2021-06-22 13:52:58.299 +02:00 [INF] added Jakobsson as parameter 2021-06-22 13:52:58.817 +02:00 [INF] Executed procedure to store data. Rows affected: 1

I have also tried to follow the "Many" example from: https://dapper-tutorial.net/execute#example---execute-stored-procedure

try
                {
                    var persons = new Person[] {
                    new Person {Id= 41, FirstName = "homeless", LastName="dude"},
                    new Person {Id= 42, FirstName = "hungry", LastName="person"}
                };

                    int rowsAffected = connection.Execute("kwc_test_person_procedure", persons, commandType: CommandType.StoredProcedure);
                    Log.Information("Executed procedure to store data. Rows affected: {0}", rowsAffected);
                    return rowsAffected;
                }

But then I get:

ORA-06550 PLS-00306 (wrong number or types of arguments in call ...)

Marc_Gravell_23354 · Answer

When using stored procedures, Dapper can't see the signature, so it assumes that all reachable members should be added. I'm guessing that Person has other properties, which is causing this failure.

try, then:

int rowsAffected = connection.Execute("kwc_test_person_procedure",
    persons.Select(x => new { x.Id, x.FirstName, x.LastName }),
    commandType: CommandType.StoredProcedure);

This creates a per-person projection of just the Id, FirstName and LastName which (it seems) should match the stored procedure parameters. You can also use this approach to give them different names, if needed, for example:

    persons.Select(x => new { id = x.Id, fName = x.FirstName, lName = x.LastName }),

Execute Stored Procedure multiple times using Dapper?

1 Answers