大家好，又见面了，我是你们的朋友全栈君。
程序有3个控件
BindingNavigator： 就是DataGridView控件上面的那个，在工程里名字： bindngrDemo
DataGridView: dgvDemo
BindingSource: 这个其实可以不要 bindseDemo
示例采用的是SQL SERVER的示例数据库pub
在pub数据库里写入分页存储过程
代码语言：javascript复制CREATE PROCEDURE [dbo].[Pagination]
@Columns VARCHAR(500), -- The columns to be displayed, divide by comma
@Tablename VARCHAR(100), -- The name of the table to be searched
@OrderColumnName VARCHAR(100), -- The name of the column to be used in order
@Order VARCHAR(50), -- The order method, ASC or DESC
@Where VARCHAR(100), -- The where condition, if there is not conditon use 1=1
@PageIndex INT, -- Current page index
@PageSize INT, -- The size of the page
@PageCount INT OUTPUT -- The total page count,define as output parameter
AS
BEGIN
DECLARE @SqlRecordCount NVARCHAR(100) -- The SQL Statement to get the total count of the records
DECLARE @SqlSelect NVARCHAR(1000) -- The SQL SELECT statment
SET @SqlRecordCount = N'SELECT @RecordCount = COUNT(*) FROM ' @Tablename ' WHERE ' @Where
DECLARE @RecordCount INT
EXEC sp_executesql @SqlRecordCount, N'@RecordCount INT OUTPUT',@RecordCount OUTPUT -- Transfer the parameter dynamic
IF(@RecordCount % @PageSize = 0)
SET @PageCount = @RecordCount / @PageSize
ELSE
SET @PageCount = @RecordCount / @PageSize 1
SET @SqlSelect = N'SELECT ' @Columns ' FROM(SELECT ROW_NUMBER() OVER (ORDER BY ' @OrderColumnName
' ' @Order ') AS tempid, * FROM '
@Tablename ' WHERE ' @Where ') AS tempTableName WHERE tempid
BETWEEN ' STR((@PageIndex - 1)*@PageSize 1) ' AND ' STR(@PageIndex * @PageSize)
EXEC(@SqlSelect)
END下面的就是VS里的代码了：
在工程里定义几个变量：
代码语言：javascript复制 public partial class frmDataPagination : Form
{
#region Define the page size
static int pageSize = 20;
static int pageCount = 0;
#endregion
...
}上面2个变量，一个定义在数据库里分页时，每页的大小； 另一个用来接收存储过程分页时，输出的总页数。
在里面加入一个产生Select命令的函数，用于向sqlDataAdapter传递sql command.
代码语言：javascript复制 private static SqlCommand GenerateSelectCommand(string Columns, string tableName, string orderColumnName, string order, string whereCondition,
int pageIndex, int pageSize, SqlConnection conn)
{
SqlCommand sqlcmd = new SqlCommand("Pagination", conn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Columns", Columns);
sqlcmd.Parameters.AddWithValue("@Tablename", tableName);
sqlcmd.Parameters.AddWithValue("@OrderColumnName", orderColumnName);
sqlcmd.Parameters.AddWithValue("@Order", order);
sqlcmd.Parameters.AddWithValue("@Where", whereCondition);
sqlcmd.Parameters.AddWithValue("@PageIndex", pageIndex);
sqlcmd.Parameters.AddWithValue("@PageSize", pageSize);
SqlParameter pageCount = new SqlParameter("@PageCount", SqlDbType.Int);
pageCount.Direction = ParameterDirection.Output;
sqlcmd.Parameters.Add(pageCount);
sqlcmd.UpdatedRowSource = UpdateRowSource.None;
return sqlcmd;
}下面是LoadData函数， 在这个函数里面可以指定自己要选的table 以及列等等参数
里面有2种数据绑定
可以直接用sql command Sql datareader
也可以sqldataAdapter Dataset 或者Datatable
代码语言：javascript复制 // Load the page data
private static void LoadData(int pageIndex, DataGridView dgvDemo)
{
string strConn = "server = (local); Database = pubs; Integrated Security = SSPI";
try
{
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
// Use sqlcommand to fetch the data
/*
SqlCommand cmd = GenerateSelectCommand("fname", "employee", "fname", "ASC", "1=1", pageIndex, pageSize, conn);
SqlDataReader rdr = cmd.ExecuteReader();
BindingSource bindseDemo = new BindingSource();
bindseDemo.DataSource = rdr;
dgvDemo.DataSource = bindseDemo;
*/
// Use SqlDataAdapter to fetch the data
SqlDataAdapter sqlDa = new SqlDataAdapter();
sqlDa.SelectCommand = GenerateSelectCommand("fname, lname, hire_date", "employee", "fname", "ASC", "1=1", pageIndex, pageSize, conn);
DataTable ds = new DataTable();
sqlDa.Fill(ds);
pageCount = (int)sqlDa.SelectCommand.Parameters["@PageCount"].Value;
BindingSource bindseDemo = new BindingSource();
bindseDemo.DataSource = ds;
dgvDemo.DataSource = bindseDemo;
sqlDa.Dispose();
// Use SqlDataAdapter Dataset to fetch the data
/*
DataSet ds = new DataSet();
SqlDataAdapter sqlDa = new SqlDataAdapter();
sqlDa.SelectCommand = GenerateSelectCommand("fname", "employee", "fname", "ASC", "1=1", pageIndex, pageSize, conn);
sqlDa.Fill(ds);
pageCount = (int)sqlDa.SelectCommand.Parameters["@PageCount"].Value;
dgvDemo.DataSource = ds.Tables[0];
sqlDa.Dispose();
*/
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Information:", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}窗体Load的代码
代码语言：javascript复制private void frmDataPagination_Load(object sender, EventArgs e)
{
// Even if there is no records, there is no exception
LoadData(1, dgvDemo);
// Set the status of the BindingNavigator control
if (pageCount == 0 || pageCount == 1)
{
bindngrDemo.MoveFirstItem.Enabled = false;
bindngrDemo.MoveLastItem.Enabled = false;
bindngrDemo.MoveNextItem.Enabled = false;
bindngrDemo.MovePreviousItem.Enabled = false;
}
else
{
bindngrDemo.MoveFirstItem.Enabled = false;
bindngrDemo.MoveLastItem.Enabled = true;
bindngrDemo.MoveNextItem.Enabled = true;
bindngrDemo.MovePreviousItem.Enabled = false;
bindngrDemo.PositionItem.Text = "1";
bindngrDemo.CountItem.Text = "of {" pageCount.ToString() "}";
}
}BindingNavigator控件的几个事件
包括4个键
向后，最后，向前，最前
代码语言：javascript复制 private void bindingNavigatorMoveNextItem_Click(object sender, EventArgs e)
{
int currentPage = Convert.ToInt32(bindngrDemo.PositionItem.Text);
if (currentPage < pageCount)
{
int page = currentPage 1;
bindngrDemo.PositionItem.Text = page.ToString();
LoadData(page, dgvDemo);
if (page == pageCount)
{
bindngrDemo.MoveNextItem.Enabled = false;
bindngrDemo.MoveLastItem.Enabled = false;
}
if (page >= 2)
{
bindngrDemo.MovePreviousItem.Enabled = true;
bindngrDemo.MoveFirstItem.Enabled = true;
}
}
else
{
MessageBox.Show("This is the last page", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void bindingNavigatorMovePreviousItem_Click(object sender, EventArgs e)
{
int currentPage = Convert.ToInt32(bindngrDemo.PositionItem.Text);
if (currentPage >= 2)
{
int page = currentPage - 1;
bindngrDemo.PositionItem.Text = page.ToString();
LoadData(page, dgvDemo);
if (page == 1)
{
bindngrDemo.MovePreviousItem.Enabled = false;
bindngrDemo.MoveFirstItem.Enabled = false;
}
if (page <= pageCount)
{
bindngrDemo.MoveNextItem.Enabled = true;
bindngrDemo.MoveLastItem.Enabled = true;
}
}
else
{
MessageBox.Show("This is the first page", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void bindingNavigatorMoveLastItem_Click(object sender, EventArgs e)
{
LoadData(pageCount, dgvDemo);
bindngrDemo.PositionItem.Text = pageCount.ToString();
bindngrDemo.MoveLastItem.Enabled = false;
bindngrDemo.MoveNextItem.Enabled = false;
bindngrDemo.MovePreviousItem.Enabled = true;
bindngrDemo.MoveFirstItem.Enabled = true;
}
private void bindingNavigatorMoveFirstItem_Click(object sender, EventArgs e)
{
LoadData(1, dgvDemo);
bindngrDemo.PositionItem.Text = "1";
bindngrDemo.MoveFirstItem.Enabled = false;
bindngrDemo.MovePreviousItem.Enabled = false;
bindngrDemo.MoveNextItem.Enabled = true;
bindngrDemo.MoveLastItem.Enabled = true;
}
}至此就全部完了，里面其他的部分就由自己编写了，比如DataGridView控件的显示等等
https://www.cnblogs.com/herbert/archive/2010/07/26/1785445.html
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/158073.html原文链接：https://javaforall.cn
