Jquery+Ajax+Json + Stored Procedures for Efficient Paging
- 2021-07-26 08:41:59
- OfStack
Before, when doing paging, many friends use Jquery paging plug-in. Before, I used jquery. paper. Friends who need it can contact me. Next, this site will share with you how to use Jquery + Ajax + Json + stored procedures to achieve efficient paging.
Realize this function with paging stored procedure, pagination, js style, not much nonsense, please see the following code for details
Paging Stored Procedure: PAGINATION
CREATE PROCEDURE [dbo].[PAGINATION]
@FEILDS VARCHAR(),-- Fields to display
@PAGE_INDEX INT,-- Current page number
@PAGE_SIZE INT,-- Page size
@ORDERTYPE BIT,-- When is Otherwise desc When is Hour asc
@ANDWHERE VARCHAR()='',--where Statement No need to add where
@ORDERFEILD VARCHAR(), -- Sorted fields
@TABLENAME VARCHAR() -- Indication of query
as
DECLARE @EXECSQL VARCHAR()
DECLARE @ORDERSTR VARCHAR()
DECLARE @ORDERBY VARCHAR()
BEGIN
set NOCOUNT on
IF @ORDERTYPE =
BEGIN
SET @ORDERSTR = ' > ( SELECT MAX(['+@ORDERFEILD+'])'
SET @ORDERBY = 'ORDER BY '+@ORDERFEILD+' ASC'
END
ELSE
BEGIN
SET @ORDERSTR = ' < ( SELECT MIN(['+@ORDERFEILD+'])'
SET @ORDERBY = 'ORDER BY '+@ORDERFEILD+' DESC'
END
IF @PAGE_INDEX = -- When the page number is number 1 Page to run directly, improving speed
BEGIN
IF @ANDWHERE=''
SET @EXECSQL = 'SELECT TOP '+STR(@PAGE_SIZE)+' '+@FEILDS+' FROM '+@TABLENAME+' '+@ORDERBY
ELSE
SET @EXECSQL = 'SELECT TOP '+STR(@PAGE_SIZE)+' '+@FEILDS+' FROM '+@TABLENAME+' WHERE '+@ANDWHERE+' '+ @ORDERBY
END
ELSE
BEGIN
IF @ANDWHERE=''
BEGIN -- When subquery results are used as new tables You must alias the table name to use it
SET @EXECSQL = 'SELECT TOP'+STR(@PAGE_SIZE)+' '+@FEILDS+' FROM '+@TABLENAME+' WHERE '+@ORDERFEILD+
@ORDERSTR+' FROM (SELECT TOP '+STR(@PAGE_SIZE*(@PAGE_INDEX-))+' '+@ORDERFEILD+
' FROM '+@TABLENAME+' '+@ORDERBY+') AS TEMP) '+ @ORDERBY
END
ELSE
BEGIN
SET @EXECSQL = 'SELECT TOP'+STR(@PAGE_SIZE)+' '+@FEILDS+' FROM '+@TABLENAME+' WHERE '+@ORDERFEILD+
@ORDERSTR+' FROM (SELECT TOP '+ STR(@PAGE_SIZE*(@PAGE_INDEX-))+' '+@ORDERFEILD+
' FROM '+@TABLENAME+' WHERE '+@ANDWHERE+' '+@ORDERBY+') AS TEMP) AND '+@ANDWHERE+' '+ @ORDERBY
END
END
EXEC (@EXECSQL)-- Put parentheses here
END
Paging style:
<style type="text/css">
a,area { -moz-outline-style: none; blr:expression(this.onFocus=this.blur()); text-decoration:none}
div.badoo { padding:px; text-align:center; }
div.badoo a { border:px solid #ededed; padding:px px; color:#; border-radius:px; margin-right:px;}
div.badoo a:hover {border:px solid #ffa; color: #ffa; }
div.badoo a:active {border:px solid #ffa; margin-right:px;}
div.badoo span { border:px solid #EDEDED; padding:px px; color:#f;font-weight:bold; background:#FAFAFA; border-radius:px; margin-right:px;}
div.badoo span.disabled { border:px solid #EDEDED; padding:px px; color:#; margin-right:px; font-weight:;}
</style>
First, create a general handler, read the contents of the database, and get the return value.
Create the file, GetData. ashx.
I'm using the stored procedure here. The stored procedure will stick out below. As for the data, it is only an instance. You can read the data by yourself according to your needs
using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
using System.Web.Script.Serialization;
using Model;
context.Response.ContentType = "text/plain";
var pageIndex = context.Request["PageIndex"];
// Judge whether the current index store does not exist, and if it does not exist, get the total number of records.
if (string.IsNullOrEmpty(pageIndex))
{
// Object for the total number of query records sql Statement
int count = ;
int.TryParse(new BLL.t_profit().SelectAllNum(), out count);
context.Response.Write(count);
context.Response.End();
}
// When data is obtained according to the index
else
{
int currentPageIndex = ;
int.TryParse(pageIndex, out currentPageIndex);
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@FEILDS",SqlDbType.NVarChar,),
new SqlParameter("@PAGE_INDEX",SqlDbType.Int,),
new SqlParameter("@PAGE_SIZE",SqlDbType.Int,),
new SqlParameter("@ORDERTYPE",SqlDbType.Int,),
new SqlParameter("@ANDWHERE",SqlDbType.VarChar,),
new SqlParameter("@ORDERFEILD",SqlDbType.VarChar,)
};
parms[].Value = "id,name,sex,tel";// Get all the fields
parms[].Value = pageIndex;// Current page index
parms[].Value = ;// Page size
parms[].Value = ;// Ascending arrangement
parms[].Value = "";// Conditional statement
parms[].Value = "id";// Sort field
List<Book> list = new List<Book>();
using (SqlDataReader sdr = Yoodor.DAL.SqlHelper.ExecuteReader(CommandType.StoredProcedure, "PAGINATION", parms))
{
while (sdr.Read())
{
list.Add(new Book { id = sdr[].ToString(), name = sdr[].ToString(), sex = sdr[].ToString(), tel = sdr[].ToString() });
}
}
context.Response.Write(new JavaScriptSerializer().Serialize(list).ToString());// Convert to Json Format
}
public string id { get; set; }
public string name { get; set; }
public string sex { get; set; }
public string tel { get; set; }
Style code:
<style type="text/css">
a,area { -moz-outline-style: none; blr:expression(this.onFocus=this.blur()); text-decoration:none}
div.badoo { padding:px; text-align:center; }
div.badoo a { border:px solid #ededed; padding:px px; color:#; border-radius:px; margin-right:px;}
div.badoo a:hover {border:px solid #ffa; color: #ffa; }
div.badoo a:active {border:px solid #ffa; margin-right:px;}
div.badoo span { border:px solid #EDEDED; padding:px px; color:#f;font-weight:bold; background:#FAFAFA; border-radius:px; margin-right:px;}
div.badoo span.disabled { border:px solid #EDEDED; padding:px px; color:#; margin-right:px; font-weight:;}
</style>
js code
<script type="text/javascript">
$(function () {
$.post("GetData.ashx", null, function (data) {
var total = data;
PageClick(, total, );
});
PageClick = function (pageIndex, total, spanInterval) {
$.ajax({
url: "GetData.ashx",
data: { "PageIndex": pageIndex },
type: "post",
dataType: "json",
success: function (data) {
// Index from the beginning
// Converts the current page index to int Type
var intPageIndex = parseInt(pageIndex);
// Get the table that displays the data
var table = $("#content");
// Be clear about the contents of the table
$("#content tr").remove();
// Add content to a table
for (var i = ; i < data.length; i++) {
table.append(
$("<tr><td>" +
data[i].id
+ "</td><td>" +
data[i].name
+ "</td><td>" +
data[i].sex
+ "</td><td>" +
data[i].tel
+ "</td></tr>")
);
} //for
// Create a page
// Will the total number of records result Get Total page number
var pageS = total
if (pageS % == ) pageS = pageS / ;
else pageS = parseInt(total / ) + ;
var $pager = $("#pager");
// Clear paging div Content in
$("#pager span").remove();
$("#pager a").remove();
// Add Parts 1 Page
if (intPageIndex == ) {
// $pager.append("<span class='disabled'> No. 1 1 Page </span>");
}
else {
// var first = $("<a href='javascript:void()' first='" + + "'> No. 1 1 Page </a>").click(function () {
// PageClick($(this).attr('first'), total, spanInterval);
// return false;
// });
// $pager.append(first);
}
// Add on 1 Page
if (intPageIndex == )
$pager.append("<span class='disabled'> Upper 1 Page </span>");
else {
var pre = $("<a href='javascript:void()' pre='" + (intPageIndex - ) + "'> Upper 1 Page </a>").click(function () {
PageClick($(this).attr('pre'), total, spanInterval);
return false;
});
$pager.append(pre);
}
// Format paging Here, you can complete the results you want according to your needs
var interval = parseInt(spanInterval); // Set interval
var start = Math.max(, intPageIndex - interval); // Set Start Page
var end = Math.min(intPageIndex + interval, pageS)// Set Last Page
if (intPageIndex < interval + ) {
end = ( * interval + ) > pageS ? pageS : ( * interval + );
}
if ((intPageIndex + interval) > pageS) {
start = (pageS - * interval) < ? : (pageS - * interval);
}
// Generate page number
for (var j = start; j < end + ; j++) {
if (j == intPageIndex) {
var spanSelectd = $("<span class='current'>" + j + "</span>");
$pager.append(spanSelectd);
} //if
else {
var a = $("<a href='javascript:void()'>" + j + "</a>").click(function () {
PageClick($(this).text(), total, spanInterval);
return false;
});
$pager.append(a);
} //else
} //for
// Upper 1 Page
if (intPageIndex == Math.ceil(total / )) {
$pager.append("<span class='disabled'> Under 1 Page </span>");
}
else {
var next = $("<a href='javascript:void()' next='" + (intPageIndex + ) + "'> Under 1 Page </a>").click(function () {
PageClick($(this).attr("next"), total, spanInterval);
return false;
});
$pager.append(next);
}
// Finally 1 Page
if (intPageIndex == pageS) {
// $pager.append("<span class='disabled'> Finally 1 Page </span>");
}
else {
// var last = $("<a href='javascript:void()' last='" + pageS + "'> Finally 1 Page </a>").click(function () {
// PageClick($(this).attr("last"), total, spanInterval);
// return false;
// });
// $pager.append(last);
}
} //sucess
}); //ajax
}; //function
}); //ready
</script>
<table id="content">
</table>
<div id="pager" class="badoo"></div>
/// <summary>
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <param name="connectionString">1 Valid database connection strings </param>
/// <param name="cmdType">SqlCommand Command type ( Stored procedure, T-SQL Statement, Wait. )</param>
/// <param name="cmdText"> The name of the stored procedure or T-SQL Statement </param>
/// <param name="commandParameters"> Provided as an array SqlCommand List of parameters used in the command </param>
/// <returns>A SqlDataReader containing the results</returns>
public static SqlDataReader ExecuteReader( CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch (Exception ex)
{
conn.Close();
throw new Exception(ex.Message);
}
}
This is the use of Jquery + Ajax + Json + stored procedures to achieve efficient paging of the entire content, I hope you like.