Win10 IoT C # Development 5,

Win10 IoT C # Development 5,

Windows 10 IoT Core is an important product of Microsoft for the IoT market. Unlike the previous Windows version, Windows Core is specially designed for IoT devices. The hardware is not limited to the x86 architecture, it can also run on the ARM architecture.

In the previous chapters, we talked about Raspberry's method of installing Win10 IoT system, setting up a development environment, deploying programs, and operating GPIO and UART, through these functions, we can obtain the data sent to us by the sensor. However, if the data cannot be pushed back to the server in time, it needs to be cached locally. Using SQLite database is a good choice. This chapter describes how to operate SQLite databases on IoT devices. If you are not familiar with the installation and deployment process, refer to the previous articles, Raspberry install the IoT system and build the development environment (http://www.cnblogs.com/cloudtech/p/5562120.html), create the IoT application and three deployment methods (http://www.cnblogs.com/cloudtech/p/5637983.html ).

 

Preparations:

Refresh Raspberry Pi 2 of Win 10 IoT Core System

Deploy a PC in the Visual Studio 2015 Development Environment

Install sqlite3 PC

 

Tutorial objective: to deploy an application to an IoT device, create an SQLite database on the device, and perform the CURD operation. Download the SQLite database of the IoT device through FTP, and use sqlite3 on the PC to view the data in the database to verify that the database operation is successful.

1. Install SQLite extension in Visual Studio 2015

Open VS2015 and select Extensions and Updates from the Tools menu.

For Platform extension for installing SQLite for VS2105, enter sqlite Search in the Search box, and select SQLite for Universal Windows Platform in Search Results to download and install.

2. Create an IoT project and reference the SQLite Extension

First, create a Universal Windows application. Open VS 2015 and click New Project. in Visual C #-> Windows-> Universal, find the Blank App (Universal Windows) Project template, select a template, enter a project name, and click OK to create a project.

After the SQLite Platform extension is added to the project, right-click the project and click Add Reference. In the tree view displayed, select Universal Windows-> Extensions. In the list, select SQLite for Universal Windows Platform.

Add a reference to the SQLite class library for the project, and select the Manage NuGet Packages for Solution menu item of NuGet Package Manager from the Tools menu.

Search for sqlitepcl in the pop-up NuGet interface, select SQLitePCL in the search result list, check CloudTechIoT5 in the right project list, and click the Install button to start installation.

3. write code

Code workflow:

Create IoT5DB on the IoT device. the SQLite database file of sdf. Create the users table in the database, which contains two fields. The id is the Integer type of the primary key, and the name is the text type. insert three data entries into the users table, the name field values are IoT-1, IoT-2, and IoT-3.

Then, change the name field value of the second data to the "IoT-dd-HH: mm: ss" format. The time is the current time.

Delete the first data.

The complete code is as follows:

MainPage. xaml. cs

Namespace CloudTechIot5 {// http://www.cnblogs.com/cloudtech // cloudtechesx@gmail.com public sealed partial class MainPage: Page, INotifyPropertyChanged {# region Fields // database name private string _ dbName; // table name private string _ tableName; // The data set of the name field private string [] _ names = {"IoT-1", "IoT-2", "IoT-3"}; # endregion # region Events public event PropertyChangedEventHandler PropertyChanged; # endregion # r Egion Properties private string _ result; // operation Result public string result {get {return _ result;} set {_ Result = value; OnPropertyChanged ("result ");}} # endregion # region Constructor public MainPage () {// initialize _ result = "Processing... "; _ dbName =" IoT5DB. sdf "; _ tableName =" users "; this. initializeComponent (); // simple MVVM framework this. dataContext = this; // create a database connection using (SQLiteConnection connection = CreateDb Connection () {// create the table CreateTable (connection); foreach (string name in _ names) {// insert data InsertRow (connection, name );} // UpdateRow (connection, string. format ("IoT-{0}", DateTime. now. toString ("dd-HH: mm: ss"), _ names [1]); // Delete the first data DeleteRow (connection, _ names [0]);} // update the interface Result = "Completed... ";}# endregion # region Methods private SQLiteConnection CreateDbConnection () {// create a connection to SQLiteConne Ction connection = new SQLiteConnection (_ dbName); if (null = connection) {throw new Exception ("create db connection failed") ;}return connection ;} private void CreateTable (SQLiteConnection connection) {// create a table string SQL = string. format ("create table if not exists {0} (id integer primary key autoincrement, name text)", _ tableName); using (ISQLiteStatement sqliteStatement = connection. prepare (SQL) {// Execute sqliteStatement. step () ;}} private void InsertRow (SQLiteConnection connection, string name) {// insert data string SQL = string. format ("insert into {0} (name) values (?) ", _ TableName); using (ISQLiteStatement sqliteStatement = connection. prepare (SQL) {// bind the sqliteStatement parameter. bind (1, name); // execute sqliteStatement. step () ;}} private void UpdateRow (SQLiteConnection connection, string newName, string oldName) {string SQL = string. format ("update {0} set name =? Where name =? ", _ TableName); using (ISQLiteStatement sqliteStatement = connection. prepare (SQL) {// bind the sqliteStatement parameter. bind (1, newName); sqliteStatement. bind (2, oldName); // execute the sqliteStatement statement. step () ;}} private void DeleteRow (SQLiteConnection connection, string name) {string SQL = string. format ("delete from {0} where name =? ", _ TableName); using (ISQLiteStatement sqliteStatement = connection. prepare (SQL) {// bind the sqliteStatement parameter. bind (1, name); // execute sqliteStatement. step () ;}} public void OnPropertyChanged (string propertyName) {// MVVM dependency attribute event PropertyChanged ?. Invoke (this, new PropertyChangedEventArgs (propertyName) ;}# endregion }}

MainPage. xaml

<Page    x:Class="CloudTechIot5.MainPage"    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"    xmlns:local="using:CloudTechIot5"    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"    mc:Ignorable="d">    <Page.Resources>        <Style TargetType="TextBlock">            <Setter Property="FontSize" Value="50"></Setter>            <Setter Property="FontWeight" Value="Bold"></Setter>            <Setter Property="Foreground" Value="Red"></Setter>            <Setter Property="HorizontalAlignment" Value="Center"></Setter>        </Style>    </Page.Resources>    <Grid Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">        <StackPanel HorizontalAlignment="Center" VerticalAlignment="Center" Orientation="Vertical">            <TextBlock Text="cloudtechesx@gmail.com"/>            <TextBlock Text="{Binding Result, UpdateSourceTrigger=PropertyChanged, Mode=OneWay}"/>        </StackPanel>    </Grid>

4. Deploy the application

Connect the power supply and network cable to Raspberry and wait until Windows 10 IoT system starts up.

On the Visual Studio 2015 toolbar, select Remote Machine for debugging. Enter the IP address of the device. Click Run to automatically deploy the application to the device.

After the application is deployed, the following interface is displayed, indicating that the database operation has been completed.

5. Verification results

Disable the deployed IoT application. By default, the FTP service is enabled for Win10 IoT, And the FTP client is used to connect to the IoT device (FileZilla is used here ), download the generated database files from the following locations on the IoT device.

\ Data \ Users \ DefaultAccount. MINWINPC \ AppData \ Local \ Packages \ {Package_Family_Name} \ LocalState \ {DbName}

 

The Package Family Name can be viewed in Package. appxmanifest.

Download sqlite3 tools in SQLite official http://www.sqlite.org/download.html.

Run the sqlite3 tools command "sqlite3 IoT5DB. sdf" in CMD to open the downloaded SQLite database file.

Enter the SQL statement "select * from users;" to view data in the users table.

The data with id 1 does not exist.

The data name field value with id 2 is IoT-10-19: 18: 52.

The data name field value with id 3 is the IoT-3.

The data in the database is consistent with the expected results.

Here C # operation Win10 IoT device local SQLite database process is complete, if the Code has optimization suggestions, welcome to leave a message or mail me (cloudtechesx@gmail.com ). You can also scan the following QR code and add my number to view previous articles.

Project source code GitHub https://github.com/CloudTechx/CloudTechIot under the CloudTechIot5 directory.

Win10 IoT C # development 1-Raspberry install IoT system and build development environment http://www.cnblogs.com/cloudtech/p/5562120.html
Win10 IoT C # Development 2-creating three deployment methods for XAML-based UI programs and applications http://www.cnblogs.com/cloudtech/p/5637983.html
Win10 IoT C # development 3-GPIO Pin control Light Emitting Diode http://www.cnblogs.com/cloudtech/p/5617902.html
Win10 IoT C # Development 4-UART serial communication http://www.cnblogs.com/cloudtech/p/5518306.html