public DataTable ExecuteGetNullTable(int dbId, string sql) { string dbType = Fun.GetDataBaseType(); switch (dbType) { case "Sql": var orderPlace = sql.ToLower().Replace("\r\n", " ").IndexOf(" order "); if (orderPlace > 1) { sql = sql.Substring(0, orderPlace); } break; } string withStr = ""; string WithStartStr = "-----WithStart-----"; string WithEndStr = "-----WithEnd-----"; int WithStartP = sql.IndexOf(WithStartStr); int WithEndP = sql.IndexOf(WithEndStr); if (WithStartP > -1 && WithEndP > -1 && WithEndP > WithStartP) { withStr = sql.Substring(WithStartStr.Length, WithEndP - WithStartP - WithStartStr.Length); sql = sql.Substring(WithEndP + WithEndStr.Length); } string sqlStr = withStr + "SELECT * FROM ( " + sql + " ) T WHERE 1<>1"; return(ExecuteSqlToTable(dbId, sqlStr)); }
public int QueryCount(string loginKey, ref ErrorInfo err, string queryCode, string whereJsonStr, IList <QueryPara> paraList, ref string sqlStr) { using (DBEntities db = new DBEntities()) { var query = db.YL_QUERY.SingleOrDefault(x => x.CODE == queryCode); TQuery ent = Fun.ClassToCopy <YL_QUERY, TQuery>(query); GlobalUser gu = Global.GetUser(loginKey); string whereStr = ""; ent.QUERY_CONF = MakeSql(gu, ent.QUERY_CONF, null, whereJsonStr, paraList, ref whereStr, Fun.GetDataBaseType()); string withStr = ""; string WithStartStr = "-----WithStart-----"; string WithEndStr = "-----WithEnd-----"; int WithStartP = ent.QUERY_CONF.IndexOf(WithStartStr); int WithEndP = ent.QUERY_CONF.IndexOf(WithEndStr); if (WithStartP > -1 && WithEndP > -1 && WithEndP > WithStartP) { withStr = ent.QUERY_CONF.Substring(WithStartStr.Length, WithEndP - WithStartP - WithStartStr.Length); ent.QUERY_CONF = ent.QUERY_CONF.Substring(WithEndP + WithEndStr.Length); } if (!string.IsNullOrEmpty(whereStr)) { ent.QUERY_CONF = withStr + "SELECT * FROM ( " + ent.QUERY_CONF + " ) TMP where " + whereStr; } else { ent.QUERY_CONF = withStr + "SELECT * FROM ( " + ent.QUERY_CONF + " ) TMP"; } var reInt = 0; try { sqlStr = ent.QUERY_CONF; reInt = ExecuteCount(query.DB_SERVER_ID.Value, ent.QUERY_CONF); } catch (Exception e) { err.IsError = true; err.Message = e.Message; } return(reInt); } }
public DataTable QueryDataTable(string loginKey, ref ErrorInfo err, string queryCode, string orderStr, string whereStr, IList <QueryPara> paraList, ref string sqlStr) { DataTable reDt = new DataTable(); using (DBEntities db = new DBEntities()) { var query = db.YL_QUERY.SingleOrDefault(x => x.CODE == queryCode); TQuery ent = Fun.ClassToCopy <YL_QUERY, TQuery>(query); GlobalUser gu = Global.GetUser(loginKey); ent.QUERY_CONF = MakeSql(gu, ent.QUERY_CONF, orderStr, whereStr, paraList, ref whereStr, Fun.GetDataBaseType()); reDt = ExecuteSqlAll(query.DB_SERVER_ID.Value, ent.QUERY_CONF, orderStr, whereStr, ref sqlStr); reDt.TableName = "tables1"; if (ent.QueryCfg != null) { foreach (var par in ent.QueryCfg) { if (reDt.Columns[par.FieldName] != null) { if (par.Show) { reDt.Columns[par.FieldName].Caption = par.Alias; //reDt.Columns[par.FieldName].ColumnName = par.Alias; } else { reDt.Columns.Remove(par.FieldName); } } } } return(reDt); } }
public DataTable QueryExecute(string loginKey, ref ErrorInfo err, string queryCode, int pageIndex, int pageSize, string orderStr, string whereJsonStr, IList <QueryPara> paraList, ref string sqlStr) { DataTable reDt = new DataTable(); GlobalUser gu = Global.GetUser(loginKey); if (gu == null) { err.IsError = true; err.Message = "用户【" + loginKey + "】登录超时,请重新登录"; return(reDt); } using (DBEntities db = new DBEntities()) { var query = db.YL_QUERY.SingleOrDefault(x => x.CODE == queryCode); TQuery ent = Fun.ClassToCopy <YL_QUERY, TQuery>(query); IList <QueryCfg> cfg = JSON.EncodeToEntity <IList <QueryCfg> >(query.QUERY_CFG_JSON); string whereStr = ""; ent.QUERY_CONF = MakeSql(gu, ent.QUERY_CONF, orderStr, whereJsonStr, paraList, ref whereStr, Fun.GetDataBaseType()); try { sqlStr = ent.QUERY_CONF; reDt = ExecuteSql(ent.QUERY_CONF, pageIndex, pageSize, orderStr, whereStr, query.DB_SERVER_ID.Value, cfg.Select(x => x.FieldName).ToList()); } catch (Exception e) { err.IsError = true; err.Message = e.Message; return(reDt); } } reDt.TableName = "tables1"; return(reDt); }