EF操作存储过程调用

1、执行返回数据列表型

    CREATE PROCEDURE [dbo].[SP_ManageForumGroupsPostRecycle]

@userId VARCHAR(128),  --用户ID

AS

BEGIN

SET NOCOUNT ON;

DECLARE @rowId INT 

SET @rowId=( @page - 1 ) * @pageSize;

----列表数据

SELECT 

G.Id,P.Title,G.PostId,A.NikeName,G.CreateDate,G.Note FROM GroupsPostRecycle AS G 

LEFT JOIN dbo.GroupsPost AS P ON g.PostId=p.Id

LEFT JOIN HTUser.dbo.AspNetUsers AS A ON A.Id=G.UserId

WHERE 1=1 And

  ( @userId IS NULL  ORp.GroupId IN(SELECT id FROM dbo.Groups WHERE G.UserId=@userId))

ORDER BY G.CreateDate DESC 
END
************************************************************************************************************************************************************ 

执行存储过程的方法
                    DBContext db=new  DBContext ();

                    SqlParameter[] sqlParms = new SqlParameter[1];

                    sqlParms[0] = new SqlParameter("@userId", userid);

   //结果集             

var result=(from  G in db.GroupsPostRecycles.sqlQuery("exec SP_ManageForumGroupsPostRecycle @userId",sqlParms )select G).tolist();

2、返回值
/*
存储过程
*/
Create proc SP_GetPostCount 
 @type int 
As
Begin
  select count(id) from post where type=@type
End 

执行
**********************************************************************************************

     DBContext db=new  DBContext ();

     SqlParameter[] sqlParms = new SqlParameter[1];

     sqlParms[0] = new SqlParameter("@type", type);
     
       Type t=typeof(int);
      var result=db.Database.SqlQuery(t,"exec SP_GetPostCount @type",sqlParms ).Cast<int>().First();


3、执行增删改

 

IF OBJECT_ID('SP_SendSystemMessageAndGroupOper') IS NOT NULL 

DROP PROCEDURE SP_SendSystemMessageAndGroupOper

GO

CREATE PROC SP_SendSystemMessageAndGroupOper

 @Ids VARCHAR(4000),

 @userid VARCHAR(225),

 @operType INT,

 @msg VARCHAR(255)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @sql VARCHAR(1000)

--插入日志

SET @sql='INSERT INTO dbo.GroupsOperLog(Title ,UserId ,GroupsID ,CreateDate , Content ,OperType)SELECT ''圈子''+GroupsName+'''+@msg+''','''+@userid+''',ID,GETDATE(),''圈子''+GroupsName+'''+@msg+''','+CONVERT(VARCHAR(10),@operType)+' FROM dbo.Groups WHERE id IN('+@ids+') AND UserId='''+@userid+''' AND [State]=0'

EXEC(@sql)

--插入系统消息

SET @sql='INSERT INTO dbo.MessageForSystem(Id ,UserId ,Title ,Content ,IsRead ,CreateDate)SELECT NEWID() ,G.UserId, ''圈子''+G1.GroupsName+'''+@msg+''', ''圈子''+G1.GroupsName+'''+@msg+''',0,getDATE() FROM dbo.GroupMembers AS G  LEFT JOIN dbo.Groups AS G1 ON G1.ID = G.GroupId WHERE G.GroupId IN('+@Ids+') and (G.ApplyState=1 or G.ApplyState=4)'

  EXEC(@sql)   

END

执行
*************************************************************************************************************************************************************

  SqlParameter[] sqlParms = new SqlParameter[4];

  sqlParms[0] = new SqlParameter("@Ids", strid);

  sqlParms[1] = new SqlParameter("@userid", userid);

   sqlParms[2] = new SqlParameter("@operType", "0");

  sqlParms[3] = new SqlParameter("@msg", "已解散");

   int count=await db.Database.ExecuteSqlCommandAsync("exec SP_SendSystemMessageAndGroupOper @Ids,@userid,@operType,@msg", sqlParms);