关于数据库存储过程分页DatagridView BindingNavigator 控件的详细实现

参考了许多的资料和不断地调试，总算把这个问题弄清楚了。实现了一个简单的分页示例，虽然这样的做法不是太好。

程序有3个控件

BindingNavigator： 就是DataGridView控件上面的那个，在工程里名字： bindngrDemo

DataGridView: dgvDemo

BindingSource: 这个其实可以不要 bindseDemo

 

示例采用的是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 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里的代码了：

在工程里定义几个变量：

 public partial class frmDataPagination : Form
    {

        #region Define the page size
        static int pageSize = 20;
        static int pageCount = 0;
        #endregion
        ...
    }

上面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种数据绑定

可以直接用sql command + Sql datareader

也可以sqldataAdapter + Dataset 或者Datatable

        // 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的代码

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个键

向后，最后，向前，最前

        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控件的显示等等