|
这篇文章主要介绍了asp.net使用js分页实现异步加载数据,需要的朋友可以参考下
1、准备工作
引入“jquery-1.8.3.min.js”,AjaxPro.2.dll”:用于前台js调用后台方法。
2、Web.config的配置 复制代码 代码如下: <?xml version="1.0" encoding="utf-8"?> <configuration> <connectionStrings> <clear/> <!-- 数据库链接 --> <add name="connSwtLoginLog" connectionString="Server=DUWEI\SQL2005;Database=SwtLoginLog;user id=sa;password=111111;Connect Timeout=120;pooling=true;min pool size=5;max pool size=10"/> </connectionStrings> <system.web> <compilation debug="true" targetFramework="4.0" /> <!-- 页面调用后台方法 --> <httpHandlers> <add verb="POST,GET" path="ajaxpro/*.ashx" type="AjaxPro.AjaxHandlerFactory,AjaxPro.2"/> </httpHandlers> </system.web> </configuration> 3、目录结构
下面就直接上代码了。
4、Login.aspx页面代码 复制代码 代码如下: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Login.aspx.cs" Inherits="AspNet.Login" %>
<!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></title> <script src="js/jquery-1.8.3.min.js" type="text/javascript"></script> <script type="text/javascript"> function initTable(dt) { var str = '<table border="1px">' + '<tr>' + '<td>' + 'LoginID' + '</td>' + '<td>' + 'SwtID' + '</td>' + '<td>' + 'UserName' + '</td>' + '<td>' + 'IP' + '</td>' + '<td>' + 'Address' + '</td>' + '<td>' + 'LogTime' + '</td>' + '<td>' + 'LogType' + '</td>' + '</tr>'; for (var i = 0; i < dt.Rows.length; i++) { str = str + '<tr>' + '<td>' + dt.Rows[i]['LoginID'] + '</td>' + '<td>' + dt.Rows[i]['SwtID'] + '</td>' + '<td>' + dt.Rows[i]['UserName'] + '</td>' + '<td>' + dt.Rows[i]['IP'] + '</td>' + '<td>' + dt.Rows[i]['Address'] + dt.Rows[i]['Address2'] + '</td>' + '<td>' + dt.Rows[i]['LogTime'] + '</td>' + '<td>' + dt.Rows[i]['LogType'] + '</td>' + '</tr>' } str = str + '</table>'; $("#d1").html(str); } function firtPage(page) { $("#pageNo").text(page); var dt = AspNet.Login.FindDate(page).value; initTable(dt); } //定义一个当前页初始为1 var pageNo = 1; //总页数 var totalPage = <%=pageCount %>; function showContent(op) { if (op == "first") { pageNo = 1; } else if (op == "previous") { if (pageNo > 1) pageNo -= 1; else pageNo = 1; } else if (op == "next") { if (pageNo < totalPage - 1) pageNo += 1; else pageNo = totalPage - 1; } else if (op == "last") { pageNo = totalPage - 1; } else if(op=="jump"){ var jump = $("#jump").val(); if(jump<1 || jump>totalPage){ pageNo = 1; }else{ pageNo = jump; } } else { pageNo = 1; } firtPage(pageNo); } $(function () { showContent("first"); }); </script> </head> <body> <form id="form1" runat="server"> <div id="d1" align="center"></div> <div align="center"> <span id="sp_ShowContent"> 第<label id="pageNo"></label>页|共<%=pageCount%>页 |<a onclick="showContent('first');" href="javascript:void(0);">首页</a> |<a onclick="showContent('previous');" href="javascript:void(0);">上一页</a> |<a onclick="showContent('next');" href="javascript:void(0);">下一页</a> |<a onclick="showContent('last');" href="javascript:void(0);">尾页</a> |跳到<input id="jump"/><a onclick="showContent('jump');" href="javascript:void(0);">GO</a> </span> </div> </form> </body> </html> 后台代码 复制代码 代码如下: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using AspNet.service;
namespace AspNet { public partial class Login : System.Web.UI.Page { //测试用 没页2条数据 public int pageSize = 2; public int pageCount; public LoginLogService logService = new LoginLogService(); protected void Page_Load(object sender, EventArgs e) { AjaxPro.Utility.RegisterTypeForAjax(typeof(Login)); if (!IsPostBack) { pageCount = logService.PageCount(pageSize); } } //AjaxPro具体使用方法可以网上例子很多 [AjaxPro.AjaxMethod] public DataTable FindDate(int currentPage) { return logService.FindDate(pageSize, currentPage); } } } 5、LoginLogService.cs 复制代码 代码如下: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using System.Data.SqlClient;
namespace AspNet.service { public class LoginLogService { public DataTable FindDate(int pageSize, int currentPage) {
string sql = "SELECT LoginID,SwtID,UserName,IP,Address,Address2,LogTime,LogType FROM ( " + "SELECT * ,ROW_NUMBER() OVER(ORDER BY LoginID) AS columnNum FROM dbo.LoginLog ) a " + "WHERE a.columnNum BETWEEN @begin AND @end"; SqlParameter[] paras = new SqlParameter[]{new SqlParameter("@begin",pageSize * (currentPage-1)+1), new SqlParameter("@end",pageSize * currentPage)}; DataTable dt = DBHelper.GetDataSet(sql, paras); return DBHelper.GetDataSet(sql, paras); } public int PageCount(int pageSize) { string sql = "SELECT COUNT(1) FROM dbo.LoginLog"; int rowCount = int.Parse(DBHelper.GetDataSet(sql).Rows[0][0].ToString()); return rowCount % pageSize == 0 ? rowCount / pageSize : rowCount / pageSize+1; } } } 6、Utils放着DBHelper.cs 复制代码 代码如下: using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration;
namespace AspNet { public static class DBHelper {
private static SqlConnection connection; public static SqlConnection Connection { get { string connectionString = ConfigurationManager.ConnectionStrings["connSwtLoginLog"].ConnectionString; if (connection == null) { connection = new SqlConnection(connectionString); connection.Open(); } else if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } else if (connection.State == System.Data.ConnectionState.Broken) { connection.Close(); connection.Open(); } return connection; } }
//ExecuteNonQuery方法是用来执行insert、delete、update语句的,返回的是影响的行数 public static int ExecuteCommand(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = cmd.ExecuteNonQuery(); return result; }
public static int ExecuteCommand(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); return cmd.ExecuteNonQuery(); }
//ExecuteScalar()返回sql语句执行后的第一行第一列的值,object类型 public static int GetScalar(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; }
public static int GetScalar(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; }
//ExecuteReader()返回一个Datareader对象,对象内容是为与命令匹配的所有行,通常用于读取数据 public static SqlDataReader GetReader(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataReader reader = cmd.ExecuteReader(); return reader; }
public static SqlDataReader GetReader(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataReader reader = cmd.ExecuteReader(); return reader; }
public static DataTable GetDataSet(string safeSql) { connection = Connection; DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); cmd.Parameters.Clear(); return ds.Tables[0]; }
public static DataTable GetDataSet(string sql, params SqlParameter[] values) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); cmd.Parameters.Clear(); return ds.Tables[0]; }
} } 复制代码 代码如下: 数据表结构:<pre code_snippet_id="274427" snippet_file_name="blog_20140404_6_6418355" name="code" class="plain">CREATE TABLE [dbo].[LoginLog]( [LoginID] [int] IDENTITY(1,1) NOT NULL, [SwtID] [int] NULL, [UserName] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL, [IP] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [Address] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL, [Address2] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL, [LogTime] [datetime] NULL, [LogType] [int] NULL CONSTRAINT [DEFAULT_LoginLog_LogType] DEFAULT ((1)), CONSTRAINT [PK_LoginLog_LoginID] PRIMARY KEY CLUSTERED ( [LoginID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]</pre> <pre></pre> <pre code_snippet_id="274427" snippet_file_name="blog_20140404_6_6418355" name="code" class="csharp"><pre code_snippet_id="274427" snippet_file_name="blog_20140404_6_6418355" name="code" class="sql"><pre code_snippet_id="274427" snippet_file_name="blog_20140404_6_6418355"></pre> <pre></pre> <pre></pre> <pre></pre> <pre></pre> <pre></pre>
</pre></pre>
|
|