c# 万能access数据库分页代码

小渣渣 · 发表于 2016-6-17 19:06:56

上面是分页效果图和数据库表的内容，先付上主要的代码：

// <summary>
/// 分页使用
/// </summary>
/// <param name="query"></param>
/// <param name="passCount"></param>
/// <returns></returns>
private string recordID(string query, int passCount)
{
OleDbCommand cmd = new OleDbCommand(query, Conn);
string result = string.Empty;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (passCount < 1)
{
result += "," + dr.GetInt32(0);
}
passCount--;
}
}
return result.Substring(1);
}
/// <summary>
/// ACCESS高效分页
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">分页容量</param>
/// <param name="strKey">主键</param>
/// <param name="showString">显示的字段</param>
/// <param name="queryString">查询字符串，支持联合查询</param>
/// <param name="whereString">查询条件，若有条件限制则必须以where 开头</param>
/// <param name="orderString">排序规则</param>
/// <param name="pageCount">传出参数：总页数统计</param>
/// <param name="recordCount">传出参数：总记录统计</param>
/// <returns>装载记录的DataTable</returns>
public DataTable ExecutePager(int pageIndex, int pageSize, string strKey, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount)
{
if (pageIndex < 1) pageIndex = 1;
if (pageSize < 1) pageSize = 10;
if (string.IsNullOrEmpty(showString)) showString = "*";
if (string.IsNullOrEmpty(orderString)) orderString = strKey + " asc ";
string myVw = string.Format(" ( {0} ) tempVw ", queryString);
OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(*) as recordCount from {0} {1}", myVw, whereString), Conn);
recordCount = Convert.ToInt32(cmdCount.ExecuteScalar());
if ((recordCount % pageSize) > 0)
pageCount = recordCount / pageSize + 1;
else
pageCount = recordCount / pageSize;
OleDbCommand cmdRecord;
if (pageIndex == 1)//第一页
{
cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, whereString, orderString), Conn);
}
else if (pageIndex > pageCount)//超出总页数
{
cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString), Conn);
}
else
{
int pageLowerBound = pageSize * pageIndex;
int pageUpperBound = pageLowerBound - pageSize;
string recordIDs = recordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, strKey, myVw, whereString, orderString), pageUpperBound);
cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw, strKey, recordIDs, orderString), Conn);
}
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
return dt;
}

复制代码

再附上全部的dbhelp.cs，用下面这个即可：

public class DBHelper
{
//变量声明处#region 变量声明处
public OleDbConnection Conn;
public string ConnString;//连接字符串
//构造函数与连接关闭数据库#region 构造函数与连接关闭数据库
/**//// <summary>
/// 构造函数
/// </summary>
/// <param name="Dbpath">ACCESS数据库路径</param>
public DBHelper(string Dbpath)
{
ConnString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=";
//ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=";
ConnString += Dbpath;
Conn = new OleDbConnection(ConnString);
Conn.Open();
}
/**//// <summary>
/// 打开数据源链接
/// </summary>
/// <returns></returns>
public OleDbConnection DbConn()
{
Conn.Open();
return Conn;
}
/**//// <summary>
/// 请在数据传递完毕后调用该函数，关闭数据链接。
/// </summary>
public void Close()
{
Conn.Close();
}
//数据库基本操作#region 数据库基本操作
/**//// <summary>
/// 根据SQL命令返回数据DataTable数据表,
/// 可直接作为dataGridView的数据源
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public DataTable SelectToDataTable(string SQL)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
DataTable Dt = new DataTable();
adapter.Fill(Dt);
return Dt;
}
/**//// <summary>
/// 根据SQL命令返回数据DataSet数据集，其中的表可直接作为dataGridView的数据源。
/// </summary>
/// <param name="SQL"></param>
/// <param name="subtableName">在返回的数据集中所添加的表的名称</param>
/// <returns></returns>
public DataSet SelectToDataSet(string SQL, string subtableName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
DataSet Ds = new DataSet();
Ds.Tables.Add(subtableName);
adapter.Fill(Ds, subtableName);
return Ds;
}
/**//// <summary>
/// 在指定的数据集中添加带有指定名称的表，由于存在覆盖已有名称表的危险，返回操作之前的数据集。
/// </summary>
/// <param name="SQL"></param>
/// <param name="subtableName">添加的表名</param>
/// <param name="DataSetName">被添加的数据集名</param>
/// <returns></returns>
public DataSet SelectToDataSet (string SQL, string subtableName, DataSet DataSetName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
DataTable Dt = new DataTable();
DataSet Ds = new DataSet();
Ds = DataSetName;
adapter.Fill(DataSetName, subtableName);
return Ds;
}
/**//// <summary>
/// 根据SQL命令返回OleDbDataAdapter，
/// 使用前请在主程序中添加命名空间System.Data.OleDb
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public OleDbDataAdapter SelectToOleDbDataAdapter(string SQL)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(SQL, Conn);
adapter.SelectCommand = command;
return adapter;
}
/**//// <summary>
/// 执行SQL命令，不需要返回数据的修改，删除可以使用本函数
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public bool ExecuteSQLNonquery(string SQL)
{
OleDbCommand cmd = new OleDbCommand(SQL, Conn);
try
{
cmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
}
// <summary>
/// 分页使用
/// </summary>
/// <param name="query"></param>
/// <param name="passCount"></param>
/// <returns></returns>
private string recordID(string query, int passCount)
{
OleDbCommand cmd = new OleDbCommand(query, Conn);
string result = string.Empty;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (passCount < 1)
{
result += "," + dr.GetInt32(0);
}
passCount--;
}
}
return result.Substring(1);
}
/// <summary>
/// ACCESS高效分页
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">分页容量</param>
/// <param name="strKey">主键</param>
/// <param name="showString">显示的字段</param>
/// <param name="queryString">查询字符串，支持联合查询</param>
/// <param name="whereString">查询条件，若有条件限制则必须以where 开头</param>
/// <param name="orderString">排序规则</param>
/// <param name="pageCount">传出参数：总页数统计</param>
/// <param name="recordCount">传出参数：总记录统计</param>
/// <returns>装载记录的DataTable</returns>
public DataTable ExecutePager(int pageIndex, int pageSize, string strKey, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount)
{
if (pageIndex < 1) pageIndex = 1;
if (pageSize < 1) pageSize = 10;
if (string.IsNullOrEmpty(showString)) showString = "*";
if (string.IsNullOrEmpty(orderString)) orderString = strKey + " asc ";
string myVw = string.Format(" ( {0} ) tempVw ", queryString);
OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(*) as recordCount from {0} {1}", myVw, whereString), Conn);
recordCount = Convert.ToInt32(cmdCount.ExecuteScalar());
if ((recordCount % pageSize) > 0)
pageCount = recordCount / pageSize + 1;
else
pageCount = recordCount / pageSize;
OleDbCommand cmdRecord;
if (pageIndex == 1)//第一页
{
cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, whereString, orderString), Conn);
}
else if (pageIndex > pageCount)//超出总页数
{
cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString), Conn);
}
else
{
int pageLowerBound = pageSize * pageIndex;
int pageUpperBound = pageLowerBound - pageSize;
string recordIDs = recordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, strKey, myVw, whereString, orderString), pageUpperBound);
cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw, strKey, recordIDs, orderString), Conn);
}
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
return dt;
}
}

复制代码

调用的方法，其实，在传参的时候，已经写的很明白了，但是，还是附上演示代码吧！

public JsonResult ALLUser()
{
object sss = new List<string>();
string draw = Request["draw"]; //请求次数
//获取分页参数
var bytes = new byte[Request.InputStream.Length];
Request.InputStream.Position = 0;
Request.InputStream.Read(bytes, 0, bytes.Length);
string str = System.Text.Encoding.UTF8.GetString(bytes);
string start = Config.GetParams("start", str); //开始条数
string length = Config.GetParams("length", str); //分页数
string select = "select * from [tb_User]";
int pagr_count;
int row_count;
DataTable dt = Config.db.ExecutePager((int.Parse(start) / int.Parse(length)) + 1, int.Parse(length), "ID", "ID,UserName,Pwd,Email,CreateTime,Status,Auth", select, null, "ID DESC", out pagr_count, out row_count);
//DataTable dt = Config.db.SelectToDataTable(select);
if (dt.Rows.Count > 0)
{
List<Models.tb_User> List = new List<Models.tb_User>();
foreach (DataRow dr in dt.Rows)
{
ThroughTrain.Admin.Models.tb_User user = new Models.tb_User();
user.ID = Convert.ToInt32(dr["ID"].ToString());
user.UserName = dr["UserName"].ToString();
user.Password = dr["Pwd"].ToString();
user.Email = dr["Email"].ToString();
user.CreateTime = dr["CreateTime"].ToString();
user.Status = Convert.ToInt32(dr["Status"].ToString());
user.Auth = Convert.ToInt32(dr["Auth"].ToString());
List.Add(user);
}
//return Json(new { draw = Convert.ToInt32(draw), recordsTotal = dt.Rows.Count, recordsFiltered = dt.Rows.Count, data = List });
return Json(new { draw = Convert.ToInt32(draw), recordsTotal = row_count, recordsFiltered = row_count, data = List });
}
else {
return Json(new { draw = Convert.ToInt32(draw), recordsTotal = 0, recordsFiltered = 0, data = sss });
}
}

复制代码

liyunong · 发表于 2017-6-12 07:27:53

谢谢楼主分享

		自动登录	找回密码
密码			注册[Register]

[资料] c# 万能access数据库分页代码

相关帖子