Monthly Archives: May 2018

Displaying Dynamic SQL Results in a UWP DataGrid

In this article we’ll show how you can display the result of any query against a SQL Server database directly in a UWP DataGrid. The grid control that we use is the new Windows Community Toolkit DataGrid. The companion sample app hosts 3 different TSQL queries (feel free to add your own), but only one instance of the DataGrid. Here’s how it looks like:

Grid_1

We’ll show you how to

  • connect to a SQL Server database from UWP,
  • read a dynamic TSQL statement into a DataTable,
  • modify the content of the DataTable progammatically – if needed,
  • bind the DataTable to a DataGrid, and
  • sort the grid on clicking a header column.

This type of dynamic data binding and visualization is particularly useful in the parts of an app that deal with administration, diagnostics, monitoring, or trouble shooting – where you don’t necessarily know upfront which queries to launch and don’t have the possibility (or resources, or priority) to define a full entity model and/or specialized UI controls.

Get that DataGrid

The DataGrid XAML Control is a port from the popular Silverlight control with the same name. Most -but not all- of the functionality has been migrated. This article does NOT focus on its full feature set, but only on dynamic data binding. Please check the official documentation and the source code of a complete sample app for more info. Its documentation is not yet fully uploaded, so some of the hyperlinks in this article will point to the (2011!) Silverlight version.

The DataGrid Control is distributed as a NuGet package within Windows Community Toolkit, currently as a prerelease :

Grid_NuGet

Here’s how the data grid is defined in the sample app. With no columns specified and AutoGenerateColumns to false it is ready for dynamic data binding:

<controls:DataGrid x:Name="ResultsGrid"
                    RowDetailsTemplate="{StaticResource DetailsTemplate}"
                    RowDetailsVisibilityMode="VisibleWhenSelected"
                    CanUserSortColumns="True"
                    Sorting="ResultsGrid_Sorting"
                    BorderThickness="2"
                    BorderBrush="DarkSlateGray"
                    AlternatingRowBackground="BlanchedAlmond"
                    GridLinesVisibility="All"
                    AutoGenerateColumns="False"
                    SelectionMode="Single" />

So let’s create some tabular data.

Populating a DataTable

DataTable is one of the core classes of the ADO.NET library. It represents an in-memory, database-agnostic structure of rows and strongly typed columns. An easy way to populate a DataTable is calling Fill() against a SqlDataAdapter that executes a SqlCommand on a SqlConnection. Here’s how this looks like in the sample app:

using (var connection = new SqlConnection(connectionString))
{
    await connection.OpenAsync();
    var command = connection.CreateCommand();
    command.CommandText = query;
    dataTable = new DataTable();

    using (var dataAdapter = new SqlDataAdapter(command))
    {
        dataAdapter.Fill(dataTable);
    }
}

Defining the DataGrid structure

In WPF, it would suffice to set AutoGenerateColumns to true and then run the following to populate the DataGrid from the DataTable:

ResultsGrid.ItemsSource = dataTable;

[Actually just data binding would even do the trick.]

The Silverlight -and hence UWP- version of the control doesn’t allow this, so there’s a tiny bit more programming required.

We’ll create a list of DataGridTextColumn instances -one for each column in the DataTable- and assign their Header text and and index Binding:

for (int i = 0; i < table.Columns.Count; i++)
{
        grid.Columns.Add(new DataGridTextColumn()
        {
            Header = table.Columns[i].ColumnName,
            Binding = new Binding { Path = new PropertyPath("[" + i.ToString() + "]") }
        });
}

If you would want to rename columns here, or skip some, or add some new ones, then this would be the place to do these changes.

Populating the DataGrid

The DataGrid is now ready to accept content. This content comes from an ObservableCollection that we populate with the ItemArray of each row in the DataTable. The collection is then set as ItemsSource of the DataGrid:

var collection = new ObservableCollection<object>();
foreach (DataRow row in table.Rows)
{
    collection.Add(row.ItemArray);
}

grid.ItemsSource = collection;

Here’s another view on the result. Same grid, same code, different query:

Grid_2

Tweaking the content

In the sample app, we manipulated the content of one of the columns. Some of the queries contain the text of a TSQL query, a long string that may contain visual formatting with tabs, carriage returns and series of blanks. That’s good to display in the details template – which is what we do. For a regular column it makes more sense to truncate the content, and get rid of the carriage returns.

Fortunately it’s easy to iterate through the rows of a DataTable and update one or more of its columns). Here’s how we remove obsolete white space from a string (using a Split() with StringSplitOptions.RemoveEmptyEntries), and truncate the SQL statements:

if (table.Columns.Contains("SQL Statement"))
{
    var column = table.Columns["SQL Statement"];

    foreach (DataRow row in table.Rows)
    {
        string sqlStatement = ((row[column] as string) ?? string.Empty).Trim();
        row[column] = string.Join(' ', sqlStatement.Split(default(string[]), StringSplitOptions.RemoveEmptyEntries)).Substring(0, 80) + "...";
    }

    table.AcceptChanges();
}

The call to AcceptChanges() is not strictly necessary but we do it to save memory. The call locally commits the changes inside the DataTable and clears the –potentially bloated- row versioning information.

Here’s how the sample app looks like for a query that has a “SQL Statement” column. The manipulated value goes to a regular column and the full text appears in the details of the selected row:

Grid_3

Sorting

Last but not least, we implemented the canonical single-column-sorting-on-header-click behavior. This is only enabled when CanUserSort on the column or CanUserSortColumns on the DataGrid is set to true. In the current release, the DataGrid only covers the visual part: it displays an arrow in the column header according the current SortDirection. We have to implement the sort ourselves in a handler hooked to the Sorting event that exposes the column and the sort direction. We set that sort direction for the clicked column and reset I for the others:

var currentSortDirection = e.Column.SortDirection;

foreach (var column in ResultsGrid.Columns)
{
    column.SortDirection = null;
}

var sortOrder = "ASC";

if ((currentSortDirection == null || currentSortDirection == DataGridSortDirection.Descending))
{
    e.Column.SortDirection = DataGridSortDirection.Ascending;
}
else
{
    sortOrder = "DESC";
    e.Column.SortDirection = DataGridSortDirection.Descending;
}

Then we sort the content itself. We assign the Sort property of the DataTable’s DefaultView, transform the resulting DataView back to a DataTable, and update the binding:

var dataView = dataTable.DefaultView;
dataView.Sort = e.Column.Header + " " + sortOrder;
dataTable = dataView.ToTable();

RefreshContents(dataTable, ResultsGrid);

Code

The sample app lives here on GitHub. When playing with it, don’t forget to bring your own connection string to MainPage.xaml.cs.

Enjoy!

Advertisements

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!