uwp连接mysql数据库_在 UWP 应用中使用 SQLite 数据库
在 UWP 應(yīng)用中使用 SQLite 數(shù)據(jù)庫Use a SQLite database in a UWP app
06/26/2020
本文內(nèi)容
可以使用 SQLite 在用戶設(shè)備上的輕量級數(shù)據(jù)庫中存儲(chǔ)和檢索數(shù)據(jù)。You can use SQLite to store and retrieve data in a light-weight database on the user's device. 本指南演示如何執(zhí)行該操作。This guide shows you how.
使用 SQLite 進(jìn)行本地存儲(chǔ)一些好處Some benefits of using SQLite for local storage
??SQLite 具有輕量和獨(dú)立的特點(diǎn)。SQLite is light-weight and self-contained. 它是沒有其他任何依賴項(xiàng)的代碼庫。It's a code library without any other dependencies. 無需進(jìn)行任何配置。There's nothing to configure.
??沒有數(shù)據(jù)庫服務(wù)器。There's no database server. 客戶端和服務(wù)器在同一進(jìn)程中運(yùn)行。The client and the server run in the same process.
??SQLite 位于公共域中，因此你可以自由地使用它并將它與應(yīng)用一起分配。SQLite is in the public domain so you can freely use and distribute it with your app.
??SQLite 可跨平臺(tái)和體系結(jié)構(gòu)工作。SQLite works across platforms and architectures.
可在此處了解有關(guān) SQLite 的詳細(xì)信息。You can read more about SQLite here.
選擇抽象層Choose an abstraction layer
我們建議使用由 Microsoft 構(gòu)建的 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) 是一個(gè)對象關(guān)系映射程序，可用于使用特定于域的對象處理關(guān)系數(shù)據(jù)。Entity Framework (EF) is an object-relational mapper that you can use to work with relational data by using domain-specific objects. 如果已使用此框架處理其他 .NET 應(yīng)用中的數(shù)據(jù)，則可以將該代碼遷移到 UWP 應(yīng)用，它將處理對連接字符串的相應(yīng)更改。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 將主動(dòng)保留這些實(shí)現(xiàn)，它們提供了圍繞低級別本機(jī) 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.
將解決方案設(shè)置為使用 Microsoft.Data.SQlite 庫Set up your solution to use the Microsoft.Data.SQlite library
我們將從基本 UWP 項(xiàng)目入手，添加類庫，然后安裝合適的 Nuget 包。We'll start with a basic UWP project, add a class library, and then install the appropriate Nuget packages.
添加到解決方案的類庫的類型以及安裝的特定程序包取決于應(yīng)用面向的最低版本的 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 項(xiàng)目的屬性頁中找到該信息。You can find that information in the properties page of your UWP project.
根據(jù) UWP 項(xiàng)目面向的最低版本的 Windows SDK，使用以下章節(jié)之一。Use one of the following sections depending on the minimum version of the Windows SDK that your UWP project targets.
項(xiàng)目的最低版本沒有將 Fall Creators Update 作為目標(biāo)The minimum version of your project does not target the Fall Creators Update
如果使用的是 Visual Studio 2015，請單擊“幫助”->“關(guān)于 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. 如果版本號(hào)較低，請安裝此處提供的更高版本的 NuGet。If your version number is lower than that, install a later version of NuGet here. 在該頁面上，你將發(fā)現(xiàn)所有版本的 Nuget 都在 Visual Studio 2015 標(biāo)題下方列出。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. 你不必使用類庫來包含你的數(shù)據(jù)訪問代碼，但我們會(huì)使用一個(gè)我們的示例。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，請選擇“已安裝”選項(xiàng)卡，并確保 Microsoft.NETCore.UniversalWindowsPlatform 程序包的版本號(hào)為 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.
選擇“瀏覽”選項(xiàng)卡，然后搜索“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.
你最低版本的項(xiàng)目已鎖定 Fall Creators UpdateThe minimum version of your project targets the Fall Creators Update
將 UWP 項(xiàng)目的最低版本升級到 Fall Creators Update 有幾個(gè)好處。There's a couple of benefits to raising the minimum version of your UWP project to the Fall Creators update.
首先，你可以使用 .NET Standard 2.0 庫而不是常規(guī)的類庫。First off, you can use .NET Standard 2.0 libraries instead of regular class libraries. 這意味著你可以將數(shù)據(jù)訪問代碼與任何其他基于 .NET 的應(yīng)用(如 WPF、Windows 窗體、Android、iOS 或 ASP.NET 應(yīng)用)共享。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.
其次，應(yīng)用不需將 SQLite 庫打包。Secondly, your app does not have to package SQLite libraries. 相反，應(yīng)用可以使用隨 Windows 一起安裝的 SQLite 版本。Instead, your app can use the version of SQLite that comes installed with Windows. 這將帶來幾個(gè)方面的好處。This helps you in a few ways.
??減小了應(yīng)用程序的大小，因?yàn)槟悴槐叵螺d SQLite 二進(jìn)制文件然后將其打包為應(yīng)用程序的一部分。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 發(fā)布了針對 SQLite 中的 bug 和安全漏洞的重要修復(fù)程序，你就不必向用戶推送你的應(yīng)用的新版本。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 協(xié)作維護(hù)。The Windows version of SQLite is maintained by Microsoft in coordination with SQLite.org.
??應(yīng)用加載時(shí)間可能更短，因?yàn)?SDK 版本的 SQLite 很有可能已被加載到內(nèi)存中。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. 你不必使用類庫來包含你的數(shù)據(jù)訪問代碼，但我們會(huì)使用一個(gè)我們的示例。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 應(yīng)用的應(yīng)用文件夾和圖像資產(chǎn)，則需要將其標(biāo)記為其屬性中的 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.
此時(shí)，你已經(jīng)有一個(gè)選擇。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
選擇“瀏覽”選項(xiàng)卡，搜索“Microsoft.Data.SQLite.core”程序包，然后安裝它。Choose the Browse tab, and search for the Microsoft.Data.SQLite.core package, and then install it.
搜索“SQLitePCLRaw.bundle_winsqlite3”程序包，然后僅將它安裝到應(yīng)用程序中的 UWP 項(xiàng)目。Search for the SQLitePCLRaw.bundle_winsqlite3 package, and then install it only to the UWP project in your solution.
將 SQLite 包含在你的應(yīng)用中To include SQLite with your app
你不必執(zhí)行此操作。You don't have to do this. 但如果你出于某種原因要將特定版本的 SQLite 包含在你的應(yīng)用中，請選擇“瀏覽”選項(xiàng)卡，然后搜索“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 數(shù)據(jù)庫中添加和檢索數(shù)據(jù)Add and retrieve data in a SQLite database
我們將執(zhí)行以下操作：We'll do these things:
1??準(zhǔn)備數(shù)據(jù)訪問類。Prepare the data access class.
2??初始化 SQLite 數(shù)據(jù)庫。Initialize the SQLite database.
3??將數(shù)據(jù)插入到 SQLite 數(shù)據(jù)庫。Insert data into the SQLite database.
4??從 SQLite 數(shù)據(jù)庫檢索數(shù)據(jù)。Retrieve data from the SQLite database.
5??添加基本用戶界面。Add a basic user interface.
準(zhǔn)備數(shù)據(jù)訪問類Prepare the data access class
從 UWP 項(xiàng)目中，添加對解決方案中的 DataAccessLibrary 項(xiàng)目的引用。From your UWP project, add a reference to the DataAccessLibrary project in your solution.
在 UWP 項(xiàng)目中，將以下 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 類并將其設(shè)為靜態(tài)。Open the DataAccess class in your DataAccessLibrary solution and make that class static.
備注
盡管我們的示例將數(shù)據(jù)訪問代碼放在靜態(tài)類中，但這只是一個(gè)設(shè)計(jì)選擇，并且是全憑自愿。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 數(shù)據(jù)庫Initialize the SQLite database
向 DataAccess 類添加一個(gè)初始化 SQLite 數(shù)據(jù)庫的方法。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();
}
}
此代碼將創(chuàng)建 SQLite 數(shù)據(jù)庫并將其存儲(chǔ)在應(yīng)用程序的本地?cái)?shù)據(jù)存儲(chǔ)區(qū)中。This code creates the SQLite database and stores it in the application's local data store.
在此示例中，我們將數(shù)據(jù)庫命名為 sqlliteSample.db，但你可以使用任何想要的名稱，只要你在你實(shí)例化的所有 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 項(xiàng)目的 App.xaml.cs 文件的構(gòu)造函數(shù)中，調(diào)用 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();
}
將數(shù)據(jù)插入到 SQLite 數(shù)據(jù)庫Insert data into the SQLite database
向 DataAccess 類添加一個(gè)將數(shù)據(jù)插入到 SQLite 數(shù)據(jù)庫的方法。Add a method to the DataAccess class that inserts data into the SQLite database. 此代碼在查詢中使用參數(shù)以阻止 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 數(shù)據(jù)庫檢索數(shù)據(jù)Retrieve data from the SQLite database
添加從 SQLite 數(shù)據(jù)庫獲取數(shù)據(jù)行的方法。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 方法將向前瀏覽返回的數(shù)據(jù)的行。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. 它將接受一個(gè)整數(shù)值，該值表示所需的數(shù)據(jù)的從零開始的列序號(hào)。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. 請根據(jù)列包含的數(shù)據(jù)的類型選擇方法。Choose a method based on what type of data the column contains.
在此例子中，序號(hào)參數(shù)并不重要，因?yàn)槲覀冞x擇了單個(gè)列中的所有條目。The ordinal parameter isn't as important in this example because we are selecting all of the entries in a single column. 但是，如果多個(gè)列是你的查詢的一部分，請使用序號(hào)值獲取你要從中拉取數(shù)據(jù)的列。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 項(xiàng)目的 MainPage.xaml 文件中，添加以下 XAML。In the MainPage.xaml file of the UWP project, add the following XAML.
Add
此基本用戶界面為用戶提供了 TextBox，可用于鍵入我們將添加到 SQLite 數(shù)據(jù)庫的字符串。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 數(shù)據(jù)庫檢索數(shù)據(jù)然后在 ListView 中顯示數(shù)據(jù)。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. 這是我們關(guān)聯(lián)到 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 數(shù)據(jù)庫的其他功能。Explore the Microsoft.Data.Sqlite to see what other things you can do with your SQLite database. 查看下面的鏈接，了解在 UWP 應(yīng)用中使用數(shù)據(jù)的其他方法。Check out the links below to learn about other ways to use data in your UWP app.
后續(xù)步驟Next steps
將應(yīng)用直接連接到 SQL Server 數(shù)據(jù)庫Connect your app directly to a SQL Server database
在跨不同平臺(tái)的不同應(yīng)用之間共享代碼Share code between different apps across different platforms
使用 Azure SQL 后端添加大綱/細(xì)節(jié)頁面Add master detail pages with Azure SQL back ends
總結(jié)
以上是生活随笔為你收集整理的uwp连接mysql数据库_在 UWP 应用中使用 SQLite 数据库的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： java中对象的生存期_深入理解Java
- 下一篇： 中文python笔记