调用分页存储过程

//调用
        public DataSet Query(string procName, ref int Total, SqlParameter[] sqlParameters)
        {
            DataSet dataSet = new DataSet();
            using (SqlConnection connection = new SqlConnection(con))
            {
                //打开数据库
                connection.Open();
                //封装执行的sql语句
                using (SqlCommand command = new SqlCommand(procName, connection))
                {
                    //将存储过程的输入参数放到comand中
                    command.Parameters.AddRange(sqlParameters);
                    //指定执行的是存储过程
                    command.CommandType = CommandType.StoredProcedure;
                    //使用适配器关联command
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(command);
                    //适配器填充数据
                    sqlDataAdapter.Fill(dataSet);
                    //获取输出参数
                    Total = int.Parse(command.Parameters["@Total"].Value.ToString());
                }
            }
            return dataSet;
        }
复制代码
///API调用

using System.Collections.Generic;
using System.Linq;
using System.Web.Http;
using System.Net.Http;
using IOT.BLL;
using IOT.Model;
using Newtonsoft.Json;
using System.Data;

namespace LianXi.Api.Controllers
{
    public class ShowController : ApiController
    {
        Iot_bll bll = new Iot_bll();
        //显示
        [HttpGet]
        public IHttpActionResult GetShow(string name="",int pageSize, int PageNumber)
        {
            //显示
            DataTable tb = bll.GetShow();
            string json = JsonConvert.SerializeObject(tb);
            List<Base_User> list = JsonConvert.DeserializeObject<List<Base_User>>(json);

            var list1 = (from s in list select s).ToList();
            //查询
            if(!string.IsNullOrEmpty(name))
            {
                list1 = list1.Where(s => s.Account.Contains(name)).ToList();
            }
            //分页
            int Total = 0;
            DataTable tb1 = bll.GetPagingDept(ref Total, pageSize, PageNumber, " 1=1 ", " * ");

            PagingModels<DataTable> paging = new PagingModels<DataTable> { tb = tb, Total = Total };

            return Json<List<Base_User>>(list1);
        }
        //添加
        [HttpPost]
        public int GetAdd(Base_User m)
        {
            int flag = bll.GetAdd(m);
            return flag;
        }
    }
}
/////BLL层调用
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using IOT.DAL;
using IOT.Model;
using Newtonsoft.Json;
using System.Data;
using System.Data.SqlClient;

namespace IOT.BLL
{
    public class Iot_bll
    {
        DBHelp dbhelp = new DBHelp();

        //显示
        public DataTable GetShow()
        {
            string sql = "select * from Base_User";
           return dbhelp.ExecTable(sql);

        }

        //添加
        public int GetAdd(Base_User m)
        {
            string sql = $"insert into Base_User values('{m.Account}', '{m.PassWord1}', '{m.PhoneNum}', '{m.CardID}',{m.VIPLive},{m.Coin},'{m.CreateTime}','{m.UpdateTime}')";
            return dbhelp.ExecNonQuery(sql);
        }
        public DataTable GetPagingDept(ref int Total, int pageSize, int pageNumber, string where, string field)
        {
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
new SqlParameter {ParameterName= "@table", Value= " Shopping ",SqlDbType= SqlDbType.NVarChar,Direction= ParameterDirection.Input, Size=200},
new SqlParameter {ParameterName= "@field", Value= field,SqlDbType= SqlDbType.NVarChar,Direction= ParameterDirection.Input, Size=2000 },
new SqlParameter {ParameterName= "@where", Value= where ,SqlDbType= SqlDbType.NVarChar,Direction= ParameterDirection.Input, Size=2000},
new SqlParameter {ParameterName= "@order", Value= " Id " ,SqlDbType= SqlDbType.NVarChar,Direction= ParameterDirection.Input, Size=200},
new SqlParameter {ParameterName= "@pageSize", Value=pageSize ,SqlDbType= SqlDbType.Int,Direction= ParameterDirection.Input},
new SqlParameter {ParameterName= "@pageNumber", Value=pageNumber ,SqlDbType= SqlDbType.Int,Direction= ParameterDirection.Input},
new SqlParameter {ParameterName= "@Total" ,SqlDbType= SqlDbType.Int,Direction= ParameterDirection.Output}
            };
            return db.Query("[SP_User]", ref Total, sqlParameters).Tables[0];
        }
    }
}