在 UWP 應用中使用 SQLite 數據庫Use a SQLite database in a UWP app
06/26/2020
本文內容
可以使用 SQLite 在用戶設備上的輕量級數據庫中存儲和檢索數據。You can use SQLite to store and retrieve data in a light-weight database on the user's device. 本指南演示如何執行該操作。This guide shows you how.
使用 SQLite 進行本地存儲一些好處Some benefits of using SQLite for local storage
??SQLite 具有輕量和獨立的特點。SQLite is light-weight and self-contained. 它是沒有其他任何依賴項的代碼庫。It's a code library without any other dependencies. 無需進行任何配置。There's nothing to configure.
??沒有數據庫服務器。There's no database server. 客戶端和服務器在同一進程中運行。The client and the server run in the same process.
??SQLite 位于公共域中，因此你可以自由地使用它并將它與應用一起分配。SQLite is in the public domain so you can freely use and distribute it with your app.
??SQLite 可跨平臺和體系結構工作。SQLite works across platforms and architectures.
可在此處了解有關 SQLite 的詳細信息。You can read more about SQLite here.
選擇抽象層Choose an abstraction layer
我們建議使用由 Microsoft 構建的 Entity Framework Core 或開源 SQLite 庫。We recommend that you use either the Entity Framework Core or the open-source SQLite library built by Microsoft.
Entity Framework CoreEntity Framework Core
Entity Framework (EF) 是一個對象關系映射程序，可用于使用特定于域的對象處理關系數據。Entity Framework (EF) is an object-relational mapper that you can use to work with relational data by using domain-specific objects. 如果已使用此框架處理其他 .NET 應用中的數據，則可以將該代碼遷移到 UWP 應用，它將處理對連接字符串的相應更改。If you've already used this framework to work with data in other .NET apps, you can migrate that code to a UWP app and it will work with appropriate changes to the connection string.
SQLite 庫SQLite library
The Microsoft.Data.Sqlite library implements the interfaces in the System.Data.Common namespace. Microsoft 將主動保留這些實現，它們提供了圍繞低級別本機 SQLite API 的直觀的包裝器。Microsoft actively maintains these implementations, and they provide an intuitive wrapper around the low-level native SQLite API.
本指南的其余部分將幫助你使用此庫。The rest of this guide helps you to use this library.
將解決方案設置為使用 Microsoft.Data.SQlite 庫Set up your solution to use the Microsoft.Data.SQlite library
我們將從基本 UWP 項目入手，添加類庫，然后安裝合適的 Nuget 包。We'll start with a basic UWP project, add a class library, and then install the appropriate Nuget packages.
添加到解決方案的類庫的類型以及安裝的特定程序包取決于應用面向的最低版本的 Windows SDK。The type of class library that you add to your solution, and the specific packages that you install depends on the minimum version of the Windows SDK that your app targets. 可以在 UWP 項目的屬性頁中找到該信息。You can find that information in the properties page of your UWP project.
根據 UWP 項目面向的最低版本的 Windows SDK，使用以下章節之一。Use one of the following sections depending on the minimum version of the Windows SDK that your UWP project targets.
項目的最低版本沒有將 Fall Creators Update 作為目標The minimum version of your project does not target the Fall Creators Update
如果使用的是 Visual Studio 2015，請單擊“幫助”->“關于 Microsoft Visual Studio”。If you're using Visual Studio 2015, click Help->About Microsoft Visual Studio. 然后，在已安裝程序的列表中，確保你具有 NuGet 包管理器版本 3.5 或更高版本。Then in the list of installed programs, make sure that you have NuGet package manager version of 3.5 or higher. 如果版本號較低，請安裝此處提供的更高版本的 NuGet。If your version number is lower than that, install a later version of NuGet here. 在該頁面上，你將發現所有版本的 Nuget 都在 Visual Studio 2015 標題下方列出。On that page, you'll find all of the versions of Nuget listed beneath the Visual Studio 2015 heading.
接下來，將類庫添加到解決方案。Next, add class library to your solution. 你不必使用類庫來包含你的數據訪問代碼，但我們會使用一個我們的示例。You don't have to use a class library to contain your data access code, but we'll use one our example. 我們將庫命名為 DataAccessLibrary，并將庫中的類命名為 DataAccess。We'll name the library DataAccessLibrary and we'll name the class in the library to DataAccess.
右鍵單擊該解決方法，然后單擊“管理解決方案的 NuGet 包”。Right-click the solution, and then click Manage NuGet Packages for Solution.
如果使用的是 Visual Studio 2015，請選擇“已安裝”選項卡，并確保 Microsoft.NETCore.UniversalWindowsPlatform 程序包的版本號為 5.2.2 或更高。If you're using Visual Studio 2015, Choose the Installed tab, and make sure that the version number of the Microsoft.NETCore.UniversalWindowsPlatform package is 5.2.2 or higher.
如果不是，請將包更新到更新的版本。If it isn't, update the package to a newer version.
選擇“瀏覽”選項卡，然后搜索“Microsoft.Data.SQLite”程序包。Choose the Browse tab, and search for the Microsoft.Data.SQLite package. 安裝該程序包的版本 1.1.1(或更低)。Install version 1.1.1 (or lower) of that package.
你最低版本的項目已鎖定 Fall Creators UpdateThe minimum version of your project targets the Fall Creators Update
將 UWP 項目的最低版本升級到 Fall Creators Update 有幾個好處。There's a couple of benefits to raising the minimum version of your UWP project to the Fall Creators update.
首先，你可以使用 .NET Standard 2.0 庫而不是常規的類庫。First off, you can use .NET Standard 2.0 libraries instead of regular class libraries. 這意味著你可以將數據訪問代碼與任何其他基于 .NET 的應用(如 WPF、Windows 窗體、Android、iOS 或 ASP.NET 應用)共享。That means that you can share your data access code with any other .NET-based app such as a WPF, Windows Forms, Android, iOS, or ASP.NET app.
其次，應用不需將 SQLite 庫打包。Secondly, your app does not have to package SQLite libraries. 相反，應用可以使用隨 Windows 一起安裝的 SQLite 版本。Instead, your app can use the version of SQLite that comes installed with Windows. 這將帶來幾個方面的好處。This helps you in a few ways.
??減小了應用程序的大小，因為你不必下載 SQLite 二進制文件然后將其打包為應用程序的一部分。Reduces the size of your application because you don't have to download the SQLite binary, and then package it as part of your application.
??如果 SQLite 發布了針對 SQLite 中的 bug 和安全漏洞的重要修復程序，你就不必向用戶推送你的應用的新版本。Prevents you from having to push a new version of your app to users in the event that SQLite publishes critical fixes to bugs and security vulnerabilities in SQLite. Windows 版本的 SQLite 由 Microsoft 與 SQLite.org 協作維護。The Windows version of SQLite is maintained by Microsoft in coordination with SQLite.org.
??應用加載時間可能更短，因為 SDK 版本的 SQLite 很有可能已被加載到內存中。App load time has the potential to be faster because most likely, the SDK version of SQLite will already be loaded into memory.
讓我們開始向你的解決方案添加 .NET Standard 2.0 類庫。Lets start by adding a .NET Standard 2.0 class library to your solution. 你不必使用類庫來包含你的數據訪問代碼，但我們會使用一個我們的示例。It's not necessary that you use a class library to contain your data access code, but we'll use one our example. 我們將庫命名為 DataAccessLibrary，并將庫中的類命名為 DataAccess。We'll name the library DataAccessLibrary and we'll name the class in the library to DataAccess.
右鍵單擊該解決方法，然后單擊“管理解決方案的 NuGet 包”。Right-click the solution, and then click Manage NuGet Packages for Solution.
備注
如果希望 .NET Standard 類庫能夠訪問 UWP 應用的應用文件夾和圖像資產，則需要將其標記為其屬性中的 EmbeddedResource 和 CopyAlways 。If you want your .NET Standard class library to be able to access app folders and image assets of your UWP app, you will need to mark it as EmbeddedResource and CopyAlways in its properties.
此時，你已經有一個選擇。At this point, you have a choice. 你可以使用 Windows 附帶的 SQLite 版本，如果你出于某種原因要使用特定版本的 SQLite，則可以在程序中包含 SQLite 庫。You can use the version of SQLite that is included with Windows or if you have some reason to use a specific version of SQLite, you can include the SQLite library in your package.
讓我們開始演示如何使用 Windows 附帶的 SQLite 版本。Let's start with how you use the version of SQLite that included with Windows.
使用隨 Windows 一起安裝的 SQLite 版本To use the version of SQLite that is installed with Windows
選擇“瀏覽”選項卡，搜索“Microsoft.Data.SQLite.core”程序包，然后安裝它。Choose the Browse tab, and search for the Microsoft.Data.SQLite.core package, and then install it.
搜索“SQLitePCLRaw.bundle_winsqlite3”程序包，然后僅將它安裝到應用程序中的 UWP 項目。Search for the SQLitePCLRaw.bundle_winsqlite3 package, and then install it only to the UWP project in your solution.
將 SQLite 包含在你的應用中To include SQLite with your app
你不必執行此操作。You don't have to do this. 但如果你出于某種原因要將特定版本的 SQLite 包含在你的應用中，請選擇“瀏覽”選項卡，然后搜索“Microsoft.Data.SQLite”程序包。But if you have a reason to include a specific version of SQLite with your app, choose the Browse tab, and search for the Microsoft.Data.SQLite package. 安裝該程序包的版本 2.0(或更低)。Install version 2.0 (or lower) of that package.
在 SQLite 數據庫中添加和檢索數據Add and retrieve data in a SQLite database
我們將執行以下操作：We'll do these things:
1??準備數據訪問類。Prepare the data access class.
2??初始化 SQLite 數據庫。Initialize the SQLite database.
3??將數據插入到 SQLite 數據庫。Insert data into the SQLite database.
4??從 SQLite 數據庫檢索數據。Retrieve data from the SQLite database.
5??添加基本用戶界面。Add a basic user interface.
準備數據訪問類Prepare the data access class
從 UWP 項目中，添加對解決方案中的 DataAccessLibrary 項目的引用。From your UWP project, add a reference to the DataAccessLibrary project in your solution.
在 UWP 項目中，將以下 using 語句添加到 App.xaml.cs 和 MainPage.xaml.cs 文件。Add the following using statement to the App.xaml.cs and MainPage.xaml.cs files in your UWP project.
using DataAccessLibrary;
在 DataAccessLibrary 解決方案中打開 DataAccess 類并將其設為靜態。Open the DataAccess class in your DataAccessLibrary solution and make that class static.
備注
盡管我們的示例將數據訪問代碼放在靜態類中，但這只是一個設計選擇，并且是全憑自愿。While our example will place data access code in a static class, it's just a design choice and is completely optional.
namespace DataAccessLibrary
{
public static class DataAccess
{
}
}
將以下 using 語句添加到此文件頂部。Add the following using statements to the top of this file.
using Microsoft.Data.Sqlite;
using System.Collections.Generic;
初始化 SQLite 數據庫Initialize the SQLite database
向 DataAccess 類添加一個初始化 SQLite 數據庫的方法。Add a method to the DataAccess class that initializes the SQLite database.
public async static void InitializeDatabase()
{
await ApplicationData.Current.LocalFolder.CreateFileAsync("sqliteSample.db", CreationCollisionOption.OpenIfExists);
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (SqliteConnection db =
new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
String tableCommand = "CREATE TABLE IF NOT " +
"EXISTS MyTable (Primary_Key INTEGER PRIMARY KEY, " +
"Text_Entry NVARCHAR(2048) NULL)";
SqliteCommand createTable = new SqliteCommand(tableCommand, db);
createTable.ExecuteReader();
}
}
此代碼將創建 SQLite 數據庫并將其存儲在應用程序的本地數據存儲區中。This code creates the SQLite database and stores it in the application's local data store.
在此示例中，我們將數據庫命名為 sqlliteSample.db，但你可以使用任何想要的名稱，只要你在你實例化的所有 SqliteConnection 對象中使用該名稱。In this example, we name the database sqlliteSample.db but you can use whatever name you want as long as you use that name in all SqliteConnection objects that you instantiate.
在 UWP 項目的 App.xaml.cs 文件的構造函數中，調用 DataAccess 類的 InitializeDatabase 方法。In the constructor of the App.xaml.cs file of your UWP project, call the InitializeDatabase method of the DataAccess class.
public App()
{
this.InitializeComponent();
this.Suspending += OnSuspending;
DataAccess.InitializeDatabase();
}
將數據插入到 SQLite 數據庫Insert data into the SQLite database
向 DataAccess 類添加一個將數據插入到 SQLite 數據庫的方法。Add a method to the DataAccess class that inserts data into the SQLite database. 此代碼在查詢中使用參數以阻止 SQL 注入攻擊。This code uses parameters in the query to prevent SQL injection attacks.
public static void AddData(string inputText)
{
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (SqliteConnection db =
new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
SqliteCommand insertCommand = new SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = "INSERT INTO MyTable VALUES (NULL, @Entry);";
insertCommand.Parameters.AddWithValue("@Entry", inputText);
insertCommand.ExecuteReader();
db.Close();
}
}
從 SQLite 數據庫檢索數據Retrieve data from the SQLite database
添加從 SQLite 數據庫獲取數據行的方法。Add a method that gets rows of data from a SQLite database.
public static List GetData()
{
List entries = new List();
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (SqliteConnection db =
new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
SqliteCommand selectCommand = new SqliteCommand
("SELECT Text_Entry from MyTable", db);
SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
entries.Add(query.GetString(0));
}
db.Close();
}
return entries;
}
Read 方法將向前瀏覽返回的數據的行。The Read method advances through the rows of returned data. 如果有剩下的行，它將返回 true，否則返回 false。It returns true if there are rows left, otherwise it returns false.
GetString 方法返回字符串形式的指定列的值。The GetString method returns the value of the specified column as a string. 它將接受一個整數值，該值表示所需的數據的從零開始的列序號。It accepts an integer value that represents the zero-based column ordinal of the data that you want. You can use similar methods such as GetDataTime and GetBoolean. 請根據列包含的數據的類型選擇方法。Choose a method based on what type of data the column contains.
在此例子中，序號參數并不重要，因為我們選擇了單個列中的所有條目。The ordinal parameter isn't as important in this example because we are selecting all of the entries in a single column. 但是，如果多個列是你的查詢的一部分，請使用序號值獲取你要從中拉取數據的列。However, if multiple columns are part of your query, use the ordinal value to obtain the column you want to pull data from.
添加基本用戶界面Add a basic user interface
在 UWP 項目的 MainPage.xaml 文件中，添加以下 XAML。In the MainPage.xaml file of the UWP project, add the following XAML.
Add
此基本用戶界面為用戶提供了 TextBox，可用于鍵入我們將添加到 SQLite 數據庫的字符串。This basic user interface gives the user a TextBox that they can use to type a string that we'll add to the SQLite database. 我們要將此 UI 中的 Button 連接到事件處理程序，后者將從 SQLite 數據庫檢索數據然后在 ListView 中顯示數據。We'll connect the Button in this UI to an event handler that will retrieve data from the SQLite database and then show that data in the ListView.
在 MainPage.xaml.cs 文件中，添加以下處理程序。In the MainPage.xaml.cs file, add the following handler. 這是我們關聯到 UI 中的 Button 的 Click事件的方法。This is the method that we associated with the Click event of the Button in the UI.
private void AddData(object sender, RoutedEventArgs e)
{
DataAccess.AddData(Input_Box.Text);
Output.ItemsSource = DataAccess.GetData();
}
完成了。That's it. 探索 Microsoft.Data.Sqlite 以了解 SQLite 數據庫的其他功能。Explore the Microsoft.Data.Sqlite to see what other things you can do with your SQLite database. 查看下面的鏈接，了解在 UWP 應用中使用數據的其他方法。Check out the links below to learn about other ways to use data in your UWP app.
后續步驟Next steps
將應用直接連接到 SQL Server 數據庫Connect your app directly to a SQL Server database
在跨不同平臺的不同應用之間共享代碼Share code between different apps across different platforms
使用 Azure SQL 后端添加大綱/細節頁面Add master detail pages with Azure SQL back ends
