C# EF 调用存储过程123

/// <summary>
/// 数据显示连表联查
/// </summary>
/// <returns></returns>
public List<WuLianWangInfo> WlWInfos(string name = "")
{
var list = ConText.WlWtext.Include("JueseId").ToList();

if (!string.IsNullOrEmpty(name))
{
list = list.Where(s => s.Name.Contains(name)).ToList();
}

return list;
}

/// <summary>
/// 删除
/// </summary>
/// <param name="Did"></param>
/// <returns></returns>
public int DeleInfo(int Did)
{
var deid = ConText.WlWtext.Find(Did);
ConText.WlWtext.Remove(deid);
return ConText.SaveChanges();
}

/// <summary>
/// 修改数据
/// </summary>
/// <param name="wuLianWang"></param>
/// <returns></returns>
public int UpdateInfo(WuLianWangInfo wuLianWang)
{
ConText.WlWtext.Attach(wuLianWang);
ConText.Entry(wuLianWang).State = System.Data.Entity.EntityState.Modified;
return ConText.SaveChanges();
}

/// <summary>
/// 根据Id获取数据
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public WuLianWangInfo FanTian(int Id)
{
return ConText.WlWtext.Find(Id);
}

/// <summary>
/// 添加数据
/// </summary>
/// <param name="WuLian"></param>
/// <returns></returns>
public int AddInfo(WuLianWangInfo WuLian)
{
ConText.WlWtext.Add(WuLian);
return ConText.SaveChanges();
}

/// <summary>
/// 调用分页存储过程
/// </summary>
/// <param name="Pageindex"></param>
/// <param name="Pagesize"></param>
/// <returns></returns>
public List<WuLianWangInfo> WPageProc(int Pageindex = 1,int Pagesize = 2)
{
var list = new List<WuLianWangInfo>();

int Pcount = 0;

SqlParameter Index = new SqlParameter("@PageIndex", Pageindex);
Index.Direction = ParameterDirection.Input;

SqlParameter Size = new SqlParameter("@PageSize", Pagesize);
Size.Direction = ParameterDirection.Input;

SqlParameter Count = new SqlParameter("@PageCount", Pcount);
Count.Direction = ParameterDirection.Output;

ConText.WlWtext.SqlQuery("exec Proc_Page", new SqlParameter[] { Index, Size, Count });

return list;
}

BLL Redis

ProdudctDal produdctDal = new ProdudctDal();
public List<ProductInfo> Show(int pageIndex, int pagesize, string name)
{
//实例化一个集合
var list = new List<ProductInfo>();

#region MyRegion
//

//连接到redis
RedisClient client = new RedisClient("127.0.0.1");

//从redis 获取 key是 products 的值，值的类型是 List<ProductInfo>
list = client.Get<List<ProductInfo>>("products");

//判断下 从redis里面获取的值 是不是null 或者条数是不是0
if (list == null || list.Count == 0)
{
//缓存没获取到值 从数据库查询一遍
list = produdctDal.Show(pageIndex, pagesize, name);

//把从数据库里面获取到的 写到缓存里面
client.Set<List<ProductInfo>>("products", list);
}
#endregion

return list;
}

 

----------------------------------查询分页------------------------------
if OBJECT_ID('P_Show') is not null
drop proc P_Show
GO
CREATE proc P_Show
@pageIndex int,
@pageSize int,
@UserName varchar(50)=null,
@totalCount int out
as

declare @sql varchar(max),--sql语句
@sqlWhere varchar(max),--查询条件
@rid int --序列号

---初始化变量
set @sql=''
set @sqlWhere=''
set @rid=(@pageIndex-1)*@pageSize
set @totalCount=(select COUNT(1) from UserInfo)

if @UserName is not null
begin
set @sqlWhere=' and UserName like ''%'+@UserName+'%'''
set @totalCount=(select COUNT(1) from UserInfo where UserName like '%'+@UserName+'%')
end

set @sql='select top '+STR(@pageSize)+' * from
(
select *,ROW_NUMBER() over(order by UserDate) as rid from UserInfo where 1=1 '+@sqlWhere+'
) temp
where rid>'+STR(@rid)
exec (@sql)