在系统开发中,如果数据库中某张数据表的数据量较大,我们一般会进行分页查询。SQL Server
中的分页查询较为常用的是利用ROW_NUMBER()
进行分页,从SQL Server 2012
开始,又新增了一种fetch
方法进行分页,下面给出这两种分页方法的具体实现代码。在测试数据库中有一张道路名称数据表——TRoad
,其中包含了多条数据,如下图:
方法一:pageIndex
表示第几页,pageNumber
表示一页多少行
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
private static string ConnectionString = "Data Source=LENOVO-PC;Initial Catalog=Test;User ID=sa;Password=123456";
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = MethodOne(1, 10);
}
// 方法一
private DataTable MethodOne(int pageIndex, int pageNumber)
{
string sql = "select * from (select ROW_NUMBER() OVER(Order by Id) as RowId,* from TRoad) as b where b.RowId BETWEEN (@pageIndex-1)*@pageNumber+1 and @pageIndex*@pageNumber order by Id";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@pageIndex", pageIndex),
new SqlParameter("@PageNumber", pageNumber)
};
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnectionString))
{
adapter.SelectCommand.Parameters.AddRange(parameters);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
adapter.SelectCommand.Parameters.Clear();
return dataTable;
}
}
}
}
运行结果如下所示:
方法二:pageIndex
表示第几页,pageNumber
表示一页多少行
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
private static string ConnectionString = "Data Source=LENOVO-PC;Initial Catalog=Test;User ID=sa;Password=123456";
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = MethodTwo(2, 10);
}
// 方法二
private DataTable MethodTwo(int pageIndex, int pageNumber)
{
string sql = "select * from [TRoad] order by Id offset (@pageIndex-1)*@pageNumber rows fetch next @pageNumber rows only";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@pageIndex", pageIndex),
new SqlParameter("@PageNumber", pageNumber)
};
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnectionString))
{
adapter.SelectCommand.Parameters.AddRange(parameters);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
adapter.SelectCommand.Parameters.Clear();
return dataTable;
}
}
}
}
运行结果如下所示:
其实我个人比较喜欢第二种方法,简洁明了,据说效率比方法一略高一些~如果项目服务器的数据库还是08版的sqlserver,那还是用方法一比较好。