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