public static bool CanGenrateFile() { string sql = "select count(*) from Iris_Reco_Image"; using (DataAccessBroker broker = DataAccessFactory.Instance()) { int count = Convert.ToInt32(broker.ExecuteScalar(sql, null, CommandType.Text)); return(count < 5); } }
public static LigerGrid ExcuteTable <T>(string hql, DataAccessParameterCollection lstParameter, LigerGrid grid, DataAccessBroker broker) where T : EntityBase, new() { object total = broker.ExecuteScalar("select count(1) as Total from (" + hql + ") as ttable", lstParameter, CommandType.Text); StringBuilder strsql = new StringBuilder(); if (grid.pageIndex != 0 && grid.pageSize != 0) { strsql.Append("select * from ( select top " + (grid.pageIndex * grid.pageSize) + " row_number() over(order by " + grid.sortName + " " + grid.sortOrder.ToLower() + ") as Row_Number, "); strsql.Append(hql.Replace(":", "@").Substring(7)); if (!string.IsNullOrEmpty(grid.Where)) { strsql.Append(" and (" + grid.Where.Replace(":", "@") + ")");; }//as strsql.Append(" ) ttable where Row_Number between " + (((grid.pageIndex - 1) * grid.pageSize) + 1) + " and " + ((grid.pageIndex) * grid.pageSize)); } else { strsql.Append(hql); if (!string.IsNullOrEmpty(grid.sortName)) { strsql.Append(" order by " + grid.sortName); if (!string.IsNullOrEmpty(grid.sortOrder)) { strsql.Append(" " + grid.sortOrder); } else { strsql.Append(" ASC"); } } } DataSet ds = broker.FillSQLDataSet(strsql.ToString(), lstParameter); EntityCollection <T> lstEntity = new EntityCollection <T>(); List <Dictionary <string, object> > dicLstRow = new List <Dictionary <string, object> >(); ToEntityList <T>(ds.Tables[0], ref dicLstRow, ref lstEntity); if (grid.IsDataEntity) { grid.Rows = lstEntity; } else { grid.Rows = dicLstRow; } grid.Total = (total != null ? Convert.ToInt32(total) : 0); return(grid); }
private static JqGrid ExcuteTable <T>(string hql, DataAccessParameterCollection lstParameter, JqGrid grid, DataAccessBroker broker) where T : EntityBase, new() { int index = 0; StringBuilder whereSql = new StringBuilder(hql); if (grid.QueryField["_search"] == "true") { foreach (KeyValuePair <string, string> kv in grid.QueryField) { if (index > 5) { DateTime result = DateTime.Now; if (DateTime.TryParse(kv.Value, out result)) { whereSql.AppendLine($" and datediff(day,{kv.Key},'{kv.Value}')=0 "); } else { whereSql.AppendLine($" and {kv.Key} like N{Utils.ToSqlLikeStr(kv.Value)} "); } } index++; } } //as object total = broker.ExecuteScalar("select count(1) as Total from (" + whereSql + ") ttable", lstParameter, CommandType.Text); StringBuilder strsql = new StringBuilder(); if (grid.page != 0 && grid.rows != 0) {//top " + (grid.page * grid.rows) + " strsql.Append("select * from ( select row_number() over(order by " + grid.sidx + " " + grid.sord.ToLower() + ") as Row_Number, "); strsql.Append(whereSql.ToString().Substring(7)); if (!string.IsNullOrEmpty(grid.Where)) { strsql.Append(" and (" + grid.Where + ")");; } //as strsql.Append(" ) ttable where Row_Number between " + (((grid.page - 1) * grid.rows) + 1) + " and " + ((grid.page) * grid.rows)); } else { strsql.Append(whereSql); if (!string.IsNullOrEmpty(grid.sidx)) { strsql.Append(" order by " + grid.sidx); if (!string.IsNullOrEmpty(grid.sord)) { strsql.Append(" " + grid.sord); } else { strsql.Append(" ASC"); } } } DataSet ds = broker.FillSQLDataSet(strsql.ToString(), lstParameter); EntityCollection <T> lstEntity = new EntityCollection <T>(); List <Dictionary <string, object> > dicLstRow = new List <Dictionary <string, object> >(); ToEntityList <T>(ds.Tables[0], ref dicLstRow, ref lstEntity); if (grid.IsDataEntity) { grid.RowsData = lstEntity; } else { grid.RowsData = dicLstRow; } grid.Total = (total != null ? Convert.ToInt32(total) : 0); return(grid); }