可以实现分页的方法有许多,我这里采用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、测试结果如下