public string ScriptResultAsCreateTable(int resultIndex, string tableName)
{
//todo: columnnames must be unique in a table. It's possible to have a result set with duplicate column names, but not a table.
//todo: bug with SELECT * FROM INFORMATION_SCHEMA.Tables - possibly hidden fields??
if (!FieldScripting.ResultIsRenderableAsCreateTable(results[resultIndex]))
{
return("--No schema for result from query.");
}
int visibleColumnCount = results[resultIndex].visibleColumnCount;
var rows = results[resultIndex].schema.Rows;
StringBuilder buffer = new StringBuilder("CREATE TABLE " + tableName + "(\r\n");
for (int fieldIndex = 0; fieldIndex < results[resultIndex].visibleColumnCount; fieldIndex++)
{
var fieldInfo = rows[fieldIndex];
buffer.Append(" " +
FieldScripting.FieldNameOrDefault(fieldInfo.ItemArray, fieldIndex) +
" " +
FieldScripting.DataTypeName(fieldInfo) +
FieldScripting.DataTypeParameterIfAny(fieldInfo) +
" " +
FieldScripting.NullOrNotNull(fieldInfo.ItemArray[(int)FieldScripting.FieldInfo.AllowsNulls])
);
if (fieldIndex + 1 < visibleColumnCount)
{
buffer.Append(",\r\n");
}
else
{
buffer.Append("\r\n");
}
}
buffer.Append(");\r\n");
return(buffer.ToString());
}
private static void renderSchemaAndData(FlexResultSet resultSet, SqlRunParameters srp)
{
var sb = srp.resultsText;
for (int i = 0; i < resultSet.results.Count; i++)
{
if (resultSet.results[i].recordsAffected > 0)
{
sb.AppendLine(String.Format("--Records affected: {0:G}\r\n\r\n", resultSet.results[i].recordsAffected));
}
string resultTableName = "#Result" + (i + 1 + srp.completedResultsCount).ToString();
sb.AppendLine(resultSet.ScriptResultAsCreateTable(i, resultTableName));
sb.Append("\r\n");
if (FieldScripting.ResultIsRenderableAsScriptedData(resultSet.results[i]))
{
sb.AppendLine(FieldScripting.ScriptResultDataAsInsert(resultSet.results[i], resultTableName, FlexResultSet.SQL2008MaxRowsInValuesClause).ToString());
}
srp.completedResultsCount += 1;
sb.Append("\r\n");
}
}
public static void renderAsCSV(FlexResultSet resultSet, SqlRunParameters srp)
{
int writtenResultSets = 0;
for (int i = 0; i < resultSet.results.Count; i++)
{
FlexResult result = resultSet.results[i];
if (result.schema != null && result.data != null)
{
writtenResultSets += 1;
if (writtenResultSets > 1)
{
srp.openNewOutputStream();
}
int columnCount = result.visibleColumnCount;
//do header
for (int colIndex = 0; colIndex < columnCount; colIndex += 1)
{
srp.WriteToStream(columnName(result, colIndex));
if (colIndex + 1 < columnCount)
{
srp.WriteToStream(",");
}
else
{
srp.WriteToStream("\r\n");
}
}
//do data rows
for (int rowIndex = 0; rowIndex < result.data.Count; rowIndex += 1)
{
for (int colIndex = 0; colIndex < columnCount; colIndex += 1)
{
//todo: fix each of these items to work with the actual scripting stuff (requires finishing major refactoring work).
object fieldData = result.data[rowIndex][colIndex];
SQLColumn fieldInfo = result.schema[colIndex];
if (fieldData == null || fieldData is DBNull)
{
//do nothing
}
else if (numberLikeDataTypesForCSV.Contains(fieldInfo.DataType))
{
//todo: may be bug in German culture where they use , as the decimal separator.
srp.WriteToStream(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false));
}
else if (dateLikeDataTypesForCSV.Contains(fieldInfo.DataType))
{
srp.WriteToStream(escapeForCSV(String.Format("{0}.{1}",
((DateTime)fieldData).ToString("s"),
((DateTime)fieldData).ToString("fff")
)));
}
else if (fieldInfo.DataType == "binary" || fieldInfo.DataType == "rowversion" || fieldInfo.DataType == "timestamp")
{
byte[] d = (byte[])result.data[rowIndex][colIndex];
srp.WriteToStream(escapeForCSV(FieldScripting.formatBinary(d, d.Length)));
}
else if (fieldInfo.DataType == "varbinary" || fieldInfo.DataType == "image")
{
srp.WriteToStream(escapeForCSV(FieldScripting.formatVarbinary(fieldData)));
}
else
{
srp.WriteToStream(escapeForCSV(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false)));
}
if (colIndex + 1 < columnCount)
{
srp.WriteToStream(",");
}
else
{
srp.WriteToStream("\r\n");
};
}
}
srp.worksheetIsValid = true;
}
}
}
public static void renderAsXMLSpreadsheet(FlexResultSet resultSet, SqlRunParameters srp)
{
//todo: refactor this and FlexResultSet to to share code and have test coverage.
srp.WriteToStream(Utils.GetResourceByName("TSqlFlex.Core.Resources.XMLSpreadsheetTemplateHeader.txt"));
for (int i = 0; i < resultSet.results.Count; i++)
{
FlexResult result = resultSet.results[i];
if (result.schema != null && result.data != null)
{
int columnCount = result.visibleColumnCount;
srp.WriteToStream(String.Format("<Worksheet ss:Name=\"Sheet{0}\">", i + 1));
srp.WriteToStream(String.Format("<Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultRowHeight=\"15\">",
columnCount,
result.data.Count + 1 /* include header row */)
);
//do header
srp.WriteToStream("<Row>");
for (int colIndex = 0; colIndex < columnCount; colIndex += 1)
{
srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">{0}</Data></Cell>", columnName(result, colIndex)));
}
srp.WriteToStream("</Row>\r\n");
//do data rows
for (int rowIndex = 0; rowIndex < result.data.Count; rowIndex += 1)
{
srp.WriteToStream("<Row>");
for (int colIndex = 0; colIndex < columnCount; colIndex += 1)
{
//todo: fix each of these items to work with the actual scripting stuff (requires finishing major refactoring work).
object fieldData = result.data[rowIndex][colIndex];
object[] fieldInfo = result.schema.Rows[colIndex].ItemArray;
string fieldTypeName = fieldInfo[(int)FieldScripting.FieldInfo.DataType].ToString();
if (fieldData == null || fieldData is DBNull)
{
srp.WriteToStream("<Cell/>");
}
else if (fieldTypeName == "bigint" || fieldTypeName == "numeric" || fieldTypeName == "smallint" || fieldTypeName == "decimal" || fieldTypeName == "smallmoney" ||
fieldTypeName == "int" || fieldTypeName == "tinyint" || fieldTypeName == "float" || fieldTypeName == "real" || fieldTypeName == "money" || fieldTypeName == "bit")
{
srp.WriteToStream(String.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false))));
}
else if (fieldTypeName == "date" || fieldTypeName == "datetime2" || fieldTypeName == "time" || fieldTypeName == "datetime" ||
fieldTypeName == "smalldatetime")
{
srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"DateTime\">{0}.{1}</Data></Cell>\r\n",
escapeForXML(((DateTime)fieldData).ToString("s")),
escapeForXML(((DateTime)fieldData).ToString("fff"))
));
}
else if (fieldTypeName == "binary" || fieldTypeName == "rowversion" || fieldTypeName == "timestamp")
{
byte[] d = (byte[])result.data[rowIndex][colIndex];
srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.formatBinary(d, d.Length))));
}
else if (fieldTypeName == "varbinary" || fieldTypeName == "image")
{
srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.formatVarbinary(fieldData))));
}
else
{
srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false))));
}
}
srp.WriteToStream("</Row>\r\n");
}
srp.WriteToStream("</Table></Worksheet>\r\n");
srp.worksheetIsValid = true;
}
}
srp.WriteToStream("</Workbook>\r\n");
}
