private static ResultSet GetTableResults(SqlConnection connection, TableInfo table) { // We could probably refactor QueryRunner to expose this functionality // to us without having to recreate it here. var command = new SqlCommand($"SELECT * FROM {table.Name} ORDER BY Id ASC", connection); var resultSet = new ResultSet(); using (var reader = command.ExecuteReader()) { for (int i = 0; i < reader.FieldCount; ++i) { var column = new ResultColumnInfo { Name = reader.GetName(i) }; ResultColumnType type; if (ResultColumnInfo.ColumnTypeMap.TryGetValue(reader.GetFieldType(i), out type)) { column.Type = type; } resultSet.Columns.Add(column); } while (reader.Read()) { var row = new List <object>(); for (int i = 0; i < reader.FieldCount; ++i) { object value = reader.GetValue(i); if (value is DateTime) { value = ((DateTime)value).ToJavascriptTime(); } row.Add(value); } resultSet.Rows.Add(row); } } return(resultSet); }
private static void DecorateColumn(ResultColumnInfo column) { switch (column.Name) { case "Post Link": column.Type = ResultColumnType.Post; break; case "User Link": column.Type = ResultColumnType.User; break; case "Comment Link": column.Type = ResultColumnType.Comment; break; case "Suggested Edit Link": column.Type = ResultColumnType.SuggestedEdit; break; default: break; } }
private static void ProcessColumn(SqlConnection cnn, int index, List<List<object>> rows, ResultColumnInfo column) { IEnumerable<object> values = rows.Select(row => row[index]); List<object> processedValues = magic_columns[column.Name](cnn, values); int rowNumber = 0; foreach (var row in rows) { row[index] = processedValues[rowNumber]; rowNumber++; } }
/// <summary> /// Executes an SQL query and populates a given <see cref="QueryResults" /> instance with the results. /// </summary> /// <param name="results"><see cref="QueryResults" /> instance to populate with results.</param> /// <param name="command">SQL command to execute.</param> /// <param name="result"><see cref="AsyncResult"/> instance to use to mark state changes.</param> /// <param name="messages"><see cref="StringBuilder" /> instance to which to append messages.</param> /// <param name="IncludeExecutionPlan">If true indciates that the query execution plans are expected to be contained /// in the results sets; otherwise, false.</param> private static void PopulateResults(QueryResults results, SqlCommand command, AsyncQueryRunner.AsyncResult result, StringBuilder messages, bool IncludeExecutionPlan) { QueryPlan plan = new QueryPlan(); using (SqlDataReader reader = command.ExecuteReader()) { if (result != null && reader.HasRows) { result.HasOutput = true; } do { // Check to see if the resultset is an execution plan if (IncludeExecutionPlan && reader.FieldCount == 1 && reader.GetName(0) == "Microsoft SQL Server 2005 XML Showplan") { if (reader.Read()) { plan.AppendStatementPlan(reader[0].ToString()); } } else { if (reader.FieldCount == 0) { if (reader.RecordsAffected >= 0) { messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected); } continue; } var resultSet = new ResultSet(); resultSet.MessagePosition = messages.Length; results.ResultSets.Add(resultSet); for (int i = 0; i < reader.FieldCount; i++) { var columnInfo = new ResultColumnInfo(); columnInfo.Name = reader.GetName(i); ResultColumnType colType; if (ResultColumnInfo.ColumnTypeMap.TryGetValue(reader.GetFieldType(i), out colType)) { columnInfo.Type = colType; } resultSet.Columns.Add(columnInfo); } int currentRow = 0; while (reader.Read()) { if (currentRow++ >= MAX_RESULTS) { results.Truncated = true; results.MaxResults = MAX_RESULTS; break; } var row = new List<object>(); resultSet.Rows.Add(row); for (int i = 0; i < reader.FieldCount; i++) { object col = reader.GetValue(i); if (col is DateTime) { var date = (DateTime)col; col = date.ToJavascriptTime(); } row.Add(col); } } if (results.Truncated) { // next result would force ado.net to fast forward // through the result set, which is way too slow break; } if (reader.RecordsAffected >= 0) { messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected); } messages.AppendFormat("({0} row(s) affected)\n\n", resultSet.Rows.Count); } } while (reader.NextResult()); command.Cancel(); } results.ExecutionPlan = plan.PlanXml; }
private static ResultSet GetTableResults(IDbConnection connection, TableInfo table) { // We could probably refactor QueryRunner to expose this functionality // to us without having to recreate it here. var command = connection.CreateCommand(); command.CommandText = String.Format("SELECT * FROM {0} ORDER BY Id ASC", table.Name); var resultSet = new ResultSet(); using (var reader = command.ExecuteReader()) { for (int i = 0; i < reader.FieldCount; ++i) { var column = new ResultColumnInfo { Name = reader.GetName(i) }; ResultColumnType type; if (ResultColumnInfo.ColumnTypeMap.TryGetValue(reader.GetFieldType(i), out type)) { column.Type = type; } resultSet.Columns.Add(column); } while (reader.Read()) { var row = new List<object>(); for (int i = 0; i < reader.FieldCount; ++i) { object value = reader.GetValue(i); if (value is DateTime) { value = ((DateTime)value).ToJavascriptTime(); } row.Add(value); } resultSet.Rows.Add(row); } } return resultSet; }
private static void ProcessColumn(SqlConnection cnn, int index, List <List <object> > rows, ResultColumnInfo column) { IEnumerable <object> values = rows.Select(row => row[index]); List <object> processedValues = magic_columns[column.Name](cnn, values); int rowNumber = 0; foreach (var row in rows) { row[index] = processedValues[rowNumber]; rowNumber++; } }
/// <summary> /// Executes an SQL query and populates a given <see cref="QueryResults" /> instance with the results. /// </summary> /// <param name="results"><see cref="QueryResults" /> instance to populate with results.</param> /// <param name="command">SQL command to execute.</param> /// <param name="result"><see cref="AsyncResult"/> instance to use to mark state changes.</param> /// <param name="messages"><see cref="StringBuilder" /> instance to which to append messages.</param> /// <param name="IncludeExecutionPlan">If true indciates that the query execution plans are expected to be contained /// in the results sets; otherwise, false.</param> private static void PopulateResults(QueryResults results, SqlCommand command, AsyncQueryRunner.AsyncResult result, StringBuilder messages, bool IncludeExecutionPlan) { QueryPlan plan = new QueryPlan(); using (SqlDataReader reader = command.ExecuteReader()) { if (result != null && reader.HasRows) { result.HasOutput = true; } do { // Check to see if the resultset is an execution plan if (IncludeExecutionPlan && reader.FieldCount == 1 && reader.GetName(0) == "Microsoft SQL Server 2005 XML Showplan") { if (reader.Read()) { plan.AppendStatementPlan(reader[0].ToString()); } } else { if (reader.FieldCount == 0) { if (reader.RecordsAffected >= 0) { messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected); } continue; } var resultSet = new ResultSet(); resultSet.MessagePosition = messages.Length; results.ResultSets.Add(resultSet); for (int i = 0; i < reader.FieldCount; i++) { var columnInfo = new ResultColumnInfo(); columnInfo.Name = reader.GetName(i); ResultColumnType colType; if (ResultColumnInfo.ColumnTypeMap.TryGetValue(reader.GetFieldType(i), out colType)) { columnInfo.Type = colType; } resultSet.Columns.Add(columnInfo); } int currentRow = 0; while (reader.Read()) { if (currentRow++ >= MAX_RESULTS) { results.Truncated = true; results.MaxResults = MAX_RESULTS; break; } var row = new List <object>(); resultSet.Rows.Add(row); for (int i = 0; i < reader.FieldCount; i++) { object col = reader.GetValue(i); if (col is DateTime) { var date = (DateTime)col; col = date.ToJavascriptTime(); } row.Add(col); } } if (results.Truncated) { // next result would force ado.net to fast forward // through the result set, which is way too slow break; } if (reader.RecordsAffected >= 0) { messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected); } messages.AppendFormat("({0} row(s) affected)\n\n", resultSet.Rows.Count); } } while (reader.NextResult()); command.Cancel(); } results.ExecutionPlan = plan.PlanXml; }