private void GetQueryGridData() { AppState appState = AppState.FromJson(Request.Form["state"]); Configuration.ApplicationRow application = Configuration.Application.First(o => o.ApplicationID == appState.Application); Configuration.QueryRow query = Configuration.Query.First(o => o.QueryID == appState.Query); List <String> zones = new List <String>(); List <String> levels = new List <String>(); if (!application.IsZoneLevelIDNull()) { zones = application.ZoneLevelRow.GetZoneRows().Select(o => o.ZoneID).ToList(); levels = application.ZoneLevelRow.GetLevelRows().Select(o => o.LevelID).ToList(); } Dictionary <String, Object> result = new Dictionary <String, Object>(); using (OleDbCommand command = query.GetDatabaseCommand()) { command.Parameters[0].Value = appState.TargetIds.Join(","); if (command.Parameters.Count > 1) { command.Parameters[1].Value = AppUser.GetRole(); } using (OleDbDataReader reader = command.ExecuteReader()) { // get the indexes of the ID columns int mapIdColumn = reader.GetColumnIndex("MapID"); int dataIdColumn = reader.GetColumnIndex("DataID"); int zoneIdColumn = zones.Count > 0 ? reader.GetColumnIndex("ZoneID") : -1; int levelIdColumn = levels.Count > 0 ? reader.GetColumnIndex("LevelID") : -1; // write the column headers List <String> headers = new List <String>(); for (int i = 0; i < reader.FieldCount; ++i) { if (i != mapIdColumn && i != dataIdColumn && i != zoneIdColumn && i != levelIdColumn) { headers.Add(reader.GetName(i)); } } result.Add("headers", headers); // write the data List <Dictionary <String, Object> > rows = new List <Dictionary <String, Object> >(); while (reader.Read()) { if (!reader.IsDBNull(mapIdColumn) && !reader.IsDBNull(dataIdColumn)) { Dictionary <String, String> id = new Dictionary <String, String>(); id.Add("m", reader.GetValue(mapIdColumn).ToString()); if (dataIdColumn > -1 && !reader.IsDBNull(dataIdColumn)) { id.Add("d", reader.GetValue(dataIdColumn).ToString()); } if (zoneIdColumn > -1 && !reader.IsDBNull(zoneIdColumn)) { string zoneId = reader.GetValue(zoneIdColumn).ToString(); if (zones.Contains(zoneId)) { id.Add("z", zoneId); } } if (levelIdColumn > -1 && !reader.IsDBNull(levelIdColumn)) { string levelId = reader.GetValue(levelIdColumn).ToString(); if (levels.Contains(levelId)) { id.Add("l", levelId); } } List <Object> values = new List <Object>(); for (int i = 0; i < reader.FieldCount; ++i) { if (i != mapIdColumn && i != dataIdColumn && i != zoneIdColumn && i != levelIdColumn) { values.Add(reader.IsDBNull(i) ? null : reader.GetValue(i)); } } Dictionary <String, Object> row = new Dictionary <String, Object>(); row.Add("id", id); row.Add("v", values); rows.Add(row); } } result.Add("rows", rows); } command.Connection.Dispose(); } ReturnJson(result); }
public void AddFromReader(OleDbDataReader reader, bool addSpace) { do { if (reader.HasRows) { HtmlGenericControl resultSetDiv = CreateDiv("ResultSet"); _container.Controls.Add(resultSetDiv); int headerColumn = reader.GetColumnIndex("Header"); string lastHeader = null; int subheaderColumn = reader.GetColumnIndex("Subheader"); string lastSubHeader = null; int lastColumn = reader.FieldCount - 1; for (int i = 0; i < 2; ++i) { if (lastColumn == headerColumn || lastColumn == subheaderColumn) { lastColumn -= 1; } } while (reader.Read()) { HtmlGenericControl rowSetDiv = CreateDiv("RowSet"); resultSetDiv.Controls.Add(rowSetDiv); bool headerAdded = false; bool subheaderAdded = false; if (headerColumn >= 0 && !reader.IsDBNull(headerColumn)) { string header = reader.GetValue(headerColumn).ToString(); headerAdded = header != lastHeader; if (headerAdded) { HtmlGenericControl headerDiv = CreateDiv("RowSetHeader", header); rowSetDiv.Controls.Add(headerDiv); lastHeader = header; } } if (subheaderColumn >= 0 && !reader.IsDBNull(subheaderColumn)) { string subheader = reader.GetValue(subheaderColumn).ToString(); subheaderAdded = subheader != lastSubHeader; if (subheaderAdded) { if (headerAdded && addSpace) { AddSpace(rowSetDiv, "Space2"); } HtmlGenericControl subheaderDiv = CreateDiv("RowSetSubheader", subheader); rowSetDiv.Controls.Add(subheaderDiv); lastSubHeader = subheader; } } if (lastColumn < 0) { if (addSpace) { AddSpace(rowSetDiv, "Space2"); } } else { if ((headerAdded || subheaderAdded) && addSpace) { AddSpace(rowSetDiv, "Space2"); } for (int i = 0; i < reader.FieldCount; ++i) { if (i != headerColumn && i != subheaderColumn) { HtmlGenericControl valueSetDiv = CreateDiv("ValueSet"); rowSetDiv.Controls.Add(valueSetDiv); HtmlGenericControl labelDiv = CreateDiv("Label", reader.GetName(i)); valueSetDiv.Controls.Add(labelDiv); if (addSpace) { HtmlGenericControl separatorDiv = CreateDiv("Separator", ""); valueSetDiv.Controls.Add(separatorDiv); } HtmlGenericControl valueDiv = CreateValueDiv(!reader.IsDBNull(i) ? reader.GetValue(i) : null); valueSetDiv.Controls.Add(valueDiv); if (i < lastColumn && addSpace) { AddSpace(rowSetDiv, "Space1"); } } } if (addSpace) { AddSpace(rowSetDiv, "Space4"); } } } } }while (reader.NextResult()); }
private void DefaultMethod() { Configuration config = AppContext.GetConfiguration(); Configuration.ApplicationRow applicationRow = config.Application.First(o => o.ApplicationID == Request.Form["app"]); Configuration.SearchRow searchRow = config.Search.First(o => o.SearchID == Request.Form["search"]); JavaScriptSerializer serializer = new JavaScriptSerializer(); Dictionary <String, Object> criteria = serializer.Deserialize <Dictionary <String, Object> >(Request.Form["criteria"]); List <String> levels = new List <String>(); if (!applicationRow.IsZoneLevelIDNull()) { levels = applicationRow.ZoneLevelRow.GetLevelRows().Select(o => o.LevelID).ToList(); } List <String> where = new List <String>(); List <Object> parameters = new List <Object>(); foreach (string criteriaID in criteria.Keys) { Configuration.SearchInputFieldRow searchInputFieldRow = config.SearchInputField.First(o => o.FieldID == criteriaID); switch (searchInputFieldRow.FieldType) { case "autocomplete": case "date": case "list": case "number": case "text": where.Add(searchInputFieldRow.ColumnName + " = ?"); parameters.Add(criteria[criteriaID]); break; case "daterange": case "numberrange": ArrayList values = (ArrayList)criteria[criteriaID]; if (values[0] != null) { where.Add(searchInputFieldRow.ColumnName + " >= ?"); parameters.Add(values[0]); } if (values[1] != null) { where.Add(searchInputFieldRow.ColumnName + " <= ?"); parameters.Add(values[1]); } break; } } Dictionary <String, Object> result = new Dictionary <String, Object>(); using (OleDbCommand command = searchRow.GetSelectCommand()) { command.CommandText = String.Format(command.CommandText, String.Join(" and ", where.ToArray())); for (int i = 0; i < parameters.Count; ++i) { command.Parameters.AddWithValue(i.ToString(), parameters[i]); } using (OleDbDataReader reader = command.ExecuteReader()) { // get the indexes of the ID columns int mapIdColumn = reader.GetColumnIndex("MapID"); int dataIdColumn = reader.GetColumnIndex("DataID"); int levelIdColumn = levels.Count > 0 ? reader.GetColumnIndex("LevelID") : -1; // write the column headers List <String> headers = new List <String>(); for (int i = 0; i < reader.FieldCount; ++i) { if (i != mapIdColumn && i != dataIdColumn && i != levelIdColumn) { headers.Add(reader.GetName(i)); } } result.Add("headers", headers); // write the data List <Dictionary <String, Object> > rows = new List <Dictionary <String, Object> >(); while (reader.Read()) { if (!reader.IsDBNull(mapIdColumn) && !reader.IsDBNull(dataIdColumn)) { Dictionary <String, String> id = new Dictionary <String, String>(); id.Add("m", reader.GetValue(mapIdColumn).ToString()); if (dataIdColumn > -1 && !reader.IsDBNull(dataIdColumn)) { id.Add("d", reader.GetValue(dataIdColumn).ToString()); } if (levelIdColumn > -1 && !reader.IsDBNull(levelIdColumn)) { string levelId = reader.GetValue(levelIdColumn).ToString(); if (levels.Contains(levelId)) { id.Add("l", levelId); } } List <Object> values = new List <Object>(); for (int i = 0; i < reader.FieldCount; ++i) { if (i != mapIdColumn && i != dataIdColumn && i != levelIdColumn) { values.Add(reader.IsDBNull(i) ? null : reader.GetValue(i)); } } Dictionary <String, Object> row = new Dictionary <String, Object>(); row.Add("id", id); row.Add("v", values); rows.Add(row); } } result.Add("rows", rows); } } ReturnJson(result); }