EF Database.ExecuteSqlCommand、Database.SqlQuery用法整理

 

SqlQuery ()返回实体、集合

//无参存储过程

var List = db.Database.SqlQuery<实体名>("exec 存储过程名");

 

//有参存储过程

//使用SqlParameter传值可以避免SQL注入

var nameParam = new System.Data.SqlClient.SqlParameter
{
　　ParameterName = "@name",
　　Value = "张三"
};

var List = db.Database.SqlQuery<实体名>("exec 存储过程名 @name", nameParam );

 

//有参存储过程且有返回值

//存储过程
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



//代码实现

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;

 

 

 多个参数最好进行代码封装

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






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

　　

 

//sql语句

var List = db.Database.SqlQuery<实体名>(" 可执行的sql语句 ");

 

 

//sql语句单个返回值

var returnString = db.Database.SqlQuery<string>(" 可执行的sql语句 ").First();

 

 

ExecuteSqlCommand()  增、删、改 返回影响行数

//无参存储过程

var ChangeRows= db.Database.ExecuteSqlCommand("exec 存储过程名");

 

//有参存储过程

//使用SqlParameter传值可以避免SQL注入

var nameParam = new System.Data.SqlClient.SqlParameter
{
　　ParameterName = "@name",
　　Value = "张三"
};

var ChangeRows = db.Database.ExecuteSqlCommand("exec 存储过程名 @name", nameParam);

 

//sql语句

var ChangeRows = db.Database.ExecuteSqlCommand(" 可执行的sql语句 ");