原文：http://www.cnblogs.com/herbert/archive/2010/07/26/1785445.html
参考了许多的资料和不断地调试，总算把这个问题弄清楚了。实现了一个简单的分页示例，虽然这样的做法不是太好。
bootstrap存储过程分页、程序有3个控件
BindingNavigator： 就是DataGridView控件上面的那个，在工程里名字： bindngrDemo
DataGridView: dgvDemo
BindingSource: 这个其实可以不要 bindseDemo
SQL通用分页。示例采用的是SQL SERVER的示例数据库pub
在pub数据库里写入分页存储过程
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 dynamicIF(@RecordCount % @PageSize = 0)
SET @PageCount = @RecordCount / @PageSize
ELSE
SET @PageCount = @RecordCount / @PageSize + 1SET @SqlSelect = N'SELECT ' + @Columns + ' FROM(SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderColumnName+' ' + @Order + ') AS tempid, * FROM ' + @Tablename + ' WHERE ' + @Where + ') AS tempTableName WHERE tempidBETWEEN ' + STR((@PageIndex - 1)*@PageSize + 1) + ' AND ' + STR(@PageIndex * @PageSize)
EXEC(@SqlSelect)
END下面的就是VS里的代码了：
在工程里定义几个变量：
public partial class frmDataPagination : Form{#region Define the page sizestatic int pageSize = 20;static int pageCount = 0;#endregion...}
sqlserver存储过程分页？上面2个变量，一个定义在数据库里分页时，每页的大小； 另一个用来接收存储过程分页时，输出的总页数。
在里面加入一个产生Select命令的函数，用于向sqlDataAdapter传递sql command.
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种数据绑定
datagridview控件连接数据库。可以直接用sql command + Sql datareader
也可以sqldataAdapter + Dataset 或者Datatable
// Load the page dataprivate 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的代码
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 controlif (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控件的几个事件
分页查询sql，包括4个键
向后，最后，向前，最前
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控件的显示等等
