Lightweight local database SQLite used in WinRT, sqlitewinrt

Lightweight local database SQLite used in WinRT, sqlitewinrt

Development Environment: Windows 8.1

Development tools: Visual Studio Express 2013 for Windows

 

SQLite database Introduction

1. SQLite is a lightweight embedded database developed using C ++ and widely used

2. SQLite is a cross-platform database that supports Windows, Linux, Android, IOS, and Windows Phone Systems.

3. SQLite in Windows Phone is an unmanaged component

4. It can be used in Windows runtime or in C # projects.

5. Access to the C # project through the Windows runtime component

6. Unlike the "Client/Server" mode of most databases, SQLite does not run in an independent process, but is embedded into a program to become a part of the program. Data operations in the same process are more efficient than inter-process communication.

7. The entire database (definition, table, data, etc.) is stored in a single file, making database migration more convenient.

 

Add SQLite references to an application

The SQLite project team has prepared SQLite for Windows Runtime for us and made it into a VS extension, making it easier to use.

Http://www.sqlite.org/download.html download and install the following two plug-ins

After the installation is complete, you can right-click the project and add reference C ++ Runtime Package and SQLite Package

After the reference, sqlite exists in the form of dll. If we need to use the methods encapsulated in dll, we need to use the P/Invoke method and re-encapsulate it in the application. This is a huge workload. Sqlite-net is recommended. This is an open-source lightweight library that allows the. Net platform to operate sqlite databases. We can use nuget to obtain:

After that, we will get two files:

This not only calls a large number of dll methods through P/Invoke, but also re-encapsulates them so that most methods support asynchronous calls, which greatly facilitates our use.

Use of SQLite-net

    public static class PasswordController    {　　　　 public static List<Password> GetAll()        {            var query = Common.Conn.Table<Password>();            return query.ToList();        }

　　　　 public static Password Get(int id)        {            var query =Common.Conn.Table<Password>().Where(p => p.Id == id);            if (query != null)            {                return query.FirstOrDefault();                //return query.ToList()[0];            }            return null;        }

　　　　 public static void Edit(Password pw)        {            var query = Common.Conn.Table<Password>().Where(p => p.Id == pw.Id);            if (query != null)            {                Common.Conn.Update(pw);            }        }        public static void Add(Password pw)        {            Common.Conn.Insert(pw);        }
　　　　 public static void Delete(int id)        {            var query = Common.Conn.Table<Password>().Where(p => p.Id == id);            if (query != null)            {                Common.Conn.Delete(query.FirstOrDefault());            }        }    }

 

 

Static class Common {private const string DbName = "safebox. db"; // "db. sqlite ";
Private static SQLiteConnection _ Conn; public static SQLiteConnection Conn {get {if (_ Conn = null) {string DbPath = Path. combine (Windows. storage. applicationData. current. localFolder. path, DbName); _ Conn = new SQLiteConnection (DbPath); // specify the local folder to create a database connection List <SQLiteConnection. columnInfo> PasswordCis = _ Conn. getTableInfo ("Password"); // It looks case-insensitive, _ Conn. getTableInfo ("password") has the same effect as if (PasswordCis. count = 0) {_ Conn. createTable <Password> (); // if the database table does not exist, create }}return _ Conn ;}}}

 

Reference:

Use of the database system in WinRT (ii) Part 1: Use of SQLite

Basic use of wp8.1 SQLite

How to reference SQLite database in Windows Phone8