快速入門:在 Visual Studio 使用 .NET 和 C# 連線和查詢 Azure SQL 資料庫Quickstart: Use .NET and C# in Visual Studio to connect to and query an Azure SQL database

本快速入門示範如何在 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 an Azure SQL database with Transact-SQL statements.

必要條件Prerequisites

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

取得 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.

建立程式碼以查詢 SQL DatabaseCreate code to query the 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.SqlClientIn 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.windows.net"; 
                    builder.UserID = "<username>";            
                    builder.Password = "<password>";     
                    builder.InitialCatalog = "<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.ReadLine();
            }
        }
    }
    

執行程式碼Run the code

  1. 若要執行應用程式,請選取 [偵錯] > [開始偵錯] ,或在工具列上選取 [開始] ,或按下 F5To 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