使用存储过程实现分页

--创建存储过程
create proc usp_userInfo
@pageSize int=7,--每页显示条数
@pageIndex int=1,--每页显示页数
@pageCount int output ,--显示总的条数
@recodNumber int output--显示总的页数
as
begin
select
u.id,
u.userName,
u.userPwd,
u.userGander,
u.userAddress
from(select *,rn= ROW_NUMBER() over(order by id) from UserInfo) as u
where u.rn between (@pageIndex-1)*@pageSize+1 and @pageSize*@pageIndex
--计算总的记录条数
set @pageCount=(select COUNT(*) from UserInfo)
--计算总的页数
set @recodNumber =CEILING( @pageCount*1.0/@pageSize)
end
--声明变量
declare @count int, @number int
--查询存储过程,给输出参数赋值
exec usp_userInfo @pageCount=@count output,@recodNumber=@number output
--输出结果
print @count
print @number

效果如图所示:

 

public partial class Form1 : Form
{
  //定义变量
  private int pageSize = 7;//当前显示的条数
  private int pageIndex = 1;//当前显示第几页
  private int pageCount;//显示当前总条数
  private int recodNumber;//显示总的页数
  public Form1()
  {
    InitializeComponent();
  }

  private void Form1_Load(object sender, EventArgs e)
  {
    ListData();
  }

  private void ListData()
  {
    //连接数据库
    string sqlconn = "server=.;database=TestDB;uid=sa;pwd=123456";
    DataTable table = new DataTable();
    //数据库中的存储过程名称(usp_userInfo)
  using (SqlDataAdapter adapter = new SqlDataAdapter("usp_userInfo", sqlconn))
  {
    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;//声明是存储过程
    SqlParameter[] parameter = new SqlParameter[]
  {
    //第一个参数必须和数据库中保持一致
    new SqlParameter("@pageSize",SqlDbType.Int){ Value=pageSize},
    new SqlParameter("@pageIndex",SqlDbType.Int){ Value=pageIndex},
    new SqlParameter("@pageCount", SqlDbType.Int){ Direction=ParameterDirection.Output },//在数据库中是输出参数,所以这里不能用Value,必须用                Direction=ParameterDirection.Output(表示输出)
    new SqlParameter("@recodNumber", SqlDbType.Int){ Direction=ParameterDirection.Output },

  };
    adapter.SelectCommand.Parameters.AddRange(parameter);
    adapter.Fill(table);
    //根据parameter数组的下标取到值
    pageCount = int.Parse(parameter[2].Value.ToString());
    recodNumber = int.Parse(parameter[3].Value.ToString());
    label1.Text = "总条数:" + pageCount.ToString();
    label2.Text = "总页数:" + recodNumber.ToString();
    label3.Text = "当前页数:" + pageIndex;
    //绑定数据源
    this.dataGridView1.DataSource = table;

  }
}

  //下一页

  private void button1_Click(object sender, EventArgs e)
  {
    if (pageIndex == recodNumber)
    {
      this.button1.Hide();
    }
    else
    {
      this.button1.Show();
      this.button2.Show();
      pageIndex++;
      ListData();
    }

  }

    //上一页

    private void button2_Click(object sender, EventArgs e)
    {
      if (pageIndex == 1)
      {
        this.button2.Hide();
      }
      else
      {
        this.button2.Show();
        this.button1.Show();
        pageIndex--;
        ListData();
      }
    }
}

posted @ 2019-01-18 17:36 李洛尘 阅读(...) 评论(...) 编辑 收藏