Go The Entity framework uses database.sqlquery<t> to execute stored procedures and return parameters

This article turns from; http://www.cnblogs.com/xchit/p/3334782.html

Currently, EF's support for stored procedures is not perfect. The following issues exist:
EF does not support a stored procedure to return a result set of a multi-table union query.
EF only supports returning all fields that return a table for conversion to the corresponding entity. There is no way to support returning partial fields.
Although a stored procedure that returns a scalar value can be imported normally, it does not automatically generate the corresponding entity for us. CS code, we still can't call or use scalar stored procedures directly in code
EF cannot directly support parameters of the output type in stored procedures.
Some other problems.
Today we use the way EF executes the SQL statement to execute the stored procedure and get the value of the output.

Create a new stored procedure first:

　　　　

?

Create PROCEDURE proc_testEF   (     @id int,     @voteCount int OUTPUT --返回值  )   AS  BEGIN    SELECT @voteCount = COUNT(*)     FROM ConfirmItem    WHERE ConfirmItemID = @id;     select * from ConfirmItem where [email protected];  END

Then write the EF call method:

　　

?

using (DBEntities context = new DBEntities())           {               var idParam = new System.Data.SqlClient.SqlParameter               {                   ParameterName = "@id",                   Value = 1               };               var votesParam = new System.Data.SqlClient.SqlParameter               {                   ParameterName = "@voteCount",                   Value = 0,                   Direction = ParameterDirection.Output               };               var results = context.Database.SqlQuery<Models.ConfirmItem>(                   "proc_testEF @id, @voteCount out",                    idParam,                   votesParam);               var person = results.Single();               var votes = (int)votesParam.Value; //得到OutPut类型值               return votes;           }

Test several times, no problem; Finally, I encapsulate the method:

　　

?

/// <summary>  /// 执行原始SQL命令  /// </summary>  /// <param name="commandText">SQL命令</param>  /// <param name="parameters">参数</param>  /// <returns>影响的记录数</returns>  public Object[] ExecuteSqlNonQuery<T>(string commandText, params Object[] parameters){    using (DBEntities context = new DBEntities())    {        var results = context.Database.SqlQuery<T>(commandText, parameters);        results.Single();        return parameters;    }}

Call Mode:

　　

?

var idParam = new System.Data.SqlClient.SqlParameter               {                   ParameterName = "@id",                   Value = 1               };               var votesParam = new System.Data.SqlClient.SqlParameter               {                   ParameterName = "@voteCount",                   Value = 0,                   Direction = ParameterDirection.Output               };               System.Data.SqlClient.SqlParameter[] parm = {                                                            idParam,                                                           votesParam                                                           };               parm = (System.Data.SqlClient.SqlParameter[])new BLL.Usual.ConfirmItemManager().ExecuteSqlNonQuery<Models.ConfirmItem>("proc_testEF @id, @voteCount out", parm);                             string s = parm[1].Value.ToString();

Of course there are other ways, just feel that this simple and convenient, than to add a solid model, it is much more convenient!

Go The Entity framework uses database.sqlquery<t> to execute stored procedures and return parameters