webapi http请求分页 web端mvc

--分页显示

--存储过程分页sql
alter proc PageFruit
@where nvarchar(max)=null,
@wherename nvarchar(max),
@pageindex int,
@pagesize int,
@pagecount int output,
@tablename nvarchar(max)
as
declare @sql nvarchar(max)
begin
if(@where is null or @wherename is null)
begin
set @sql='select @count=COUNT(*) from '+CONVERT(nvarchar(100),@tablename)+''
set @sql+=' select * from
(select ROW_NUMBER() over(order by ID)as Rowid,* from '+CONVERT(nvarchar(100),@tablename)+')as tab
where Rowid between '+CONVERT(nvarchar(100),((@pageindex-1)*@pagesize+1))+' and '+CONVERT(nvarchar(100),(@pageindex*@pagesize))+''

exec sp_executesql @sql,N'@count int output',@pagecount output
print @sql
end
else
begin
set @sql='select @count=COUNT(*) from '+CONVERT(nvarchar(100),@tablename)+' where '+CONVERT(nvarchar(100),@wherename)+' like ''%'+CONVERT(nvarchar(100),@where)+'%'''

set @sql+=' select * from
(select ROW_NUMBER() over(order by ID)as Rowid,* from '+CONVERT(nvarchar(100),@tablename)+' where '+CONVERT(nvarchar(100),@wherename)+'='''+CONVERT(nvarchar(100),@where)+''') as tab
where Rowid between '+CONVERT(nvarchar(100),((@pageindex-1)*@pagesize+1))+' and '+CONVERT(nvarchar(100),(@pageindex*@pagesize))+''

exec sp_executesql @sql,N'@count int output',@pagecount output
print @sql
end
end

Ado.net调用存储过程分页

public pagelist<Models.ErrorModel> Show(int pageindex, string pagesize, string likewhere)
{
string tablename = "TBLEtcExitError";

string strwhere = "Lane";
//switch((int)wherestr)
//{
// case 1: strwhere = null;break;
// case 2: strwhere = "lane"; break;
// case 3: strwhere = "StationID"; break;
// case 4: strwhere = "VehicleLicense"; break;
//}

using (SqlConnection conn = new SqlConnection(DBHelper.ConnString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("PageFruit",conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] pa ={
new SqlParameter("@tablename",SqlDbType.NVarChar,100),
new SqlParameter("@pageindex",SqlDbType.Int),
new SqlParameter("@pagesize",SqlDbType.Int),
new SqlParameter("@pagecount",SqlDbType.Int),
new SqlParameter("@wherename",SqlDbType.NVarChar,500),
new SqlParameter("@where",SqlDbType.NVarChar,500),
};
pa[0].Value = tablename;
pa[1].Value = pageindex;
pa[2].Value = pagesize;
pa[3].Direction = ParameterDirection.Output;
pa[4].Value = strwhere;
pa[5].Value = likewhere;
cmd.Parameters.AddRange(pa);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable ta = new DataTable();
da.Fill(ta);
List<ErrorModel> lists = DBHelper.DataTable2List<ErrorModel>(ta).ToList();
pagelist<ErrorModel> list = new pagelist<ErrorModel>();
list.getlist = lists;
list.pagecount = (int)pa[3].Value;
return list;
}

web 端http请求

public ActionResult EtcExitErrorIndex(string likewhere,int pageindex =1)
{
int pagesize=5;
Uri uri = new Uri("XXXXXXXXXXXX");
//通过Uri来传递和接收Http响应请求 （get/put/post/delete等），异步处理
HttpClient client = new HttpClient();
client.BaseAddress = uri;//将请求的地址复制给http对象
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));//指定传输的数据格式
//响应Http请求,并接收Http返回的信息
var tmpVal = "";
HttpResponseMessage response = client.GetAsync("/api/EtcExitErrorApi/Show?pageindex=" + pageindex + "&pagesize=" + pagesize + "&likewhere=" + likewhere).Result;
if (response.IsSuccessStatusCode)
{
tmpVal = response.Content.ReadAsStringAsync().Result;
}
client.Dispose();
var Vehicle = JsonConvert.DeserializeObject<pagelist<ErrorModel>>(tmpVal);
ViewBag.index = pageindex;
ViewBag.pagecount = Vehicle.pagecount;
ViewBag.count = Vehicle.count;
ViewBag.where = likewhere;
return View(Vehicle.getlist);
}