Reformatting your SQL Server code with C#

In the last post, I've shown how you can use the Microsoft libraries to parse your SQL Server code in C#. You can use the same parser to reformat your code, but that would be a lot of work: parse the code, check all structures and reformat the code according to your needs.

But there is an easier way: the SqlScriptGenerator class. This class makes a breeze to get your unformatted code and reformat it according to your options. In this article, we will develop a WPF program that takes a SQL Code snippet typed in TextBox and reformats it using the options you give.

Create a WPF program and name it SqlReformatter. In the solution explorer, add the NuGet package Microsoft.SqlServer.TransactSql.ScriptDom. In the XAML file for the Main page, add this code:

<Window x:Class="SqlReformatter.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        mc:Ignorable="d"
        Title="MainWindow" Height="700" Width="1024">
    <Grid>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="200"/>
            <ColumnDefinition Width="*"/>
        </Grid.ColumnDefinitions>
        <StackPanel Grid.Column="0" Margin="5" x:Name="stackChecks">
            <CheckBox Content="AlignClauseBodies" Click="OptionClick"/>
            <CheckBox Content="AlignColumnDefinitionFields" Click="OptionClick"/>
            <CheckBox Content="AlignSetClauseItem" Click="OptionClick"/>
            <CheckBox Content="AsKeywordOnOwnLine" Click="OptionClick"/>
            <CheckBox Content="IncludeSemicolons" Click="OptionClick"/>
            <CheckBox Content="IndentSetClause" Click="OptionClick"/>
            <CheckBox Content="IndentViewBody" Click="OptionClick"/>
            <CheckBox Content="MultilineInsertSourcesList" Click="OptionClick"/>
            <CheckBox Content="MultilineInsertTargetsList" Click="OptionClick"/>
            <CheckBox Content="MultilineSelectElementsList" Click="OptionClick"/>
            <CheckBox Content="MultilineSetClauseItems" Click="OptionClick"/>
            <CheckBox Content="MultilineViewColumnsList" Click="OptionClick"/>
            <CheckBox Content="MultilineWherePredicatesList" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeCloseParenthesisInMultilineList" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeFromClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeGroupByClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeHavingClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeJoinClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeOffsetClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeOpenParenthesisInMultilineList" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeOrderByClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeOutputClause" Click="OptionClick"/>
            <CheckBox Content="NewLineBeforeWhereClause" Click="OptionClick"/>
            <StackPanel Margin="0,5">
                <TextBlock Text="KeywordCasing"/>
                <ComboBox SelectionChanged="CaseChanged" x:Name="cbxCase">
                    <ComboBoxItem>LowerCase</ComboBoxItem>
                    <ComboBoxItem>UpperCase</ComboBoxItem>
                    <ComboBoxItem>PascalCase</ComboBoxItem>
                </ComboBox>
            </StackPanel>
            <StackPanel Orientation="Horizontal" Margin="0,5">
                <TextBlock Text="IndentationSize"/>
                <TextBox Width="60" Margin="5,0" TextChanged="IndentChanged" x:Name="txtIdent"/>
            </StackPanel>
        </StackPanel>
        <Button VerticalAlignment="Bottom" HorizontalAlignment="Right" Grid.Column="0"
                Margin="5" Content="Reformat" Click="ReformatSqlClick"/>
        <Grid Grid.Column="1">
            <Grid.RowDefinitions>
                <RowDefinition Height="*"/>
                <RowDefinition Height="*"/>
            </Grid.RowDefinitions>
            <TextBox x:Name="SourceBox" Margin="5" Grid.Row="0"/>
            <TextBox x:Name="DestBox" Margin="5" Grid.Row="1" IsReadOnly="True"/>
        </Grid>
    </Grid>
</Window>

As you can see, we are adding at the right a set of Checkboxes, each one with one property of the SqlScriptOptions class. When we check each box, we will change the reformatting options. To do that, we must use this code:

private void OptionClick(object sender, RoutedEventArgs e)
{
    if (sender is CheckBox checkBox)
    {
        var option = checkBox.Content.ToString();
        PropertyInfo pinfo = typeof(SqlScriptGeneratorOptions).GetProperty(option);
        pinfo?.SetValue(_options, checkBox.IsChecked == true);
    }
}

In this case, we are using reflection to get the property corresponding to the clicked Checkbox and we set its value according to the IsChecked value. We must also add two event handlers, for the case combobox and for the indent textbox:

private void CaseChanged(object sender, SelectionChangedEventArgs e)
{
    var selectedCase = (sender as ComboBox)?.SelectedIndex;
    if (selectedCase != null)
        _options.KeywordCasing = (KeywordCasing) selectedCase;
}

private void IndentChanged(object sender, TextChangedEventArgs e)
{
    if (int.TryParse((sender as TextBox)?.Text, out int size))
        _options.IndentationSize = size;
}

Once we have this set, we need to add the code to the click of the Reformat button:

private void ReformatSqlClick(object sender, RoutedEventArgs e)
{
    var sqlSrc = SourceBox.Text;
    if (string.IsNullOrWhiteSpace(sqlSrc))
        return;
    var processed = ParseSql(sqlSrc);
    if (processed.errors.Any())
    {
        var sb = new StringBuilder("Errors found:");
        foreach (var error in processed.errors)
        {
            sb.AppendLine($"     Line: {error.Line}  Col: {error.Column}: {error.Message}");
        }
    }
    else
    {
        var scriptGenerator = new Sql150ScriptGenerator(_options);
        scriptGenerator.GenerateScript(processed.sqlTree, out string sqlDst);
        DestBox.Text = sqlDst;
    }
}

We parse the code in the source box, and if there are any errors, we show them in the destination box. If there are no errors, the code is reformatted according to the selected options. The ParseSql method is similar to the one shown in the last article:

private static (TSqlFragment sqlTree, IList<ParseError> errors) ParseSql(string procText)
{
    var parser = new TSql150Parser(true);
    using (var textReader = new StringReader(procText))
    {
        var sqlTree = parser.Parse(textReader, out var errors);

        return (sqlTree, errors);
    }
}

Now, we only need to initialize the UI in the beginning, so all the options are up-to-date with the SqlScriptGeneratorOptions instance:

public MainWindow()
{
    InitializeComponent();
    _options = new SqlScriptGeneratorOptions();
    txtIdent.Text = _options.IndentationSize.ToString();
    cbxCase.SelectedIndex = (int) _options.KeywordCasing;
    foreach (var child in stackChecks.Children)
    {
        if (child is CheckBox check)
        {
            var checkContent = check.Content.ToString();
            PropertyInfo pinfo = typeof(SqlScriptGeneratorOptions).GetProperty(checkContent);
            check.IsChecked = (bool?)pinfo?.GetValue(_options) == true;
        }

    }
}

This code also uses reflection to get the property values and fill the data in the boxes. When you run this program, you have something like this:

You can change any options and click the Reformat button and the code will be reformatted accordingly.

Conclusions

As you can see, with very little code you can create a SQL Reformatter to reformat your code and make sure that it agrees with your standards. This formatter has many options and can be used also to parse Sql source code.

All the source code for this article is at https://github.com/bsonnino/SqlReformatter

Conclusions

Leave a Reply Cancel reply