使用EF来调用存储过程

public List<GetStaff_Result> listStaff(int pageSize, int pageIndex, int DepId, string SearchName, DateTime Date_temp, ref int pageCount) { List<GetStaff_Result> list = new List<GetStaff_Result>(); using (CustomerSysEntities2 cus = new CustomerSysEntities2()) { //将内容存储到list中 list = cus.GetStaff(pageSize, pageIndex, DepId, SearchName, Date_temp).ToList(); } pageCount = (int)list[0].resultcount;//总页数 //分配页数 if (pageCount % pageSize == 0) { pageCount = pageCount / pageSize; return list; } else { pageCount = pageCount / pageSize + 1; return list; } }

 List<SqlParameter> paramArray = new List<SqlParameter>();//申明一个集合
                        paramArray.Add(new SqlParameter("@MeterNumber", MeterNumber));//输入参数
                        paramArray.Add(new SqlParameter("@LastMeterPointer", LastMeterPointer));//输入参数
                        paramArray.Add(new SqlParameter("@ThisMeterPointer", ThisMeterPointer));//输入参数
                        paramArray.Add(new SqlParameter("@ChargeDate", time));//输入参数
                        SqlParameter sqlParm4 = new SqlParameter("@GasUserID_EX", SqlDbType.VarChar, 50);
                        sqlParm4.Direction = ParameterDirection.Output;//输出参数
                        SqlParameter sqlParm5 = new SqlParameter("@CurrentPrice", SqlDbType.VarChar, 50);
                        sqlParm5.Direction = ParameterDirection.Output;//输出参数
                        SqlParameter sqlParm6 = new SqlParameter("@Syl", SqlDbType.VarChar, 50);
                        sqlParm6.Direction = ParameterDirection.Output;//输出参数
                        SqlParameter sqlParm7 = new SqlParameter("@RetGasUserID", SqlDbType.VarChar, 50);
                        sqlParm7.Direction = ParameterDirection.Output;//输出参数
                        paramArray.Add(sqlParm4);
                        paramArray.Add(sqlParm5);
                        paramArray.Add(sqlParm6);
                        paramArray.Add(sqlParm7);                      
                        try
                        {
                            db（上下文类）.Database.ExecuteSqlCommand("EXEC [XAO_Meter_InsertChaoBiao] @MeterNumber,@LastMeterPointer,@ThisMeterPointer,@ChargeDate,@GasUserID_EX out,@CurrentPrice out,@Syl out,@RetGasUserID out", paramArray.ToArray());
                        }
                        catch (System.Data.Entity.Validation.DbEntityValidationException ex)//捕获异常
                        {
                            var msg = string.Empty;
                            var errors = (from u1 in ex.EntityValidationErrors select u1.ValidationErrors).ToList();
                            foreach (var item1 in errors)
                                msg += item1.FirstOrDefault().ErrorMessage;
                            Common.writeLog(msg, "异常数据", "");//记录日志文件
                            throw;
                        }
                        string gasuserid = paramArray[4].Value.ToString();
                        string currenprice = paramArray[5].Value.ToString();
                        price = currenprice;
                        string syl = paramArray[6].Value.ToString();
                        yue = syl;
                        string retgasUserID = paramArray[7].Value.ToString();//取值

 

1、无参数查询
var model = db.Database.SqlQuery<UserInfo>("select* from UserInfoes ").ToList();

2、有参查询
var model = db.Database.SqlQuery<UserInfo>("select* from UserInfoes where id=@ID ",new SqlParameter("@ID",id)).ToList();

3、结合linq查询

var model = (from p in db.userinfo.SqlQuery("select * from UserInfoes where id=@ID", new SqlParameter("@ID", 1)) select p).ToList();

4：EF原始查询单独表

model = db.userinfo.OrderByDescending(a => a.ID).Skip(10 * (当前页 - 1)).Take(10).ToList();
 

5，EF 执行存储过程或delete 删除语句

SqlParameter[] para = new SqlParameter[] { 
new SqlParameter("@ID",id)
};
db.Database.ExecuteSqlCommand("delete UserInfoes  where id=@ID", para);

存储过程 删除，有几个参数，存储后面要带几个参数以逗号分隔

SqlParameter[] para = new SqlParameter[] { 
new SqlParameter("@ID",id)
};
db.Database.ExecuteSqlCommand("sp_Userinfos_deleteByID @ID", para);

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

1：首先先贴上一个简单的存储过程

USE [testActice]
GO
/****** Object:  StoredProcedure [dbo].[selectTable]    Script Date: 06/12/2017 15:38:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[selectTable]
@id int,
@name varchar(250)

AS
BEGIN
SET NOCOUNT ON;
declare @sql nvarchar(1000)
set @sql='select * from myTable '
if @id >0
begin
  set @sql=@sql+' where id='+cast(@id as varchar(10))
end
if @name <>''
begin
  set @sql=@sql+' and name='+@name
end

exec sp_executesql @sql
set nocount off;
END

2：使用EF调用存储过程，并返回结果集

  List<myTable> s = new List<myTable>();
            SqlParameter[] sqlparms=new SqlParameter[2];
            sqlparms[0] = new SqlParameter("@ID", id);
            sqlparms[1] = new SqlParameter("@name", name);
            var result = this.db.Database.SqlQuery<myTable>("exec selectTable @ID,@name", sqlparms);
            foreach (var c in result)
            {
                s.Add(new myTable { detail = c.detail, ID = c.ID, mes = c.mes, myTableID = c.myTableID, name = c.name });
            }
            return s;