Example #1
0
 public static string FieldNameOrDefault(SQLColumn column, int fieldIndex)
 {
     if (String.IsNullOrEmpty(column.ColumnName))
     {
         return("anonymousColumn" + (fieldIndex + 1).ToString());
     }
     return(EscapeObjectName(column.ColumnName));
 }
Example #2
0
 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));
     }
 }
Example #3
0
 private static string QuestionMarkOrNot(SQLColumn s)
 {
     if (!s.AllowNulls)
     {
         return("");
     }
     if (!SqlDataTypeToCSharp.TryGetValue(s.DataType, out var dataType) || dataType == "string")
     {
         return("");
     }
     return("?");
 }
Example #4
0
 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));
     }
 }
Example #5
0
        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);
        }
Example #6
0
        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));
        }
Example #7
0
        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("");
        }
Example #8
0
 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;
 }
Example #9
0
        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);
        }
Example #10
0
        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 "";
        }
Example #11
0
        //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 ? "'" : ""));
        }
Example #12
0
 public static string FieldNameOrDefault(SQLColumn column, int fieldIndex)
 {
     if (String.IsNullOrEmpty(column.ColumnName))
     {
         return "anonymousColumn" + (fieldIndex + 1).ToString();
     }
     return EscapeObjectName(column.ColumnName);
 }
Example #13
0
        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;
                }
            }
        }
Example #14
0
        //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 ? "'" : "")));
        }
Example #15
0
        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");
        }
Example #16
0
 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);
     }
 }