Convert SQL Type to .NET Type

SqlUtilities.GetDotnetType.cs

using Microsoft.SqlServer.TransactSql.ScriptDom;

public static class SqlUtilities
{
    public static string GetDotnetType(this SqlDataTypeOption sqlDataType, bool isNullable = false)
    {
        if (IsUnsupportedType())
        {
            return string.Empty;
        }

        var dotnetType = typeof(string);
        switch (sqlDataType)
        {
            case SqlDataTypeOption.BigInt: dotnetType = typeof(long); break;
            case SqlDataTypeOption.Binary:
            case SqlDataTypeOption.Image:
            case SqlDataTypeOption.VarBinary: dotnetType = typeof(byte[]); break;
            case SqlDataTypeOption.Bit: dotnetType = typeof(bool); break;
            case SqlDataTypeOption.Char: dotnetType = typeof(char); break;
            case SqlDataTypeOption.Time: dotnetType = typeof(TimeOnly); break;
            case SqlDataTypeOption.Date: dotnetType = typeof(DateOnly); break;
            case SqlDataTypeOption.DateTime:
            case SqlDataTypeOption.SmallDateTime: dotnetType = typeof(DateTime); break;
            case SqlDataTypeOption.DateTime2:
            case SqlDataTypeOption.DateTimeOffset: dotnetType = typeof(DateTimeOffset); break;
            case SqlDataTypeOption.Decimal:
            case SqlDataTypeOption.Money:
            case SqlDataTypeOption.Numeric: dotnetType = typeof(decimal); break;
            case SqlDataTypeOption.Float: dotnetType = typeof(double); break;
            case SqlDataTypeOption.Int: dotnetType = typeof(int); break;
            case SqlDataTypeOption.NChar:
            case SqlDataTypeOption.NVarChar:
            case SqlDataTypeOption.Text:
            case SqlDataTypeOption.VarChar: dotnetType = typeof(string); break;
            case SqlDataTypeOption.Real: dotnetType = typeof(float); break;
            case SqlDataTypeOption.SmallInt: dotnetType = typeof(short); break;
            case SqlDataTypeOption.TinyInt: dotnetType = typeof(byte); break;
            case SqlDataTypeOption.UniqueIdentifier: dotnetType = typeof(Guid); break;
        }

        return dotnetType.Name + (isNullable ? "?" : string.Empty);

        bool IsUnsupportedType()
        {
            var types = new[] 
            {
                SqlDataTypeOption.Sql_Variant,
                SqlDataTypeOption.Timestamp,
                SqlDataTypeOption.Rowversion,
            };
            return types.Contains(sqlDataType);
        }
    }

    public static string GetDotnetType(this string sqlDataType, bool isNullable = false)
    {
        RemoveAnyPercision();

        if (!Enum.TryParse(sqlDataType, true, out SqlDataTypeOption dataTypeOption))
        {
            return string.Empty;
        }

        return dataTypeOption.GetDotnetType(isNullable);

        void RemoveAnyPercision()
        {
            int index = sqlDataType.IndexOf("(");
            if (index != -1)
            {
                sqlDataType = sqlDataType.Substring(0, index).Trim();
            }
        }
    }
}

RemoveAnyPercision()? - Precision?

1. I'd return null instead of an empty string when the type is unsupported.
2. In RemoveAnyRecision, use int index = sqlDataType.IndexOf('(', StringComparison.Ordinal); instead. This won't allocate a string and use binary comparison when searching.
3. I'd implement IsUnsupportedType like this:

bool IsUnsupportedType()
{
    return sqlDataType is SqlDataTypeOption.Sql_Variant
        or SqlDataTypeOption.Timestamp
        or SqlDataTypeOption.Rowversion;
}

This won't allocate and will be much faster.

OK so I mentioned on Twitter that I would take a pass at it. Here is a summary of the changes I made:

1. I renamed the class to me more explicitt about its purpose.
2. I've simplified most functions to expression-bodied functions, as they don't have a terribly complex codepath.
3. The primary method has been changed to a switch expression and dramatically simplified to:
   • Eliminate the need to check separately for unsupported types
   • Eliminate the need for the dotnetType variable and any subsequent multi-allocations and/or assignments
   • Eliminate switch case failovers, which can lead to unintended results
4. Internal functions have been eliminated, as they are more difficult to unit test & achieve proper code coverage.
5. I've renamed GetDotNetType to GetDotNetTypeString, since the function does not return 'Type`.
6. I've modified RemoveAnyPercision by:
   • renaming it to RemoveSqlPrecision to be more explicit about what it does
   • incorporated @CollinAlpert's feedback to reduce allocations and speed up the results
   • simplified the return into a ternary expression
7. I've added a function to format the .NET type to a string and optimized it for speed and memory allocations.
8. I've moved simple overloads to the top of the file, so they "fall through" down to the "base" method".
9. I've changed the string extension for GetDotNetTypeString to reduce allocations and simplified the return to a ternary expression.
10. I've separated the public functions from the private ones and put each section in alphabetical order to make it easier to navigate through the code file.
11. I've fully-documented the file so that any other developers using the code understand what it does and how it works.

Here is the code.

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System;

/// <summary>
/// A set of Extension Methods that primarily deal with <see cref="SqlDataTypeOption"/>.
/// </summary>
public static class SqlDataTypeOptionExtensions
{

    #region Public Methods

    /// <summary>
    /// Returns a string describing the .NET type mapping for a given <see cref="SqlDataTypeOption"/>. 
    /// </summary>
    /// <param name="sqlDataType">A string representing the data type from the SQL source.</param>
    /// <param name="isNullable">Specifies whether or not the result should be nullable.</param>
    /// <returns>
    /// A <see cref="string"/> representing the .NET type corresponding to the given <see cref="SqlDataTypeOption"/>,
    /// with any appropriate nullability annotations.
    /// </returns>
    public static string GetDotNetTypeString(this string sqlDataType, bool isNullable = false) =>
        // RWM: If we can parse the SqlType string with the precision removed, return the .NET type, otherwise return nothing.
        Enum.TryParse(RemoveSqlPrecision(sqlDataType), true, out SqlDataTypeOption dataTypeOption)
            ? GetDotNetTypeString(dataTypeOption, isNullable)
            : string.Empty;

    /// <summary>
    /// Returns a string describing the .NET type mapping for a given <see cref="SqlDataTypeOption"/>. 
    /// </summary>
    /// <param name="sqlDataType">A <see cref="SqlDataTypeOption"/> representing the data type from the SQL source.</param>
    /// <param name="isNullable">Specifies whether or not the result should be nullable.</param>
    /// <returns>
    /// A <see cref="string"/> representing the .NET type corresponding to the given <see cref="SqlDataTypeOption"/>,
    /// with any appropriate nullability annotations.
    /// </returns>
    public static string GetDotNetTypeString(this SqlDataTypeOption sqlDataType, bool isNullable = false) =>
        sqlDataType switch
        {
            // RWM: This list REALLY should be sorted in alphabetical order by the SqlDataTypeOption enum.
            SqlDataTypeOption.BigInt            => FormatDotNetTypeString(typeof(long),            isNullable),
            SqlDataTypeOption.Binary            => FormatDotNetTypeString(typeof(byte[]),          isNullable),
            SqlDataTypeOption.Image             => FormatDotNetTypeString(typeof(byte[]),          isNullable),
            SqlDataTypeOption.VarBinary         => FormatDotNetTypeString(typeof(byte[]),          isNullable),
            SqlDataTypeOption.Bit               => FormatDotNetTypeString(typeof(bool),            isNullable),
            SqlDataTypeOption.Char              => FormatDotNetTypeString(typeof(char),            isNullable),
            SqlDataTypeOption.Time              => FormatDotNetTypeString(typeof(TimeOnly),        isNullable),
            SqlDataTypeOption.Date              => FormatDotNetTypeString(typeof(DateOnly),        isNullable),
            SqlDataTypeOption.DateTime          => FormatDotNetTypeString(typeof(DateTime),        isNullable),
            SqlDataTypeOption.SmallDateTime     => FormatDotNetTypeString(typeof(DateTime),        isNullable),
            SqlDataTypeOption.DateTime2         => FormatDotNetTypeString(typeof(DateTimeOffset),  isNullable),
            SqlDataTypeOption.DateTimeOffset    => FormatDotNetTypeString(typeof(DateTimeOffset),  isNullable),
            SqlDataTypeOption.Decimal           => FormatDotNetTypeString(typeof(decimal),         isNullable),
            SqlDataTypeOption.Money             => FormatDotNetTypeString(typeof(decimal),         isNullable),
            SqlDataTypeOption.Numeric           => FormatDotNetTypeString(typeof(decimal),         isNullable),
            SqlDataTypeOption.Float             => FormatDotNetTypeString(typeof(double),          isNullable),
            SqlDataTypeOption.Int               => FormatDotNetTypeString(typeof(int),             isNullable),
            SqlDataTypeOption.NChar             => FormatDotNetTypeString(typeof(string),          isNullable),
            SqlDataTypeOption.NVarChar          => FormatDotNetTypeString(typeof(string),          isNullable),
            SqlDataTypeOption.Text              => FormatDotNetTypeString(typeof(string),          isNullable),
            SqlDataTypeOption.VarChar           => FormatDotNetTypeString(typeof(string),          isNullable),
            SqlDataTypeOption.Real              => FormatDotNetTypeString(typeof(float),           isNullable),
            SqlDataTypeOption.SmallInt          => FormatDotNetTypeString(typeof(short),           isNullable),
            SqlDataTypeOption.TinyInt           => FormatDotNetTypeString(typeof(byte),            isNullable),
            SqlDataTypeOption.UniqueIdentifier  => FormatDotNetTypeString(typeof(Guid),            isNullable),
            
            // RWM: If it's not explicitly mentioned, it's unsupported.
            _ => string.Empty
        };

    #endregion

    #region Private Methods

    /// <summary>
    /// Returns a string representation of a .NET type, including nullability annotations. 
    /// </summary>
    /// <param name="type">The <see cref="Type"/> to format.</param>
    /// <param name="isNullable">Specifies whether or not the result should be nullable.</param>
    /// <returns>A string containing the simple name of the .NET type, followed by a "?" if the type is nullable.</returns>
    /// <remarks>
    /// Explicit interpolation chosen because it is the fastest. 
    /// See https://stackoverflow.com/questions/32342392/string-interpolation-vs-string-format
    /// </remarks>
    private static string FormatDotNetTypeString(Type type, bool isNullable = false) =>
        $"{type.Name}{(isNullable ? "?" : string.Empty)}";

    /// <summary>
    /// Removes precision information from the sql type string.
    /// </summary>
    /// /// <param name="sqlDataType">A <see cref="string"/> containging the SQL data type for the column.</param>
    /// <returns>A string representing the SQL data type, but without precision details.</returns>
    private static string RemoveSqlPrecision(string sqlDataType)
    {
        int index = sqlDataType.IndexOf("(", StringComparison.Ordinal);
        return index >= 0 ? sqlDataType.Substring(0, index).Trim() : sqlDataType;
    }

    #endregion

}

I hope that helps!