Archive

Archive for the ‘ADO.NET’ Category

Using SqlClient APIs and Dapper with Fall Creators Update SDK

25/10/2017 Leave a comment

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

Advertisements

How to specify table schema name with Entity Framework Code First

07/01/2013 Comments off

Using Entity Framework Code First approach, if we need to specify a schema name other than the default dbo, we need to override the OnModelCreating method of DbContext class:

public class MyDbContext : DbContext
{
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Entity<MyTable1Entity>().ToTable("MyTable1", schemaName: "myschema");
    base.OnModelCreating(modelBuilder);
  }
}

In this example, we map the MyTable1Entity object to MyTable1 table, specifying that it is contained in the myschema schema.

Categories: ADO.NET, C#, Entity Framework