[Win10] Use SQLite database in applications, win10sqlite

[Win10] Use SQLite database in applications, win10sqlite

In most applications, setting up local data storage is inevitable. For simple data storage, we can use LocalSettings or IsolatedStorageFile for local data storage. However, this simple key-value storage method is not enough if the data is complex or associated. In this case, the database is used for storage. When it comes to databases, small and lightweight file-based SQLite is very suitable for this scenario.

1. Install SQLite for Universal App Platform VSIX Extension

Open the tool-extension and update in the menu bar, select the online tab on the left, and enter SQLite in the search box in the upper right corner.

Public class Person {[PrimaryKey] // primary key. [AutoIncrement] // automatic growth. Public int Id {get; set;} [MaxLength (5)] // corresponds to the database varchar size. Public string Name {get; set ;}}

Note that the namespace of MaxLengthAttribute is SQLite. Net. Attributes, rather than System. ComponentModel. DataAnnotations In the. Net Framework.

2. Compile the front-end Xaml code on the test page.

<Page x: Class = "SqliteDemo. mainPage "xmlns =" http://schemas.microsoft.com/winfx/2006/xaml/presentation "xmlns: x = "http://schemas.microsoft.com/winfx/2006/xaml"> <Grid Background = "{ThemeResource ApplicationPageBackgroundThemeBrush}"> <StackPanel Margin = "100"> <TextBlock Text = "add"> </TextBlock> <TextBox Header = "name" x: name = "txtAddName"> </TextBox> <Button Content = "add to Database" Click = "BtnAdd_Click"> </Button> <TextBlock Text = "query" Margin = "0, 50, 0, 0 "> </TextBlock> <Button Content =" query all "Click =" BtnGetAll_Click "> </Button> </StackPanel> </Grid> </Page>

3. Compile the background cs code of the test page

Public sealed partial class MainPage: Page {public MainPage () {this. initializeComponent ();} private async void BtnAdd_Click (object sender, RoutedEventArgs e) {string name = txtAddName. text; using (var conn = AppDatabase. getDbConnection () {// The Person object to be added. Var addPerson = new Person () {Name = name}; // Number of affected rows. Var count = conn. insert (addPerson); string msg = $ "the Id of the newly added Person object is {addPerson. id}, Name is {addPerson. name} "; await new MessageDialog (msg ). showAsync () ;}} private async void BtnGetAll_Click (object sender, RoutedEventArgs e) {using (var conn = AppDatabase. getDbConnection () {StringBuilder msg = new StringBuilder (); var dbPerson = conn. table <Person> (); msg. appendLine ($ "Total {dbPerson. count ()} Person objects. "); Foreach (var person in dbPerson) {msg. appendLine ($ "Id: {person. id}; Name: {person. name} ");} await new MessageDialog (msg. toString ()). showAsync ();}}}

4. Compile the AppDatabase class

Public static class AppDatabase {// <summary> // path of the database file. LocalFolder is used here, and the database file name is test. db. /// </Summary> public readonly static string DbPath = Path. combine (ApplicationData. current. localFolder. path, "test. db "); public static SQLiteConnection GetDbConnection () {// connect to the database. If the database file does not exist, create an empty database. Var conn = new SQLiteConnection (new SQLitePlatformWinRT (), DbPath); // create a table corresponding to the Person model. If the table already exists, ignore this operation. Conn. CreateTable <Person> (); return conn ;}}

Iv. Run

Note: You must change the project compilation to x86 or x64 instead of Any.

After the program runs, add the following data:

After adding the four data items, click the query button.

It seems that there is no problem. Slow down. Didn't our Person model mark MaxLength as 5 on the Name attribute? Why is the Emperor still there ?! (Alexander A: How can I lay down my gun)

Check the information in the garden and learn:

New knowledge point GET! (I have never noticed this problem before ......)

Therefore, you can only judge before inserting data.

5. view the stored database

First open the Package. appxmanifest file of the project, and then select the packaging tab.

Copy the package name string.

Open the file browser and enter % USERPROFILE % \ AppData \ Local \ Packages \

Search for the copied package name in the upper right corner.

Open this folder.

Open the LocalState folder and you will find a file named test. db. This is our database.

Then we will copy it and open it with the SQLite database software. Here I am using SQLiteStudio, and its official website will be available in a bit of virus.

Follow the steps above to see the table structure of the Person class we have defined.

We can also see the data we just inserted.

Vi. Conclusion 1. Why do I write this blog?

Today I saw the MS-UAP wrote an article about the use of SQLite blog, just think of it before I always want to write a blog about how to use SQLite WinRT. Pure fill, part of the content and the repeated MS-UAP write, this article can be used as a supplement.

2. Applicable audience

①. Not necessarily UWP developers who have user requirements for SQLite. net Library is PCL and can be used on other platforms (such as Winform). You can pass the parameters of the corresponding platform in the SQLiteConnection constructor.

2. ORM fans and SQL users who hate SQL. An SQL statement like me can only hold the thigh of ORM. So if it is SQL enthusiasts, please go to the MS-UAP to write that article, the use of SQLite library is similar to the C style, use the traditional SQL statements.