We will build a Windows 10 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 then click OK
- Choose minimum version be same as target version then click OK.
Install SQLite
- Visual Studio Extension (.vsix)
- In Visual Studio 2015, install SQLite: Tools >> Extensions and Updates ...
- Or Download from SQLite.org >> 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 http://sqlite.org
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-rc1-final
- 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-rc1-final
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; }
[StringLength(40)]
[Required]
public string FirstName { get; set; }
[StringLength(40)]
[Required]
public string LastName { get; set; }
[StringLength(100)]
[Required]
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 {
[Key]
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
- 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 List<SkillLookup> GetSkills() {
List<SkillLookup> skills = new List<SkillLookup>() {
new SkillLookup() {Skill = "Sailor"},
new SkillLookup() {Skill = "Baker"},
new SkillLookup() {Skill = "Walker"},
new SkillLookup() {Skill = "Gardner"},
new SkillLookup() {Skill = "Carpenter"},
new SkillLookup() {Skill = "Fisher"},
new SkillLookup() {Skill = "Harper"},
new SkillLookup() {Skill = "Winter"},
};
return skills;
}
public static List<Contact> GetContacts(SkillsContext db) {
List<Contact> contacts = new List<Contact>() {
new Contact() {
FirstName = "Jim",
LastName = "Sailor",
Email = "jim@sailor.com",
Skill = db.Skills.FirstOrDefault(s => s.Skill == "Sailor").Skill
}, new Contact() {
FirstName = "Jane",
LastName = "Harper",
Email = "jane@harper.com",
Skill = db.Skills.FirstOrDefault(s => s.Skill == "Harper").Skill
}, new Contact() {
FirstName = "Ed",
LastName = "Winter",
Email = "ed@winter.com",
Skill = db.Skills.FirstOrDefault(s => s.Skill == "Winter").Skill
},
new Contact() {
FirstName = "Bob",
LastName = "Fisher",
Email = "jim@fisher.com",
Skill = db.Skills.FirstOrDefault(s => s.Skill == "Fisher").Skill
}, new Contact() {
FirstName = "Susanne",
LastName = "Smith",
Email = "susanne@baker.com",
Skill = db.Skills.FirstOrDefault(s => s.Skill == "Baker").Skill
}, new Contact() {
FirstName = "James",
LastName = "Gardner",
Email = "james@gardner.com",
Skill = db.Skills.FirstOrDefault(s => s.Skill == "Gardner").Skill
}, new Contact() {
FirstName = "Sue",
LastName = "Carpenter",
Email = "sue@carpenter.com",
Skill = db.Skills.FirstOrDefault(s => s.Skill == "Carpenter").Skill
}, new Contact() {
FirstName = "Perry",
LastName = "Walker",
Email = "perry@walker.com",
Skill = db.Skills.FirstOrDefault(s => s.Skill == "Walker").Skill
}
};
return contacts;
}
}
- 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()) {
db.Database.Migrate();
if (!db.Skills.Any()) {
db.Skills.AddRange(DummyData.GetSkills());
db.SaveChanges();
db.Contacts.AddRange(DummyData.GetContacts(db));
db.SaveChanges();
}
}
- Since Migrate() is an extension method, you will need to add the following using statement
using Microsoft.Data.Entity;
- 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 attribute to the <Grid> tag to provide the grid control with a left margin of 200 and a top margin of 100:
Margin="200,100,200,0"
- Add the following XAML code inside <Grid>:
<Grid.ColumnDefinitions>
<ColumnDefinition Width="80" />
<ColumnDefinition Width="*" />
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>
<RowDefinition Height="40" />
<RowDefinition Height="40" />
<RowDefinition Height="40" />
<RowDefinition Height="40" />
<RowDefinition Height="40" />
<RowDefinition Height="*" />
</Grid.RowDefinitions>
<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">
<ListView.ItemTemplate>
<DataTemplate>
<StackPanel Orientation="Horizontal" >
<TextBlock Text="{Binding FirstName}" />
<TextBlock Text="{Binding LastName}" Margin="5,0,0,0" />
</StackPanel>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
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()
};
db.Contacts.Add(contact);
db.SaveChanges();
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.
- Hit F5 on the keyboard or click Debug >> Start Without Debugging
- The application will build and launch on your local machine:
Note:
When you run your app in debug mode, there are two sets of three numbers, white on black, that appear in the top left corner of the application's window. These numbers are framerate counters to help monitor app performance. You can have them not show, if you like, by disabling the following code in the App.xaml.cs file:
#if DEBUG
if (System.Diagnostics.Debugger.IsAttached) {
this.DebugSettings.EnableFrameRateCounter = true;
}
#endif
- 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. It is located somewhere under
c:\Users\{your user name}\AppData\Local\Packages. 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 EF Core.
Continue with the
tutorial which explains how to deploy your application to the Raspberry Pi 2 IoT device.
