AJAX paging (content involves database stored procedures) and ajax stored procedures

AJAX paging (content involves database stored procedures) and ajax stored procedures

<1>

First, we declare and define the stored procedure in the database (SQL Server ).

Use sales -- specify the database if (exists (select * from sys. objects where name = 'proc _ location_Paging ') -- if the proc_location_paging stored procedure exists, delete drop proc proc_location_Paginggocreate proc proc_location_Paging -- create a stored procedure (@ pageSize int, -- page size @ currentpage int, -- current page @ rowCount int output, -- total number of rows (outgoing parameter) @ pageCount int output -- total number of pages (outgoing parameter )) asbeginselect @ rowCount = COUNT (locid) from location -- assign @ rowCount a select @ pageCount = CEILING ( (Count (locid) + 0.0)/@ pageSize) from location -- assign a select top (@ pagesize) * from (select ROW_NUMBER () over (order by locid) to @ pageCount) as rowID, * from location) as t1where rowID> (@ pageSize * (@ currentpage-1) endgo --------------------------------- the above indicates that the stored procedure has been defined. --------------------------------- The following describes how to execute this stored procedure. We can see that the results declare @ rowCount int, @ pageCount int -- declare two parameters first -- execute the proc_location_Paging stored procedure. @ RowCount, @ pageCount is followed by output, which indicates that both of them are output parameters exec proc_location_Paging 10, 1, @ rowCount output, @ pageCount output select @ rowCount, @ pageCount -- query the values of these two parameters

<2>

Because the database is accessed directly, we write the following method into the DAL layer. Here I write it into SqlHelper.

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> // obtain the Connection database string /// </summary> private static string connStr = ConfigurationManager. connectionStrings ["ConnStr"]. connectionString; public static DataTable ExecuteP RocPageList (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"; // name of the stored procedure cmd. commandType = CommandType. storedProcedure; // set the command to the stored procedure type (that is, specify that we are executing a stored procedure) rowCount = 0; pageCount = 0; // rowCount is randomly given here, pageCount is assigned a value because the parameter is passed out. You must assign a value to the out parameter in the method before using it. However, although it is assigned an initial value, during the execution of the stored procedure, the stored procedure will assign values to these two parameters and return them to us. That is the value we want SqlParameter [] parameters = {new SqlParameter ("@ pageSize", pageSize ), new SqlParameter ("@ currentpage", currentPage), new SqlParameter ("@ rowCount", rowCount), new SqlParameter ("@ pageCount", pageCount )}; // In the stored procedure, @ rowCount and @ pageCount are an output parameter, while in the parameters array, and the fourth parameter is used to replace the two output parameters, so here we need to replace the two parameters in the parameters array. Set as output parameter. Parameters [2]. direction = ParameterDirection. output; parameters [3]. direction = ParameterDirection. output; cmd. parameters. addRange (parameters); // The cmd command object passed to us. DataTable dt = new able (); using (SqlDataAdapter adapter = new SqlDataAdapter (cmd) {adapter. fill (dt); // run the stored procedure in the database and Fill the result in the dt table.} // After the stored procedure is executed, the stored procedure passes these two output parameters. So here we get the two return parameters. RowCount = Convert. ToInt32 (parameters [2]. Value); pageCount = Convert. ToInt32 (parameters [3]. Value); return dt ;}}}}}

Create an Aticel. cs class in the DAL folder (layer) to generate a list

Using System; using System. collections. generic; using System. linq; using System. web; using System. data; using LLSql. DAL; using WebApplication1.Model; namespace WebApplication1.DAL {public class Aticel {public static List <Location> GetPageListByPageIndex (int pageSize, int currentpage, out int rowCount, out int pageCount) {DataTable dt = SqlHelper. executeProcPageList (pageSize, currentpage, out rowCount, out pag ECount); var list = new List <Location> (); // declare a generic object list if (dt! = Null & dt. rows. count> 0) {// converts a able into a list = (from p in dt. asEnumerable () // (traversing DataTable) select new Model. location {Locid = p. field <int> ("locid"), // assign the Field in DateTable to the attribute LocName = p. field <string> ("locName"), ParentId = p. field <int> ("parentId"), LocType = p. field <short> ("locType"), ElongCode = p. field <string> ("elongCode"), CityCode = p. field <string> ("CityCode"), BaiduPos = p. field <string> ("BaiduPos"), Versions = p. field <short> ("Version ")}). toList ();} return list; // return this list }}}

<3>

Create a GetPageData file in the API folder. the ashx page (BLL layer) calls Aticel In the ADL layer. the GetPageListByPageIndex () method in the cs class obtains a list and converts the list into a Json string, which is obtained by AJAX asynchronous requests.

Using System; using System. collections. generic; using System. linq; using System. web; using System. web. script. serialization; namespace WebApplication1.API {// <summary> /// summary of GetPageData /// </summary> public class GetPageData: IHttpHandler {// <summary> // obtain data based on the page number of the current page that the user passes. // </summary> /// <param name = "context"> </param> public void ProcessRequest (HttpContext context) {context. response. cont EntType = "text/plain"; int pageSize = 10; // set the page size. Each page displays 10 data records: int currentPage = Convert. toInt32 (context. request. queryString ["currentPage"]); // set int rowCount = 0 on the current page; // pass it as the out parameter to the method, and assign the int pageCount = 0 value to rowCount in the method; // pass it as the out parameter to the method, and assign the value of string jsonData = null to rowCount in the method; List <Model. location> list = DAL. aticel. getPageListByPageIndex (pageSize, currentPage, out rowCount, out pageCount); if (list! = Null & list. count> 0) {// create a Json serializer and convert the object into a Json-Format String JavaScriptSerializer jsz = new JavaScriptSerializer (); jsonData = jsz. serialize (list); // converts a list object into a json string context. response. write (jsonData);} else {context. response. write ("no") ;}} public bool IsReusable {get {return false ;}}}}

Front-end page (display the data obtained by AJAX requests as well as the page)

<% @ Page Language = "C #" AutoEventWireup = "true" CodeBehind = "WebForm1.aspx. cs" Inherits = "WebApplication1.WebForm1" %> <! DOCTYPE html PUBLIC "-// W3C // dtd xhtml 1.0 Transitional // EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">