Exec Stored Procedure with output parameter in Entity Framework Core 2

c# entity-framework-core sql-server stored-procedures

Question

I'm trying to refactor code to use dbContext.Database.ExecuteSqlCommandAsync instead of SqlCommand

List<SqlParameter> parameters = new List<SqlParameter>();

parameters.Add(new SqlParameter("@certificateTypeId", "TA"));
parameters.Add(new SqlParameter("@personId", 1m));
parameters.Add(new SqlParameter("@selfAssessment", xDocument.ToString()));
parameters.Add(new SqlParameter("@selfAssessmentVersion", 1m));
parameters.Add(new SqlParameter("@sponsorPersonId", 1m));

var outputParam = new SqlParameter("@certificationEventId", SqlDbType.Decimal, 10);
outputParam.Direction = ParameterDirection.Output;
parameters.Add(outputParam);

string sql = "[My Stored Proc] " + string.Join(" ", parameters.Select(ToEfString).ToArray());

// runtime value: [My Stored Proc] @certificateTypeId @personId @selfAssessment @selfAssessmentVersion @sponsorPersonId @certificationEventId OUT

var result = await dbContext.Database.ExecuteSqlCommandAsync(sql, parameters);

return ((SqlDecimal)outputParam.SqlValue).Value;

I'm getting this error:

SqlException: Incorrect syntax near @personId

What's wrong?

1
1
10/13/2017 9:05:48 AM

Accepted Answer

You need to add comma's in between the parameters:

[My Stored Proc] @certificateTypeId, @personId, @...
4
10/13/2017 10:57:40 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow