在 UWP 應用程式中使用 SQLite 資料庫Use a SQLite database in a UWP app

您可以使用 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

我們建議您使用 Entity Framework Core 或 Microsoft 所建置的開放原始碼 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.

若要試試看,請參閱在使用新資料庫的通用 Windows 平台 (UWP) 上開始使用 EF CoreTo try it out, see Getting started with EF Core on Universal Windows Platform (UWP) with a New Database.

SQLite 程式庫SQLite library

Microsoft.Data.Sqlite 程式庫會在 System.Data.Common 命名空間中實作介面。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.

Windows SDK 的最低版本

根據做為您 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,並將程式庫中的類別命名為 DataAccessWe'll name the library DataAccessLibrary and we'll name the class in the library to DataAccess.

螢幕擷取畫面:顯示 [新增專案] 對話方塊,其中顯示已選取 [已安裝] > [Visual C#] > [Windows 通用],並醒目提示 [類別庫] 選項。

以滑鼠右鍵按一下解決方案,然後按一下 [管理解決方案的 NuGet 套件]。Right-click the solution, and then click Manage NuGet Packages for Solution.

[方案總管] 面板的螢幕擷取畫面,已使用滑鼠右鍵按一下專案,且醒目提示 [管理方案的 NuGet 套件] 選項。

如果您使用 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.

.NETCore 版本

如果不是的話,請將套件更新到較新版本。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.

[Microsoft Data SQLite] 對話方塊的螢幕擷取畫面,並指出 [版本] 文字欄位。

繼續進行本指南中的在 SQLite 資料庫中新增和擷取資料一節。Move onto the Add and retrieve data in a SQLite database section of this guide.

您專案的最低版本是以 Fall Creators Update 為目標The 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 Forms、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 中錯誤和安全性弱點的重大修正時。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,並將程式庫中的類別命名為 DataAccessWe'll name the library DataAccessLibrary and we'll name the class in the library to DataAccess.

螢幕擷取畫面:顯示 [新增專案] 對話方塊,其中顯示已選取 [已安裝] > [Visual C#] > [.NET Standard],並醒目提示 [類別庫] 選項。

以滑鼠右鍵按一下解決方案,然後按一下 [管理解決方案的 NuGet 套件]。Right-click the solution, and then click Manage NuGet Packages for Solution.

[方案總管] 面板的另一個螢幕擷取畫面,已使用滑鼠右鍵按一下專案,且醒目提示 [管理 NuGet 套件] 選項。

注意

如果您希望 .NET Standard 類別庫能夠存取 UWP 應用程式的應用程式資料夾和映射資產,則必須在其 [屬性] 中將其標示為 EmbeddedResourceCopyAlwaysIf 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.

SQLite Core 套件

搜尋 SQLitePCLRaw.bundle_winsqlite3 套件,然後只將它安裝到您解決方案中 UWP 專案。Search for the SQLitePCLRaw.bundle_winsqlite3 package, and then install it only to the UWP project in your solution.

SQLite PCL Raw 套件

在您的應用程式中包含 SQLiteTo 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.

[Microsoft Data SQLite] 對話方塊的螢幕擷取畫面,顯示已選取 [最新穩定 2.0.0 版]。

在 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.

資料存取類別庫

將下列 using 陳述式新增到您 UWP 專案中的 App.xaml.csMainPage.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<String> GetData()
{
    List<String> entries = new List<string>();

   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,否則會傳回 falseIt 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. 您可以使用類似的方法,例如 GetDataTimeGetBooleanYou 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.

<Grid Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
    <StackPanel>
        <TextBox Name="Input_Box"></TextBox>
        <Button Click="AddData">Add</Button>
        <ListView Name="Output">
            <ListView.ItemTemplate>
                <DataTemplate>
                    <TextBlock Text="{Binding}"/>
                </DataTemplate>
            </ListView.ItemTemplate>
        </ListView>
    </StackPanel>
</Grid>

這個基本使用者介面會提供 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 中 ButtonClick 事件產生關聯的方法。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

請參閱在 UWP app 中使用 SQL Server 資料庫See Use a SQL Server database in a UWP app.

在不同平台的不同應用程式之間共用程式碼Share code between different apps across different platforms

請參閱在桌面應用程式與 UWP 之間共用程式碼See Share code between desktop and UWP.

在 Azure SQL 後端新增主要詳細資料頁面Add master detail pages with Azure SQL back ends

請參閱客戶訂單資料庫範例 (英文)。See Customer Orders Database sample.