An example of ADO calling paging query stored procedure

Time:2020-10-8

1、 Paging stored procedures

----------Writing a paging query using stored procedures-----------------------
Set NOCOUNT off -- close sqlserver message
--Set NOCOUNT on -- turn on sqlserver message
go
create proc usp_getMyStudentsDataByPage
--Input parameters
@PageSize int = 7, - number of records per page
@PageIndex int = 1, - what page of record do you want to view
--Output parameters
@RecordCount int output, -- the total number of records
@Pagecount int output -- total number of pages
as
begin
--1. Write query statements to find out the data users want
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. Calculate the total number of records
set @recordcount=(select count(*) from MyStudent)
--3. Calculate the total number of pages
set @pagecount=ceiling(@recordcount*1.0/@pagesize)
end
 
--Define output parameters before calling
declare @rc int,@pc int
exec usp_getMyStudentsDataByPage @pagesize=7,@pageindex=4, @[email protected] output,@[email protected] output
print @rc
print @pc

2、 ADO calls stored procedure

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 passed Ado.Net Calling stored procedures
{
 public partial class Form1 : Form
 {
  public Form1()
  {
   InitializeComponent();
  }
  Private int PageIndex = 1; // current page number to view
  Private int PageSize = 7; // number of records displayed per page

  Private int pagecount; // total pages
  Private int RecordCount; // total number of entries
  //The first page of data is displayed when the form is loaded
  private void Form1_Load(object sender, EventArgs e)
  {
   LoadData();
  }
  private void LoadData()
  {
   //Load data according to PageIndex
   string constr = "Data Source=steve-pc;Initial Catalog=itcast2014;Integrated Security=True";
   #region 1
   //using (SqlConnection conn = new SqlConnection(constr))
   //{
   //// change the SQL statement into a stored procedure name
   // string sql = "usp_getMyStudentsDataByPage";
   // using (SqlCommand cmd = new SqlCommand(sql, conn))
   // {
   //// tells the SqlCommand object that the stored procedure being executed is not a SQL statement
   //  cmd.CommandType = CommandType.StoredProcedure;
   //// add parameters (there are several parameters in the stored procedure, so we need to add several parameters here)
   //// @ PageSize int = 7, - number of records per page
   //// @ PageIndex int = 1, - what page of record do you want to view
   //// @ RecordCount int output, - total number of records
   //// @ pagecount int output -- total pages
   //  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);
   //// open connection
   //  conn.Open();
   //// execute
   //using(SqlDataReader reader=cmd.ExecuteReader())
   //{
    //reader.Read()
   //}
   //pms[2].Value
   // }
   //}
   #endregion
   //DataAdapter mode
   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);
    //Get the output parameters and assign them to label
    Label1.text = "total number of items:" PMS [2] Value.ToString ();
    Label2.text = "total pages:" PMS [3] Value.ToString ();
    Label3.text = "current page:" PageIndex;
    //Data binding
    this.dataGridView1.DataSource = dt;
   }
  }
  //Next page
  private void button2_Click(object sender, EventArgs e)
  {
   pageIndex++;
   LoadData();
  }
  //Previous page
  private void button1_Click(object sender, EventArgs e)
  {
   pageIndex--;
   LoadData();
  }
 }
}

design sketch:

3、 Through ado.net The difference between calling stored procedures and calling SQL statements with parameters.

1> Change SQL statement into stored procedure name

2> Set the commandtype of the SqlCommand object to CommandType.StoredProcedure

The essence of this step is to prefix the stored procedure name with “exec”

3> Set the parameters of the SqlCommand object according to the parameters of the stored procedure.

4> If there is an output parameter, you need to set the direction property of the output parameter to: direction= ParameterDirection.Output

4、 If the stored procedure is executed by calling the executereader() method of the command object, you must wait until the reader object is closed before getting the output parameters.

The above example of ADO calling paging query stored procedure explains all the content shared by Xiaobian. I hope to give you a reference, and also hope that you can support developeppaer more.