实现DataGridview中的分页显示

数据库存储过程 分页与SQL调用

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

程序有3个控件

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

BindingSource: 这个其实可以不要 bindseDemo

using System;

using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text;

using System.Windows.Forms; using System.Collections; using System.Data.SqlClient; using System.Configuration;

namespace FeiTianSeo {

public partial class Test1 : Form {

static int pageSize = 20; static int pageCount = 0; public Test1() {

InitializeComponent(); }

public static string connectionString =

ConfigurationManager.ConnectionStrings[\"conn\"].ToString();

private void Test1_Load(object sender, EventArgs e) {

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 = \"共{\" + pageCount.ToString() + \页\"; }

}

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; }

private static void LoadData(int pageIndex, DataGridView dgvDemo) {

//string strConn = \"server = (local); Database = pubs; Integrated Security = SSPI\"; try {

using (SqlConnection conn = new SqlConnection(connectionString)) {

conn.Open();

SqlDataAdapter sqlDa = new SqlDataAdapter();

sqlDa.SelectCommand = GenerateSelectCommand(\"id, UserName, linkAdress\", \"Links\", \"id\", \"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\\"ASC\ 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); } }

private void button1_Click(object sender, EventArgs e) {

//string sqlstr = \"select * from Links\";

//SqlConnection conn = new SqlConnection(connectionString); //DataSet ds = new DataSet();

//SqlDataAdapter ada = new SqlDataAdapter(sqlstr, conn); //ada.Fill(ds);

//this.dgvDemo.DataSource = ds.Tables[0];

}

private void bindingNavigatorMoveNextItem_Click_1(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 bindingNavigatorMoveLastItem_Click_1(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 bindingNavigatorMovePreviousItem_Click_1(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 bindingNavigatorMoveFirstItem_Click_1(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; } } }