快捷導(dǎo)航

ADO調(diào)用分頁(yè)查詢存儲(chǔ)過(guò)程的實(shí)例講解

更新時(shí)間：2017年12月12日 09:10:03 作者：hao_1234_1234

下面小編就為大家分享一篇ADO調(diào)用分頁(yè)查詢存儲(chǔ)過(guò)程的實(shí)例講解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧

一、分頁(yè)存儲(chǔ)過(guò)程

----------使用存儲(chǔ)過(guò)程編寫一個(gè)分頁(yè)查詢-----------------------
set nocount off --關(guān)閉SqlServer消息
--set nocount on --開(kāi)啟SqlServer消息
go
create proc usp_getMyStudentsDataByPage
--輸入?yún)?shù)
@pagesize int=7,--每頁(yè)記錄條數(shù)
@pageindex int=1,--當(dāng)前要查看第幾頁(yè)的記錄
--輸出參數(shù)
@recordcount int output,--總的記錄的條數(shù)
@pagecount int output --總的頁(yè)數(shù)
as
begin
--1.編寫查詢語(yǔ)句，把用戶要的數(shù)據(jù)查詢出來(lái)
select
t.fid,
t.fname,
t.fage,
t.fgender,
t.fmath,
t.fclassid,
t.fbirthday
from (select *,rn=row_number() over(order by fid asc) from MyStudent) as t
where t.rn between (@pageindex-1)*@pagesize+1 and @pagesize*@pageindex
--2.計(jì)算總的記錄條數(shù)
set @recordcount=(select count(*) from MyStudent)
--3.計(jì)算總頁(yè)數(shù)
set @pagecount=ceiling(@recordcount*1.0/@pagesize)
end
 
--調(diào)用前定義輸出參數(shù)
declare @rc int,@pc int
exec usp_getMyStudentsDataByPage @pagesize=7,@pageindex=4, @recordcount=@rc output,@pagecount=@pc output
print @rc
print @pc

二、ADO調(diào)用存儲(chǔ)過(guò)程

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.Data.SqlClient;
namespace _02通過(guò)Ado.Net調(diào)用存儲(chǔ)過(guò)程
{
 public partial class Form1 : Form
 {
  public Form1()
  {
   InitializeComponent();
  }
  private int pageIndex = 1;//當(dāng)前要查看的頁(yè)碼
  private int pageSize = 7;//每頁(yè)顯示的記錄條數(shù)

  private int pageCount;//總頁(yè)數(shù)
  private int recordCount;//總條數(shù)
  //窗體加載的時(shí)候顯示第一頁(yè)的數(shù)據(jù)
  private void Form1_Load(object sender, EventArgs e)
  {
   LoadData();
  }
  private void LoadData()
  {
   //根據(jù)pageIndex來(lái)加載數(shù)據(jù)
   string constr = "Data Source=steve-pc;Initial Catalog=itcast2014;Integrated Security=True";
   #region 1
   //using (SqlConnection conn = new SqlConnection(constr))
   //{
   // //將sql語(yǔ)句變成存儲(chǔ)過(guò)程名稱
   // string sql = "usp_getMyStudentsDataByPage";
   // using (SqlCommand cmd = new SqlCommand(sql, conn))
   // {
   //  //告訴SqlCommand對(duì)象，現(xiàn)在執(zhí)行的存儲(chǔ)過(guò)程不是SQL語(yǔ)句
   //  cmd.CommandType = CommandType.StoredProcedure;
   //  //增加參數(shù)(存儲(chǔ)過(guò)程中有幾個(gè)參數(shù)，這里就需要增加幾個(gè)參數(shù))
   //  //@pagesize int=7,--每頁(yè)記錄條數(shù)
   //  //@pageindex int=1,--當(dāng)前要查看第幾頁(yè)的記錄
   //  //@recordcount int output,--總的記錄的條數(shù)
   //  //@pagecount int output --總的頁(yè)數(shù)
   //  SqlParameter[] pms = new SqlParameter[] { 
   //  new SqlParameter("@pagesize",SqlDbType.Int){Value =pageSize},
   //  new SqlParameter("@pageindex",SqlDbType.Int){Value =pageIndex},
   //  new SqlParameter("@recordcount",SqlDbType.Int){ Direction=ParameterDirection.Output},
   //  new SqlParameter("@pagecount",SqlDbType.Int){Direction=ParameterDirection.Output}
   //  };
   //  cmd.Parameters.AddRange(pms);
   //  //打開(kāi)連接
   //  conn.Open();
   //  //執(zhí)行
   //using(SqlDataReader reader=cmd.ExecuteReader())
   //{
    //reader.Read()
   //}
   //pms[2].Value
   // }
   //}
   #endregion
   //DataAdapter方式
   DataTable dt = new DataTable();
   using (SqlDataAdapter adapter = new SqlDataAdapter("usp_getMyStudentsDataByPage", constr))
   {
    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
    SqlParameter[] pms = new SqlParameter[] { 
     new SqlParameter("@pagesize",SqlDbType.Int){Value =pageSize},
     new SqlParameter("@pageindex",SqlDbType.Int){Value =pageIndex},
     new SqlParameter("@recordcount",SqlDbType.Int){ Direction=ParameterDirection.Output},
     new SqlParameter("@pagecount",SqlDbType.Int){Direction=ParameterDirection.Output}
     };
    adapter.SelectCommand.Parameters.AddRange(pms);
    adapter.Fill(dt);
    //獲取輸出參數(shù)并且賦值給label
    label1.Text = "總條數(shù)：" + pms[2].Value.ToString();
    label2.Text = "總頁(yè)數(shù)：" + pms[3].Value.ToString();
    label3.Text = "當(dāng)前頁(yè)：" + pageIndex;
    //數(shù)據(jù)綁定
    this.dataGridView1.DataSource = dt;
   }
  }
  //下一頁(yè)
  private void button2_Click(object sender, EventArgs e)
  {
   pageIndex++;
   LoadData();
  }
  //上一頁(yè)
  private void button1_Click(object sender, EventArgs e)
  {
   pageIndex--;
   LoadData();
  }
 }
}

效果圖：