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); }
private static JqGrid QueryTable <T>(WhereBuilder wb, JqGrid grid, string dbInstanceName) where T : EntityBase, new() { return(QueryTable <T>(wb.SQLString, wb.Parameters, grid, dbInstanceName)); }
private static JqGrid QueryTable <T>(string sqlString, DataAccessParameterCollection parameters, JqGrid grid, string dbInstanceName) where T : EntityBase, new() { string hql = sqlString; string where = grid.Where; if (!string.IsNullOrEmpty(where)) { hql += " and (" + where + ")"; } if (parameters == null) { parameters = new DataAccessParameterCollection(); } if (grid != null && grid.LstParms != null && grid.LstParms.Count > 0) { foreach (var item in grid.LstParms) { parameters.Add(item.Name, new DataAccessParameter(item.Name, item.Value)); } } if (!string.IsNullOrEmpty(dbInstanceName)) { using (DataAccessBroker broker = DataAccessFactory.Instance(dbInstanceName)) { return(ExcuteTable <T>(hql, parameters, grid, broker)); } } else { using (DataAccessBroker broker = DataAccessFactory.Instance()) { return(ExcuteTable <T>(hql, parameters, grid, broker)); } } }
public static JqGrid QueryGrid <T>(IQuery queryObj, JqGrid grid) where T : EntityBase, new() { WhereBuilder wb = queryObj.ParseSQL(); return(QueryTable <T>(wb, grid, grid.DbInstanceName)); }