public static string FieldNameOrDefault(SQLColumn column, int fieldIndex)
{
if (String.IsNullOrEmpty(column.ColumnName))
{
return("anonymousColumn" + (fieldIndex + 1).ToString());
}
return(EscapeObjectName(column.ColumnName));
}
public static string RenderCSharpProperty(SQLColumn s, string name)
{
if (CSharpRenderer.SqlDataTypeToCSharp.ContainsKey(s.DataType))
{
return(String.Format(propertyBoilerplate, CSharpRenderer.SqlDataTypeToCSharp[s.DataType], name));
}
else
{
return(String.Format(propertyBoilerplate, "object", name));
}
}
private static string QuestionMarkOrNot(SQLColumn s)
{
if (!s.AllowNulls)
{
return("");
}
if (!SqlDataTypeToCSharp.TryGetValue(s.DataType, out var dataType) || dataType == "string")
{
return("");
}
return("?");
}
public static string RenderCSharpProperty(SQLColumn s, string name)
{
if (SqlDataTypeToCSharp.ContainsKey(s.DataType))
{
return(string.Format(propertyBoilerplate,
SqlDataTypeToCSharp[s.DataType],
name,
QuestionMarkOrNot(s)));
}
else
{
return(string.Format(propertyBoilerplate, "object", name));
}
}
public static string DisambiguateAndRenderCSharpProperty(SQLColumn s, Dictionary<string, int> columnNamesAndCounts)
{
string name = CSharpRenderer.FieldNameToCSharpPropertyName(s.ColumnName);
if (columnNamesAndCounts.ContainsKey(name))
{
columnNamesAndCounts[name] += 1;
name = String.Format("{0}_{1}", name, columnNamesAndCounts[name]);
}
else
{
columnNamesAndCounts.Add(name, 1);
}
return RenderCSharpProperty(s, name);
}
public static string DisambiguateAndRenderCSharpProperty(SQLColumn s, Dictionary <string, int> columnNamesAndCounts)
{
string name = CSharpRenderer.FieldNameToCSharpPropertyName(s.ColumnName);
if (columnNamesAndCounts.ContainsKey(name))
{
columnNamesAndCounts[name] += 1;
name = String.Format("{0}_{1}", name, columnNamesAndCounts[name]);
}
else
{
columnNamesAndCounts.Add(name, 1);
}
return(RenderCSharpProperty(s, name));
}
public static string DataTypeParameterIfAny(SQLColumn fieldInfo)
{
if (fieldInfo.DataType == "nvarchar" || fieldInfo.DataType == "varchar" || fieldInfo.DataType == "nchar" || fieldInfo.DataType == "char" || fieldInfo.DataType == "binary" || fieldInfo.DataType == "varbinary")
{
if (fieldInfo.ColumnSize == Int32.MaxValue)
{
return("(MAX)");
}
return("(" + fieldInfo.ColumnSize.ToString() + ")");
}
else if (fieldInfo.DataType == "numeric" || fieldInfo.DataType == "decimal")
{
return("(" + fieldInfo.NumericPrecision.ToString() + "," + fieldInfo.NumericScale.ToString() + ")");
}
else if (fieldInfo.DataType == "real")
{
return("(24)");
}
else if (fieldInfo.DataType == "float")
{
//from MSDN: SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53.
return("(53)");
}
else if (fieldInfo.DataType == "datetimeoffset" || fieldInfo.DataType == "time")
{
//see: http://msdn.microsoft.com/en-us/library/bb630289.aspx
if (fieldInfo.NumericScale <= 2)
{
return("(2)");
}
if (fieldInfo.NumericScale <= 4)
{
return("(4)");
}
return("");
}
else if (fieldInfo.DataType == "datetime2")
{
if (fieldInfo.NumericScale < 7)
{
return($"({fieldInfo.NumericScale.ToString()})");
}
return("");
}
return("");
}
public static string DataTypeName(SQLColumn fieldInfo)
{
if (fieldInfo.DataType == "real")
{
return "float"; //this could be a float or a real. There is no simple way to tell via ado.net. Will try to keep it consistent with float.
}
else if (fieldInfo.DataType.EndsWith(".sys.hierarchyid"))
{
return "hierarchyid";
}
else if (fieldInfo.DataType.EndsWith(".sys.geography"))
{
return "geography";
}
else if (fieldInfo.DataType.EndsWith(".sys.geometry"))
{
return "geometry";
}
return fieldInfo.DataType;
}
private static CultureInfo englishUSCulture = new CultureInfo("en-US"); //default culture for formatting.
public static string DataTypeName(SQLColumn fieldInfo)
{
if (fieldInfo.DataType == "real")
{
return("float"); //this could be a float or a real. There is no simple way to tell via ado.net. Will try to keep it consistent with float.
}
else if (fieldInfo.DataType.EndsWith(".sys.hierarchyid"))
{
return("hierarchyid");
}
else if (fieldInfo.DataType.EndsWith(".sys.geography"))
{
return("geography");
}
else if (fieldInfo.DataType.EndsWith(".sys.geometry"))
{
return("geometry");
}
return(fieldInfo.DataType);
}
public static string DataTypeParameterIfAny(SQLColumn fieldInfo)
{
if (fieldInfo.DataType == "nvarchar" || fieldInfo.DataType == "varchar" || fieldInfo.DataType == "nchar" || fieldInfo.DataType == "char" || fieldInfo.DataType == "binary" || fieldInfo.DataType == "varbinary")
{
if (fieldInfo.ColumnSize == Int32.MaxValue)
{
return "(MAX)";
}
return "(" + fieldInfo.ColumnSize.ToString() + ")";
}
else if (fieldInfo.DataType == "numeric" || fieldInfo.DataType == "decimal")
{
return "(" + fieldInfo.NumericPrecision.ToString() + "," + fieldInfo.NumericScale.ToString() + ")";
}
else if (fieldInfo.DataType == "real")
{
return "(24)";
}
else if (fieldInfo.DataType == "float")
{
//from MSDN: SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53.
return "(53)";
}
else if (fieldInfo.DataType == "datetimeoffset" || fieldInfo.DataType == "time")
{
//see: http://msdn.microsoft.com/en-us/library/bb630289.aspx
if (fieldInfo.NumericScale <= 2)
{
return "(2)";
}
if (fieldInfo.NumericScale <= 4)
{
return "(4)";
}
return "";
}
return "";
}
//todo: may need some refactoring :-)
public static string valueAsTSQLLiteral(object data, SQLColumn fieldInfo, bool forTSQLScript = true)
{
if (data == null || data is DBNull)
{
return "NULL";
}
if (data is string)
{
if ((data as string).Contains('\0'))
{
return formatVarbinary(data);
}
}
if (fieldInfo.DataType == "char")
{
return formatChar(data, forTSQLScript);
}
else if (fieldInfo.DataType == "varchar" || fieldInfo.DataType == "text")
{
return formatVarchar(data, forTSQLScript);
}
else if (fieldInfo.DataType == "nchar")
{
return formatNchar(data, forTSQLScript);
}
else if (fieldInfo.DataType == "nvarchar" || fieldInfo.DataType == "ntext" || fieldInfo.DataType == "xml")
{
return formatNvarchar(data, forTSQLScript);
}
else if (fieldInfo.DataType == "bigint" || fieldInfo.DataType == "numeric" || fieldInfo.DataType == "smallint" || fieldInfo.DataType == "decimal" || fieldInfo.DataType == "smallmoney" ||
fieldInfo.DataType == "int" || fieldInfo.DataType == "tinyint" || fieldInfo.DataType == "float" || fieldInfo.DataType == "real" || fieldInfo.DataType == "money")
{
return getDataAsAppropriateNumericFormat(data);
}
else if (fieldInfo.DataType == "binary" || fieldInfo.DataType == "rowversion" || fieldInfo.DataType == "timestamp")
{
return formatBinary(data, fieldInfo.ColumnSize);
}
else if (fieldInfo.DataType == "date")
{
return formatDate(data, forTSQLScript);
}
else if (fieldInfo.DataType == "datetimeoffset")
{
return formatDatetimeoffset(data, forTSQLScript);
}
else if (fieldInfo.DataType == "datetime2")
{
return formatDatetime2(data, forTSQLScript);
}
else if (fieldInfo.DataType == "time")
{
return formatTime(data, forTSQLScript);
}
else if (fieldInfo.DataType == "datetime")
{
return formatDateTime(data, forTSQLScript);
}
else if (fieldInfo.DataType == "smalldatetime")
{
return formatSmallDateTime(data, forTSQLScript);
}
else if (fieldInfo.DataType == "bit")
{
return formatBit(data);
}
else if (fieldInfo.DataType == "varbinary" || fieldInfo.DataType == "image")
{
return formatVarbinary(data);
}
else if (fieldInfo.DataType == "uniqueidentifier")
{
return formatGuid(data, forTSQLScript);
}
else if (fieldInfo.DataType == "sql_variant")
{
return getDataAsSql_variantFormat(data, forTSQLScript);
}
else if (fieldInfo.DataType.EndsWith("hierarchyid"))
{
return formatHierarchyId(data);
}
else if (fieldInfo.DataType.EndsWith("geography"))
{
return formatGeography(data, forTSQLScript);
}
else if (fieldInfo.DataType.EndsWith("geometry"))
{
return formatGeometry(data, forTSQLScript);
}
//shouldn't get here. In-place for future data type compatibility.
if (data is string)
{
return String.Format("{0}{1}{2}",
(forTSQLScript ? "N'" : ""),
((string)data).Replace("'", "''"),
(forTSQLScript ? "'" : ""));
}
return String.Format("{0}{1}{2}",
(forTSQLScript ? "N'" : ""),
data.ToString(),
(forTSQLScript ? "'" : ""));
}
public static string FieldNameOrDefault(SQLColumn column, int fieldIndex)
{
if (String.IsNullOrEmpty(column.ColumnName))
{
return "anonymousColumn" + (fieldIndex + 1).ToString();
}
return EscapeObjectName(column.ColumnName);
}
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;
}
}
}
//todo: may need some refactoring :-)
public static string valueAsTSQLLiteral(object data, SQLColumn fieldInfo, bool forTSQLScript = true)
{
if (data == null || data is DBNull)
{
return("NULL");
}
if (data is string)
{
if ((data as string).Contains('\0'))
{
return(formatVarbinary(data));
}
}
if (fieldInfo.DataType == "char")
{
return(formatChar(data, forTSQLScript));
}
else if (fieldInfo.DataType == "varchar" || fieldInfo.DataType == "text")
{
return(formatVarchar(data, forTSQLScript));
}
else if (fieldInfo.DataType == "nchar")
{
return(formatNchar(data, forTSQLScript));
}
else if (fieldInfo.DataType == "nvarchar" || fieldInfo.DataType == "ntext" || fieldInfo.DataType == "xml")
{
return(formatNvarchar(data, forTSQLScript));
}
else if (fieldInfo.DataType == "bigint" || fieldInfo.DataType == "numeric" || fieldInfo.DataType == "smallint" || fieldInfo.DataType == "decimal" || fieldInfo.DataType == "smallmoney" ||
fieldInfo.DataType == "int" || fieldInfo.DataType == "tinyint" || fieldInfo.DataType == "float" || fieldInfo.DataType == "real" || fieldInfo.DataType == "money")
{
return(getDataAsAppropriateNumericFormat(data));
}
else if (fieldInfo.DataType == "binary" || fieldInfo.DataType == "rowversion" || fieldInfo.DataType == "timestamp")
{
return(formatBinary(data, fieldInfo.ColumnSize));
}
else if (fieldInfo.DataType == "date")
{
return(formatDate(data, forTSQLScript));
}
else if (fieldInfo.DataType == "datetimeoffset")
{
return(formatDatetimeoffset(data, forTSQLScript));
}
else if (fieldInfo.DataType == "datetime2")
{
return(formatDatetime2(data, forTSQLScript));
}
else if (fieldInfo.DataType == "time")
{
return(formatTime(data, forTSQLScript));
}
else if (fieldInfo.DataType == "datetime")
{
return(formatDateTime(data, forTSQLScript));
}
else if (fieldInfo.DataType == "smalldatetime")
{
return(formatSmallDateTime(data, forTSQLScript));
}
else if (fieldInfo.DataType == "bit")
{
return(formatBit(data));
}
else if (fieldInfo.DataType == "varbinary" || fieldInfo.DataType == "image")
{
return(formatVarbinary(data));
}
else if (fieldInfo.DataType == "uniqueidentifier")
{
return(formatGuid(data, forTSQLScript));
}
else if (fieldInfo.DataType == "sql_variant")
{
return(getDataAsSql_variantFormat(data, forTSQLScript));
}
else if (fieldInfo.DataType.EndsWith("hierarchyid"))
{
return(formatHierarchyId(data));
}
else if (fieldInfo.DataType.EndsWith("geography"))
{
return(formatGeography(data, forTSQLScript));
}
else if (fieldInfo.DataType.EndsWith("geometry"))
{
return(formatGeometry(data, forTSQLScript));
}
//shouldn't get here. In-place for future data type compatibility.
if (data is string)
{
return(String.Format("{0}{1}{2}",
(forTSQLScript ? "N'" : ""),
((string)data).Replace("'", "''"),
(forTSQLScript ? "'" : "")));
}
return(String.Format("{0}{1}{2}",
(forTSQLScript ? "N'" : ""),
data.ToString(),
(forTSQLScript ? "'" : "")));
}
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];
SQLColumn fieldInfo = result.schema[colIndex];
if (fieldData == null || fieldData is DBNull)
{
srp.WriteToStream("<Cell/>");
}
else if (fieldInfo.DataType == "bigint" || fieldInfo.DataType == "numeric" || fieldInfo.DataType == "smallint" || fieldInfo.DataType == "decimal" || fieldInfo.DataType == "smallmoney" ||
fieldInfo.DataType == "int" || fieldInfo.DataType == "tinyint" || fieldInfo.DataType == "float" || fieldInfo.DataType == "real" || fieldInfo.DataType == "money" || fieldInfo.DataType == "bit")
{
srp.WriteToStream(String.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false))));
}
else if (fieldInfo.DataType == "date" || fieldInfo.DataType == "datetime2" || fieldInfo.DataType == "datetime" ||
fieldInfo.DataType == "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 (fieldInfo.DataType == "binary" || fieldInfo.DataType == "rowversion" || fieldInfo.DataType == "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 (fieldInfo.DataType == "varbinary" || fieldInfo.DataType == "image")
{
srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.formatVarbinary(fieldData))));
}
else if (fieldInfo.DataType == "time")
{
srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s65\"><Data ss:Type=\"DateTime\">1899-12-31T{0}:{1}:{2}.{3}</Data></Cell>\r\n",
((TimeSpan)fieldData).Hours.ToString("00"),
((TimeSpan)fieldData).Minutes.ToString("00"),
((TimeSpan)fieldData).Seconds.ToString("00"),
((TimeSpan)fieldData).Milliseconds.ToString("000")
));
}
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");
}
public static string RenderCSharpProperty(SQLColumn s, string name)
{
if (CSharpRenderer.SqlDataTypeToCSharp.ContainsKey(s.DataType))
{
return String.Format(propertyBoilerplate, CSharpRenderer.SqlDataTypeToCSharp[s.DataType], name);
}
else
{
return String.Format(propertyBoilerplate, "object", name);
}
}