ExecuteSqlCommand with output parameter

user_313913 0
Viewed 50150

I'm using Entity Framework in an ASP.NET MVC3 application and I'm trying to use the following code:

var token = "";
this.Database.ExecuteSqlCommand("exec dbo.MyUsp", new SqlParameter("token", token));

My stored proc signature is:

CREATE PROCEDURE MyUSP(@token varchar(10) OUT)
(...)

When I use this code I get an error saying that parameter "@token" was expected but not supplied.

How do I tell EF that the token parameter is for output?

asp.net-mvc-3 entity-framework asp.net-mvc
6 Answers
var db = new DBContext();
var outParam = new SqlParameter
{
    ParameterName = "@Param",
    DbType = System.Data.DbType.String,
    Size = 20,
    Direction = System.Data.ParameterDirection.Output
};
var r = db.Database.ExecuteSqlCommand("EXEC MyStoredProd @Param OUT",outParam );
Console.WriteLine(outParam.Value);

The main part i see everyone is missing, is the OUT keyword needed after @Param.

user_43286640

Below is what I do for Oracle using the DevArt driver. I have a package.proc called P_SID.SID_PGet that returns a single string value. The proc is:

PROCEDURE SID_PGet(io_SID OUT varchar2) is
Begin
   io_SID:=GetSID; -- GetSID just goes off and gets the actual value
End;

Below is how I call it and retrieve the SID value (I'm using this with EF 4.1 code first and this method is in the DbContext):

/// <summary>
/// Get the next SID value from the database
/// </summary>
/// <returns>String in X12345 format</returns>
public string GetNextSId()
{
    var parameter = new Devart.Data.Oracle.OracleParameter("io_SID", Devart.Data.Oracle.OracleDbType.VarChar, ParameterDirection.Output);
    this.Database.ExecuteSqlCommand("BEGIN P_SID.SID_PGet(:io_SID); END;", parameter);
    var sid = parameter.Value as string;

    return sid;
}
user_1773470
Related