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

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 )

Connecting to %s