C # Ajax paging instances based on database stored procedures

In this paper, we describe the Ajax paging implementation method of C # based on database stored procedure. Share to everyone for your reference. Specific as follows:

First we declare the definition stored procedure in the database (SQL Server)

The code is as follows:

Use sales– to specify a database

if (exists (SELECT * from sys.objects where name= ' proc_location_paging ')) – Delete if this proc_location_paging stored procedure exists

drop proc Proc_location_paging

Go

create proc proc_location_paging– creating a stored procedure

(

@pageSize int,– Page Size

@currentpage int,– Current Page

@rowCount int output,– Total rows (outgoing parameters)

@pageCount int output– Total pages (outgoing parameters)

)

As

Begin

Select @rowCount = COUNT (locid) from location– assignment to @rowcount

Select @pageCount = CEILING ((count (locid) +0.0)/@pageSize) from location– assigns a value to @pagecount

Select Top (@pagesize) * FROM (select Row_number () over (order by Locid) as rowid,* from location) as T1

where RowID > (@pageSize * (@currentpage-1))

End

Go

——————————— above means that the stored procedure is already defined.

——————————— The following is the execution of this stored procedure. We can see the results.

declare @rowCount int, @pageCount int– declare two parameters first

– Execute proc_location_paging this stored procedure. @rowCount, there is output at the back of the @pageCount indicating that both

exec proc_location_paging 10,1, @rowCount output, @pageCount output

Select @rowCount, @pageCount – Queries the values of both parameters

Because it accesses the database directly, we write the following method into the DAL layer, where I write it to SqlHelper

The code is as follows:

Using System;

Using System.Collections.Generic;

Using System.Linq;

Using System.Text;

Using System.Configuration;

Using System.Data.SqlClient;

Using System.Data;

Using System.Reflection;

Namespace Llsql.dal

{

public class SqlHelper

{

<summary>

Get Connection Database string

</summary>

private static string connstr = configurationmanager.connectionstrings["ConnStr"]. ConnectionString;

public static DataTable executeprocpagelist (int pageSize, int. currentpage, out int rowCount, out int pagecount)

{

using (SqlConnection conn = new SqlConnection (CONNSTR))

{

Conn. Open ();

using (SqlCommand cmd = conn. CreateCommand ())

{

Cmd.commandtext = "proc_location_paging"; The name of the stored procedure

Cmd.commandtype = CommandType.StoredProcedure; The SET command is a stored procedure type (i.e.: Indicates that we are performing a stored procedure)

RowCount = 0;

PageCount = 0;//Here to assign a value to Rowcount,pagecount, because when using out to pass parameters, in the method must be assigned to the out parameter to use it, but although here to give it an initial value, but in the execution of the stored procedure, The stored procedure assigns values to these two parameters and returns them back to us, which is what we want to value.

sqlparameter[] Parameters ={

New SqlParameter ("@pageSize", PageSize),

New SqlParameter ("@currentpage", CurrentPage),

New SqlParameter ("@rowCount", RowCount),

New SqlParameter ("@pageCount", PageCount)

};

Because @rowcount and @pagecount are an output parameter in the stored procedure, and parameters this array, the third, and fourth parameters are used to replace the two output parameters, So the two parameters in this array of parameters are set as output parameters.

PARAMETERS[2]. Direction = ParameterDirection.Output;

PARAMETERS[3]. Direction = ParameterDirection.Output;

Cmd. Parameters.addrange (Parameters); Pass parameters to our cmd command object

DataTable dt = new DataTable ();

Using (SqlDataAdapter adapter = new SqlDataAdapter (cmd))

{

Adapter. Fill (dt);//to the database to execute the stored procedure and populate the results into the DT table

}

When the stored procedure finishes executing, the stored procedure passes the two output parameters. So we're here to get these two return parameters.

RowCount = Convert.ToInt32 (parameters[2]. Value);

PageCount = Convert.ToInt32 (parameters[3]. Value);

return DT;

}

}

}

}

}

In addition to the Declaration, Running GuestArticles are original, reproduced please link to the form of the address of this article
C # Ajax paging instances based on database stored procedures

This address: http://www.paobuke.com/develop/c-develop/pbk23173.html

Related content Examples of exporting Excel in C # The two ways to initialize the C # console base list WinForm implementation of a mouse-piercing form skeleton effect about finalize mechanism and reference, reference queue usage
Easily learn C # 's foreach Iteration Statement C # Implementation calculates a point around another point to rotate a specified Radian recoil value method C # write DES encryption, decrypt Class C # implements the method of moving the mouse to the graph display value

C # Ajax paging instances based on database stored procedures