WIN10 Generic Application UWP use SQLite "Graphics Tutorial"

In the development of Win10 UWP, we can use SQLite. This article explains how to use SQLite in UWP. Because SQLite is Cross-platform, the version is numerous, I just started to use when do not know which to install, what WP8, WP8.1, Win RT ... I can't touch my head. I hope this article can make people take a little detour.

One, add sqlite extension

First, you add the SQLite library. Home page is http://sqlite.org/, download address in: http://sqlite.org/download.html. This page has the download address of all platforms, download this:

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 reboot the VS2015.

Second, add SQLite reference

Or create a new Mvvm-sidekick project and compile it so that NuGet can automatically add the required references. The use of Mvvm-sidekick is detailed in my previous blog.

You can now add a sqlite reference. Right-click on the project to add a reference:

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

There are two in the reference:

Third, add entity classes

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

public class Useritem
{
///<summary>
///Self-added 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 time {get; set;}

  ///<summary>
///addresses
///</summary> public
string Addressing {get; set;}
<summary>
///ignores this value
///</summary>
[Ignore] public
string Someproperty {get; set;}
}

IV. Add Command

First, the simple operation of SQLite feel. 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 generate a command code using code snippet Propcmd:

Add user named Commandadduser,

await MVVMSidekick.Utilities.TaskExHelper.Yield ();
String path = Path.Combine (Windows.Storage.ApplicationData.Current.LocalFolder.Path, app.dbfilename);
using (var db = new Sqliteconnection (new SQLITEPLATFORMWINRT (), path))
{
db. Createtable<useritem> ();
Useritem item = new Useritem {address = "Beijing", age =, Someproperty = "haha", UserName = "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 the button on the page:

OK, now you can run a look at it.

The data can already be added and read.

Of course, the above code is very ugly, we'd better split the layer, the database to interact with the part of the show. Do not appear in the VM directly accessing the database code.

V. Reconstruction

Just now we used SQLite to connect the database directly in the command and then access the insert or read data, which is very ugly. It is best to extract the code that accesses the database and put it on a separate level.

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

This class uses a single example:

The Init method is then invoked at the time of app initialization to initialize the database. Where to initialize, we find the StartupFunctions.cs file in the startups directory, the initialization code can be written in the Runallconfig () method:

Then in the Services directory to add a DataService.cs file, where you can use the commonly used additions and deletions are realized, such as:

How to use this service, I am accustomed to using Mvvm-sidekick provided a lightweight IOC container, or in the Startups directory StartupFunctions.cs file Runallconfig () method, add such a line:

Then go back to the Mainpage_model.cs file and modify the command in the VM.

Inserting a user is much simpler:

Get all Users:

So the code on the VM layer is much less, to achieve reuse.

Vi. Other

I also wanted to write about how to read the database, but in the article http://www.cnblogs.com/h82258652/p/4802076.html the H-great God has described in detail how to find the database and how to read the database, here is no longer to repeat. In addition he mentioned that the project must be compiled into x86 or x64, not for any CPU, but also to be noted.

As for how to take the data out to bind to the ListView, it should be very simple.

And here's the base class for the single example, which is also attached here:

Add a win UWP use SQLite database in development

Environment configuration

1. Download and install SQLite

To use SQLite, the first is to download SQLite from sqlite.org, attach the link to the download page http://sqlite.org/download.html, and then select the UAP version:

Download is a VSIX installation package, after installation reboot VS, select Tools-> Extensions and Updates ...-> installed->, you will see SDKs for SQLite App Pla Tform, Description SQLite has been installed!!

2. Add a reference to SQLite in the project

Now, in our vs, we already have SQLite integration, add the reference to SQLite in the corresponding engineering directory, right-click References?> add Reference and choose Add to SQLite.

3. Add. NET support

If you want to use C # development, you need to further configure the project itself, which requires nuget help.

Right click on the project name, select Manage NuGet Packages ..., and then search Sqlitepcl,install.

After the installation is complete, a SQLITEPCL reference is given in project references.

The part of the Red Square that is circled in the picture, where the reference to the SQLite is above, and the following is a reference to the SQLITEPCL.

After this is done, you can use the SQLite development!! Of course, in the corresponding file, do not forget to add a using SQLITEPCL Oh!!

Defining SQL Statement Constants

In actual development, usually is the SQL statement Unified management, some developers in the use of DB process also like to use some contract to make coding more standardized, in order to more clearly, here only provide some of the most basic SQL statements for your reference.

private static String db_name = "sqlitesample.db";

private static String table_name = "sampletable";

private static String sql_create_table = "CREATE TABLE IF not EXISTS" + table_name + "(Key text,value TEXT);";

private static String Sql_query_value = "Select VALUE from" + table_name + "WHERE Key = (?);";

private static String Sql_insert = "INSERT into" + table_name + "VALUES (?,?);";

private static String sql_update = "UPDATE" + table_name + "SET Value =?" WHERE Key =? ";

private static String Sql_delete = "DELETE from" + table_name + "WHERE Key =?"

Create the first table

When you have defined some SQL commands, you can do a series of operations on the database, the most basic of course is create TABLE, the following gives the example code:

_connection = new Sqliteconnection (db_name);

using (var statement = _connection. Prepare (sql_create_table))

{

Statement. Step ();

}

Because the code that executes the SQL statement is roughly the same, don't dwell too much on it, give the sample code, where key and value are string variables.

INSERT

using (var statement = _connection. Prepare (Sql_insert))

{

Statement. Bind (1, key);

Statement. Bind (2, value);

Statement. Step ();

}

DELETE

using (var statement = _connection. Prepare (Sql_delete))

{

Statement. Bind (1, key);

Statement. Step ();

}

UPDATE

using (var statement = _connection. Prepare (sql_update))

{

Statement. Bind (1, value);

Statement. Bind (2, key);

Statement. Step ();

}

QUERY

using (var statement = _connection. Prepare (Sql_query_value))

{

Statement. Bind (1, key);

Sqliteresult result = statement. Step ();

if (Sqliteresult.row = = result)

{

Value = statement[0] as String;

}

}

It is worth noting that, in the process of executing step (), statement returns a Sqliteresult return value that, in traversing the returned results, determines Result is not sqliteresult.row can be, about sqliteresult specific content, please refer to sqlite.org documentation.

Other

Said half a day using SQLite development, then we create a database in the store where? The system defaults to the path: C:\Users\ (username) \appdata\local\packages\ (packagename) \ Localstate, which username of course do not need to explain the small, of course, is your username!packagename can be in the project directory inside the Package.appxmanifest file view, as shown:

Under this path, we can find a file named after our db_name, such as sqlitesample.db.

So, how can we look at this database, there are many third-party tools available, small series is recommended SQLite Expert, simple and friendly interface, easy to use, and has a personal version, completely free OH.

Conclusion

In UWP development, the use of SQLite is a very simple thing, with Windows powerful storage features and simple development interface, let us develop a wonderful UWP application bar!