public void TestToText() { QueryResults results = new QueryResults(); results.Messages = @"1 2 "; ResultSet first = new ResultSet(); first.MessagePosition = 0; first.Columns.Add(new ResultColumnInfo() {Name = "a"}); first.Rows.Add(new List<object>() { "xxx" }); ResultSet second = new ResultSet(); second.MessagePosition = 4; second.Columns.Add(new ResultColumnInfo() {Name = "hello"}); second.Rows.Add(new List<object>() { "x" }); results.ResultSets.Add(first); results.ResultSets.Add(second); var transformed = results.ToTextResults(); Assert.AreEqual(true, transformed.TextOnly); var expected = @"a --- xxx 1 hello ----- x 2 "; var actual = string.Join("\r\n", transformed.Messages.Split('\n').Select(s => s.Trim())); Assert.AreEqual(expected , actual); }
private static List<int> GetMaxLengths(ResultSet resultSet) { var maxLengths = new List<int>(); foreach (ResultColumnInfo colInfo in resultSet.Columns) { maxLengths.Add(colInfo.Name.Length); } foreach (var row in resultSet.Rows) { for (int i = 0; i < resultSet.Columns.Count; i++) { object col = row[i]; int curLength; if (nativeTypes.Contains(resultSet.Columns[i].Type)) { curLength = col == null ? 4 : col.ToString().Length; } else { var data = col as JContainer; if (data == null) { curLength = 4; } else { curLength = (data.Value<string>("title") ?? "").Length; } } maxLengths[i] = Math.Max(curLength, maxLengths[i]); } } return maxLengths; }
private static string FormatResultSet(ResultSet resultSet) { var buffer = new StringBuilder(); List<int> maxLengths = GetMaxLengths(resultSet); for (int j = 0; j < maxLengths.Count; j++) { maxLengths[j] = Math.Min(maxLengths[j], MAX_TEXT_COLUMN_WIDTH); buffer.Append(resultSet.Columns[j].Name.PadRight(maxLengths[j] + 1, ' ')); } buffer.AppendLine(); for (int k = 0; k < maxLengths.Count; k++) { buffer.Append("".PadRight(maxLengths[k], '-')); buffer.Append(" "); } buffer.AppendLine(); foreach (var row in resultSet.Rows) { for (int i = 0; i < resultSet.Columns.Count; i++) { object col = row[i]; string currentVal; if (nativeTypes.Contains(resultSet.Columns[i].Type)) { if (col != null && resultSet.Columns[i].Type == ResultColumnType.Date) { currentVal = Util.FromJavaScriptTime((long)col).ToString("yyyy-MM-dd HH:mm:ss"); } else { currentVal = (col ?? "null").ToString(); } } else { var data = col as JContainer; if (data != null && data["title"] != null) { currentVal = (data.Value<string>("title") ?? "null"); } else { var sInfo = col as SiteInfo; if (sInfo != null) { currentVal = sInfo.Name; } else { currentVal = "null"; } } } buffer.Append(currentVal.PadRight(maxLengths[i] + 1, ' ')); } buffer.AppendLine(); } return buffer.ToString(); }
/// <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 QueryResults MockResults() { var rows = new List<List<object>>(); rows.Add(new List<object>()); rows[0].Add("hello"); var resultSet = new ResultSet(); resultSet.Rows = rows; QueryResults results = new QueryResults() { Url = "test", QueryId = 100 }; results.ResultSets.Add(resultSet); return results; }