How to build a SQL Connection Dialog in UWP

This article explains how to build a dialog in UWP that sets up a connection from your app to any SQL Server database on your network or in the cloud. Since the Windows Fall Creators Update we can -finally- directly connect to SQL Server from UWP. In that SDK .net core was extended with a large portion of the object model of ye olde ADO.NET, hosted in the System.Data.SqlClient namespace. In this article we’ll use some of these to build a connection dialog to SQL Server, with the following features:

  • You can type a server name or select a recently use one.
  • You can select the security mode.
  • You can enter your credentials when SQL Authentication is used.
  • You can select the database from a list.
  • You have direct access to the connection string.
  • You can test the connection.

When the dialog closes, it will serve you with a validated connection string (unless you cancelled the operation).

Here’s how the dialog looks like in its default mode, with different input fields:

SqlDialogDefaultInput

And here’s how it looks like in direct access mode, where you can edit the connection string:

SqlDialogDirectInput

Basic structure

The SQL Connection Dialog is based on a ContentDialog. Under the title (which has a nice SVG Icon – thanks to TheNounProject) there’s a ProgressBar from which we toggle the IsIndeterminate property to indicate whether the control is busy – i.e. establishing a connection or fetching the database names. The first row in the input form is a custom editable combobox for the server name. Then come some regular input controls –ComboBox, TextBox, PasswordBox– for the security parameters. At the bottom of the input zone there’s a ComboBox that hosts the names of the databases. All text fields on the input form have their IsSpellCheckEnabled set to false – we assume you don’t want red squigglies under server names or user accounts.

All input fields are bound to properties of a SqlConnectionBuilder. As an example, here’s the code for the UserId field:

private SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
private string userId;

/// <summary>
/// Gets or sets the SQL user identifier.
/// </summary>
public string UserId
{
    get { return userId; }
    set
    {
        userId = value;
        builder.UserID = userId;
        OnPropertyChanged();
    }
}

And its corresponding XAML:

<TextBox Text="{x:Bind UserId, Mode=TwoWay}"
         IsEnabled="{x:Bind SqlSecurity, Mode=OneWay}" />

The ellipsis button in the top left corner sets the control in direct input mode, showing a large multi-row textbox to manipulate the connection string.

A ContentDialog can have maximum three buttons at the bottom, and we’re using all of these. The PrimaryButton allows to test the current connectionstring, the SecondaryButton returns a validated connectstring to the caller and closes the dialog, and the CloseButton … well … closes.

An Editable ComboBox

The name of the server to connect to can be chosen from a dropdown with recently used successful connections (stored in LocalSettings) or it can be typed in manually. Now UWP does not come with an editable ComboBox, so we needed to simulate one through an AutoSuggestBoxToggleButton combination. The toggle button opens the AutoSuggestBox’s dropdown. Here’s the XAML:

<AutoSuggestBox x:Name="SuggestBox"
                Style="{StaticResource AutoSuggestBoxNoSpellCheckingStyle}"
                Text="{x:Bind Server, Mode=TwoWay}"
                ItemsSource="{x:Bind MostRecentConnections}"
                VerticalAlignment="Stretch"
                IsSuggestionListOpen="{Binding IsChecked, ElementName=Toggle, Mode=TwoWay}"
                FontFamily="Segoe UI" />
<ToggleButton x:Name="Toggle"
                Grid.Column="1"
                VerticalAlignment="Stretch"
                Width="32"
                Background="Transparent"
                FontFamily="Segoe UI">
    <FontIcon Foreground="{ThemeResource ComboBoxDropDownGlyphForeground}"
                FontSize="12"
                FontFamily="Segoe MDL2 Assets"
                Glyph=""
                IsHitTestVisible="False" />
</ToggleButton>

A few classes from .NET SQL Server ecosystem were not ported to .net core. One of these is SqlDataSourceEnumerator, which allows to lookup SQL Server instances on the local network. So we can’t do this on UWP.

Maintaining the size

The UWP ContentDialog is quite stubborn when it comes to setting and resetting its size. It prefers to decide its height and width depending on its children and its host, and tends to ignore hard-coding values. We decided not to fight the dialog, and let it define its own size – based on the default look with the multiple input fields. But we don’t want the dialog to shrink when switching to direct input mode (with only one textbox). All the content is always in place so the dialog never feels the need to resize. We’re just playing with Opacity and Z-Order when switching the mode. A Grid has no ZIndex attached property for its content, so we reverse the Children collection instead. Here’s what happens when we switch from default mode to direct mode:

DefaultGrid.Opacity = 1;
DirectGrid.Opacity = 0;
Host.Children.Remove(DirectGrid);
Host.Children.Insert(0, DirectGrid);

Of course there’s similar code the other way around. It keeps the dialog the same size, and it freezes the mode switch button at its position, making it easy to rapidly check the connection string and come back.

Testing the connection

For testing the connection, we create a SqlConnection instance from the current connection string, and try to Open it:

using (SqlConnection con = new SqlConnection(builder.ConnectionString))
{
    await con.OpenAsync();
}

We’re updating the UI during the call (remember there’s a ProgressBar), so it’s a good idea to use OpenAsync here. The Using statement will make sure that we close the connection automatically.

When the connection is successful, we’ll show it to the user, with a MessageDialog:

SqlDialogTest

And when the connection is not successful, we display the error:

SqlDialogTestFail

Fetching the database list

The list of database names on a SQL Server Instance can only be fetched through a successful connection to the server. Knowing that every unsuccessful connection attempt waists at least a few seconds, we don’t want to try to connect on every property change. The list of databases is fetched in the combo box’s own DropDownOpened event:

<ComboBox x:Name="DatabaseComboBox"
          DropDownOpened="DatabaseComboBox_DropDownOpened"
          ItemsSource="{x:Bind Databases, Mode=OneWay}"
          SelectionChanged="Database_SelectionChanged" />

Here’s the code. We create a new SqlCommand on the open connection, execute it, and loop though the result with a SqlDataReader:

using (var connection = new SqlConnection(builder.ConnectionString))
{
    await connection.OpenAsync();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT [name] FROM sys.databases ORDER BY [name]";

        using (SqlDataReader reader = await command.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                databases.Add(reader.GetString(0));
            }
        }
    }
}

Again, we used the asynchronous versions of the operations (ExecureReaderAsync and ReadAsync) to keep the UI responsive.

Achievement unlocked: connected

When the user clicks the Connect button, we validate the current connection string in just the same way as for the Test button. If the connection is successful, we store the current server name of top of the most recent servers – the list that appears in the editable combobox on top. Then we assign the dialog’s ConnectionString property for the caller to grab. As mentioned, the SqlConnectionDialog does not return a connection, but a validated connection string:

try
{
    using (SqlConnection con = new SqlConnection(builder.ConnectionString))
    {
        await con.OpenAsync();
    }

    MostRecentConnection = Server;
    ConnectionString = builder.ConnectionString;
}

How to use it

Here’s how a client app uses the dialog. It opens the dialog, waits for the result, checks whether the user cancelled or not, and finally grabs the connection string:

var dialog = new ConnectionDialog();
var result = await dialog.ShowAsync();

// User Cancelled
if (result == ContentDialogResult.None)
{
    return;
}

var connectionString = dialog.ConnectionString;

The sample client app uses the provided connection string to fetch the list of tables from the select database with their number of rows:

SqlDialogResult

The sample app with the SqlConnectionDialog lives here on GitHub.

Enjoy!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s