Use of the lightweight local database SQLite in WINRT

Development environment: Windows 8.1

Development tools: Visual Studio Express for Windows

Introduction to SQLite Database

1.SQLite is a lightweight embedded database, developed using C + +, with a very wide range of

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

3. SQLite in Windows Phone is an unmanaged component

4. You can use it in the Windows runtime or in a C # project

5. In a C # project, you need to access it through the Windows runtime components

6. Unlike the "client/ Server side" mode of most databases, SQLite does not run in a separate process, but is embedded in the program as part of the program. Data manipulation in the same process has greater efficiency than communication between processes

7. The entire database (definition, tables, data, etc.) is stored in a single file, making it easier to migrate databases.

add SQLite to your appReferences

The SQLite project team has created SQLite for Windows Runtime and made ita vs extension, making it easier to use.

http://www.sqlite.org/download.html Download and install the following two plugins

After installation, you can add references in the project right-click Add C + + Runtime package and SQLite Package

After referencing SQLite is in the form of DLL, if we need to use the method encapsulated in the DLL we need to use the P/invoke method and ourselves in the application to re-encapsulate the workload is huge. It is recommended to use Sqlite-net. This is an open source lightweight library that allows the. Net platform to manipulate the SQLite database. We can get through NuGet:

After completion we will get two files:

This is not only through the P/invoke call a large number of DLLs in the method, and re-encapsulation, so that the majority of methods support asynchronous invocation, 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 a local folder to create a database connection
                     List <SQLiteConnection.ColumnInfo> PasswordCis = _Conn.GetTableInfo ("Password"); // It looks like it is not case sensitive, and _Conn.GetTableInfo ("password") works
                     if (PasswordCis.Count == 0)
                     {
                         _Conn.CreateTable <Password> (); // If there is no such database table, create it
                     }
                 }
                 return _Conn;
             }
         }
     } 

Reference:

The use of database system in WINRT (ii) use of part 1:sqlite

Basic use of wp8.1 SQLite

How to refer to the SQLite database in Windows Phone8

Use of the lightweight local database SQLite in WINRT