通过存储过程来实现分页实例
高手飘过，发给那些在摸索中的新手们，在此表示那些热心肠的高手们，谢谢你们了~
///////////////////////////////////////////////
建立类文件SqlHelper.cs
////////////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace ERP.userclass
{
class SqlHelper
{
private static readonly string strCon = "server='数据库IP';uid='用户名';pwd='密码';database=数据库名";
SqlConnection con = new SqlConnection(strCon);
private DataSet ds;
private SqlCommand cmd;
private SqlDataAdapter sda;
private SqlDataReader myReader;
private SqlTransaction transaction;
//检查连接的方法；若连接是关闭的，则打开连接
public void CheckConnection()
{
if (this.con.State == ConnectionState.Closed)
{
this.con.Open();
}
}
public DataSet ExecuteDataSetProc(string strSQL,int pagesize ,int pageindex)
{
CheckConnection();
DataSet ds1 = new DataSet();
try
{
cmd = new SqlCommand(strSQL, con);
= CommandType.StoredProcedure;
SqlParameter[] para ={ new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int) };
para[0].Value = pagesize;
para[1].Value = pageindex;
cmd.Parameters.AddRange(para);
SqlDataAdapter dap = new SqlDataAdapter();
dap.SelectCommand = cmd;
dap.Fill(ds1, "sickinfo");
}
catch (Exception e)
{
e.Message.ToString();
}
finally
{
con.Close();
}
return ds1;
}
public int ExecuteNonQuery(string strSQL)
{
int count = 0;
CheckConnection();
try
{
cmd = new SqlCommand(strSQL, con);
count = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
con.Close();
}
return count;
}
public int ReturnRowCount(string strSQL)
{
CheckConnection();
try
{
cmd = new SqlCommand(strSQL, con);
return Convert.ToInt32(cmd.ExecuteScalar().ToString());
}
catch
{
return 0;
}
finally
{
con.Close();
}
}
}
}
////////////////////////////////////////////
using System;
using System.Collections.Generic;
using
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace ADMEWinForm
{
public partial class Form1 : Form
{
SqlHelper db = new SqlHelper();
public int rowcount;
public int NowPage = 1, PageCount = 1, PageSize = 20;
public Form1()
{
InitializeComponent();
}
public void BindData()
{
setBtnStatus();
//这里是调用存储过程并传入页码和当前页面
this.dataGridView1.DataSource = db.ExecuteDataSetProc("Pagination", PageSize, NowPage).Tables["sickinfo"];
}
private void Form1_Load(object sender, EventArgs e)
{
BindData();
}
//计算余页
public int OverPage()
{
int pages = 0;
if (rowcount % PageSize != 0)
pages = 1;
else
pages = 0;
return pages;
}
public void setBtnStatus()
{
rowcount= db.ReturnRowCount("select count(1) from TGongwen ");
PageCount = rowcount / PageSize + OverPage();
this.label2.Text="共"+PageCount+"页 共"+rowcount+"条记录";
this.label1.Text = "当前是第" + NowPage + "页";
if (NowPage > 1)
{
this.button5.Enabled = true;
this.button6.Enabled = true;
}
else
{
this.button5.Enabled = false;
this.button6.Enabled = false;
}
if (NowPage == PageCount)
{
this.button7.Enabled = false;
this.button8.Enabled = false;
}
else
{
this.button7.Enabled = true;
this.button8.Enabled = true;
}
}
private void button5_Click(object sender, EventArgs e)
{
NowPage = 1;
Form1_Load(sender,e);
}
private void button6_Click(object sender, EventArgs e)
{
NowPage -= 1;
Form1_Load(sender,e);
}
private void button7_Click(object sender, EventArgs e)
{
NowPage += 1;
Form1_Load(sender,e);
}
private void button8_Click(object sender, EventArgs e)
{
NowPage = PageCount;
Form1_Load(sender,e);
}
}
}
///////////////////////存储过程/////////////////////////////////////////////////
CREATE PROCEDURE Pagination
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1 -- 页码
AS
declare @strSQL varchar(5000) -- 主语句
set @strSQL ='select top ' +str(@PageSize) +' * from sickinfo where id>(select isnull(max(id),0) from(select top '+ str((@PageIndex-1)*@PageSize) +' id from sickinfo order by id )as T)order by id '
if(@PageIndex =1)
begin
set @strSQL='select top '+str(@PageSize)+' * from sickinfo order by id desc '
end
else
begin
set @strSQL ='select top ' +str(@PageSize) +' * from sickinfo where id<(select isnull (min(id),0) from(select top '+str((@PageIndex-1)*@PageSize) +' id from sickinfo order by id desc )as T)order by id desc '
end
exec ( @strSQL)
GO
///////////////////////////////////////////////
没测试下语法 不用写那么复杂 select 一般不需要返回参数 你用datareader查询就返回datareder dataset就返回dataset 和执行sql语句一样 例如
程序代码：
