各位学长们，帮帮我啊，我先在DataGridView，绑定数据库数据，然后想让他每页显示10页，进行分页。按上一页，下一页，首页，末页。
解决方案 »
- 在C#程序里打开一文件（word,excel,pdf,txt.....）任何格式的 打开后不可以编辑和另存为 有什么方法实现
- 网页视频播放地址代码怎么找？
- MessageQueue调试提示“路径语法无效”怎么回事呀？
- 关于取指定的颜色相近的颜色算法。
- VS.net2005编写SQL SERVER2005扩展存储过程的问题
- 事件的+=
- ASP.NET高级开发(6)群,群号：22068906，北京的朋友赶紧加了。群口号：代码共享,技术共享.四海为家.皆为朋友..
- 在C＃中如何提供类似C＋＋中的#include 预编译指令？
- 在c#中如何将一个文件流保存为一个ini文件
- 关于DataAdapter的update 指定database的问题
- 求高手指教，关于Windform的：窗体之间图片的拖动
- 求解JQ 传array到MVC3.0如何写
static int rowsall = 0;//总行数
static int pageall = 0;//总页数
static int page = 0;//第几页
static int count = 20;//返回20行
static int start = 0;//从第start行开始返回
#endregion #region 建立数据库链接
/// <summary>
/// 建立数据库连接
/// </summary>
/// <returns>返回SqlConnection对象</returns>
public SqlConnection getcon()
{
SqlConnection myCon = new SqlConnection(connectionString);
return myCon;
} #region 设置DataGridView分页显示的参数，和初次绑定
/// <summary>
/// 设置DataGridView分页显示的参数，和初次绑定
/// </summary>
/// <param name="sqlstr">设置查询的sql语句</param>
/// <param name="table">设置返回绑定的DataSet中的表的名称</param>
/// <param name="dgv">要绑定的DataGridView</param>
public void upPage(string sqlstr,string table,DataGridView dgv)
{
rowsall = dbexecute.getds(sqlstr, table).Tables[table].Rows.Count;//总行数
if (rowsall == 0)
{
//如果没有数据则将第一页、上一页、下一页、最后一页设置为不可用；并设置其他参数
toolStripButton2.Enabled = false;
toolStripButton3.Enabled = false;
toolStripButton4.Enabled = false;
toolStripButton5.Enabled = false;
page = 0;
pageall = 0;
rowsall = 0;
dgv.DataSource = null;
tslRowsall.Text = rowsall.ToString();
tslPageAll.Text = pageall.ToString();
tslPage.Text = page.ToString();
return;
}
if (rowsall > 0)//判断是否有内容
{
page = 1;//如果有内容，设置为第一页
start = 0;
}
int yushu = rowsall % count;//是否存在余行
if (yushu == 0)//不存在余行时设置总页数
{
if (rowsall > 0 && rowsall <= count)
{
pageall = 1;
}
else
{
pageall = rowsall / count;
}
}
else//存在余行时设置总页数
{
pageall = rowsall / count + 1;
}
{//设置显示数据，
tslRowsall.Text = rowsall.ToString();
tslPageAll.Text = pageall.ToString();
tslPage.Text = page.ToString();
if (pageall > 0)
{ //设置跳转到第几页
tscbPage.Items.Clear();
for (int i = 1; i <= pageall; i++)
tscbPage.Items.Add(i);
}
}
selectsql = sqlstr; //设置sql语句
dgv.DataSource = gettb(selectsql ,start,count,"table")‘;//绑定DataGridView }
#endregion #region
/// <summary>
/// 分页返回DataTable
/// </summary>
/// <param name="sql">查询的sql语句</param>
/// <param name="i">从第i行开始返回</param>
/// <param name="j">共返回j行记录</param>
/// <param name="tablename">返回DataSet中的表明</param>
/// <returns>返回DataTable</returns>
public DataTable gettb(string sql, int start, int count, string tablename)
{
SqlConnection con = this.getcon();
DataSet myds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sql, con); sda.Fill(myds, start, count, tablename);
return myds.Tables[tablename];
}
#endregion /// 第一页
private void toolStripButton2_Click(object sender, EventArgs e)
{
if (pageall > 1)
{
start = 0;
page = 1;
tslPage.Text = page.ToString();
this.dataGridView1.DataSource = gettb(selectsql ,start,count,"table")‘;//绑定DataGridView
}
} // 上一页
private void toolStripButton3_Click(object sender, EventArgs e)
{
if (page >1)
{
page--;
start -= 20;
tslPage.Text = page.ToString();
this.dataGridView1.DataSource = gettb(selectsql ,start,count,"table")‘;//绑定DataGridView
} } /// 下一页
private void toolStripButton4_Click(object sender, EventArgs e)
{
if (page < pageall)
{
page++;
start += 20;
tslPage.Text = page.ToString();
this.dataGridView1.DataSource = gettb(selectsql ,start,count,"table")‘;//绑定DataGridView
}
} //最后一页
private void toolStripButton5_Click(object sender, EventArgs e)
{
if (pageall > 0)
{
start = (pageall - 1) * count;
page = pageall;
tslPage.Text = page.ToString();
this.dataGridView1.DataSource = gettb(selectsql ,start,count,"table")‘;//绑定DataGridView
}
}
上边红色部分获取总行数是调用的数据操作层里的getds方法返回一个数据集，类和方法如下：class DBExecute
{
string G_str_connectionString = @"Data Source=70AB360C9ABA49E\SQLEXPRESS;Database=db_CRM;integrated security=true";//这里设置成你自己的连接
public DBExecute(){}
public DBExecute(string M_str_connectionString)
{
G_str_connectionString = M_str_connectionString;
}
#region 建立数据库链接
/// <summary>
/// 建立数据库连接
/// </summary>
/// <returns>返回SqlConnection对象</returns>
public SqlConnection getcon()
{
string M_str_sqlcon = G_str_connectionString;
SqlConnection myCon = new SqlConnection(M_str_sqlcon);
return myCon;
}
#endregion
#region 查询数据库返回一个DataSet对象
/// <summary>
/// 查询数据库返回一个DataSet对象
/// </summary>
/// <param name="M_str_sqlstr">SQL语句</param>
/// <param name="M_str_table">表名</param>
/// <returns>返回DataSet对象</returns>
public DataSet getds(string M_str_sqlstr, string M_str_table)
{
DataSet myds = new DataSet();
SqlConnection sqlcon = this.getcon();
SqlDataAdapter sqlda = new SqlDataAdapter(M_str_sqlstr, sqlcon);
sqlda.Fill(myds, M_str_table);
return myds;
}
#endregion
}
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using GROUP.Manage;public partial class shownews1 : System.Web.UI.Page
{
BaseClass BaseClass1 = new BaseClass();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack) getGoods(); }
private void getGoods()
{
//获取数据
string strsql = "select * from 新闻信息 where 新闻类别 like '" + Request.Params["id"].ToString() + "' order by 流水号 desc";
DataTable dt = BaseClass1.ReadTable(strsql); //实现分页
PagedDataSource objPds = new PagedDataSource();
objPds.DataSource = dt.DefaultView;
objPds.AllowPaging = true;
objPds.PageSize = 12;
int CurPage = Convert.ToInt32(this.LabelPage.Text);
objPds.CurrentPageIndex = CurPage - 1;
if (objPds.CurrentPageIndex < 0)
{
objPds.CurrentPageIndex = 0;
} //只有一页时禁用上页、下页按钮
if (objPds.PageCount == 1)
{
LinkButtonPrev.Enabled = false;
LinkButtonNext.Enabled = false;
}
else//多页时
{
//为第一页时
if (CurPage == 1)
{
LinkButtonPrev.Enabled = false;
LinkButtonNext.Enabled = true;
}
//是最后一页时
if (CurPage == objPds.PageCount)
{
LinkButtonPrev.Enabled = true;
LinkButtonNext.Enabled = false;
}
} this.LabelTotalPage.Text = Convert.ToString(objPds.PageCount);
GridView1.DataSource = objPds;
GridView1.DataBind(); }
//首页
protected void LinkButtonFirst_Click(object sender, EventArgs e)
{
this.LabelPage.Text = "1";
getGoods();
}
//上一页
protected void LinkButtonPrev_Click(object sender, EventArgs e)
{
this.LabelPage.Text = Convert.ToString(int.Parse(this.LabelPage.Text) - 1);
getGoods();
}
//下一页
protected void LinkButtonNext_Click(object sender, EventArgs e)
{
this.LabelPage.Text = Convert.ToString(int.Parse(this.LabelPage.Text) + 1); ;
getGoods();
}
//末页
protected void LinkButtonLast_Click(object sender, EventArgs e)
{
this.LabelPage.Text = this.LabelTotalPage.Text;
getGoods();
}
}
有一个属性的，设置一下就不用像一楼那么多代码了..
那个属性的名字你一看就明白他的意思了
dgv有强大的机制保证在不分页时大量数据浏览也一样的流畅. 它有virtual mode机制,可以在往下拖动时动态显示数据.
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using AddToWaitpay.control;namespace test
{
public partial class 数据分页 : Form
{
public 数据分页()
{
InitializeComponent();
}
dbhelp help = new dbhelp();
private int pagesize = 10;//每页显示？条
private int pageindex = 1;//当前页索引
private int datacount = 0;//数据总数
private int pagecount = 0;
private void 数据分页_Load(object sender, EventArgs e)
{
this.showdata(1,10);
this.getpagecount();
this.label1.Text = "1";
this.label2.Text = pagecount.ToString();
}
public void getpagecount()
{
string sqlpagecount = "select count(*) from PurchasePrice";
DataTable dt= help.ReturnDataTable(sqlpagecount,help.conn2);
this.datacount=Convert.ToInt32(dt.Rows[0][0].ToString());
if (datacount % pagesize == 0)
{
this.pagecount = datacount / pagesize;//可以分多少页
}
else
{
this.pagecount = (datacount / pagesize)+1;//可以分多少页
}
}
public void showdata(int pageindex,int pagesize)
{
string sql = "select top " + pagesize + " * from PurchasePrice where Supplyid not in (select top ("+pagesize*(pageindex-1)+") Supplyid from PurchasePrice )";
DataTable dt = help.ReturnDataTable(sql,help.conn2);
this.dataGridView1.DataSource=dt.DefaultView;
} private void button1_Click(object sender, EventArgs e)
{
this.pageindex = 1;
this.label1.Text = this.pageindex.ToString();
this.showdata(pageindex,this.pagesize);
this.button2.Enabled = false;
this.button1.Enabled = false;
}
//上一页
private void button2_Click(object sender, EventArgs e)
{
if (this.pageindex > 1)
{
this.showdata(this.pageindex - 1, this.pagesize);
this.pageindex -= 1;
this.label1.Text = this.pageindex.ToString(); this.button3.Enabled = true;
this.button4.Enabled = true;
}
else
{
this.button2.Enabled = false;
this.button1.Enabled = false;
}
}
//下一页
private void button3_Click(object sender, EventArgs e)
{
if (this.pageindex < pagecount)
{
this.showdata(this.pageindex + 1, this.pagesize);
this.pageindex += 1;
this.label1.Text = this.pageindex.ToString();
this.button1.Enabled = true;
this.button2.Enabled = true;
}
else
{
this.button3.Enabled = false;
this.button4.Enabled = false;
}
}
//末页
private void button4_Click(object sender, EventArgs e)
{
this.showdata(this.pagecount, this.pagesize);
this.pageindex = this.pagecount;
this.label1.Text = this.pageindex.ToString();
this.button4.Enabled = false;
this.button3.Enabled = false;
this.button2.Enabled = true;
this.button1.Enabled = true;
} }
}
比如20条数据每页的话，点击下一页按扭：Sql语句是 从数据库中 取 第 21到40 条信息。
点击首页按据：查询前20条信息 点击尾页：是查询 末尾 20条数据不难，以后如果你从事 与数据库 相关 编程 的工作 都会用到这些！觉得我说的有用，就给几分就行了。呵呵
我们以前也做过
增加一个每页显示多少行的属性DisplayRowCount
然后四个按钮的方法写进去
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;//Winform datagridview 大数量查询分页显示 微软的解决办法
namespace WindowsApplication1
{
public partial class Form1 : Form
{
// WinForm上的控件
Button prevBtn = new Button();
Button nextBtn = new Button();
Button firstBtn = new Button();
Button lastBtn = new Button();
static DataGrid myGrid = new DataGrid();
static Label pageLbl = new Label();
// 分页的变量
static int pageSize = 4; // 每页显示多少
static int leftpageSiz; // 分页余数
static int totalPages = 0; // 总共页数
static int currentPage = 0; // 当前页数.
static string firstVisibleCustomer = ""; // First customer on page to determine location for move previous.
static string lastVisibleCustomer = ""; // Last customer on page to determine location for move next.
// DataSet to bind to DataGrid.
static DataTable custTable;
// Initialize connection to database and DataAdapter.
static SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
static SqlDataAdapter custDA = new SqlDataAdapter("", nwindConn);
static SqlCommand selCmd = custDA.SelectCommand; public Form1()
{
InitializeComponent(); // Initialize controls and add to form.
this.ClientSize = new Size(360, 274);
this.Text = "NorthWind Data";
myGrid.Size = new System.Drawing.Size(729, 240);
myGrid.Dock = System.Windows.Forms.DockStyle.Top;
myGrid.AllowSorting = true;
myGrid.CaptionText = "NorthWind Customers";
myGrid.ReadOnly = true;
myGrid.AllowNavigation = false;
myGrid.PreferredColumnWidth = 150; firstBtn.Text = "First";
firstBtn.Size = new Size(48, 24);
firstBtn.Location = new Point(22, 240);
firstBtn.Click += new EventHandler(First_OnClick); prevBtn.Text = "Prev";
prevBtn.Size = new Size(48, 24);
prevBtn.Location = new Point(92, 240);
prevBtn.Click += new EventHandler(Prev_OnClick); nextBtn.Text = "Next";
nextBtn.Size = new Size(48, 24);
nextBtn.Location = new Point(160, 240);
nextBtn.Click += new EventHandler(Next_OnClick); lastBtn.Text = "Last";
lastBtn.Size = new Size(48, 24);
lastBtn.Location = new Point(230, 240);
lastBtn.Click += new EventHandler(Last_OnClick);
pageLbl.Text = "没有记录";
pageLbl.Size = new Size(130, 16);
pageLbl.Location = new Point(300, 244);
this.Controls.Add(myGrid);
this.Controls.Add(prevBtn);
this.Controls.Add(firstBtn);
this.Controls.Add(nextBtn);
this.Controls.Add(lastBtn);
this.Controls.Add(pageLbl); // 获取第一页数据
GetData("Default");
DataView custDV = new DataView(custTable, "", "ID", DataViewRowState.CurrentRows);
myGrid.SetDataBinding(custDV, ""); }
public static void First_OnClick(object sender, EventArgs args)
{
GetData("First");
}
public static void Prev_OnClick(object sender, EventArgs args)
{
GetData("Previous");
}
public static void Next_OnClick(object sender, EventArgs args)
{
GetData("Next");
}
public static void Last_OnClick(object sender, EventArgs args)
{
GetData("Last");
}
private void Form1_Load(object sender, EventArgs e)
{ }
public static void GetData(string direction)
{
// Create SQL statement to return a page of records.
selCmd.Parameters.Clear();
switch (direction)
{
case "First":
selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers ";
break; case "Next":
selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers " +
"WHERE ID > @ID ORDER BY ID";
selCmd.Parameters.Add("@ID", SqlDbType.VarChar, 5).Value = lastVisibleCustomer;
break; case "Previous":
selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers " +
"WHERE ID < @ID ORDER BY ID DESC";
selCmd.Parameters.Add("@ID", SqlDbType.VarChar, 5).Value = firstVisibleCustomer;
break; case "Last":
selCmd.CommandText = "SELECT TOP " + leftpageSiz + " * FROM Customers ORDER BY ID DESC";
break; default:
selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers ORDER BY ID"; // Determine total pages.
SqlCommand totCMD = new SqlCommand("SELECT Count(*) FROM Customers", nwindConn);
nwindConn.Open();
int totalRecords = (int)totCMD.ExecuteScalar();
nwindConn.Close();
totalPages = (int)Math.Ceiling((double)totalRecords / pageSize);
if ((totalRecords % pageSize) == 0)
{
leftpageSiz = pageSize;
}
else
{
leftpageSiz = totalRecords % pageSize;
}
break;
}
// Fill a temporary table with query results.
DataTable tmpTable = new DataTable("Customers");
int recordsAffected = custDA.Fill(tmpTable);
// If table does not exist, create it.
if (custTable == null)
custTable = tmpTable.Clone();
// Refresh table if at least one record returned.
if (recordsAffected > 0)
{
switch (direction)
{
case "First":
currentPage = 1;
break;
case "Next":
currentPage++;
break;
case "Previous":
currentPage--;
break;
case "Last":
currentPage = totalPages;
break;
default:
currentPage = 1;
break;
}
pageLbl.Text = "Page " + currentPage + " of " + totalPages;
// Clear rows and add new results.
custTable.Rows.Clear();
foreach (DataRow myRow in tmpTable.Rows)
custTable.ImportRow(myRow);
// Preserve first and last primary key values.
DataRow[] ordRows = custTable.Select("", "ID ASC");
firstVisibleCustomer = ordRows[0][0].ToString();
lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString();
}
} }
}
@sqlstr nvarchar(4000)="", --查询字符串
@currentpage int=0, --第N页
@pagesize int=0 , --每页行数
@count int output --总行数
---@rowcount as 总行数,@currentpage as 当前页
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
set @count= ceiling(1.0*@rowcount/@pagesize)
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
GO
用 c# 程序进行分页显示： SqlCommand sqlcomm = new SqlCommand("wz_sqlpager", sqlconn0);
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter[] Parameters = new SqlParameter[4];
Parameters[0] = new SqlParameter("@sqlstr", SqlDbType.NVarChar);
Parameters[0].Value = sqltxt; // sql 查询命令
Parameters[1] = new SqlParameter("@currentpage", SqlDbType.Int);
Parameters[1].Value = u_hh; //需要显示的行号
Parameters[2] = new SqlParameter("@pagesize", SqlDbType.Int);
Parameters[2].Value = 10; // 每页的行数
Parameters[3] = new SqlParameter("@count", SqlDbType.Int);
Parameters[3].Direction = ParameterDirection.Output;
Parameters[3].Value = 0; // 总行数
sqlcomm.Parameters.AddRange(Parameters); if (Tables.Tables.Count != 0)
{
if (Tables.Tables[1].Rows.Count!= 0)
Tables.Tables[1].Clear();
}
tableAdapter.SelectCommand = sqlcomm;
tableAdapter.Fill(Tables);
如果用 datagridview 显示结果，则datagridview 窗口显示行数: // hs 行数 datagridview.Rows.Add(1);
hs= (datagridview.DisplayRectangle.Height - datagridview.ColumnHeadersHeight )/ datagridview.Rows[0].Height;
datagridview.Rows.RemoveAt(0);