快速入门:使用 .NET Core (C#) 查询 Azure SQL 数据库Quickstart: Use .NET Core (C#) to query an Azure SQL database

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

Tip

以下 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 an Azure SQL Database

必备条件Prerequisites

对于本教程的内容,你需要:For this tutorial, you need:

Note

本快速入门使用 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.

获取 SQL Server 连接信息Get SQL server connection information

获取连接到 Azure SQL 数据库所需的连接信息。Get the connection information you need to connect to the 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. 在“概览”页中,查看单一数据库的“服务器名称”旁边的完全限定的服务器名称,或者托管实例的“主机”旁边的完全限定的服务器名称 。On the Overview page, review the fully qualified server name next to Server name for a single database or the fully qualified server name next to Host for a managed instance. 若要复制服务器名称或主机名称,请将鼠标悬停在其上方,然后选择“复制”图标 。To copy the server name or host name, hover over it and select the Copy icon.

获取 ADO.NET 连接信息(可选)Get ADO.NET connection information (optional)

  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 并在 标记之间粘贴以下 XML<Project>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>
    

插入用于查询 SQL 数据库的代码Insert code to query 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.

Note

若要使用 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