I add sql parameters but I get the error "not added"

Question

I get this error:

Procedure or function 'GetCurrencyAtDate' expects parameter '@soruceCurrencyId', which was not supplied.

This is my stored procedure:

ALTER PROCEDURE [dbo].[GetCurrencyAtDate]
    @soruceCurrencyId int,
    @targetCurrencyId int,
    @year int,
    @month int,
    @day int,
    @result decimal OUTPUT
AS
BEGIN
    DECLARE @targetDate DATETIME
    SET @targetDate = DATEADD(DAY, @day - 1, DATEADD(MONTH, @month - 1, DATEADD(YEAR, @year - 1900, 0)))

    SELECT TOP 1 @result = Currency
    FROM KurTable
    WHERE SourceKur = @soruceCurrencyId
      AND TargetKur = @targetCurrencyId
      AND Date <= @targetDate
    ORDER BY ABS(DATEDIFF(DAY, Date, @targetDate));
END

I am using Entity Framework Core and wrote command via DbContext functions:

using (var context = new EfDataContext())
{
    var date = DateTime.Now;
    var year = date.Year;
    var month = date.Month;
    var day = date.Day;

    var resultParameter = new SqlParameter
                {
                    ParameterName = "@result",
                    Direction = ParameterDirection.Output,
                    SqlDbType = SqlDbType.Decimal,
                };

    var command = context.Database.GetDbConnection().CreateCommand();
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "GetCurrencyAtDate";

    command.Parameters.Add(new SqlParameter("@sourceCurrencyId", SqlDbType.Int) { Value = sourceCurrency });
    command.Parameters.Add(new SqlParameter("@targetCurrencyId", SqlDbType.Int) { Value = targetCurrency });
    command.Parameters.Add(new SqlParameter("@year", SqlDbType.Int) { Value = year });
    command.Parameters.Add(new SqlParameter("@month", SqlDbType.Int) { Value = month });
    command.Parameters.Add(new SqlParameter("@day", SqlDbType.Int) { Value = day });
    command.Parameters.Add(resultParameter);

    context.Database.OpenConnection();

    var result = await command.ExecuteScalarAsync();
    currencyValue = Convert.ToDecimal(result);
}

Where is the problem?

Answer 1

There are several issues with this code that would make it very slow even if it worked - it misuses EF Core only to execute ADO.NET code, it forgets to close the connection, the stored procedure itself is overcomplicated, and eg uses ABS(DATEDIFF(DAY, Date, @targetDate)) to do what ORDER BY Date would also do. The date is passed as parts then reconstructed in a complicated way instead of using DATEFROMPARTS

All of the options below can be used with a stored procedure but in this case, it's just not needed.

To actually get the latest exchange rate from a table all you'd need using eg Dapper to reduce the boilerplate would be this:

var sql=@" SELECT TOP 1 Currency
    FROM KurTable
    WHERE SourceKur = @fromCur
        AND TargetKur = @toCur
        AND Date <= @date
    ORDER BY Date DESC";

using (var con=new SqlConnection(connectionString))
{
    var rate=con.ExecuteScalar<decimal>(sql,new { 
        fromCur=sourceCurrency,
        toCur=targetCurrency,
        date=DateTime.Today
    });
}

That's all. Dapper will create a SqlCommand with parameters based on the anonymous type properties, using the same names and types, open the connection, execute the query, return the result and close the connection. It will also case the command and mappings so it doesn't have to rebuild it next time.

In this example the connection is created in a using block so it gets disposed once the block exits.

You can use a DbContext's connection directly if you want too.

var con=context.Database.GetDbConnection();
var rate=con.ExecuteScalar<decimal>(sql,new { 
        fromCur=sourceCurrency,
        toCur=targetCurrency,
        date=DateTime.Today
});

EF Core 7

EF Core 7 offers the same functionality too, using SqlQuery :

var rate=context.Database
    .SqlQuery<decimal>($@"SELECT Currency
        FROM KurTable
        WHERE SourceKur = {sourceCurrency}
            AND TargetKur = {targetCurrency}
            AND Date <= {targetDate}
        ORDER BY Date DESC")
    .FirstOrDefault();

This isn't a string interpolation operation. The formattable string is used to generate parameters and fill their values.

SqlQuery returns an IQueryable<> which means we could use OrderByDescending to specify the sort order too:

var rate=context.Database
    .SqlQuery<decimal>($@"SELECT Currency,Date
        FROM KurTable
        WHERE SourceKur = {sourceCurrency}
            AND TargetKur = {targetCurrency}
            AND Date <= {targetDate}")
    .OrderByDescending(c=>c.Date)
    .Select(c=>c.Currency)
    .FirstOrDefault();

And, of course if the table was an entity, we could just write a LINQ query.

var rate=context.ExchangeRates
                .Where(xr=>xr.SourceCurrency==sourceCurrency &&
                           xr.TargetCurrency==targetCurrency &&
                           xr.Date <= targetDate)
                .OrderByDescending(xr=>xr.Date)
                .Select(xr=>xr.Currency)
                .FirstOrDefault();

Using a stored procedure

A stored procedure that returns the latest exchange rate for a specific date would look like this:

ALTER PROCEDURE [dbo].[GetCurrencyAtDate]
    @sourceCurrencyId INT,
    @targetCurrencyId int,
    @targetDate DATE
AS
BEGIN
    SELECT TOP 1 Currency
    FROM KurTable
    WHERE SourceKur = @soruceCurrencyId
        AND TargetKur = @targetCurrencyId
        AND Date <= @targetDate
    ORDER BY Date DESC
END

This can be called either with Dapper or EF Core 7:

var rate= con.ExecuteScalar<decimal>("GetCurrencyAtDate",
    new { 
        sourceCurrencyId =sourceCurrency,
        targetCurrencyId =targetCurrency,
        targetDate =DateTime.Today
    },
    commandType: CommandType.StoredProcedure);

In EF Core 7 :

var rate=context.Database
    .SqlQuery<decimal>($"exec GetCurrencyAtDate 
    @sourceCurrencyID={sourceCurrency}, 
    @targetCurrencyId= {targetCurrency}, 
    @targetDate = {targetDate}")
    .FirstOrDefault();