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

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>