快速入门:使用 Visual Studio 中的 .NET 和 C# 来连接和查询 Azure SQL 数据库中的数据库或 Azure SQL 托管实例Quickstart: Use .NET and C# in Visual Studio to connect to and 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

本快速入门展示了如何使用 Visual Studio 中的 .NET Framework 和 C# 代码通过 Transact-SQL 语句查询 Azure SQL 数据库中的数据库。This quickstart shows how to use the .NET Framework and C# code in Visual Studio to query a database in Azure SQL Database with Transact-SQL statements.

先决条件Prerequisites

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

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

获取连接到数据库所需的连接信息。Get the connection information you need to connect to the 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 a 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.

创建用于查询 Azure SQL 数据库中的数据库的代码Create code to query the database in Azure SQL Database

  1. 在 Visual Studio 中,选择“文件” > “新建” > “项目”。In Visual Studio, select File > New > Project.

  2. 在“新建项目”对话框中,选择“Visual C#”,然后选择“控制台应用(.NET Framework)” 。In the New Project dialog, select Visual C#, and then select Console App (.NET Framework).

  3. 输入“sqltest”作为项目名称,然后选择“确定”。Enter sqltest for the project name, and then select OK. 创建新项目。The new project is created.

  4. 选择“项目” > “管理 NuGet 包” 。Select Project > Manage NuGet Packages.

  5. 在“NuGet 包管理器”中,选择“浏览”选项卡,然后搜索并选择“System.Data.SqlClient” 。In NuGet Package Manager, select the Browse tab, then search for and select System.Data.SqlClient.

  6. 在“System.Data.SqlClient”页上选择“安装” 。On the System.Data.SqlClient page, select Install.

    • 如果出现提示,请选择“确定”继续安装。If prompted, select OK to continue with the installation.
    • 如果显示“接受许可证”窗口,则选择“我接受” 。If a License Acceptance window appears, select I Accept.
  7. 安装完成后,可以关闭“NuGet 包管理器”。When the install completes, you can close NuGet Package Manager.

  8. 在代码编辑器中,将 Program.cs 内容替换为以下代码。In the code editor, replace the Program.cs contents with the following code. 替换 <server><username><password><database> 的值。Replace your values for <server>, <username>, <password>, and <database>.

    重要

    本示例中的代码使用示例 AdventureWorksLT 数据,在创建数据库时可以选择该数据作为源。The code in this example uses the sample AdventureWorksLT data, which you can choose as source when creating your database. 如果数据库有不同数据,请在 SELECT 查询中使用自己数据库中的表。If your database has different data, use tables from your own database in the SELECT query.

    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 = "<server>.database.chinacloudapi.cn"; 
                    builder.UserID = "<username>";            
                    builder.Password = "<password>";     
                    builder.InitialCatalog = "<database>";
    
                    using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                    {
                        Console.WriteLine("\nQuery data example:");
                        Console.WriteLine("=========================================\n");
    
                        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))
                        {
                            connection.Open();
                            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.ReadLine();
            }
        }
    }
    

运行代码Run the code

  1. 若要运行该应用,请选择“调试” > “开始调试”,或选择工具栏上的“开始”,或按 F5 。To run the app, select Debug > Start Debugging, or select Start on the toolbar, or press F5.
  2. 验证是否返回了数据库中的前 20 个类别/产品行,然后关闭应用窗口。Verify that the top 20 Category/Product rows from your database are returned, and then close the app window.

后续步骤Next steps