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?
2条答案
按热度按时间q9yhzks01#
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 whatORDER BY Date
would also do. The date is passed as parts then reconstructed in a complicated way instead of usingDATEFROMPARTS
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:
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.
EF Core 7
EF Core 7 offers the same functionality too, using SqlQuery :
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:And, of course if the table was an entity, we could just write a LINQ query.
Using a stored procedure
A stored procedure that returns the latest exchange rate for a specific date would look like this:
This can be called either with Dapper or EF Core 7:
In EF Core 7 :
2ul0zpep2#
Just Declare Your Stored Procedure Parameters with NULL Default Value.