Entity FrameWork use Database.SqlQuery execute a storedprocedure and return to the parameter

 

Currently , EF support for stored procedures are not perfect . The following problems :

EF does not support stored procedure returns multi-table query result set.

EF supports only return back all the fields in a table to be converted into the corresponding entities. Can not support the return portion field situation.

Although you can import properly stored procedure that returns a scalar value , but not for us to automatically generate the corresponding entity . cs code, we still can not be called directly in code or use a scalar stored procedure

EF does not directly support stored procedures Output type parameter.

some other issues .

Today we use the EF way to execute sql statement execute a stored procedure and get OutPut value.

 

First create a stored procedure :

 

 

  

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

 

 

then write EF invoke methods :

 

 

  

 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 encapsulation 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; 
            } 
        } 

 

 

invocation :

 

 

  

 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(); 

 

 

course there are other ways , I just think this is simple and convenient , compared to adding solid model , it should be a lot easier !