I'm trying to use stored procedures with new Entity Framework Core. I need to start new project soon which will be ASP.Net 5, but not sure if Entity Framework will fit for the job. The application will be triggering several stored procedures per minute and I need output parameters. Will EF be good for this or should I use ADO.Net?
I have tried FromSql and database.ExecuteSqlCommand but no luck.
using (AppDbContext db = factory.Create())
{
var in1 = new SqlParameter
{
ParameterName = "ParamIn1",
DbType = System.Data.DbType.Int64,
Direction = System.Data.ParameterDirection.Input
};
var in2 = new SqlParameter
{
ParameterName = "ParamIn2",
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var out1 = new SqlParameter
{
ParameterName = "ParamOut1",
DbType = System.Data.DbType.Int64,
Direction = System.Data.ParameterDirection.Output
};
var out2 = new SqlParameter
{
ParameterName = "ParamOut2",
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Output
};
var result = db.Database.ExecuteSqlCommand("exec spTestSp", in1, in2, out1, out2);
}
It should work, but I believe you also need to include the parameter names and the OUT
keyword in the command statement
var sql = "exec spTestSp @ParamIn1, @ParamIn2, @ParamOut1 OUT, @ParamOut2 OUT";
var result = db.Database.ExecuteSqlCommand(sql, in1, in2, out1, out2);
var out1Value = (long) out1.Value;
var out2Value = (string) out2.Value;
Full example of the solution as described above by @Nkosi and partially by @Whistler for the ease of future readers!
In my example, I was trying to execute a stored procedure existed in my database to get specific path.
string tableName = "DocumentStore";
string path;
var in1 = new SqlParameter
{
ParameterName = "TableName",
Value = tableName,
Size = Int32.MaxValue,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var out1 = new SqlParameter
{
ParameterName = "Path",
DbType = System.Data.DbType.String,
Size = Int32.MaxValue,
Direction = System.Data.ParameterDirection.Output
};
//_context is DbContext Object
_context.Database.ExecuteSqlCommand("EXEC GetFileTableRootPath @TableName, @Path OUT", in1,out1);
path = out1.Value.ToString();