An example of ADO invoking a paged query stored procedure _ Practical Tips

The following small series for you to share an ADO call paged query stored procedures of the example, with a good reference value, I hope to help everyone to better use ADO for paging. be interested in ADO and follow the small part.

One, paging stored procedures

----------Write a paged query using stored procedures-----------------------SET NOCOUNT off--close SQL Server Messages--set nocount ON-- Open SQL Server message gocreate proc usp_getmystudentsdatabypage--input parameter @pagesize int=7,--number of records per page @pageindex int=1,-- The current number of pages to view the record--output parameters @recordcount int output,--total records @pagecount int output-- The total number of pages asbegin--1. Write the query statement, the user to query the data out Selectt.fid,t.fname,t.fage,t.fgender,t.fmath,t.fclassid,t.fbirthdayfrom ( Select *,rn=row_number () over (order by FID ASC) from Mystudent) as Twhere t.rn between (@pageindex-1) * @pagesize +1 and @pag esize* @pageindex--2. Calculates the total number of record bars set @recordcount = (select count (*) from Mystudent)--3. Total pages Calculated set @pagecount =ceiling (@ recordcount*1.0/@pagesize) End-Defines the output parameter declare @rc int before the call, @pc intexec usp_getmystudentsdatabypage @pagesize =7,@ pageindex=4, @recordcount = @rc output, @pagecount = @pc outputprint @rcprint @pc

Second, ADO calls the 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 Calling the stored procedure through ADO {public partial class Form1:form {public Form1 () {InitializeComponent (); } private int pageIndex = 1;//Current page number to view private int pageSize = 7;//number of record bars per page private int pagecount;//total pages private int re  cordcount;//total number of bars//When the form is loaded, the data for the first page is displayed private void Form1_Load (object sender, EventArgs e) {loaddata (); } private void LoadData () {//per pageindex to load data string constr = "DataSource source=steve-pc;initial catalog=itcast2014;inte   grated security=true "; #region 1//using (SqlConnection conn = new SqlConnection (CONSTR))//{////Change SQL statement to stored procedure name//String sql = "Usp_ge   Tmystudentsdatabypage "; using (SqlCommand cmd = new SqlCommand (SQL, conn))//{/////Tell SqlCommand object, the stored procedure executed now is not SQL statement//CMD.   CommandType = CommandType.StoredProcedure; //  //Add parameters (there are several parameters in the stored procedure, you need to add a few parameters here)////@pagesize int=7,--number of records per page////@pageindex int=1,--currently want to view the first page of the record///@recordcount int output,--Total number of records///@pagecount int output--Total pages//sqlparameter[] PMS = new sqlparameter[] {//new Sqlpa Rameter ("@pagesize", SqlDbType.Int) {value =pagesize},//New SqlParameter ("@pageindex", SqlDbType.Int) {value = PageIndex},//New SqlParameter ("@recordcount", SqlDbType.Int) {direction=parameterdirection.output},//New Sqlparam   Eter ("@pagecount", SqlDbType.Int) {direction=parameterdirection.output}//}; Cmd.   Parameters.addrange (PMS); Open the connection//conn.   Open (); Execute//using (SqlDataReader reader=cmd. ExecuteReader ())//{//reader. Read ()//}//pms[2].   Value//}//} #endregion//dataadapter way 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); Gets the output parameter and assigns a value to the label Label1. Text = "Total number of bars:" + pms[2].    Value.tostring (); Label2. Text = "Total Pages:" + pms[3].    Value.tostring (); Label3.    Text = "Current page:" + pageIndex;   Data binding this.dataGridView1.DataSource = DT;   }}//Next private void button2_click (object sender, EventArgs e) {pageindex++;  LoadData ();   }//prev private void Button1_Click (object sender, EventArgs e) {pageindex--;  LoadData (); } }}

：

The difference between calling a stored procedure by ADO and calling an SQL statement with parameters.

1> the SQL statement into the stored procedure name

2> Setting the CommandType of the SqlCommand object to CommandType.StoredProcedure

The essence of this step is to add an "exec" to the stored procedure name

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

4> If an output parameter is required to set the output parameter, the Direction property is: Direction=parameterdirection.output

Iv. If the stored procedure is executed by invoking the ExecuteReader () method of the Command object, then to get the output parameters, you must wait until the reader object is closed before you can get the output parameters.

Above this ADO call paged query stored procedure instance explanation is small to share all the content of everyone, hope to give you a reference, also hope that we support a lot of topic.alibabacloud.com.

Related recommendations:

Parsing the SQL Server database to perform additions and deletions to the operation

Ado. NET Practical Example Introduction

Ado. NET implementing a tutorial on SQL Server database operations