Update SqlSever with optional/condition parameters from C#/Dapper

I have an application where I take data from an API and sends it to my SQL Server database. Sometimes I just want to update one certain column in the database from my Application with new data.

This is the code I run in the application to send data right now

public void TestinPE(long? instID, float? Value)
{
    using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("BorsdataDB")))
    {
        //Person newPerson = new Person { ModelName = Variable, LastName = lastName, EmailAddress = emailAddress, PhoneNumber = phoneNumber };
        List<KpiReturn> updateKpi = new List<KpiReturn>();
        updateKpi.Add(new KpiReturn { instID = instID, psValue = Value });
        connection.Execute("dbo.UpdateRec @instID,@psValue", updateKpi);
    }
}

And the procedure I'm calling looks like this

ALTER PROCEDURE [dbo].[UpdateRec]
    @instID int,
    @psValue float = null,
    @peValue float = null
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE StockData
    SET peValue = ISNULL(@peValue, peValue), 
        psValue = ISNULL(@psValue, psValue)
    WHERE instID = @instID
END

Let's say now that I want to update psValue it works great, but if I instead try to update peValue, and I change my C# code to

updateKpi.Add(new KpiReturn { instID = instID, peValue = Value });
connection.Execute("dbo.UpdateRec @instID,@peValue", updateKpi);

the psValue in my SQL Server updates, it always update the first parameter in the query in this case @psValue float = null.

It works fine if I run the query in SQL as shown below, it ignores psValue, but updates peValue. But I can get this to happen when trying to pass parameters from C#

exec dbo.UpdateRec @instID = 2, @peValue = 500

Any hints for me on this one?

Solution

It works fine if I run the query in SQL as shown below, it ignores psValue, but updates peValue. But I can get this to happen when trying to pass parameters from C#

exec dbo.UpdateRec @instID = 2, @peValue = 500

These code snippets are passing parameter values by ordinal instead of name so the proc code will see the values as @instID and @psValue regardless of the provided parameter name:

updateKpi.Add(new KpiReturn { instID = instID, psValue = Value });
connection.Execute("dbo.UpdateRec @instID,@psValue", updateKpi);

updateKpi.Add(new KpiReturn { instID = instID, peValue = Value });
connection.Execute("dbo.UpdateRec @instID,@peValue", updateKpi);

One way to fix it is to use named parameter syntax instead of the ordinal method, similarly to script you ran in SSMS:

updateKpi.Add(new KpiReturn { instID = instID, psValue = Value });
connection.Execute("dbo.UpdateRec @instID=@instID, @psValue=@psValue", updateKpi);

updateKpi.Add(new KpiReturn { instID = instID, peValue = Value });
connection.Execute("dbo.UpdateRec @instID=@instID, @peValue=@peValue", updateKpi);