快速入門:使用 .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.

必要條件Prerequisites

在本教學課程中,您需要:For this tutorial, 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.

取得 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 並在 <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>
    

插入程式碼以查詢 SQL 資料庫Insert code to query SQL database

  1. 在文字編輯器中開啟 Program.csIn 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.windows.net>"; 
                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