Integrating Cognitive Service Speech Recognition in UWP apps

09/08/2018 1 comment

The Speech service, part of Cognitive Services, is powered by the same technologies used in other Microsoft products, including Cortana and Microsoft Office.

We just need to create a speech resource in Azure Portal to obtain the keys to use it in our apps. Note that, at the time of writing, the service is in Preview and is available only in East Asia, North Europe and West US.

The service is available either using the SDK or the REST API. Let’s see how to use the former in a UWP app.

First of all, we have to add the Microsoft.CognitiveServices.Speech NuGet package to the solution:

Microsoft.CognitiveServices.Speech NuGet package

Microsoft.CognitiveServices.Speech NuGet package

Then, we create a simple UI with a Button to start recognition and a TextBox to show the result:

<Grid Padding="50">
    <Grid.RowDefinitions>
        <RowDefinition Height="Auto" />
        <RowDefinition Height="*" />
    </Grid.RowDefinitions>
    <Button
        x:Name="RecognitionButton"
        Grid.Row="0"
        Margin="0,0,0,20"
        Click="RecognitionButton_Click"
        Content="Start Recognition" />
    <TextBox
        x:Name="RecognitionTextBox"
        Grid.Row="1"
        HorizontalAlignment="Stretch"
        Header="Recognized text"
        IsReadOnly="True" />
</Grid>

As the app need to use the microphone, it’s important to add the corresponding capability by double clicking the Package.appxmanifest file and go to the Capabilities tab:

Addung Micophone Capability to app

Addung Micophone Capability to app

Then, we can finally write the code to perform recognition:

private async void RecognitionButton_Click(object sender, RoutedEventArgs e)
{
    const string SpeechSubscriptionKey = "";
    const string SpeechRegion = "";
    const string Culture = "it-IT";

    var isMicAvailable = await CheckEnableMicrophoneAsync();
    if (!isMicAvailable)
    {
        return;
    }

    RecognitionButton.Content = "Recognizing...";
    RecognitionButton.IsEnabled = false;
    RecognitionTextBox.Text = string.Empty;

    var cognitiveSpeechFactory = SpeechFactory.FromSubscription
        (SpeechSubscriptionKey, SpeechRegion);

    // Starts recognition. It returns when the first utterance has been 
    // recognized.
    using (var cognitiveRecognizer = cognitiveSpeechFactory.
        CreateSpeechRecognizer(Culture, OutputFormat.Simple))
    {
        var result = await cognitiveRecognizer.RecognizeAsync();

        // Checks result.
        if (result.RecognitionStatus == RecognitionStatus.Recognized)
        {
            RecognitionTextBox.Text = result.Text;
        }
        else
        {
            await new MessageDialog(result.RecognitionFailureReason,
                result.RecognitionStatus.ToString()).ShowAsync();
        }
    }

    RecognitionButton.Content = "Start recognition";
    RecognitionButton.IsEnabled = true;
}

private async Task<bool> CheckEnableMicrophoneAsync()
{
    var isMicAvailable = false;

    try
    {
        var mediaCapture = new MediaCapture();
        var settings = new MediaCaptureInitializationSettings
        {
            StreamingCaptureMode = StreamingCaptureMode.Audio
        };

        await mediaCapture.InitializeAsync(settings);
        isMicAvailable = true;
    }
    catch
    {
        await Windows.System.Launcher.LaunchUriAsync
            (new Uri("ms-settings:privacy-microphone"));
    }

    return isMicAvailable;
}

The lines 3-5 must be completed with the Speech subscription key that we can find on Azure portal, the name of the region in which the service has been created (eastasia, northeurope or westus at this time) and the language of the speech. At the moment, the service supports Arabic, Italian, German, Japanes, English, Portoguese, Spanish, Russian, France and Chinese.

At lines 7-11 we check whether the microphone is available. The CheckEnableMicrophoneAsync methods (43-65) tries to initialize a MediaCapture object for audio: in this way, if necessary, the app will prompt the user to consent the microphone usage.

After that, we can finally start the real recognition process. We instantiate a SpeechFactory at lines 17-18 and use it for creating the Cognitive Speech recognizer at lines 22-23. The RecognizeAsync method (line 25) actually starts speech recognition, and stops after the first utterance is recognized.

If the RecognitionStatus property is equal to Recognized (line 28), it means that the recognition succeeded, so we can read the Text property to access the recognized text.

You can download the sample app using the link below:

Integrating Cognitive Service Speech in UWP apps

As said before, RecognizeAsync returns when the first utterance has been recognized, so it is suitable only for single shot recognition like command or query. For long-running recognition, we can use the StartContinuousRecognitionAsync method instead.

Advertisements

Introducing the Windows UI Library

25/07/2018 1 comment

As we can read at https://docs.microsoft.com/en-us/uwp/toolkits/winui:

The Windows UI Library is a set of NuGet packages that provide controls and other user interface elements for UWP apps. It also enables down-level compatibility with earlier versions of Windows 10, so your app works even if users don’t have the latest OS.

Thanks to it, we can use UWP XAML controls, that usually are shipped and updated as part of the Windows SDK, even on older versions of Windows 10 down to the Anniversary Update (1607). In addition, the library contains also updated versions of existing controls, so we won’t have to include version checks or conditional XAML markup to use these controls or features on earlier versions of Windows.

At this moment, the library is available as a Prerelease package. Let’s see how to start using it in our apps.

Let’s create a new UWP app using the Blank app template and be sure to select Build 1803 (17134) as Target version and at least Anniversary Update (14393) as Min version. Right click on the project, select Manage NuGet Packages and search for Microsoft.UI.Xaml (the Include Prerelease checkbox must be active):

Adding Windows UI Library from NuGet

Adding Windows UI Library from NuGet

After the installation, a TXT files will be automatically opened in Visual Studio telling us to add WinUI Theme Resources to our app. We simply need to add the following code in the App.xaml:

<Application.Resources>
    <XamlControlsResources xmlns="using:Microsoft.UI.Xaml.Controls" />
</Application.Resources>

Let’s say that we want to use the new MenuBar control, that isn’t part of Windows SDK v17134 and we can’t install the Insider Preview SDK. First of all, we need to add the XAML namespace in the page declaration:

<Page
    x:Class="WinUILibraryDemo.MainPage"
    ...
    xmlns:controls="using:Microsoft.UI.Xaml.Controls"
    ...>

Then, simply define the menu in XAML:

<Grid>
    <Grid.RowDefinitions>
        <RowDefinition Height="Auto" />
        <RowDefinition Height="*" />
    </Grid.RowDefinitions>

    <controls:MenuBar Grid.Row="0">
        <controls:MenuBarItem Title="File" AccessKey="F">
            <MenuFlyoutItem Click="FileNew_Click" Text="New" />
            <MenuFlyoutItem Text="Open..." />
            <MenuFlyoutItem Icon="Save" Text="Save" />
            <MenuFlyoutSeparator />
            <MenuFlyoutItem Text="Exit" />
        </controls:MenuBarItem>

        <controls:MenuBarItem Title="Edit" AccessKey="E">
            <MenuFlyoutItem Text="Undo" />
            <MenuFlyoutSeparator />
            <MenuFlyoutItem Icon="Cut" Text="Cut" />
            <MenuFlyoutItem Icon="Copy" Text="Copy" />
            <MenuFlyoutItem Icon="Paste" Text="Paste" />
        </controls:MenuBarItem>

        <controls:MenuBarItem Title="Help" AccessKey="H">
            <MenuFlyoutItem Icon="Help" Text="About..." />
        </controls:MenuBarItem>
    </controls:MenuBar>
</Grid>

At lines 7-27 we are creating a MenuBar with tree MenuBarItem. Note that menu commands are standard MenuFlyoutItem objects:

Windows UI Library Menu control

Windows UI Library Menu control

Another new interesting control is the DropDownButton, that is available starting from the Windows 10 SDK Preview Build 17686. Thanks to the Windows UI Library, we can use it right now in Windows 10 Anniversary Update and later:

<controls:DropDownButton Grid.Row="1" HorizontalAlignment="Center">
    <controls:DropDownButton.Content>
        <SymbolIcon Symbol="Mail" />
    </controls:DropDownButton.Content>
    <controls:DropDownButton.Flyout>
        <MenuFlyout Placement="Bottom">
            <MenuFlyoutItem Icon="Send" Text="Send" />
            <MenuFlyoutItem Icon="MailReply" Text="Reply" />
            <MenuFlyoutItem Icon="MailReplyAll" Text="Reply All" />
        </MenuFlyout>
    </controls:DropDownButton.Flyout>
</controls:DropDownButton>

With this XAML, we obtain the following UI, with the Flyout appearing when we tap on the button (as we expect):

Windows UI Library DropDownButton control

Windows UI Library DropDownButton control

These are only two examples of what the Windows UI Library allows to do. Keep in mind that is an early prerelease, so we can expect new controls, new properties on existing controls and new features as the project evolves.

Managing the Windows Timeline within a Windows Template Studio app

01/06/2018 2 comments

One of the most interesting features that comes with Windows 10 April 2018 Update (v1803) is the Timeline, that tracks our PC activities history (like a sort of browser history): it allows users to pick up where they left off within an app up to 30 days in the past, congregating all the work we do on our PC into one convenient timeline for us to look through. It works across devices too, meaning you can resume work you were doing on another device whenever it suits you.

Starting from Windows SDK for Windows 10 Fall Creators Update (10.0.16299), developers have the ability to create the so called User Activities to notify the system of a user work stream that can be continued on another device, or at another time on the same device. Creating a User Activity causes it to appear in Windows Timeline and in Cortana’s Pick up where I left off feature.

Creating a User Activity is straightforward, implementing the “pick up where I left off” feature we need to support Activation Deep Links, in order to resume the app with specific context. To handle them effectively, it is necessary to write some boilerplate code. Fortunately, in cases like this we can leverage the great Windows Template Studio, a Visual Studio 2017 Extension that accelerates the creation of new Universal Windows Platform apps using a wizard-based experience, providing code for most common scenarios, such as master/detail navigation, suspend and resume, settings and, of course, deep links support. So, let’s see how to use it to manage the Windows Timeline.

After installing the extension, Windows Template Studio will be available as a new project template in the New Project dialog. Let’s select it to start the wizard:

New Windows Template Studio app

New Windows Template Studio app

For our purpose it is important to add Uri Scheme support, in the Features section of the wizard:

Add Uri Scheme support to app

Add Uri Scheme Support To app

Let’s click the Create button and wait until the app is ready. As said, the project is well-formed and includes a lot of boilerplate code. Let’s see how to update it to add support for User Activities and Timeline.

First of all, we need to use the UserActivityChannel class to create a User Activity and set its properties for the timeline. The following code helps us:

public class Activity
{
    public string ActivityId { get; set; }

    public string DisplayText { get; set; }

    public string Description { get; set; }

    public Color BackgroundColor { get; set; }

    public string ActivationUri { get; set; }

    public static Activity Create(string displayText, string activationUri)
        => Create(null, displayText, null, null, activationUri);

    public static Activity Create(string displayText, string description,
        string activationUri)
        => Create(null, displayText, description, null, activationUri);

    public static Activity Create(string activityId, string displayText,
        string description, Color? backgroundColor, string activationUri)
        => new Activity(activityId ??
            (NavigationService.Frame.Content as Page)?.GetType().Name,
            displayText, description,
            backgroundColor ?? Color.FromArgb(0, 0, 0, 0),
            activationUri);

    private Activity(string activityId, string displayText, string description,
        Color backgroundColor, string activationUri)
    {
        ActivityId = activityId;
        DisplayText = displayText;
        Description = description;
        BackgroundColor = backgroundColor;
        ActivationUri = activationUri;
    }
}

public static class ActivityManager
{
    public static UserActivitySession CurrentActivity { get; private set; }

    public static async Task<UserActivitySession> GenerateActivityAsync(
        Activity activity)
    {
        //Get the default UserActivityChannel and query it for our UserActivity.
        //If the activity doesn't exist, one is created.
        var channel = UserActivityChannel.GetDefault();
        var userActivity = await channel.GetOrCreateUserActivityAsync(
            activity.ActivityId);

        //Populate required properties
        userActivity.VisualElements.DisplayText = activity.DisplayText;
        userActivity.VisualElements.Description = activity.Description;
        userActivity.VisualElements.BackgroundColor = activity.BackgroundColor;
        userActivity.ActivationUri = new Uri(activity.ActivationUri);

        //Save
        await userActivity.SaveAsync(); //save the new metadata

        //Dispose of any current UserActivitySession, and create a new one.
        CurrentActivity?.Dispose();
        CurrentActivity = userActivity.CreateSession();

        return CurrentActivity;
    }
}

The Activity class at lines 1-37 allows us to pass all the information we need to create the activity itself. Then, the ActivityManager (lines 39-67) actually creates the Activity in the Timeline at lines 49-59. The ActivityId property, as the name implies, is used to identify the activity that we want to create. The app should name activities in such a way that same ID is generated each time the user is in a particular location in the app. For example, if our application is page-based, we use an identifier for the page, if it’s document based, use the name of the doc (or a hash of the name). In this case, it is set to the current page name if not otherwise specified.

Going forward, line 56 is one of the most important: it sets the ActivitionUri property of the activity, that we need to check to determine if our app is activated through the Timeline. We’ll talk about it in a moment.

Creating a User Activity using these classes is straightforward. Let’s open the MainPage.xml file and add the following XAML:

<StackPanel
    Width="500"
    HorizontalAlignment="Center"
    VerticalAlignment="Center"
    Spacing="20">
    <TextBox
        x:Name="ActivityNameTextBox"
        Header="Activity name"
        Text="Activity name sample" />
    <TextBox
        x:Name="ActivityDescriptionTextBox"
        Header="Activity data (description)"
        Text="Activity description sample" />
    <TextBox
        x:Name="ActivationUriTextBox"
        Header="Activitation arguments"
        Text="my-sample-arguments" />
    <Button
        x:Name="CreateActivityButton"
        Click="CreateActivityButton_Click"
        Content="Create User Activity" />
</StackPanel>

We use these fields to get the information needed by the Activity: in this sample, we set activity name, description and activation arguments. Then, in the code-behind we handle the CreateActivityButton Click event:

private async void CreateActivityButton_Click(object sender, RoutedEventArgs e)
{
    var activity = Activity.Create(ActivityNameTextBox.Text,
        ActivityDescriptionTextBox.Text,
        $"timeline:?args={ActivationUriTextBox.Text}");

    await ActivityManager.GenerateActivityAsync(activity);
}

We simply need to create the Activity using the Activity.Create method (line 3-5) and then invoke the GenerateActivityAsync method (line 7) of ActivityManager. Let’s note the format at line 5, because it will be very important: it is formed by a constant part and the value that the user has inserted in the corresponding TextBox.

This part is complete. We can run the app, set the required information and click the Create User Activity button to add the activity to the timeline. For example (note that we may need to click the “show activities” link near to the date in order to show all the timeline elements):

Windows Timeline

Windows Timeline

So far, we are able add all our activities to the timeline. But, on the other hand, we need to prepare our app so that it can be activated by a click on the timeline element. So, we must register a Protocol in the application manifest.

Let’s double click on the Package.appxamifest file in the Solution Explorer and go to the Declarations tab. In the Available Declarations drop down, we have to select Protocol and then click the Add button. As we are using the string timeline as activation uri prefix, let’s specify this value in the Name field:

Add Protocol Uri to app

Add Protocol Uri to app

In this way, our app will be automatically invoked when the operating system detects a request with this custom protocol. The last thing to do is to write the code to respond to this kind of activation.

Because we have created the app with Windows Template Studio adding Uri Scheme support, in the Activation folder we can find the SchemeActivationHandler.cs file, with a standard app activation workflow. We can easily modify it to handle our scenario:

protected override async Task HandleInternalAsync(
    ProtocolActivatedEventArgs args)
{
    if (args.Uri.ToString().ToLowerInvariant().StartsWith("timeline:"))
    {
        string arguments = null;

        try
        {
            if (args.Uri.Query != null)
            {
                // The following will extract the secret value and pass it to the
                // page. Alternatively, you could pass all or some of the Uri.
                var decoder = new Windows.Foundation.WwwFormUrlDecoder(
                    args.Uri.Query);
                arguments = decoder.GetFirstValueByName("args");
            }
        }
        catch (Exception)
        {
            // NullReferenceException if the URI doesn't contain a query
            // ArgumentException if the query doesn't contain a param
            // called 'secret'
        }

        // It's also possible to have logic here to navigate to different pages.
        // e.g. if you have logic based on the URI used to launch
        NavigationService.Navigate(typeof(Views.MainPage), arguments);
    }
    else if (args.PreviousExecutionState != ApplicationExecutionState.Running)
    {
        // If the app isn't running and not navigating to a specific page based
        // on the URI, navigate to the home page
        NavigationService.Navigate(typeof(Views.MainPage));
    }

    await Task.CompletedTask;
}

The HandleInternalAsync method is automatically invoked when the app is activated by a custom protocol. At line 4 we check whether the uri starts with timeline (this is necessary, for example, if we want to handle multiple protocols) and then we extract the args parameter from query string. Finally, we navigate to the MainPage passing this value to it.

For the sake of simplicity, in the MainPage.xaml.cs we show a MessageDialog if it is activated with a parameter:

protected override async void OnNavigatedTo(NavigationEventArgs e)
{
    var args = Convert.ToString(e.Parameter);
    if (!string.IsNullOrWhiteSpace(args))
    {
        var dialog = new MessageDialog(args, "Activation arguments");
        await dialog.ShowAsync();
    }

    base.OnNavigatedTo(e);
}

Now everything is in place. We can click an activity in the timeline, even when our app is closed: it will be automatically launched and a dialog will appear showing the activation arguments we set.

This is a very basic example to showcase the Windows Timeline feature. In more complex scenarios, we can use also Adaptive Cards, but this is the argument for another blog post.

You can download the sample app using the link below:

Managing the Windows Timeline within a Windows Template Studio app

Custom Vision and Azure Functions integration with UWP and Xamarin on ioProgrammo

16/01/2018 Comments off

I have written a new article for the n°221 of ioProgrammo (December 2017). This time I show how to use Custom Vision Service with Azure Functions in order to create a service that is able to provide information about the objects that are recognized. Then, I explain how to integrate it with UWP and Xamarin apps.

ioProgrammo n°221 (December 2017)

ioProgrammo n°221 (December 2017)

Categories: .NET, Azure, C#, Xamarin

How to effectively use the ColorPicker control

27/11/2017 1 comment

ColorPicker is one of the easiest to use controls that comes with the Fall Creators Update. We just need to add it in the XAML to show a color picker that allows the user to browse through and select colors:

This kind of usage is straightforward, but typically we need to show the picker only when the user actually wants to select a color. So, we can for example insert the picker as a Flyout for a Button. But, suppose that we want to create a kind of classic UI in which we have a rectangle that, once clicked, opens the picker, and every color change will instantly update the rectangle background. How can we accomplish this?

First of all, we can define the ColorPicker as a Flyout for a Rectangle:

<Rectangle
    x:Name="ColorRectangle"
    Width="64" Height="64"
    Margin="20"
    Stroke="Black">
    <Rectangle.Fill>
        <SolidColorBrush Color="{x:Bind PopupColorPicker.Color, Mode=OneWay}" />
    </Rectangle.Fill>
    <FlyoutBase.AttachedFlyout>
        <Flyout>
            <ColorPicker
                x:Name="PopupColorPicker"
                ColorSpectrumShape="Ring"
                Color="White" />
        </Flyout>
    </FlyoutBase.AttachedFlyout>
</Rectangle>

Note that, at lines 6-8, we have bound the Fill property of the Rectangle to the Color property of the Picker. In this way, every change of the selected color will automatically reflect in the Rectangle background.

But an important thing is missing: since we are using the FlyoutBase.AttachedFlyout property, we need to manually open the Flyout, in this case when the user taps on the Rectangle. Let’s see how to do that using a behavior.

Let’s start by adding the Microsoft.Xml.Behavoirs.Uwp.Managed NuGet packages to the project. Then, we need to write the following declarations in the XAML page:

<Page
    ...
    xmlns:Interactivity="using:Microsoft.Xaml.Interactivity"
    xmlns:Core="using:Microsoft.Xaml.Interactions.Core"
    >
    
    <Rectangle ...   
        ...
        <Interactivity:Interaction.Behaviors>
            <Core:EventTriggerBehavior EventName="Tapped">
                <local:OpenFlyoutAction />
            </Core:EventTriggerBehavior>
        </Interactivity:Interaction.Behaviors>
    </Rectangle>

We use an EventTriggerBehavoir for the Tapped event. When it occurs, we execute the OpenFlyoutAction, that is defined as follows:

public class OpenFlyoutAction : DependencyObject, IAction
{
    public object Execute(object sender, object parameter)
    {
        FlyoutBase.ShowAttachedFlyout((FrameworkElement)sender);
        return null;
    }
}

It simply opens the Flyout that is attached to the sender. We don’t need anything else: we can start the app, taps the Rectangle and try to select a color that will be instantly shown in the Rectangle itself.

You can download the app sample using the link below:
How to effectively use the ColorPicker control

The new PersonPicture control in the Fall Creators Update

08/11/2017 1 comment

PersonPicture is a simple yet powerful control that comes in the Universal Windows Platform with the Fall Creators Update. It allows to display the avatar image for a person, if one is available; if not, it displays the person’s initials or a generic glyph. It supports also showing an image, number or text as badge. It’s usage is straightforward. For example:

<StackPanel
    HorizontalAlignment="Center"
    VerticalAlignment="Center"
    Orientation="Horizontal">
    <PersonPicture
        x:Name="personPicture"
        Width="48"
        Height="48" />
    <TextBlock
        x:Name="nameTextBlock"
        Margin="5,0,0,0"
        VerticalAlignment="Center" />
</StackPanel>

In the OnNavigatedTo method, let’s add the following code to retrieve name and picture of the currently logged user:

protected override async void OnNavigatedTo(NavigationEventArgs e)
{
    var user = (await User.FindAllAsync(UserType.LocalUser, 
        UserAuthenticationStatus.LocallyAuthenticated)).FirstOrDefault();

    var firstName = (string)await user.GetPropertyAsync(
        KnownUserProperties.FirstName);
    var lastName = (string)await user.GetPropertyAsync(
        KnownUserProperties.LastName);
    var profileStream = await user.GetPictureAsync(UserPictureSize.Size64x64);

    personPicture.DisplayName = $"{firstName} {lastName}".Trim();

    if (profileStream != null)
    {
        var stream = await profileStream.OpenReadAsync();
        var bitmapImage = new BitmapImage();
        bitmapImage.SetSource(stream);
        personPicture.ProfilePicture = bitmapImage;
    }

    nameTextBlock.Text = personPicture.DisplayName;
}

To make this code work, we need to add the User Account Information capability in the app’s package manifest. After doing that, we’ll obtain something like that:

PersonPicture control showing profile image

PersonPicture control showing profile image

As we have set both the DisplayName and the ProfilePicture, the control shows the latter, because it has precedence over the other information. On the other hand, if we comment line 19, i.e. we do not set the ProfilePicture property, the result will be the following:

PersonPicture control showing profile initials

PersonPicture control showing profile initials

As we can see, the control automatically extracts the initials from the DisplayName property and shows them inside the control.

Beside that, we can directly bind the control to a Contact object that contains information about the person, by simply setting its Contact property.

If we want to show a badge on the image, for example a number, we just need to set the BadgeNumber property:

personPicture.BadgeNumber = 42;
PersonPicture control showing badge number

PersonPicture control showing badge number

To hide the badge after setting it, we need to set the BadgeNumber property to 0.

You can download the app sample using the link below:
The new PersonPicture control in the Fall Creators Update

Using SqlClient APIs and Dapper with Fall Creators Update SDK

25/10/2017 2 comments

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:

Selecting Fall Creator Update as Target SDK

Selecting Fall Creator 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):

The database schema for the UWP sample

The database schema for the UWP sample

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:

Using SqlClient APIs with UWP

Using SqlClient APIs with UWP

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:
Using SqlClient APIs and Dapper with Fall Creators Update SDK