Examples of ADO calling paging query stored procedures, and ado examples

Examples of ADO calling paging query stored procedures, and ado examples

I. Paging Stored Procedure

---------- Compile a paging query using Stored Procedures --------------------- set nocount off -- disable SqlServer message -- set nocount on -- enable SqlServer message gocreate proc usp_getMyStudentsDataByPage -- input parameter @ pagesize int = 7, -- number of records per page @ pageindex int = 1, -- the number of records on the page to be viewed -- output parameter @ recordcount int output, -- total number of records @ pagecount int output -- total number of pages asbegin -- 1. compile a query statement to query the user's data 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 @ pagesize * @ pageindex -- 2. calculate the total number of records set @ recordcount = (select count (*) from MyStudent) -- 3. set @ pagecount = ceiling (@ recordcount * 1.0/@ pagesize) end -- Define the output parameter declare @ rc int, @ pc intexec usp_getMyStudentsDataByPage @ pagesize = 7, @ pageindex = 4, @ recordcount = @ rc output, @ pagecount = @ pc outputprint @ rcprint @ pc

Ii. ADO call 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 through Ado. net call Stored Procedure {public partial class Form1: Form {public Form1 () {InitializeComponent ();} private int pageIndex = 1; // The current page number to be viewed: private int pageSize = 7; // The number of records displayed per page: private int pageCount; // the total number of pages: private int recordCount; // total number of entries // display the private void Form1_Load (object sender, EventArgs e) {LoadData ();} private void LoadData () on the first page when the form is loaded () {// load Data string constr = "Data Source = steve-pc; Initial Catalog = itcast2014; Integrated Security = True "; # region 1 // using (SqlConnection conn = new SqlConnection (constr) // {// convert the SQL statement into the stored procedure name // string SQL = "usp_getMyStudentsDataByPage "; // using (SqlCommand cmd = new SqlCommand (SQL, conn) // {// tell the SqlCommand object that the currently executed stored procedure is not an SQL statement // cmd. commandType = CommandType. storedProcedure; // Add parameters (several parameters are included in the stored procedure, and several parameters need to be added here) /// @ pagesize int = 7, -- number of records per page /// @ pageindex int = 1, -- the records on the page to be viewed /// @ recordcount int output, -- total number of records /// @ pagecount int output -- total number of 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 the connection // conn. open (); // run // 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); // obtain the output parameter and assign it to label label1.Text = "Total number of entries:" + pms [2]. value. toString (); label2.Text = "Total number of pages:" + pms [3]. value. toString (); label3.Text = "Current page:" + pageIndex; // bind data to this. dataGridView1.DataSource = dt ;}// next page private void button2_Click (object sender, EventArgs e) {pageIndex ++; LoadData () ;}// Previous Page private void button#click (object sender, eventArgs e) {pageIndex --; LoadData ();}}}

:

Iii. Differences between calling stored procedures through ado.net and calling SQL statements with parameters.

1> change the SQL statement to the stored procedure name.

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

In this step, an "exec" is added before the stored procedure name"

3> set SqlCommand object parameters based on stored procedure parameters.

4> If an Output parameter exists, set the Direction attribute 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 to obtain the output parameter.

The above example of calling the paging query stored procedure in ADO is all the content that I have shared with you. I hope to give you a reference and support for the help house.