项目中有对未知数据源获取数据用于展示的需求,用dapper和datareader来分页,处理起来比较方便些。
c#方法:
public object View(string dbLinkId, int rows = 0, int page = 0)
{
Models.DBC.DataLink.DataLink info = Get(dbLinkId);
Models.Integrate.DataBase dbsrc = new Integrate.DBSrcImpl().Get(info.DbsrcId) as Models.Integrate.DataBase;
DynamicParameters p;
string sql = getSql(dbLinkId, dbsrc, out p);
int start = 0;
if (page == 0)
{
page = 1;
}
if (rows == 0)
{
rows = 20;
}
start = (page - 1) * rows;
int end = start + rows;
int totalCount = 0;
try
{
using (DbConnection conn = DapperFactory.CreateConnection(dbsrc))
{
string countsql = string.Format("select count(0) from({0})t", sql);
int count = conn.ExecuteScalar<int>(countsql, p);
DataTable dt = new DataTable();
IDataReader reader = conn.ExecuteReader(sql, p);
for (int i = 0; i < reader.FieldCount; i++)
{
dt.Columns.Add(reader.GetName(i));
}
while (reader.Read())
{
totalCount++;
if (totalCount > end)
break;
if (totalCount >= start && totalCount <= end)
{
DataRow r = dt.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
r[i] = reader[i];
}
dt.Rows.Add(r);
}
}
return Common.DataGrid(dt, count);//转成easyui datagrid数据源格式
}
}
catch (Exception e)
{
throw new Exception(string.Format("查询源数据库出错<br>原因:{0}<br>sql语句:{1}", e.Message, sql));
}
}
js:
function getdata(id,r,p) {
var rows = 50;
var page = 1;
if (r) rows = r;
if (p) page = p;
$.post('/dblink/ListView', { id: id, rows: rows, page: page }, function (data) {
accecpResult(data, function () {
var ds = data.data.rows;
var columns = [[]];
if (ds.length > 0) {
for (var key in ds[0]) {
columns[0].push({ field: key, title: key });
}
}
tab_view.datagrid({
data: data.data,
rownumbers: true,
singleSelect: true,
striped: true,
fit: true,
border: false,
pagination: true,
pageSize: 50,
columns: columns
});
var p = tab_view.datagrid('getPager');
(p).pagination({
pageNumber: page,
beforePageText: '第',
afterPageText: '页 共 {pages} 页',
displayMsg: '共{total}条数据',
onSelectPage: function (pageNumber, pageSize) {
getdata(id, pageSize, pageNumber);
}
});
})
})
}