/// <summary>
/// 分页查询数据并返回DataTable的公共方法
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="field">需要查询的字段</param>
/// <param name="pageSize">每页显示数据的条数</param>
/// <param name="start">排除的数据量</param>
/// <param name="sqlWhere">where条件</param>
/// <param name="sortName">排序名称</param>
/// <param name="sortOrder">排序方式</param>
/// <returns></returns>
public static DataTable GetTable(String tableName, String field, int pageSize, int start, String sqlWhere, String sortName, String sortOrder, out Int32 total)
{
String sql = String.Format("select {0} from(select *,ROW_NUMBER() over(order by {1} {2}) rowid from {3} where {4}) a where rowid>{5} and rowid<={6}",
field, sortName, sortOrder, tableName, sqlWhere, start, pageSize + start);
DataTable dt = GetTable(sql, CommandType.Text, null);
sql = "select count(1) from " + tableName + " where " + sqlWhere;
total = Convert.ToInt32(SqlDBHelper.ExecuteScalar(sql, CommandType.Text, null));
return(dt);
}
/// <summary>
/// 总条数
/// </summary>
/// <param name="layoutItem"></param>
/// <param name="site_id"></param>
/// <returns></returns>
private int GetTotalCount(LayoutItem layoutItem, string site_id)
{
string sql = string.Empty;
try
{
sql = string.Format("select count(1) from (select * from ({0}) ss where site_id='{1}') sm", layoutItem.Sql, site_id);
return(SqlDBHelper.ExecuteScalar(sql, CommandType.Text, null).GetInt32());
}
catch (Exception ex)
{
LogHelper.Log.Error("数据库访问出错:" + sql, ex);
return(0);
}
}
/// <summary>
/// 获取待插入的DataTable数据
/// </summary>
/// <param name="siteId"></param>
/// <param name="indexs"></param>
/// <param name="layoutItem"></param>
/// <returns></returns>
private DataTable GetDbData(string siteId, string[] indexs, LayoutItem layoutItem)
{
string exeSql = string.Empty;
try
{
exeSql = @"select * from(
select *,ROW_NUMBER() over(order by " + layoutItem.Orderby + ") num_ from (select * from ( " + layoutItem.Sql + " ) ss where site_id='" + siteId + "') sm) m where num_ in(" + string.Join(",", indexs) + ")";
return(SqlDBHelper.GetTable(exeSql, CommandType.Text, null));
}
catch (Exception ex)
{
LogHelper.Log.Error("数据库访问出错:" + exeSql, ex);
return(new DataTable());
}
}