快速入门:使用 .NET Core (C#) 查询 Azure SQL 数据库中的数据库或 Azure SQL 托管实例Quickstart: Use .NET Core (C#) to query a database in Azure SQL Database or Azure SQL Managed Instance

适用于:是 Azure SQL 数据库 是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

在本快速入门中,将使用 .NET Core 和 C# 代码连接到数据库。In this quickstart, you'll use .NET Core and C# code to connect to a database. 然后,将运行 Transact-SQL 语句来查询数据。You'll then run a Transact-SQL statement to query data.

提示

以下 Microsoft Learn 模块可帮助你免费学习如何开发和配置可查询 Azure SQL 数据库中数据库的 ASP.NET 应用程序The following Microsoft Learn module helps you learn for free how to Develop and configure an ASP.NET application that queries a database in Azure SQL Database

先决条件Prerequisites

若要完成本快速入门,你需要:To complete this quickstart, you need:

备注

本快速入门使用 mySampleDatabase 数据库。This quickstart uses the mySampleDatabase database. 若要使用其他数据库,需更改数据库引用并修改 C# 代码中的 SELECT 查询。If you want to use a different database, you will need to change the database references and modify the SELECT query in the C# code.

获取服务器连接信息Get server connection information

获取连接到 Azure SQL 数据库中的数据库所需的连接信息。Get the connection information you need to connect to the database in Azure SQL Database. 在后续过程中,将需要完全限定的服务器名称或主机名称、数据库名称和登录信息。You'll need the fully qualified server name or host name, database name, and login information for the upcoming procedures.

  1. 登录 Azure 门户Sign in to the Azure portal.

  2. 导航到“SQL 数据库”或“SQL 托管实例”页 。Navigate to the SQL Databases or SQL Managed Instances page.

  3. 在“概述”页上,在“Server 名称”旁查看 Azure SQL 数据库中的数据库的完全限定服务器名称,或在“Host”旁边查看 Azure VM 上 Azure SQL 托管实例中或 SQL Server 的完全限定服务器名称(或 IP 地址) 。On the Overview page, review the fully qualified server name next to Server name for the database in Azure SQL Database or the fully qualified server name (or IP address) next to Host for an Azure SQL Managed Instance or SQL Server on Azure VM. 若要复制服务器名称或主机名称,请将鼠标悬停在其上方,然后选择“复制”图标。To copy the server name or host name, hover over it and select the Copy icon.

备注

有关 Azure VM 上的 SQL Server 的连接信息,请参阅连接到 SQL Server 实例For connection information for SQL Server on Azure VM, see Connect to a SQL Server instance.

获取 ADO.NET 连接信息(可选 - 仅限 SQL 数据库)Get ADO.NET connection information (optional - SQL Database only)

  1. 导航到“mySampleDatabase”页,并在“设置”下选择“连接字符串” 。Navigate to the mySampleDatabase page and, under Settings, select Connection strings.

  2. 查看完整的 ADO.NET 连接字符串。Review the complete ADO.NET connection string.

    ADO.NET 连接字符串

  3. 如果想要使用 ADO.NET 连接字符串,请复制它。Copy the ADO.NET connection string if you intend to use it.

创建新的 .NET Core 项目Create a new .NET Core project

  1. 打开命令提示符,然后创建一个名为 sqltest 的文件夹。Open a command prompt and create a folder named sqltest. 导航到此文件夹,并运行以下命令。Navigate to this folder and run this command.

    dotnet new console
    

    此命令将创建新的应用项目文件,包括初始 C# 代码文件 (Program.cs)、XML 配置文件 (sqltest.csproj) 和所需的二进制文件。This command creates new app project files, including an initial C# code file (Program.cs), an XML configuration file (sqltest.csproj), and needed binaries.

  2. 在文本编辑器中,打开 sqltest.csproj 并在 <Project> 标记之间粘贴以下 XML。In a text editor, open sqltest.csproj and paste the following XML between the <Project> tags. 此 XML 会添加 System.Data.SqlClient 作为依赖项。This XML adds System.Data.SqlClient as a dependency.

    <ItemGroup>
        <PackageReference Include="System.Data.SqlClient" Version="4.6.0" />
    </ItemGroup>
    

插入代码以查询 Azure SQL 数据库中的数据库Insert code to query the database in Azure SQL Database

  1. 在文本编辑器中打开 Program.cs 文件。In a text editor, open Program.cs.

  2. 将内容替换为以下代码,为服务器、数据库、用户名和密码添加相应的值。Replace the contents with the following code and add the appropriate values for your server, database, username, and password.

备注

若要使用 ADO.NET 连接字符串,请将代码中设置服务器、数据库、用户名和密码的 4 行替换为以下行。To use an ADO.NET connection string, replace the 4 lines in the code setting the server, database, username, and password with the line below. 在字符串中,设置用户名和密码。In the string, set your username and password.

builder.ConnectionString="<your_ado_net_connection_string>";

using System;
using System.Data.SqlClient;
using System.Text;

namespace sqltest
{
    class Program
    {
        static void Main(string[] args)
        {
            try 
            { 
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                builder.DataSource = "<your_server.database.chinacloudapi.cn>"; 
                builder.UserID = "<your_username>";            
                builder.Password = "<your_password>";     
                builder.InitialCatalog = "<your_database>";
         
                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                {
                    Console.WriteLine("\nQuery data example:");
                    Console.WriteLine("=========================================\n");
                    
                    connection.Open();       
                    StringBuilder sb = new StringBuilder();
                    sb.Append("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName ");
                    sb.Append("FROM [SalesLT].[ProductCategory] pc ");
                    sb.Append("JOIN [SalesLT].[Product] p ");
                    sb.Append("ON pc.productcategoryid = p.productcategoryid;");
                    String sql = sb.ToString();

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
                            }
                        }
                    }                    
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
            Console.WriteLine("\nDone. Press enter.");
            Console.ReadLine(); 
        }
    }
}

运行代码Run the code

  1. 在提示符处,运行以下命令。At the prompt, run the following commands.

    dotnet restore
    dotnet run
    
  2. 验证是否返回前 20 行。Verify that the top 20 rows are returned.

    Query data example:
    =========================================
    
    Road Frames HL Road Frame - Black, 58
    Road Frames HL Road Frame - Red, 58
    Helmets Sport-100 Helmet, Red
    Helmets Sport-100 Helmet, Black
    Socks Mountain Bike Socks, M
    Socks Mountain Bike Socks, L
    Helmets Sport-100 Helmet, Blue
    Caps AWC Logo Cap
    Jerseys Long-Sleeve Logo Jersey, S
    Jerseys Long-Sleeve Logo Jersey, M
    Jerseys Long-Sleeve Logo Jersey, L
    Jerseys Long-Sleeve Logo Jersey, XL
    Road Frames HL Road Frame - Red, 62
    Road Frames HL Road Frame - Red, 44
    Road Frames HL Road Frame - Red, 48
    Road Frames HL Road Frame - Red, 52
    Road Frames HL Road Frame - Red, 56
    Road Frames LL Road Frame - Black, 58
    Road Frames LL Road Frame - Black, 60
    Road Frames LL Road Frame - Black, 62
    
    Done. Press enter.
    
  3. 选择 Enter 关闭应用程序窗口。Choose Enter to close the application window.

后续步骤Next steps