uwp连接mysql数据库_在 UWP 应用中使用 SQL Server 数据库

在 UWP 应用中使用 SQL Server 数据库Use a SQL Server database in a UWP app

06/26/2020

本文内容

通过使用 System.Data.SqlClient 命名空间中的类，你的应用可以直接连接到 SQL Server 数据库然后存储和检索数据。Your app can connect directly to a SQL Server database and then store and retrieve data by using classes in the System.Data.SqlClient namespace.

在本指南中，我们将向你介绍一种操作方法。In this guide, we'll show you one way to do that. 如果你将 Northwind 示例数据库安装到 SQL Server 实例上，然后使用这些代码段，你最终将获得显示了来自 Northwind 示例数据库的产品的基本 UI。If you install the Northwind sample database onto your SQL Server instance, and then use these snippets, you'll end up with a basic UI that shows products from the Northwind sample database.

本指南中显示的代码段基于此更完整的示例。The snippets that appear in this guide are based on this more complete sample.

首先，设置你的解决方案First, set up your solution

要将你的应用直接连接到 SQL Server 数据库，请确保你的项目的最低版本以秋季创意者更新为目标。To connect your app directly to a SQL Server database, make sure that the minimum version of your project targets the Fall Creators update. 你可以在 UWP 项目的属性页中找到该信息。You can find that information in the properties page of your UWP project.

在清单设计器中打开你的 UWP 项目的 Package.appxmanifest 文件。Open the Package.appxmanifest file of your UWP project in the manifest designer.

在“功能”选项卡中，如果要使用 Windows 身份验证对 SQL Server 进行身份验证，则选中“企业身份验证”复选框。In the Capabilities tab, select the Enterprise Authentication checkbox if you are using Windows Authentication for authenticating your SQL Server.

重要

无论你是否使用 Windows 身份验证，都需要选择“Internet (客户端和服务器)”、“Internet (客户端)”和“私有网络(客户端和服务器)” 。You will also need to select Internet (Client & Server), Internet (Client), and Private Networks (Client & Server), regardless of whether or not you're using Windows Authentication.

在 SQL Server 数据库中添加和检索数据Add and retrieve data in a SQL Server database

在本节中，我们将执行以下操作：In this section, we'll do these things:

1️⃣添加连接字符串。Add a connection string.

2️⃣创建用于保存产品数据的类。Create a class to hold product data.

3️⃣从 SQL Server 数据库检索产品。Retrieve products from the SQL Server database.

4️⃣添加基本用户界面。Add a basic user interface.

5️⃣使用产品填充 UI。Populate the UI with Products.

备注

本节介绍了一种组织你的数据访问代码的方法。This section illustrates one way to organize your data access code. 这不仅仅是为了提供如何使用 System.Data.SqlClient 在 SQL Server 数据库中存储和检索数据的示例。It's meant only to provide an example of how you can use System.Data.SqlClient to store and retrieve data from a SQL Server database. 你可以采用对你的应用程序设计最有意义的任何方式组织你的代码。You can organize your code in any way that makes the most sense to your application's design.

添加连接字符串Add a connection string

在 App.xaml.cs 文件中，向 App 类添加一个属性，该属性为你的解决方案中的其他类提供了对连接字符串的访问权限。In the App.xaml.cs file, add a property to the App class, that gives other classes in your solution access to the connection string.

我们的连接字符串指向 SQL Server Express 实例中的 Northwind 数据库。Our connection string points to the Northwind database in a SQL Server Express instance.

sealed partial class App : Application

{

// Connection string for using Windows Authentication.

private string connectionString =

@"Data Source=YourServerName\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=SSPI";

// This is an example connection string for using SQL Server Authentication.

// private string connectionString =

// @"Data Source=YourServerName\YourInstanceName;Initial Catalog=DatabaseName; User Id=XXXXX; Password=XXXXX";

public string ConnectionString { get => connectionString; set => connectionString = value; }

...

}

创建用于保存产品数据的类Create a class to hold product data

我们将创建一个实现 INotifyPropertyChanged 事件的类，以便将 XAML UI 中的属性绑定到此类中的属性。We'll create a class that implements the INotifyPropertyChanged event so that we can bind attributes in our XAML UI to the properties in this class.

public class Product : INotifyPropertyChanged

{

public int ProductID { get; set; }

public string ProductCode { get { return ProductID.ToString(); } }

public string ProductName { get; set; }

public string QuantityPerUnit { get; set; }

public decimal UnitPrice { get; set; }

public string UnitPriceString { get { return UnitPrice.ToString("######.00"); } }

public int UnitsInStock { get; set; }

public string UnitsInStockString { get { return UnitsInStock.ToString("#####0"); } }

public int CategoryId { get; set; }

public event PropertyChangedEventHandler PropertyChanged;

private void NotifyPropertyChanged(string propertyName)

{

PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));

}

}

从 SQL Server 数据库检索产品Retrieve products from the SQL Server database

创建一个从 Northwind 示例数据库获取产品的方法，然后将这些产品作为 Product 实例的 ObservableCollection 集合返回。Create a method that gets products from the Northwind sample database, and then returns them as an ObservableCollection collection of Product instances.

public ObservableCollection GetProducts(string connectionString)

{

const string GetProductsQuery = "select ProductID, ProductName, QuantityPerUnit," +

" UnitPrice, UnitsInStock, Products.CategoryID " +

" from Products inner join Categories on Products.CategoryID = Categories.CategoryID " +

" where Discontinued = 0";

var products = new ObservableCollection();

try

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

if (conn.State == System.Data.ConnectionState.Open)

{

using (SqlCommand cmd = conn.CreateCommand())

{

cmd.CommandText = GetProductsQuery;

using (SqlDataReader reader = cmd.ExecuteReader())

{

while (reader.Read())

{

var product = new Product();

product.ProductID = reader.GetInt32(0);

product.ProductName = reader.GetString(1);

product.QuantityPerUnit = reader.GetString(2);

product.UnitPrice = reader.GetDecimal(3);

product.UnitsInStock = reader.GetInt16(4);

product.CategoryId = reader.GetInt32(5);

products.Add(product);

}

}

}

}

}

return products;

}

catch (Exception eSql)

{

Debug.WriteLine("Exception: " + eSql.Message);

}

return null;

}

添加基本用户界面Add a basic user interface

将以下 XAML 添加到 UWP 项目的 MainPage.xaml 文件。Add the following XAML to the MainPage.xaml file of the UWP project.

此 XAML 将创建一个 ListView(用来显示你在上一个代码段中返回的每个产品)，并会将 ListView 中的每个行的属性绑定到我们在 Product 类中定义的属性。This XAML creates a ListView to show each product that you returned in the previous snippet, and binds the attributes of each row in the ListView to the properties that we defined in the Product class.

SelectionMode="Single"

ScrollViewer.VerticalScrollBarVisibility="Auto"

ScrollViewer.IsVerticalRailEnabled="True"

ScrollViewer.VerticalScrollMode="Enabled"

ScrollViewer.HorizontalScrollMode="Enabled"

ScrollViewer.HorizontalScrollBarVisibility="Auto"

ScrollViewer.IsHorizontalRailEnabled="True"

Margin="20">

Text="{x:Bind ProductCode}"

Width="50" />

Text="{x:Bind ProductName}"

Width="300" />

Width="200" />

Width="80" />

Width="80" />

在 ListView 中显示产品Show products in the ListView

打开 MainPage.xaml.cs 文件，并将代码添加到 MainPage 类的构造函数，该类可将 ListView 的 ItemSource 属性设置为 Product 实例的 ObservableCollection。Open the MainPage.xaml.cs file, and add code to the constructor of the MainPage class that sets the ItemSource property of the ListView to the ObservableCollection of Product instances.

public MainPage()

{

this.InitializeComponent();

InventoryList.ItemsSource = GetProducts((App.Current as App).ConnectionString);

}

启动项目并看到来自 Northwind 示例数据库的产品出现在 UI 中。Start the project and see products from the Northwind sample database appear in the UI.

探索 System.Data.SqlClient 命名空间以了解 SQL Server 数据库中的数据的其他作用。Explore the System.Data.SqlClient namespace to see what other things you can do with data in your SQL Server database.

连接数据库时遇到问题？Trouble connecting to your database?

在大多数情况下，需要更改 SQL Server 配置的某些方面。In most cases, some aspect of the SQL Server configuration needs to be changed. 如果能够从其他类型的桌面应用程序(例如 Windows 窗体或 WPF 应用程序)连接到数据库，请确保已为 SQL Server 启用 TCP/IP。If you're able to connect to your database from another type of desktop application such as a Windows Forms or WPF application, ensure that you've enabled TCP/IP for SQL Server. 可以在“计算机管理”控制台中执行该操作。You can do that in the Computer Management console.

然后，确保 SQL Server Browser 服务正在运行。Then, make sure that your SQL Server Browser service is running.

后续步骤Next steps

使用轻量级数据库在用户设备上存储数据Use a light-weight database to store data on the users device

在跨不同平台的不同应用之间共享代码Share code between different apps across different platforms

使用 Azure SQL 后端添加大纲/细节页面Add master detail pages with Azure SQL back ends