SqlClient APIs, one of the most requested feature for UWP, have finally been added in the Fall Creators Update. Now we can connect to SQL Server directly from our apps, without the need of an external service, thanks to the System.Data.SqlClient namespace, in which we find familiar objects: SqlConnection, SqlCommand, SqlDataReader, etc.

Let’s see how to leverage these new APIs in a simple UWP app. First of all, we need to select Windows 10 Fall Creators Update as Target SDK:

In this way, we can now use the classic code we are used to:

using System.Data.SqlClient; // ... using (var conn = new SqlConnection(connectionString)) { using (var cmd = conn.CreateCommand()) { cmd.CommandText = "<query>"; using (var reader = await cmd.ExecuteReaderAsync()) { while (reader.Read()) { // ... } } } }

Moreover, as we can use .NET Standard, we can take advantage of Dapper to greatly simply the data mapping. So, let’s add it via NuGet and try to build an app that access the following database schema (you can download it using the link at the end of the article):

Our app allows to search for characters that live in the specified city. So, first of all let’s create the POCO classes:

public class City { public int Id { get; set; } public string Name { get; set; } } public class Character { public int Id { get; set; } public string Name { get; set; } public DateTime FirstAppearance { get; set; } public string ImageUrl { get; set; } }

And then let’s define the user interface in XAML:

<TextBox //... x:Name="QueryTextBox" PlaceholderText="Search for names..." /> <ComboBox // ... x:Name="CitiesComboBox" DisplayMemberPath="Name" SelectedValuePath="Id" /> <Button // ... x:Name="SearchButton" Click="SearchButton_Click" Content="Search" /> <GridView //... x:Name="CharactersGridView"> <GridView.ItemTemplate> <DataTemplate> <Grid Margin="0,0,0,10"> <Image Width="250" Height="250" Source="{Binding ImageUrl}" Stretch="UniformToFill" /> <Border VerticalAlignment="Bottom" Background="Gray"> <TextBlock Margin="5,5,0,5" Foreground="White" Style="{ThemeResource BaseTextBlockStyle}" Text="{Binding Name}" /> </Border> </Grid> </DataTemplate> </GridView.ItemTemplate> </GridView>

At startup, we have to load all the cities and bind them to the CitiesComboBox element:

protected override async void OnNavigatedTo(NavigationEventArgs e) { var cities = await GetCitiesAsync(); CitiesComboBox.ItemsSource = cities; CitiesComboBox.SelectedIndex = 0; base.OnNavigatedTo(e); } public async Task<IEnumerable<City>> GetCitiesAsync() { using (var conn = new SqlConnection(connectionString)) { var cities = await conn.QueryAsync<City>( @"SELECT c.CityId As Id, c.Name FROM Cities c ORDER BY c.Name"); return cities; } }

In the GetCitiesAsync method (lines 10-21), we first create the SqlConnection, then we use the QueryAsync extension method from Dapper (lines 14-17), that executes the query against the database and retrieves the results as an IEnumerable collection of City, performing a binding based on column names.

When the user presses the Search button, we need to start the actual search:

private async void SearchButton_Click(object sender, RoutedEventArgs e) { var characters = await GetCharactersAsync(QueryTextBox.Text, Convert.ToInt32(CitiesComboBox.SelectedValue)); CharactersGridView.ItemsSource = characters; } public async Task<IEnumerable<Character>> GetCharactersAsync(string name, int cityId) { using (var conn = new SqlConnection(connectionString)) { var characters = await conn.QueryAsync<Character>( @"SELECT c.CharacterId As Id, c.Name, c.FirstAppearance, c.ImageUrl FROM Characters c WHERE c.Name LIKE @name AND c.CityId = @cityId ORDER BY c.Name", new { Name = $"%{name}%", CityId = cityId }); return characters; } }

The GetCharactersAsync method at lines 9-28 takes name and cityId as arguments and uses them to build the query: it is a standard SQL query (as above), but in this case we have also two parameters with the @ symbol (lines 17-18). We specify them using an anonymous class that has properties with the same case-insentive names as the parameters (lines 20-24). Dapper will create the corresponding SqlParameter objects and passes them to the query engine.

In conclusion, running the app we should see something like that:

Keep in mind that we can’t use LocalDB when connecting from a UWP app, otherwise we’ll get a PlatformNotSupportedException error. So we must use SQL Server, SQL Server Express or SQL Azure.

You can download the app sample, with the database script, using the link below:

