How can a stored procedure, that is called in C#, correctly insert data without giving all optional parameters?

Question

I'm trying to insert data with a stored procedure into my SQL Server database, from a C# program using Dapper. When I try to do this, I do not get any error, but some data is unfortunately inserted into the wrong column.

The database consists of a lot of tables and columns, hence the use of a stored procedure to insert data correctly. I have extensively tested the stored procedure in SQL Server itself and know for sure that the data is placed correctly when directly executing SQL code on the server.

Here is the code calling the stored procedure, and a tiny part of the stored procedure script.

public void AddIndividual(string genus, string species, string collection)
{
    using (IDbConnection connection = new SqlConnection(connectionString: Helper.CnnVal("Rapento.Properties.Settings.Database1ConnectionString")))
    {
        List<Individual> individual = new List<Individual>();
        individual.Add(new Individual { GivenGenusName = genus, GivenSpeciesName = species, GivenCollectionName = collection });
        connection.Execute("dbo.AddIndividual @GivenGenusName, @GivenSpeciesName, @GivenCollectionName", individual);
    }
}


CREATE PROCEDURE AddIndividual
    @GivenGenusName varchar(255) = null,
    @GivenSpeciesName varchar(255) = null,
    @GivenDeterminedBy varchar(255) = null,
    @GivenDeterminationDate varchar(255) = null,
    ....
    @GivenCollectionName varchar(255) = null,

In the database, I can see that GenusName and SpeciesName are inserted correctly, but CollectionName is inserted into the column DeterminedBy. This is not how it's written in the SQL script. It comes to my attention that DeterminedBy is the third parameter in the script, which makes me think that the parameters are not passed on based on the names I give them, but based on their position (the order I in which pass them).

So my question is: How can I insert this parameter in the correct column without having to pass every single optional parameter in the procedure?

I hope I gave you all the info you need. Thanks!

Answer 1

When executing a stored procedure as you are above:

connection.Execute("dbo.AddIndividual @GivenGenusName, @GivenSpeciesName, @GivenCollectionName", individual);

... what you are doing is supplying a list of values, not a list of parameters. If you are skipping parameters in the stored procedure, you need to specify the names of the parameters you are filling, like this (Assumption: The parameter names are the same as your variable names):

connection.Execute("dbo.AddIndividual @GivenGenusName = @GivenGenusName, @GivenSpeciesName = @GivenSpeciesName, @GivenCollectionName = @GivenCollectionName", individual);

If you just supply values, they fill in the parameters starting with the first parameter. This is what your query is doing. The primary bit of confusion here is that the values you have set up are handled as variables, not parameters.