"Win10" uses the SQLite database in the app

In most applications, one of the unavoidable things to do is to set up such local data stores. Simple data storage We can do local data storage using LocalSettings or IsolatedStorageFile (Isolated storage) and so on. However, if the data is complex, or if there is an association relationship, this simple key-value storage method is not enough. This is the time to use a database for storage. When it comes to databases, small, lightweight file-based SQLite is ideal for this kind of use.

First, install the SQLite for Universal App Platform VSIX Extension

Open the menu bar tools-Extensions and updates, select the Online tab on the left, and in the upper right corner of the search box enter SQLite.

Install the above SQLite for Universal App Platform extension. After you wait for the installation to complete, restart Visual Studio.

Second, add reference 1 to the project, add a reference to SQLite

Create a new project (you can, of course, add it to an existing project, just a demo). After you wait for the new to complete, add a reference.

Follow the steps in the picture to find the SQLite for Universal App Platform, and tick and then press the OK button in the lower right corner.

2. Add Sqlite.net Reference

Alternatively, you can type: Install-package SQLITE.NET-PCL directly in the Package Manager console to install.

3. Make sure the citation is correct

Make sure the project is referencing both packages

Third, start coding 1, write the person model class for testing

 Public classperson{[PrimaryKey]//primary key. [AutoIncrement]//automatic growth.      Public intId {Get; Set; } [MaxLength (5)]//corresponds to the size of the database varchar.      Public stringName {Get; Set; }}

It is important to note that the namespace in which the Maxlengthattribute resides is SQLite.Net.Attributes, not the System.ComponentModel.DataAnnotations in the. Net framework.

2, writing the test page of the foreground Xaml code

<Pagex:class= "Sqlitedemo.mainpage"xmlns= "Http://schemas.microsoft.com/winfx/2006/xaml/presentation"xmlns:x= "Http://schemas.microsoft.com/winfx/2006/xaml">    <GridBackground="{ThemeResource Applicationpagebackgroundthemebrush}">        <StackPanelMargin= "+">            <TextBlockText= "Add"></TextBlock>            <TextBoxHeader= "First Name"x:name= "Txtaddname"></TextBox>            <ButtonContent= "Add into Database"Click= "btnAdd_Click"></Button>            <TextBlockText= "Query"Margin= "0,50,0,0"></TextBlock>            <ButtonContent= "Query All"Click= "Btngetall_click"></Button>        </StackPanel>    </Grid></Page>

3, write the test page of the background CS code

 Public Sealed Partial classmainpage:page{ PublicMainPage () { This.    InitializeComponent (); }    Private Async voidbtnAdd_Click (Objectsender, RoutedEventArgs e) {        stringName =Txtaddname.text; using(varconn =appdatabase.getdbconnection ()) {            //the person object that needs to be added.             varAddperson =NewPerson () {Name =name}; //The number of rows affected.             varCount =Conn.            Insert (Addperson); stringmsg = $"The Id of the new Person object is {addperson.id},name = {addperson.name}"; await NewMessagedialog (msg).        Showasync (); }    }    Private Async voidBtngetall_click (Objectsender, RoutedEventArgs e) {        using(varconn =appdatabase.getdbconnection ()) {StringBuilder msg=NewStringBuilder (); varDbperson = conn. Table<person>(); Msg. Appendline ($"a total of {dbperson.count ()} person objects in the database. "); foreach(varPersoninchDbperson) {msg. Appendline ($"Id:{person. Id};name:{person. Name}"); }            await NewMessagedialog (Msg. ToString ()).        Showasync (); }    }}

4, Write Appdatabase class

 Public Static classappdatabase{/// <summary>    ///the path to the database file, where Localfolder is used, and the database file is named Test.db. /// </summary>     Public ReadOnly Static stringDbPath = Path.Combine (ApplicationData.Current.LocalFolder.Path,"test.db");  Public Staticsqliteconnection getdbconnection () {//connect to the database and create an empty database if the database file does not exist.         varconn =NewSqliteconnection (NewSQLITEPLATFORMWINRT (), DbPath); //creates a table for the person model, ignoring the action if it already exists. Conn. Createtable<person>(); returnConn; }}

Four, the operation

Note: You must change the compilation of the project to x86 or x64, not any.

After the program is running, we add a few data first:

After adding these 4 data, we click the Query button.

There seems to be no problem. Wait, is our person model not labeled MaxLength 5 on the Name attribute? Why is Alexander still here?! (Alixander: How did I get laid?)

Consult the information in the garden to know:

New knowledge Point get! (The problem has never been noticed before ...) ）

So to judge the words, only before inserting data to judge.

V. View the stored database

First open the project's Package.appxmanifest file, and then select the Packaging tab.

Copy the string of the package name.

Open file Browser, path input%userprofile%\appdata\local\packages\

Then, in the upper-right corner, search for the package name that you just copied.

Open this folder.

Then open localstate This folder, inside can find a test.db file, this is our database.

Then we copy it and use the SQLite database software to open it, here I am using Sqlitestudio, hundred poison will have its official website.

By following the steps above, you will be able to see the table structure for the person class that we define.

And you can see the data we just inserted.

Vi. The conclusion 1, why write this blog?

Today saw Ms-uap wrote a blog about the use of SQLite, just remember before I have been also wanted to write a blog about how to use SQLite under WinRT. Pure pits, part of the content and Ms-uap write repetition, this article can be supplemented.

2, this article applies to the crowd

①, the use of SQLite people, not necessarily the developers of the UWP, because this sqlite.net library is PCL, can be used in other platforms (such as Winform) can also use, in the sqliteconnection of the constructor to pass the corresponding platform parameters.

②, ORM enthusiasts, hate SQL crowd. Like my SQL is not very good can only hold an ORM thigh. So if it's a SQL enthusiast, go to the one that Ms-uap wrote, and the SQLite library that used it was a C-like style, using traditional SQL statements.

"Win10" uses the SQLite database in the app