A truly Universal App will run on all Windows 10 targets, with only a targeted CPU change and rebuild required. This second part of this blog series sets up a UAP/UWP project to make use of the file base SQLite database engine. This part creates the project, gathers the required bits and sets up the user interface. Parts three covers the functional code.
The Universal App (or should we say Universal Windows Platform App, or Universal Windows App .. I await clarification of the correct terminology .. lets use Universal App or UA for now), was developed in Visual Studio 2015 RC as the UA bits weren’t available for VS 2015 RTM at that time. As things will change with the release of Windows 10 here is the development environment:
A UA is developed with a GUI to Create and drop a SQLite database, Add a new table and drop it, and to Insert, Update, Select and Delete sensor records. For this activity the sensor data is generated randomly but there will be a subsequent version of this app that will use real sensors, on an Iot device (Raspberry Pi 2). The app in in its current form will run on a Windows 10 desktop, phone and IoT device.
The code for this project has been added to the IOT Samples Codeplex project: https://iotsampler.codeplex.com/
1.1. Add a new C# Windows Blank Universal app to the solution. (Note not Windows 8 - Universal):
Give it a suitable name. I called mine MySQLiteUWPApp. . Note that there is only ONE project created (UWP).. The XAML and CSharp code is the same for the desktop, mobile and embedded versions of the app. The difference is in how it is compiled.
All Windows 10 platforms support UWP apps. But there are extensions specific to the each platform. For example Windows 10 IoT extensions support GPIO, I2C, SPIO etc. Mobile extensions support phone functions. Etc. UWP provides a guaranteed core API layer across all devices.
The Windows 10 Extension SDKs. The Desktop, Mobile and IoT extensions are checked.
1.2. Examine the project’s references and check that none of the extensions above are checked..
This way the app will run on the desktop, phone and IoT devices, all Windows 10.
We are creating a Universal app, which with only a recompilation after changing the target CPU, will run on any Windows 10 platform. An x86 build will run on the Windows 10 desktop and an Windows 10 IoT enabled Minnow Board Max. An ARM build will run on a Windows 10 phone and a Windows 10 IoT enabled Raspberry PI 2 device.
1.3. Add SQLite capability to the Solution via NuGet:
Two files sqlite.cs and sqliteasync.cs will be added to the project. This adds capabilities to projects within the current solution only.
1.4. Add the other SQLite reference:
Note the C++ package that also gets added. You will need to close and reopen References to see this. There is also a version of this in other places such as on the SQLite download page:
In a manner similar to Windows 8 Universal Apps , we define table records in terms of field properties of a class.
2.1 Add a new class file to the project and copy the following definition into it (adjust the namespace for your project):
using System; using SQLite; namespace MySQLiteUWPApp { [Table("Sensors")] public class Sensor { [PrimaryKey, AutoIncrement] public int id { get; set; } public DateTime dateTime { get; set;} public string Name { get; set; } public int Value { get; set; } } }
Note the primary key definition. By specifying it as an AutoIncrement integer, when records are added you don’t specifyteh id as it is auto generated. Note also that SQLite has a DataeTime data type.
There are textboxes to:
There are buttons to:
The user interface is focused upon three sensors; two temperature and one humidity. There is a button for each to with the specified table:
There is also a button to action a query of the database table:
The app UI
Being a UWP/UA app, the UI is implemented using XAML. Rows with more than one element are implemented as RelativePanels as the UI elements can be placed in the panel relative to each other. This simplifies things especially later on in this activity when the buttons are resized when the screen width changes, using a VisualStateManager. For example, the following XAML code places all of the sensor insert buttons in a horizontal relative layout:
<RelativePanel Grid.Row="2"> <Button x:Name="button21" Content="Temperature1 Insert" Height="38" Margin="5,5,0,5" VerticalAlignment="Top" Click="button_Click" Width="200" FontSize="13.333"/> <Button RelativePanel.RightOf="button21" x:Name="button22" Content="Temperature2 Insert" Height="38" Margin="10,5,0,5" VerticalAlignment="Top" Click="button_Click" Width="200" FontSize="13.333"/> <Button RelativePanel.RightOf="button22" x:Name="button23" Content="Humidity1 Insert" Height="38" Margin="10,5,0,0" VerticalAlignment="Top" Width="200" Click="button_Click" HorizontalAlignment="Left" FontSize="13.333" /> </RelativePanel>
2.1 Set the XAML layout to 10” IoT Device. We’ll look at a phone layout later.
2.2 Add the following Grid Rows to the <Grid> in MainPage.xaml:
<Grid.RowDefinitions> <RowDefinition Height="Auto"/> <RowDefinition Height="40"/> <RowDefinition Height="Auto"/> <RowDefinition Height="40"/> <RowDefinition Height="Auto"/> <RowDefinition Height="40"/> <RowDefinition Height="Auto"/> <RowDefinition Height="Auto"/> <RowDefinition Height="Auto"/> <RowDefinition Height="Auto"/> <RowDefinition Height="Auto"/> </Grid.RowDefinitions>
2.3 Add the following elements (labels and the TextBox for the database filename) to the Grid:
<TextBox Grid.Row="0" x:Name="textBox" Height="9" Margin="10,5,0,5" TextWrapping="NoWrap" Text="sensors.3db" VerticalAlignment="Top" d:LayoutOverrides="LeftPosition, RightPosition"/> <TextBlock Grid.Row="1" Text="Append a new record:" HorizontalAlignment="Left" VerticalAlignment="Center" /> <TextBlock Grid.Row="3" Text="Update first record:" HorizontalAlignment="Left" VerticalAlignment="Center" /> <TextBlock Grid.Row="5" Text="Delete first record:" HorizontalAlignment="Left" VerticalAlignment="Center" />
2.4 RelativePanels: The rest of the UI elements are contained in RelativePanels as follows. Copy these into the Grid:
<RelativePanel Grid.Row="2"> <Button x:Name="button21" Content="Temperature1 Insert" Height="38" Margin="5,5,0,5" VerticalAlignment="Top" Click="button_Click" Width="200" FontSize="13.333"/> <Button RelativePanel.RightOf="button21" x:Name="button22" Content="Temperature2 Insert" Height="38" Margin="10,5,0,5" VerticalAlignment="Top" Click="button_Click" Width="200" FontSize="13.333"/> <Button RelativePanel.RightOf="button22" x:Name="button23" Content="Humidity1 Insert" Height="38" Margin="10,5,0,0" VerticalAlignment="Top" Width="200" Click="button_Click" HorizontalAlignment="Left" FontSize="13.333" /> </RelativePanel> <RelativePanel Grid.Row="4"> <Button x:Name="button41" Content="Temperature1 Update" Height="38" Margin="5,5,0,5" VerticalAlignment="Top" Click="button_Click" Width="200" FontSize="13.333"/> <Button RelativePanel.RightOf="button41" x:Name="button42" Content="Temperature2 Update" Height="38" Margin="10,5,0,5" VerticalAlignment="Top" Click="button_Click" Width="200" FontSize="13.333"/> <Button RelativePanel.RightOf="button42" x:Name="button43" Content="Humidity1 Update" Height="38" Margin="10,5,0,5" VerticalAlignment="Top" Width="200" Click="button_Click" HorizontalAlignment="Left" d:LayoutOverrides="HorizontalAlignment" FontSize="13.333" /> </RelativePanel> <RelativePanel Grid.Row="6"> <Button x:Name="button61" Content="Temperature1 Delete" Height="38" Margin="5,5,0,5" VerticalAlignment="Top" Click="button_Click" Width="200" FontSize="13.333"/> <Button RelativePanel.RightOf="button61" x:Name="button62" Content="Temperature2 Delete" Height="38" Margin="10,5,0,5" VerticalAlignment="Top" Click="button_Click" RenderTransformOrigin="1.745,0.421" HorizontalAlignment="Left" Width="200" FontSize="13.333"/> <Button RelativePanel.RightOf="button62" x:Name="button63" Content="Humidity1 Delete" Height="38" Margin="10,5,0,5" VerticalAlignment="Top" Width="200" Click="button_Click" HorizontalAlignment="Left" d:LayoutOverrides="HorizontalAlignment" FontSize="13.333"/> </RelativePanel> <RelativePanel Grid.Row="7"> <Button x:Name="button71" Content="Query Database" Height="38" Margin="5,5,0,5" VerticalAlignment="Top" Width="200" Click="button_Click" FontSize="13.333"/> <TextBlock RelativePanel.RightOf="button71" x:Name="textBoxQueryBeginsWithLabel" HorizontalAlignment="Left" Margin="10,5,0,5" TextWrapping="Wrap" Text="Begins with:"/> <TextBox RelativePanel.RightOf="textBoxQueryBeginsWithLabel" x:Name="textBoxQueryBeginsWith" Margin="10,5,5,0" TextWrapping="NoWrap" Text="Temperature" HorizontalAlignment="Left" Width="140"/> </RelativePanel> <RelativePanel Grid.Row="8"> <Button x:Name="button81" Content="Add Table" Height="38" Margin="5,5,0,5" VerticalAlignment="Top" Width="200" Click="button_Click" FontSize="13.333"/> <TextBox RelativePanel.RightOf="button81" x:Name="textBoxTableName" Margin="5,5,0,5" TextWrapping="NoWrap" Text="Sensor" HorizontalAlignment="Left" Width="200" IsReadOnly="True"/> <Button RelativePanel.RightOf="textBoxTableName" x:Name="button82" Content="Drop Table" Height="38" Margin="10,5,0,5" VerticalAlignment="Top" Click="button_Click" HorizontalAlignment="Left" Width="200" FontSize="13.333"/> </RelativePanel> <RelativePanel Grid.Row="9"> <Button x:Name="button91" Content="New Database" Height="38" Margin="5,5,0,5" VerticalAlignment="Top" Width="200" Click="button_Click" FontSize="13.333"/> <Button RelativePanel.RightOf="button91" x:Name="button92" Content="Open Database" Height="38" Margin="10,5,0,5" VerticalAlignment="Top" Width="200" Click="button_Click" Grid.RowSpan="2" FontSize="13.333"/> <Button RelativePanel.RightOf="button92" x:Name="button93" Content="Drop Database" Height="38" Margin="10,5,0,5" VerticalAlignment="Top" Click="button_Click" HorizontalAlignment="Left" Width="200" FontSize="13.333"/> </RelativePanel>
Next: The functional code (Coming)
Previous: Win 10 IoT- Universal App – SQLite Database- Part 1 “About SQLite”