SQL Query Formatter

SqlQueryFormatter.cs

using System;
using System.Data;
using System.IO;

public static class SqlQueryFormatter
{
    public static string FormatCommand(this IDbCommand command)
    {
        if (command is null) throw new ArgumentNullException(nameof(command));
        
        using (var writer = new StringWriter())
        {
            WriteCommand(writer, command);
            return writer.ToString();
        }
    }
    
    public static void WriteCommand(this TextWriter writer, IDbCommand command)
    {
        if (writer is null) throw new ArgumentNullException(nameof(writer));
        if (command is null) throw new ArgumentNullException(nameof(command));
        
        if (command.Parameters.Count != 0)
        {
            foreach (IDbDataParameter parameter in command.Parameters)
            {
                WriteParameter(writer, parameter);
            }
            
            writer.WriteLine();
        }
        
        WriteCommandText(writer, command);
    }
    
    private static void WriteCommandText(TextWriter writer, IDbCommand command)
    {
        if (command.CommandType != CommandType.StoredProcedure)
        {
            writer.WriteLine(command.CommandText);
            return;
        }
        
        writer.Write("EXEC ");
        writer.Write(command.CommandText);
        
        string separator = " ";
        foreach (IDbDataParameter parameter in command.Parameters)
        {
            if (parameter.Direction == ParameterDirection.ReturnValue) continue;
            
            writer.Write(separator);
            separator = ", ";
            
            if (!parameter.ParameterName.StartsWith("@")) writer.Write("@");
            writer.Write(parameter.ParameterName);
            writer.Write(" = ");
            if (!parameter.ParameterName.StartsWith("@")) writer.Write("@");
            writer.Write(parameter.ParameterName);
            
            switch (parameter.Direction)
            {
                case ParameterDirection.Output:
                case ParameterDirection.InputOutput:
                {
                    writer.Write(" OUTPUT");
                    break;
                }
            }
        }
        
        writer.WriteLine(";");
    }
    
    private static void WriteParameter(TextWriter writer, IDbDataParameter parameter)
    {
        writer.Write("DECLARE ");
        if (!parameter.ParameterName.StartsWith("@")) writer.Write('@');
        writer.Write(parameter.ParameterName);
        writer.Write(" As ");
        writer.Write(ConvertParameterType(parameter));
        if (parameter.Value != null && parameter.Direction != ParameterDirection.ReturnValue)
        {
            writer.Write(" = ");
            writer.Write(ConvertParameterValue(parameter));
        }
        writer.WriteLine(";");
    }
    
    private static string ConvertParameterType(IDbDataParameter parameter)
    {
        switch (parameter.DbType)
        {
            case DbType.AnsiString:
            {
                return parameter.Size < 0 ? "varchar(max)" : $"varchar({parameter.Size})";
            }
            case DbType.String:
            {
                return parameter.Size < 0 ? "nvarchar(max)" : $"nvarchar({parameter.Size})";
            }
            case DbType.AnsiStringFixedLength:
            {
                return parameter.Size < 0 ? "char(max)" : $"char({parameter.Size})";
            }
            case DbType.StringFixedLength:
            {
                return parameter.Size < 0 ? "nchar(max)" : $"nchar({parameter.Size})";
            }
            case DbType.Byte:
            case DbType.SByte:
            {
                return "tinyint";
            }
            case DbType.Int16:
            case DbType.UInt16:
            {
                return "smallint";
            }
            case DbType.Int32:
            case DbType.UInt32:
            {
                return "int";
            }
            case DbType.Int64:
            case DbType.UInt64:
            {
                return "bigint";
            }
            case DbType.Date:
            {
                return "date";
            }
            case DbType.Time:
            {
                return "time";
            }
            case DbType.DateTime:
            {
                return "datetime";
            }
            case DbType.DateTime2:
            {
                return "datetime2";
            }
            case DbType.DateTimeOffset:
            {
                return "datetimeoffset";
            }
            case DbType.Guid:
            {
                return "uniqueidentifier";
            }
            case DbType.Boolean:
            {
                return "bit";
            }
            case DbType.Double:
            {
                return "float";
            }
            case DbType.Single:
            {
                return "real";
            }
            case DbType.Currency:
            {
                return "money";
            }
            case DbType.Decimal:
            {
                return $"decimal({parameter.Scale}, {parameter.Precision})";
            }
            case DbType.Xml:
            {
                return "xml";
            }
            case DbType.Binary:
            {
                return parameter.Size < 0 ? "varbinary(max)" : $"varbinary({parameter.Size})";
            }
            default:
            {
                return "sql_variant";
            }
        }
    }
    
    private static string ConvertParameterValue(IDbDataParameter parameter)
    {
        if (Convert.IsDBNull(parameter.Value)) return "Null";
        
        switch (parameter.DbType)
        {
            case DbType.Boolean:
            {
                if (parameter.Value is bool value) return value ? "1" : "0";
                break;
            }
            case DbType.String:
            case DbType.StringFixedLength:
            case DbType.Xml:
            {
                if (parameter.Value is string value) return "N'" + value.Replace("'", "''") + "'";
                break;
            }
            case DbType.AnsiString:
            case DbType.AnsiStringFixedLength:
            {
                if (parameter.Value is string value) return "'" + value.Replace("'", "''") + "'";
                break;
            }
            case DbType.Guid:
            {
                if (parameter.Value is Guid value) return $"'{value:D}'";
                break;
            }
            case DbType.Date:
            {
                if (parameter.Value is DateTime value) return $"'{value:yyyyMMdd}'";
                break;
            }
            case DbType.DateTime:
            case DbType.DateTime2:
            {
                if (parameter.Value is DateTime value) return $"'{value:yyyyMMdd HH:mm:ss.FFFFFFF}'";
                break;
            }
            case DbType.DateTimeOffset:
            {
                if (parameter.Value is DateTimeOffset dto) return $"'{dto:yyyyMMdd HH:mm:ss.FFFFFFF zzz}'";
                if (parameter.Value is DateTime value) return $"'{value:yyyyMMdd HH:mm:ss.FFFFFFF}'";
                break;
            }
            case DbType.Time:
            {
                if (parameter.Value is TimeSpan value) return $"'{value:hh\\:mm\\:ss\\.fFFFFFF}'";
                break;
            }
            case DbType.Binary:
            {
                if (parameter.Value is byte[] value) return "0x" + string.Concat(Array.ConvertAll(value, b => b.ToString("X2")));
                break;
            }
            default:
            {
                return Convert.ToString(parameter.Value);
            }
        }
        
        return "Null";
    }
}

Excellent!