Monday, November 30, 2015

Using SQLite with Windows 10 UWP & EF7

We will build a UWP app that uses the following two tables:
The Skills table is simply a lookup table that contains a list of skills that will populate a drop-down-list (or combo-box) in our UI.
Create a new UWP app in Visual Studio 2015:
  • File >> New >> Project
  • Templates >> Visual C# >> Windows >> Universal
  • Choose the “Blank App (Universal Windows)” template
  • Give the application name UwpSkills the click OK

Install SQLite

  • Visual Studio Extension (.vsix)
  • In Visual Studio 2015, install SQLite: Tools >> Extensions and Updates ...
  • Or Download from >> Download page
Enter SQLite in the search box. Select “SQLite for Universal App Platform” then click on the Install button.

Why SQLite?

  • SQLite is an open source product
  • The world’s most widely used small database
  • SQLite runs on almost all platforms, including mobile devices
  • There is great support for SQLite in UWP apps
  • Can be easily accessed from C# & VB
  • Rich DB with many features. Not as sophisticated as Oracle or SQL Server
  • Home of SQLite is

Install Entity Framework

In order to use SQLite with EF7, we will need to install the EF SQLite:
  • Tools >> NuGet Package Manager >> Package Manager Console
  • Inside the Package Manager Console, run:
Install-Package EntityFramework.SQLite –Version 7.0.0-beta6
  • In order to run code first migrations, we will also need to install EF commands packages. Run the following:
Install-Package EntityFramework.Commands –Version 7.0.0-beta6
Install-Package Microsoft.CodeAnalysis.CSharp –Version 1.0.0

Create your model

It is time to define entity classes and a DB context class that make up your model.
  • Under the project node, create a folder named Models
  • Add a new class named Contact.cs
  • Replace the Contact class name and its contents with the following code:
public class Contact {
   public int ContactId { get; set; }
   public string FirstName { get; set; }
   public string LastName { get; set; }
   public string Email { get; set; }

   public string Skill { get; set; }
   public SkillLookup SkillLookup { get; set; }
  • Resolve the namespace for the StringLength annotation.
  • Similarly, create another class inside the Models folder named SkillLookup and replace the class name and its contents with the following code:
public class SkillLookup {
   public string Skill { get; set; }

   public List<Contact> Contacts { get; set; }
  • Resolve the namespace for the Key annotation.
  • In the Models folder, add a new class named SkillsContext.
  • Replace the SkillsContext class name and its contents with the following code:
public class SkillsContext : DbContext {
   public DbSet<Contact> Contacts { get; set; }
   public DbSet<SkillLookup> Skills { get; set; }

   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
      string databaseFilePath = "SkillDB.db";
      try {
          databaseFilePath = Path.Combine(ApplicationData.Current.LocalFolder.Path, databaseFilePath);
      } catch (InvalidOperationException) { }

      optionsBuilder.UseSqlite($"Data source={databaseFilePath}");
  • Resolve namespaces for DbSet, Path, and ApplicationData.
  • Build the application to ensure you do not have any compile-time errors.

Create the SQLite database

Now that you have a model, you can use migrations to create the SQLite database.
  • Tools >> NuGet Package Manager >> Package Manager Console
  • In the “Package Manager Console” run the following Package Manager command to scaffold a migration that creates an initial set of tables for your model:
Add-Migration InitialMigration
  • If you compile, there will be an error in the migration file. Due to a bug in the migration scaffolder in Beta6 you will need to manually edit the generated migration. Remove (or comment out) the .Annotation("Sqlite:Autoincrement", true) and add a comma (,) at the end of the line before it.
  • Build the application and make sure you do not have any compile-time errors.
  • For testing purposes, we will add dummy data that gets created when the application starts. In the Models folder, add a class named DummyData.
  • Replace the DummyData class name and its contents with the following code:
public static class DummyData {
  public static void Initialize(SkillsContext context) {
      if (!context.Skills.Any()) {
          var sailor = context.Skills.Add(new SkillLookup { Skill = "Sailor" }).Entity;
          var fisher = context.Skills.Add(new SkillLookup { Skill = "Fisherman" }).Entity;
          var smith = context.Skills.Add(new SkillLookup { Skill = "Gold Smith" }).Entity;
          var gardner = context.Skills.Add(new SkillLookup { Skill = "Gardner" }).Entity;
          var mason = context.Skills.Add(new SkillLookup { Skill = "Mason" }).Entity;

              new Contact() {
                  FirstName = "Jim",
                  LastName = "Sailor",
                  Email = "",
                  Skill = sailor.Skill
              new Contact() {
                  FirstName = "Jane",
                  LastName = "Sailor",
                  Email = "",
                  Skill = sailor.Skill
              new Contact() {
                  FirstName = "Bob",
                  LastName = "Fisher",
                  Email = "",
                  Skill = fisher.Skill
              new Contact() {
                  FirstName = "Susanne",
                  LastName = "Smith",
                  Email = "",
                  Skill = smith.Skill
              new Contact() {
                  FirstName = "James",
                  LastName = "Gardner",
                  Email = "",
                  Skill = gardner.Skill
              new Contact() {
                  FirstName = "Perry",
                  LastName = "Mason",
                  Email = "",
                  Skill = mason.Skill
  • Once again, do a quick build to ensure that you do not have any compiler errors.

Apply any pending migrations

Since we want the database to be created on the device that the app runs on, we will add some code to apply any pending migrations to the local database on application startup. The first time that the app runs, this will take care of creating the local database for us.
  • Right-click on App.xaml in Solution Explorer and select View Code
  • Add the following code at the bottom of the class constructor:

                using (var db = new SkillsContext()) {
  • Resolve the namespaces for SkillsContext and ApplyMigration.

Building the UI

We will now build a basic user interface so that our application can interact with data.
  • Open MainPage.xaml
  • Add the page load handler inside the root element Page tag:
  • Add the following XAML code inside <Grid>:
   <ColumnDefinition Width="80" />
   <ColumnDefinition Width="*" />
   <RowDefinition Height="40" />
   <RowDefinition Height="40" />
   <RowDefinition Height="40" />
   <RowDefinition Height="40" />
   <RowDefinition Height="40" />
   <RowDefinition Height="*" />

<TextBlock Text="First Name" Grid.Column="0" Grid.Row="0" />
<TextBox Name="FirstName" Grid.Column="1" Grid.Row="0" Margin="0,0,0,5" />

<TextBlock Text="Last Name" Grid.Column="0" Grid.Row="1" />
<TextBox Name="LastName" Grid.Column="1" Grid.Row="1" Margin="0,0,0,5" />

<TextBlock Text="Email" Grid.Column="0" Grid.Row="2" />
<TextBox Name="Email" Grid.Column="1" Grid.Row="2" Margin="0,0,0,5" />

<TextBlock Text="Skill" Grid.Column="0" Grid.Row="3" />
<ComboBox Name="Skills" Grid.Column="1" Grid.Row="3" 
         DisplayMemberPath="Skill" SelectedValuePath="Skill" Margin="0,0,0,5" />

<Button Click="Add_Click" Grid.Column="1" Grid.Row="4" Margin="0,0,0,5" >Add</Button>

<ListView Name="Contacts" Grid.ColumnSpan="2" Grid.Row="5">
           <StackPanel Orientation="Horizontal" >
               <TextBlock Text="{Binding FirstName}" />
               <TextBlock Text="{Binding LastName}" Margin="5,0,0,0" />
Now we will add some code to wire up the UI with our SQLite DB.
  • Right-click MainPage.xaml in Solution Explorer and select View Code
  • Add the following methods to the class:
private void Page_Loaded(object sender, RoutedEventArgs e) {
   using (var db = new SkillsContext()) {
       Skills.ItemsSource = db.Skills.ToList();
       Contacts.ItemsSource = db.Contacts.OrderBy( c => c.FirstName) .ToList();

private void Add_Click(object sender, RoutedEventArgs e) {
   using (var db = new SkillsContext()) {
       var contact = new Contact {
           FirstName = FirstName.Text,
           LastName = LastName.Text,
           Email = Email.Text,
           Skill = Skills.SelectedValue.ToString()


       Skills.SelectedValue = null;
       FirstName.Text = string.Empty;
       LastName.Text = string.Empty;
       Email.Text = string.Empty;
       Contacts.ItemsSource = db.Contacts.OrderBy(c => c.FirstName).ToList();

  • Resolve the namespace for SkillsContext.

Running the UWP application

You can now run the application to see it in action.
  • Debug >> Start Without Debugging
  • The application will build and launch on your local machine:

  • Enter some data then click on the Add button.
  • When you click on the Add button, the data will be saved in your SQLite database. It will then be retrieved and displayed below the button.
  • Close the application.

Running application on the phone emulator

This app can run equally well on a phone.

If you have not installed phone emulators yet, click on the down-arrow beside “Local Machine” and select “Download New Emulators…”.
Once you have installed the new emulators, make sure you restart Visual Studio.
  • In Visual Studio, set the processor to x86 and the emulator to “Mobile Emulator 10.0.10240.0 720p 5 inch 1GB” then click on the emulator to run the application on the phone: This will first start the emulator, deploy the app on it, then run the app. Depending on your processor speed, the emulator may take some time to launch.
Enter a new contact on your phone. You will determine that the app works equally well.

Where is the database file?

In the SkillsContext.cs file, add a breakpoint to the line right after the following:
databaseFilePath = Path.Combine(ApplicationData.Current.LocalFolder.Path, databaseFilePath);

Run the application and inspect the value of the databaseFilePath variable. Determine just the path then go to that location. You will find the database file named SkillDB.db.

To view the contents of this database file:
Take a moment to appreciate that SQLite in now a first class citizen in EF7.

Wednesday, November 4, 2015

OData v4 Endpoint Using ASP.NET Web API 2.2 & Visual Studio 2015

In this tutorial, we will do the following:
  1. Create an empty web application in Visual Studio.
  2. Add a Student model and use Code First Entity Framework to create the database and seed it with sample data.
  3. Create an OData v4 controller, which will act as the OData service endpoint.
  4. Create a separate client console application that will access the OData service.
Let’s get started.

Creating the Server OData Student Service

We will first create a new web project in Visual Studio 2015.
File >> New > Project >> Installed >> Templates >> Visual C# >> Web
Select the ASP.NET Web Application template. Name the project "StudentService".


Visual Studio Extensions

We need to add two extensions that pertain the V4 of the OData standard, namely:
  1. OData v4 Web API Scaffolding
  2. OData v4 Client Code Generator
To install these extensions:
  • Tools >> Extensions and Updates…
  • Enter “odata” in the search box and install these two extensions:

Add a Model Class

In Solution Explorer, right-click the Models folder. From the context menu, select Add >> Class. Name the class Student. In the Student.cs file, replace the class code with the following:
public class Student {
  public int StudentId { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public string Major { get; set; }
Entity Framework Code First
Install the NuGet package for EF. From the Tools menu, select NuGet Package Manager > Package Manager Console. In the Package Manager Console window, type:
Install-Package EntityFramework
In the Web.config file, add the following section after the closing </configSections> tag:
   <add name="StudentDB" connectionString="Data Source=(localdb)\v11.0; 
   Initial Catalog=StudentDB; Integrated Security=True; MultipleActiveResultSets=True; 
   providerName="System.Data.SqlClient" />
Note: If you are using localdb version 12, then the connection string data source would be: 
Data Source=(localdb)\mssqllocaldb.
Next, add a class named StudentContext to the Models folder:
public class StudentContext : DbContext {
   public StudentContext() : base("name=StudentDB") { }
   public DbSet<Student> Students { get; set; }


1) To enable migrations run the following command in the Package Manager Console:
enable-migrations -ContextTypeName StudentContext -MigrationsDirectory Migrations\StudentMigrations
2) Open the Configuration.cs file in the /Migrations/StudentMigrations folder. Replace the Seed() method with the following code:
protected override void Seed(StudentContext context) {
  s => new { s.FirstName, s.LastName },
  new Student { FirstName = "Andrew", LastName = "Peters", Major = "Pharmacy" },
  new Student { FirstName = "Brice", LastName = "Lambson", Major = "Business" },
  new Student { FirstName = "Rowan", LastName = "Miller", Major = "Medicine" },
  new Student { FirstName = "Tom", LastName = "Doe", Major = "Engineering" },
  new Student { FirstName = "Bob", LastName = "Fox", Major = "City Planning" },
  new Student { FirstName = "Sue", LastName = "Ace", Major = "Forestry" },
  new Student { FirstName = "Joe", LastName = "Gad", Major = "Mining" },
  new Student { FirstName = "Sam", LastName = "Roy", Major = "Energy" }
3) Add a migration by running the following command in the Package Manager Console:
add-migration -ConfigurationTypeName StudentService.Migrations.StudentMigrations.Configuration "InitialCreate"
4) Next, we will create and seed the database by running this command in the Package Manager Console:
update-database -ConfigurationTypeName StudentService.Migrations.StudentMigrations.Configuration

Create OData Controller:

To the Controllers folder, add a controller and select “Microsoft OData V4 Web API Controller using Entity Framework”


Make the following choices on the “Add Controller” wizard:


Click on the Add button. The controller gets created.
Open the StudentsController. You will find this information in the class comment:
The WebApiConfig class requires additional changes. Merge these statements into the Register method of the WebApiConfig class as applicable. Note that OData URLs are case sensitive.
using System.Web.OData.Builder;
using System.Web.OData.Extensions;
using StudentService.Models;
ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
config.MapODataServiceRoute("odata", "odata", builder.GetEdmModel());

Run the server-side application

Hit Ctrl-F5 to run the web application in a browser at address /odata/Students. This should display:
Note: The URL is case-sensitive with OData.
      "StudentId":5,"FirstName":"Bob","LastName":"Fox","Major":"City Planning"
Try out these additional endpoints after adjusting the port number to suit your environment:

Create an OData v4 Client Console App

Keep the server application running. Start a new instance of Visual Studio and create an independent Console Application.

File >> Add > Project >> Installed >> Visual C# >> Console Application

Name the project StudentsClientApp.

Generate the Service Proxy

Right-click the StudentsClientApp console project. Select Add >> New Item >> Visual C# Items >> Code >> OData Client. 
Name the template "". 


Open the file. Set the value of MetadataDocumentUri to the metadata URL of your service. In the case of my example this would be:
public const string MetadataDocumentUri = http://localhost:58005/odata/$metadata;
Note: You must adjust the port number to match your environment.
Note: Needless to say, you need to change the port number to suit your environment.
As soon as you save, the proxy class will be created in the StudentClient.cs file. If this is not the case, then right-click on it and choose “Run Custom Tool”.


Now that we have a proxy, we can write the code that accesses the service.
Replace the Program class with the following code:

class Program {
    // Get an entire entity set.
    static void ListAllStudents(Default.Container container) {
        foreach (var s in container.Students) {
            Console.WriteLine("{0}, {1}, {2}", s.FirstName, s.LastName, s.Major);
        Console.WriteLine(string.Concat(Enumerable.Repeat("=", 50)));

    static void AddStudent(Default.Container container, Student student) {
        var serviceResponse = container.SaveChanges();
        foreach (var operationResponse in serviceResponse) {
            Console.WriteLine("Response: {0}\n", operationResponse.StatusCode);

    static void Main(string[] args) {
        // Adjust the following port number to suit your environment.        string serviceUri = "http://localhost:51850/odata/";
        var container = new Default.Container(new Uri(serviceUri));
        int count = container.Students.Count();
        var student = new Student() {
            FirstName = "First " + (count + 1),
            LastName = "Last " + (count + 1),
            Major = "Major " + (count + 1)

        AddStudent(container, student);

After making sure the Server application is running, run the Client application by right-clicking on the console application and choosing: Debug >> Start new instance


You should see the following console window:


Note that every time you run the client a new student gets added. The output above shows the list of students before and after a record is added.
I trust this walk-through is useful to you.