CODEWITHSUNDEEP
entity-framework-core
Mark G
Mark G

Reputation: 3116

Using DbParameter w/ ExecuteSqlCommand in EF Core

Unfortunately there doesn't appear to be any documentation for ExecuteSqlCommand, I could only find documentation for FromSql. I'm able to successfully call a stored procedure using ExecuteSqlCommand with named parameters that returns rows affected.

However, I'm attempting to change the Direction property of one DbParameter to ParameterDirection.Output. This doesn't appear to work and I can find no examples anyway of this usage. The closest I saw was issue #3115 in the EF Core GitHub repo, but there was no usage of out parameters.

Of course I can use DbCommand to achieve this, but does ExecuteSqlCommand only use DbParameter for named parameters and direction is ignored? If so, then ideally this should be documented somewhere - maybe the Raw SQL Queries article can be expanded upon.

Upvotes: 1

Views: 2736

Answers (2)

Andrew Bennett
Andrew Bennett

Reputation: 350

Here is a way to do this in a database provider agnostic manner:

    using DbCommand dbCommand = context.Database
        .GetDbConnection()
        .CreateCommand();

    DbParameter outputParam = dbCommand.CreateParameter();
    outputParam.Direction = System.Data.ParameterDirection.Output;
    outputParam.DbType = System.Data.DbType.Int32;

Upvotes: 0

Mark G
Mark G

Reputation: 3116

This is a database provider specific issue since it doesn't work for MySQL, but works for SQL Server. Below is code that demonstrates use for the latter:

var outParam = new SqlParameter("@ParamOut", DbType.Int32)
    { Direction = ParameterDirection.Output };

ctx.Database.ExecuteSqlCommand("EXEC dbo.MyTest @ParamOut OUTPUT", outParam);

Upvotes: 1

Related Questions