C paging instance of AJAX based on database stored procedures
- 2020-12-16 06:05:11
- OfStack
This article gives an example of how C# implements AJAX paging based on database stored procedures. Share to everybody for everybody reference. The details are as follows:
First we declare and define stored procedures in the database (SQL Server)
use sales -- Specified database
if(exists(select * from sys.objects where name='proc_location_Paging')) -- If the proc_location_paging The stored procedure is deleted if it exists
drop proc proc_location_Paging
go
create proc proc_location_Paging -- Create stored procedures
(
@pageSize int, -- Page size
@currentpage int, -- The current page
@rowCount int output, -- Total number of rows ( Out parameter )
@pageCount int output -- Total number of pages ( Out parameter )
)
as
begin
select @rowCount= COUNT(locid) from location -- to @rowCount The assignment
select @pageCount= CEILING((count(locid)+0.0)/@pageSize) from location -- to @pageCount The assignment
select top (@pagesize)* from (select ROW_NUMBER() over(order by locid) as rowID,* from location) as t1
where rowID >(@pageSize*(@currentpage-1))
end
go
--------------------------------- The above indicates that the stored procedure has been defined.
--------------------------------- The following is the execution of the stored procedure. We can look at the results
declare @rowCount int,@pageCount int -- Declare two parameters first
-- perform proc_location_Paging This stored procedure. @rowCount,@pageCount The back has a output 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
Since we are accessing the database directly, we write the following method to the DAL layer, which I will write to SqlHelper here
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>
/// Gets the 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; // Set the command to the stored procedure type ( That is, indicate what we are doing 1 Three stored procedures )
rowCount = 0;
pageCount = 0;// I'll give you whatever you want here rowCount . pageCount I'm going to assign a value because I'm going to use out When you pass a parameter, it's inside the method 1 Need to give out Parameter assignment is the only way to use it, but even though we've given it an initial value here, during the execution of the stored procedure, the stored procedure will assign values to these two parameters and return them back to us, which is the value we want
SqlParameter[] parameters ={
new SqlParameter("@pageSize",pageSize),
new SqlParameter("@currentpage",currentPage),
new SqlParameter("@rowCount",rowCount),
new SqlParameter("@pageCount",pageCount)
};
// Because in a stored procedure @rowCount with @pageCount is 1 Output parameters (output), while parameters In this array, number one 3 And the first 4 The parameter is used to replace the two output parameters, so we need to replace the two output parameters parameters The two parameters in the array are set as output parameters.
parameters[2].Direction = ParameterDirection.Output;
parameters[3].Direction = ParameterDirection.Output;
cmd.Parameters.AddRange(parameters); // The parameters passed to us cmd Command object
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(dt);// Go to the database to execute the stored procedure and populate the results with dt In the table
}
// After the stored procedure completes execution, the stored procedure passes these two output parameters. So let's get these two return arguments here.
rowCount = Convert.ToInt32(parameters[2].Value);
pageCount = Convert.ToInt32(parameters[3].Value);
return dt;
}
}
}
}
}
Creating 1 Aticel. cs class in the DAL folder (layer) produces 1 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 pageCount);
var list = new List<Location>();// The statement 1 A generic object list
if (dt != null && dt.Rows.Count > 0)
{
// will DataTable Converted to 1 a list
list = (from p in dt.AsEnumerable() // (traversal DataTable )
select new Model.Location
{
Locid = p.Field<int>("locid"), // will DateTable Field assigned to Location Properties in a class
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; // Will this list Return back
}
}
}
Create 1 GetPageData. ashx page (BLL layer) in the API folder. Call the GetPageListByPageIndex() method in the ES30en. cs class in the ADL layer, get 1 list and convert this list into 1 Json string.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
namespace WebApplication1.API
{
/// <summary>
/// GetPageData Summary description of
/// </summary>
public class GetPageData : IHttpHandler
{
/// <summary>
/// Retrieves the data based on the previous page number passed by the user
/// </summary>
/// <param name="context"></param>
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
int pageSize = 10; // Set page size , Each page shows 10 The data
int currentPage = Convert.ToInt32(context.Request.QueryString["currentPage"]); // Set current page
int rowCount = 0; // As a out Parameters are passed to the method , Give in the method rowCount The assignment
int pageCount = 0; // As a out Parameters are passed to the method , Give in the method rowCount The assignment
string jsonData = null;
List<Model.Location> list= DAL.Aticel.GetPageListByPageIndex(pageSize, currentPage, out rowCount, out pageCount);
if (list != null && list.Count > 0)
{
// create Json Serializer, which converts an object to 1 a Json Formatted string
JavaScriptSerializer jsz = new JavaScriptSerializer();
jsonData = jsz.Serialize(list); // will 1 a list Object conversion to json Formatted string
context.Response.Write(jsonData);
}
else
{
context.Response.Write("no");
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
Front page (display the data obtained by AJAX request on 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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title> use AJAX paging </title>
<script src="jquery-1.11.2.js" type="text/javascript"></script>
<style type="text/css">
table{ margin:80px 500px; }
td{ width:50px; height:auto}
</style>
<script type="text/javascript">
$(function () {
$.get("API/GetPageData.ashx?currentPage=2", function (obj) { // Assume that the current page is the first 2 page currentPage=2
//debugger;
var JsonData = $.parseJSON(obj);
//alert(JsonData[0].Locid);
//debugger;
for (var i = 0; i < JsonData.length; i++) {
var data = "<tr><td >" + JsonData[i].Locid + "</td><td >" + JsonData[i].LocName + "</td><td >" + JsonData[i].ParentId + "</td><td >" + JsonData[i].LocType + "</td><td >" + JsonData[i].ElongCode + "</td><td >" + JsonData[i].CityCode + "</td><td >" + JsonData[i].BaiduPos + "</td><td >" + JsonData[i].Versions + "</td></tr>";
$("#t1").append(data);
}
})
})
</script>
</head>
<body>
<table border="1" cellpadding="5" cellspacing="0" style="margin-top:100px;width:600px;" id="t1">
<tr><td> Serial number </td><td > City name </td><td > The father ID</td><td >locType</td><td >elongCode</td><td >CityCode</td><td >BaiduPos</td><td >Version</td></tr>
</table>
</body>
</html>I hope this article has been helpful for your C# programming.