WIN10 UWP Development Series: Using SQLite

In the app development process, there must be some data to be stored locally, a simple configuration can be serialized and stored as a file, such as LocalSettings, or saved in isolated storage. However, if there is more data, local database support is required. In UWP development, you can use SQLite. This article explains how to use SQLite in the UWP. Because SQLite is a cross-platform, the version is numerous, I just started to use when I do not know which to install, what WP8, WP8.1, Win RT ... I can't feel my head. I hope this article can make people less detours.

In fact, this article is written in half to see that the great God has written this: http://www.cnblogs.com/ms-uap/p/4798269.html

And this http://www.cnblogs.com/h82258652/p/4802076.html.

The content is also very detailed, but in order to gather the number of blogs, or stick to finish it. By the way, we also introduce some methods of using Mvvm-sidekick ^_^

First, add SQLite extension

The first thing to add is the SQLite library. Homepage is http://sqlite.org/, in: http://sqlite.org/download.html. This page has all the platforms, download this:

and then install.

Another way is to install it directly in VS2015 extensions and updates, searching for SQLite:

After you install the extension, you need to restart VS2015.

Second, add SQLite reference

Or create a new Mvvm-sidekick project and compile it to let NuGet automatically add the required references. The use of Mvvm-sidekick is detailed in my previous blogs.

You can now add a sqlite reference. Right-click on the item and add a reference:

You also need to install a SQLITE-NET-PCL, which is equivalent to an ORM:

These two are available in the reference:

Third, add the entity class

Add a models folder to your project and add a Useritem class:

public class useritem { // <summary> /// self-increment primary key // </summary> [autoincrement, PrimaryKey] public int Id { get; set;} // <summary> /// name // </summary> public string UserName { get; set;} // <summary> /// age is not empty // </summary> [notnull] public int age { get; set;} // <summary> /// address // </summary> public string Address { get; set;} // <summary> /// Ignore this value // </summary> [Ignore] public string someproperty { get; set;} }

iv. Add Command

Let's start by simply manipulating the SQLite experience. Add two buttons to the MainPage page, one to add the user, and one to read the user:

Add a database name to the App.xaml.cs:

Then add two command in the Mainpage_model.cs file, you can use the code snippet Propcmd to generate a command code:

Add a user named Commandadduser,

Await mvvmsidekick.utilities. Taskexhelper. Yield (); st Ring path = path Applicationdataapp using var db = new sqliteconnection(new SQLITEPLATFORMWINRT(), Path)) { Db. createtable<Useritem> (); useritem item = new useritem {Address = "Beijing", age = $, Someproperty = "haha", use Rname = "Wang Xiaoming" }; Db. Insert (item); }

Get the list of users named Commandgetusers:

Await mvvmsidekick.utilities. Taskexhelper. Yield (); string Path = path. Combine (windows.storage. ApplicationData. Current.LocalFolder.Path, App. Dbfilename); StringBuilder sb = new StringBuilder(); using (var db = new sqliteconnection(new SQLITEPLATFORMWINRT (), path)) { var list = db. table<Useritem> (); foreach (var item in list) { Sb. Appendline ($ "{item. Id} {item. UserName} {item. Age} {item. Address}"); } await new messagedialog(sb.) ToString ()). Showasync (); }

Then bind the two command to a button on the page:

OK, now it's time to run a look.

The data can already be added and read.

Of course the above code is very ugly, we'd better split the layer, the database of the part of the interaction to take out. Do not see code directly accessing the database in the VM.

Five, reconstruction

When I learned to program, I often talked about the three-tier architecture, what is the three-tier architecture? First, the previous image:

The bottom mutton block, the middle Lamb row, the top seafood, the top layer of seafood needs the bottom of the heat to steam, the middle of the lamb platoon to the bottom of the hot air to send up ... Ah, writing this article in the middle of the night is almost drooling ...

It's a little off topic. Still look at our project, just now we use SQLite is directly in the command to connect the database and then access to insert or read data, which is very ugly. It is best to extract the code that accesses the database and put it in a separate layer.

Create a new services folder in the project and add a DbContext.cs file.

This class uses a single example:

Then call the Init method to initialize the database when the app initializes. Where to initialize, we find the StartupFunctions.cs file in the Startups directory, and the initialization code can be written in the Runallconfig () method:

And then in the Services directory to add a DataService.cs file, here can be commonly used additions and deletions are implemented, such as:

How does this service work, I used to use a lightweight IOC container provided by Mvvm-sidekick, or the Runallconfig () method of the StartupFunctions.cs file under the Startups directory, adding this line:

Go back to the Mainpage_model.cs file, modify the command in the VM,

Inserting users is much more concise:

Get all Users:

In this way, the code on the VM layer is much less, and the reuse is implemented.

Vi. Other

would also like to write how to read the database, but in the H great God http://www.cnblogs.com/h82258652/p/4802076.html This article has described in detail how to find this database and how to read the database, here will not repeat. In addition, he mentioned that the project must be compiled into x86 or x64, not for any CPU, and also to be careful.

As for how to get data out to bind to the ListView, it should be easy.

And then there's the base class for the Singleton, which is also attached here:

WIN10 UWP Development Series: Using SQLite