1、准备工作

引入“jquery-1.8.3.min.js”，AjaxPro.2.dll”：用于前台js调用后台方法。

2、Web.config的配置

复制代码 代码如下:

3、目录结构
 

下面就直接上代码了。

4、Login.aspx页面代码

复制代码 代码如下:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Login.aspx.cs" Inherits="AspNet.Login" %>

后台代码

复制代码 代码如下:

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];
}

}
}

复制代码 代码如下:

数据表结构：

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]