/// <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()); } }