public ReportDictionary RunFinalQuery(UserCreatedQueryObject FinalQuery, string username) { // create static values to construct the SQL query var arrayCount = FinalQuery.ColumnNames.Count; var sqlSelect = @"SELECT "; var sqlFrom = @" FROM cameo_DWH.dbo." + FinalQuery.TableName.ToString(); var queryName = FinalQuery.QueryName; // create arrays to store the selected columns, where and group clauses (if any) List <string> columnArray = new List <string>(); List <string> columnArrayForDictionary = new List <string>(); List <string> groupArray = new List <string>(); List <string> whereArray = new List <string>(); // iterate through the list of columns and add each value to the correct array for (var i = 0; i < arrayCount; i++) { if (FinalQuery.AdditionalActions[i].columnsToAggregate == "None") { if (FinalQuery.IncludeInViews[i].columnsToInclude == "Yes") { columnArray.Add("ISNULL(" + FinalQuery.ColumnNames[i].ColumnName.ToString() + ",'') AS " + FinalQuery.ColumnNames[i].ColumnName.ToString()); groupArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString()); columnArrayForDictionary.Add(FinalQuery.ColumnNames[i].ColumnName.ToString()); } } else { //columnArray.Add(FinalQuery.AdditionalActions[i].columnsToAggregate.ToString() + "(" + FinalQuery.ColumnNames[i].ColumnName.ToString() + ") AS " + FinalQuery.ColumnNames[i].ColumnName.ToString()); columnArrayForDictionary.Add(FinalQuery.ColumnNames[i].ColumnName.ToString()); columnArray.Add(FinalQuery.AdditionalActions[i].columnsToAggregate.ToString() + "(" + FinalQuery.ColumnNames[i].ColumnName.ToString() + ") AS " + FinalQuery.ColumnNames[i].ColumnName.ToString()); //groupArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString()); //if (FinalQuery.IncludeInViews[i].columnsToInclude == "Yes") //{ //groupArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString()); //} } if (FinalQuery.FilterOperators[i].FilterOperator != "None") { if (FinalQuery.FilterOperators[i].FilterOperator == "=") { if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE") { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + FinalQuery.FilterOperators[i].FilterOperator.ToString() + " " + "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'"); } else { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + FinalQuery.FilterOperators[i].FilterOperator.ToString() + " " + FinalQuery.FilterValues[i].FilterValue.ToString()); } } else if (FinalQuery.FilterOperators[i].FilterOperator == "not=") { if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE") { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + "<>" + " " + "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'"); } else { whereArray.Add(" WHERE " + FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + "<>" + " " + FinalQuery.FilterValues[i].FilterValue.ToString()); } } else if (FinalQuery.FilterOperators[i].FilterOperator == "greaterThan") { if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE") { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + ">" + " " + "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'"); } else { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + ">" + " " + FinalQuery.FilterValues[i].FilterValue.ToString()); } } else if (FinalQuery.FilterOperators[i].FilterOperator == "lessThan") { if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE") { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + "<" + " " + "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'"); } else { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + "<" + " " + FinalQuery.FilterValues[i].FilterValue.ToString()); } } else if (FinalQuery.FilterOperators[i].FilterOperator == "greaterEqual") { if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE") { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + ">=" + " " + "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'"); } else { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + ">=" + " " + FinalQuery.FilterValues[i].FilterValue.ToString()); } } else if (FinalQuery.FilterOperators[i].FilterOperator == "lessEqual") { if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE") { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + "<=" + " " + "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "'"); } else { whereArray.Add(" WHERE " + FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + "<=" + " " + FinalQuery.FilterValues[i].FilterValue.ToString()); } } else if (FinalQuery.FilterOperators[i].FilterOperator == "between") { if (FinalQuery.ColumnTypeSelection[i].columnTypes == "STRING" || FinalQuery.ColumnTypeSelection[i].columnTypes == "DATE") { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + "BETWEEN" + " " + "'" + FinalQuery.FilterValues[i].FilterValue.ToString() + "' AND '" + FinalQuery.SecondFilterValue[i].SecondFilterValue.ToString() + "'"); } else { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + "BETWEEN" + " " + FinalQuery.FilterValues[i].FilterValue.ToString() + " AND " + FinalQuery.SecondFilterValue[i].SecondFilterValue.ToString()); } } else if (FinalQuery.FilterOperators[i].FilterOperator == "contains") { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + "LIKE" + " " + "'%" + FinalQuery.FilterValues[i].FilterValue.ToString() + "%'"); } else if (FinalQuery.FilterOperators[i].FilterOperator == "isOneOf") { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + "IN" + " ('" + FinalQuery.FilterValues[i].FilterValue.ToString().Replace(",", "','") + "')"); } else if (FinalQuery.FilterOperators[i].FilterOperator == "isNotOneOf") { whereArray.Add(FinalQuery.ColumnNames[i].ColumnName.ToString() + " " + "NOT IN" + " ('" + FinalQuery.FilterValues[i].FilterValue.ToString().Replace(",", "','") + "')"); } } } // Add comma seperators to the select section foreach (var j in columnArray) { sqlSelect = sqlSelect + j + ","; } //System.Diagnostics.Debug.WriteLine(sqlSelect); // add the FROM section of the SQL query sqlSelect = sqlSelect.Remove(sqlSelect.Length - 1, 1) + sqlFrom; // add WHERE clause values if any exist and remove the additional AND from the end of the string if (whereArray.Count > 0) { sqlSelect += " WHERE "; foreach (var g in whereArray) { sqlSelect = sqlSelect + g + " AND "; } sqlSelect = sqlSelect.Remove(sqlSelect.Length - 4, 3); } // add an optional GROUP BY clause if (groupArray.Count > 0) { sqlSelect = sqlSelect + " GROUP BY "; foreach (var h in groupArray) { sqlSelect = sqlSelect + h + ","; } } // remove extra commas from the end of the string sqlSelect = sqlSelect.Remove(sqlSelect.Length - 1, 1); //System.Diagnostics.Debug.WriteLine(sqlSelect); // create a multidimensional dictionary object Dictionary <string, List <string> > results = new Dictionary <string, List <string> >(); // add keys to the dictionary (column headers) if (columnArrayForDictionary.Count > 0) { for (var colArray = 0; colArray < columnArrayForDictionary.Count; colArray++) { //List<string> list = new List<string>(); List <string> arraystring = new List <string>(); results.Add(columnArrayForDictionary[colArray], arraystring); } } // open a localised connection to SQL Server and pass in the generated query. Push each row returned to the dictionary values using (SqlConnection conn = new SqlConnection("Data Source =OAKSQL03; Initial Catalog = Cameo_DWH; User Id=camarchive; Password=camarchive; Pooling=False; Connect Timeout = 30")) { SqlCommand comm = new SqlCommand(sqlSelect, conn); comm.Connection.Open(); comm.ExecuteNonQuery(); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { Dictionary <string, List <string> > clm = new Dictionary <string, List <string> >(); for (int h = 0; h < columnArrayForDictionary.Count; h++) { dynamic fieldMetaData = new ExpandoObject(); var sqlType = reader.GetDataTypeName(h); //var sqlColName = reader.GetName(h).First(); //System.Diagnostics.Debug.WriteLine(sqlColName); if (sqlType == "varchar" || sqlType == "nvarchar") { results[reader.GetName(h)].Add(reader.GetString(h)); } else if (sqlType == "bigint") { results[reader.GetName(h)].Add(reader.GetInt64(h).ToString()); } else if (sqlType == "float") { results[reader.GetName(h)].Add(reader.GetFloat(h).ToString()); } else if (sqlType == "int") { results[reader.GetName(h)].Add(reader.GetInt32(h).ToString()); } else if (sqlType == "decimal") { results[reader.GetName(h)].Add(reader.GetDecimal(h).ToString()); } else if (sqlType == "numeric") { results[reader.GetName(h)].Add(reader.GetDecimal(h).ToString()); } else if (sqlType == "datetime") { if (reader.GetDateTime(h).ToString() == null) { results[reader.GetName(h)].Add(""); } else { //results[reader.GetName(h)].Add(reader.GetDateTime(h).ToShortDateString().ToString()); results[reader.GetName(h)].Add(reader.GetDateTime(h).ToString()); } } } } comm.Connection.Close(); } // pass the final dictionary object to the global dictionary ReportDictionary report = new ReportDictionary(); report.ReportResult = results; SQLString queryToSave = new SQLString(); queryToSave.StoreSql(sqlSelect, queryName, username); // return a partial view to the JQuery request and pass in the dictionary class return(report); }