Create a stored procedure with output parameters and implement DataGridView paging functionality in C #

Shortcomings, please correct me!

To create a stored procedure with output parameters

if exists(Select *  fromsysobjectswhereName='Usp_getpage1')Drop procedureUsp_getpage1GoCreate procedureUsp_getpage1--Stored Procedure name@count intOutput--Output Parameters@countIndex int=1,--parameters with default values@countPage int=5--parameters with default values as --an SQL statement. Row_number () over () returns the serial number of the result set partition expert, with the first row of each partition starting from 1, where the alias ID is given. Temp is also an alias, which is the data of the front parenthesisSelect *  from(SelectRow_number () Over(Order  bySTUDENTNO) ID,*  fromStudentTemp whereId>(@countIndex-1)*@countPage  andId<=@countIndex*@countPage--assigning values to output parametersSet @count=Ceiling((Select Count(*) fromStudent*1.0/@countPage) --ceiling Round The rhythm, multiplied by 1.0, is the int divided by int or int. Floating-point type divided by integral typeGo--executes a stored procedure that has a default value that can be given without a value, but the output parameter needs to declare a variable to receiveDeclare @num intExecuteUsp_getpage1@count=@numOutputPrint @num 

Create a class that has a static method, which is called, and the reference namespace

Using System.Data;
Using System.Data.SqlClient;

Class SqlHelper    {public        static readonly string connstr = "Data source=.;i Nitial catalog=myschoolmoredata;integrated security=true ";        <summary>////        three parameters get data result set///</summary>//        <param name= "CommandText" >sql command, Stored Procedure name </param>//        <param name= "CType" > Command string Type </param>//        <param name= "PS" > Parameter data params keyword indicates nullable </param>/////        <returns> Returns the result set of a DataTable </returns> public        static DataTable LoadData (String commandtext,commandtype ctype,params sqlparameter[]ps)        {            SqlDataAdapter da = new SqlDataAdapter (CommandText, connstr);            Da. SelectCommand.Parameters.AddRange (PS);            Da.SelectCommand.CommandType = CType;            DataTable dt = new DataTable ();            Da. Fill (DT);            return dt;                }    }

Interface--One DataGridView space, two buttons

Declaration of three global variables

       int pageIndex = 1;//page        int pagecount = 5;//Each page shows how many data        int count = 0;//Receive output parameter

　　

private void Form1_Load (object sender, EventArgs e) event after initial interface

SqlParameter p=new SqlParameter ("@count", SqlDbType.Int);//@count output parameters, and stored procedures must have the same name. Claim type

P.direction = ParameterDirection.Output; Tell the server the parameter output direction
Call method Static


This.count = (int) p.value;//record first page

Events written on previous page buttons

private void Btnper_click (object sender, EventArgs e)        {   //When the page number is 1, return            if (pageindex==1)            {                MessageBox.Show ("Already the first page");                return;             }            pageindex--;//Click the last page number minus 1            //Declare the parameter array, the parameter must match the variable name declared by the stored procedure            SqlParameter []ps={new SqlParameter ("@countIndex", PageIndex),                              new SqlParameter ("@countPage", PageCount),                              new SqlParameter ("@count", SqlDbType.Int)                            };            Sets the direction of the output parameter            ps[2]. Direction = ParameterDirection.Output;            DataTable dt = Sqlhelper.loaddata ("Usp_getpage1", CommandType.StoredProcedure, PS);//commandtype.storedprocedure Tells the server that it is executing a stored procedure that is not an SQL statement

            Dgvdata.datasource = dt;//binding Data
            This.count = (int) ps[2]. value;//Record Page}

Then the next page of the button's event code and the previous page is similar to just the variable pageindex is the pageindex++ code as follows:

   private void Btnnext_click (object sender, EventArgs e)        {            if (pageindex==this.count)//different            {                MessageBox.Show ("It's the last page already");                return;            }            pageindex++;//Click Next page to add a different place            //Declaration parameter array            sqlparameter[] PS ={new SqlParameter ("@countIndex", PageIndex),                              New SqlParameter ("@countPage", PageCount),                              new SqlParameter ("@count", SqlDbType.Int)                            };            Sets the direction of the output parameter            ps[2]. Direction = ParameterDirection.Output;            DataTable dt = Sqlhelper.loaddata ("Usp_getpage1", CommandType.StoredProcedure, PS);            Dgvdata.datasource = dt;//bound data            this.count = (int) ps[2]. value;//to record the output value every time        }

　　

Summary: 1. Proper creation of stored procedures is important

2. The name of the parameter must be consistent with the variable name of the stored procedure

3. The output parameter must be declared while setting his direction.

Settings for 4.commandtype.storedprocedure