C#调用WebApi实现分页查询

可以实现分页的方法有许多，我这里采用ADO.NET的方式。

1、首先需要写一个Sqlhelper,里面包含了ExecuteReader方法，查询时通过该方法执行，代码参考如下：

public static SqlDataReader ExecuteReader(SqlConnection conn, string sql, CommandType cmdType, params SqlParameter[] pms)
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.CommandType = cmdType;
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                try
                {
                    conn.Open();
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch
                {
                    conn.Close();
                    conn.Dispose();
                    throw;
                }

            }
        }

2、
本次测试采用的WebAPI的形式，我们定义了一个ApiResponse类作为返回值，代码参考如下：

public class ApiResponse
    {
        public ApiResponse(string message, bool status = false)
        {
            this.Message = message;
            this.Status = status;
        }

        public ApiResponse(bool status, object result)
        {
            this.Status = status;
            this.Result = result;
        }

        public string Message { get; set; }

        public bool Status { get; set; }

        public object Result { get; set; }
    }

3、
我们还加入了模糊查询的方式，sql语句中，@Search不用加引号。

public ApiResponse GetAllStudent(QueryParameter param)
        {
            List<Student> students = new List<Student>();
            SqlParameter[] parameters = new SqlParameter[]
                    {
                        new SqlParameter("@Search",param.Search),//查询内容
                        new SqlParameter("@PageIndex",param.PageIndex),//页码
                        new SqlParameter("@PageSize",param.PageSize),//页面大小
                    };
            string SqlString = "Select id,student_name FROM(SELECT *,rn=row_number() over(order by id asc) FROM student_info " +
                "WHERE student_name LIKE @Search) AS t WHERE t.rn BETWEEN(@PageIndex-1)*@PageSize+1 AND @PageSize*@PageIndex";
            try
            {
                using (Connection)
                {
                    using (SqlDataReader dr = Tools.SqlHelper.ExecuteReader(Connection, SqlString, CommandType.Text, parameters))
                    {
                        while (dr.Read())
                        {
                            Student student = new Student();
                            student.Id = Convert.ToInt32(dr["id"]);
                            student.Name = Convert.ToString(dr["student_name"]);
                            students.Add(student);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                return new ApiResponse(ex.Message);
            }
            if (students != null)
                return new ApiResponse(true, students);
            return new ApiResponse("获取数据失败");
        }

4、在BLL层还需要再处理一下Search的值，我们需要实现没有值传入时，他可以分页表中所有数据。

public ApiResponse Query(string search, int pageIndex, int pageSize)
        {
            QueryParameter param = new QueryParameter();
            if (string.IsNullOrWhiteSpace(search))
                param.Search = "%";//sql通配符；
            else 
                param.Search = "%"+search+"%";
            param.PageIndex = pageIndex;
            param.PageSize = pageSize;
            return myDAL_Student.GetAllStudent(param);//myDAL_Student是自己创建的DAL层
        }

5、最后我们编写好controller层就可以开始测试分页功能了

    [Route("api/[controller]/[action]")]
    [ApiController]
    public class StudentController : ControllerBase
    {
        private readonly BLL_Student myBLL_Student;
        public StudentController()
        {
            this.myBLL_Student = new BLL_Student();
        }
        [HttpGet]
        public ApiResponse GetAll(string search,int pageIndex,int pageSize) => myBLL_Student.Query(search,pageIndex,pageSize);
    }

6、测试结果如下