教學課程:使用 C# 和 ADO.NET 在 Azure SQL Database 的單一資料庫中設計關聯式資料庫Tutorial: Design a relational database in a single database within Azure SQL Database C# and ADO.NET

Azure SQL Database 是 Microsoft Cloud (Azure) 中的關聯式資料庫即服務 (DBaaS)。Azure SQL Database is a relational database-as-a-service (DBaaS) in the Microsoft Cloud (Azure). 在本教學課程裡,您將了解如何搭配使用 Visual Studio 與 Azure 入口網站和 ADO.NET 執行下列操作:In this tutorial, you learn how to use the Azure portal and ADO.NET with Visual Studio to:

  • 使用 Azure 入口網站建立單一資料庫*Create a single database using the Azure portal*
  • 使用 Azure 入口網站設定伺服器層級的 IP 防火牆規則Set up a server-level IP firewall rule using the Azure portal
  • 使用 ADO.NET 和 Visual Studio 連線到資料庫Connect to the database with ADO.NET and Visual Studio
  • 使用 ADO.NET 建立資料表Create tables with ADO.NET
  • 使用 ADO.NET 插入、更新和刪除資料Insert, update, and delete data with ADO.NET
  • 使用 ADO.NET 查詢資料Query data ADO.NET

*如果您沒有 Azure 訂用帳戶,請在開始前建立免費帳戶*If you don't have an Azure subscription, create a free account before you begin.

必要條件Prerequisites

Visual Studio 2017 的安裝An installation of Visual Studio 2017

建立空白的單一資料庫Create a blank single database

使用一組定義的計算和儲存體資源建立 Azure SQL Database 的單一資料庫。A single database in Azure SQL Database is created with a defined set of compute and storage resources. 此資料庫建立於 Azure 資源群組內,並使用資料庫伺服器進行管理。The database is created within an Azure resource group and is managed using an database server.

遵循以下步驟來建立空白單一資料庫。Follow these steps to create a blank single database.

  1. 按一下 Azure 入口網站左上角的 [建立資源]。Click Create a resource in the upper left-hand corner of the Azure portal.

  2. 在 [新增] 頁面上,選取 [Azure Marketplace] 區段中的 [資料庫],然後按一下 [精選] 區段中的 [SQL Database]。On the New page, select Databases in the Azure Marketplace section, and then click SQL Database in the Featured section.

    建立空白資料庫

  3. 使用下列資訊填寫 SQL Database 表單,如上圖所示︰Fill out the SQL Database form with the following information, as shown on the preceding image:

    設定Setting       建議的值Suggested value 說明Description
    資料庫名稱Database name yourDatabaseyourDatabase 如需有效的資料庫名稱,請參閱資料庫識別碼For valid database names, see Database identifiers.
    訂用帳戶Subscription yourSubscriptionyourSubscription 如需訂用帳戶的詳細資訊,請參閱訂用帳戶For details about your subscriptions, see Subscriptions.
    資源群組Resource group yourResourceGroupyourResourceGroup 如需有效的資源群組名稱,請參閱命名規則和限制For valid resource group names, see Naming rules and restrictions.
    選取來源Select source 空白資料庫Blank database 指定應建立空白資料庫。Specifies that a blank database should be created.
  4. 按一下 [伺服器] 來使用現有的伺服器伺服器,或建立及設定新的資料庫伺服器。Click Server to use an existing database server or create and configure a new database server. 選取現有的伺服器,或按一下 [建立新伺服器] 並且在 [新伺服器] 表單中填寫下列資訊︰Either select an existing server or click Create a new server and fill out the New server form with the following information:

    設定Setting       建議的值Suggested value 說明Description
    伺服器名稱Server name 任何全域唯一名稱Any globally unique name 如需有效的伺服器名稱,請參閱命名規則和限制For valid server names, see Naming rules and restrictions.
    伺服器管理員登入Server admin login 任何有效名稱Any valid name 如需有效的登入名稱,請參閱資料庫識別碼For valid login names, see Database identifiers.
    密碼Password 任何有效密碼Any valid password 您的密碼至少要有 8 個字元,而且必須使用下列幾種字元的其中三種︰大寫字元、小寫字元、數字和非英數字元。Your password must have at least eight characters and must use characters from three of the following categories: upper case characters, lower case characters, numbers, and non-alphanumeric characters.
    位置Location 任何有效位置Any valid location 如需區域的相關資訊,請參閱 Azure 區域For information about regions, see Azure Regions.

    建立資料庫伺服器

  5. 按一下 [選取] 。Click Select.

  6. 按一下 [定價層] 可指定服務層、DTU 或虛擬核心的數目,以及儲存體數量。Click Pricing tier to specify the service tier, the number of DTUs or vCores, and the amount of storage. 您可以瀏覽 DTU/虛擬核心數目的選項,以及可供您每個服務層級使用的儲存體。You may explore the options for the number of DTUs/vCores and storage that is available to you for each service tier.

    在選取服務層、DTU 或虛擬核心數目和儲存體數量之後,按一下 [套用]。After selecting the service tier, the number of DTUs or vCores, and the amount of storage, click Apply.

  7. 為空白資料庫輸入 [定序] (在此教學課程中使用預設值)。Enter a Collation for the blank database (for this tutorial, use the default value). 如需定序的詳細資訊,請參閱定序For more information about collations, see Collations

  8. 您現在已完成 SQL Database 表單,請按一下 [建立] 來佈建單一資料庫。Now that you've completed the SQL Database form, click Create to provision the single database. 這個步驟可能需要幾分鐘的時間。This step may take a few minutes.

  9. 在工具列上,按一下 [通知] 以監視部署程序。On the toolbar, click Notifications to monitor the deployment process.

    通知

建立伺服器層級 IP 防火牆規則Create a server-level IP firewall rule

QL Database 服務會在伺服器層級建立 IP 防火牆。The SQL Database service creates an IP firewall at the server-level. 此防火牆會防止外部應用程式和工具連線到伺服器及伺服器上的任何資料庫,除非防火牆規則允許其 IP 通過防火牆。This firewall prevents external applications and tools from connecting to the server and any databases on the server unless a firewall rule allows their IP through the firewall. 若要啟用對單一資料庫的外部連線,您必須先新增 IP 位址 (IP 位址範圍) 的 IP 防火牆規則新增。To enable external connectivity to your single database, you must first add an IP firewall rule for your IP address (or IP address range). 依照下列步驟來建立 SQL Database 伺服器層級的 IP 防火牆規則Follow these steps to create a SQL Database server-level IP firewall rule.

重要

SQL Database 服務會透過連接埠 1433 通訊。The SQL Database service communicates over port 1433. 如果您嘗試從公司網路連線到這項服務,您網路的防火牆可能不允許透過連接埠 1433 的輸出流量。If you are trying to connect to this service from within a corporate network, outbound traffic over port 1433 may not be allowed by your network's firewall. 若情況如此,除非系統管理員開啟連接埠 1433,否則您無法連線至您的單一資料庫。If so, you cannot connect to your single database unless your administrator opens port 1433.

  1. 部署完成之後,按一下左側功能表中的 [SQL 資料庫],然後按一下 [SQL 資料庫] 頁面上的 [yourDatabase]。After the deployment completes, click SQL databases from the left-hand menu and then click yourDatabase on the SQL databases page. 資料庫的概觀頁面隨即開啟,其中會顯示完整伺服器名稱 (例如 yourserver.database.windows.net),並提供進一步的組態選項。The overview page for your database opens, showing you the fully qualified Server name (such as yourserver.database.windows.net) and provides options for further configuration.

  2. 請複製此完整伺服器名稱,以便從 SQL Server Management Studio 連線到伺服器和資料庫。Copy this fully qualified server name for use to connect to your server and databases from SQL Server Management Studio.

    伺服器名稱

  3. 在工具列上按一下 [設定伺服器防火牆]。Click Set server firewall on the toolbar. SQL Database 伺服器的 [防火牆設定] 頁面隨即開啟。The Firewall settings page for the SQL Database server opens.

    伺服器層級 IP 防火牆規則

  4. 按一下工具列上的 [新增用戶端 IP],將目前的 IP 位址新增至新的 IP 防火牆規則。Click Add client IP on the toolbar to add your current IP address to a new IP firewall rule. IP 防火牆規則可以針對單一 IP 位址或 IP 位址範圍開啟連接埠 1433。An IP firewall rule can open port 1433 for a single IP address or a range of IP addresses.

  5. 按一下 [檔案] 。Click Save. 系統會為目前的 IP 位址建立伺服器層級 IP 防火牆規則,以便在 SQL Database 伺服器上開啟連接埠 1433。A server-level IP firewall rule is created for your current IP address opening port 1433 on the SQL Database server.

  6. 依序按一下 [確定],然後關閉 [防火牆設定] 頁面。Click OK and then close the Firewall settings page.

您的 IP 位址現在可以通過 IP 防火牆。Your IP address can now pass through the IP firewall. 您現在可以使用 SQL Server Management Studio 或您選擇的其他工具來連線至您的單一資料庫。You can now connect to your single database using SQL Server Management Studio or another tool of your choice. 務必使用先前建立的伺服器管理帳戶。Be sure to use the server admin account you created previously.

重要

根據預設,已對所有 Azure 服務啟用透過 SQL Database IP 防火牆存取。By default, access through the SQL Database IP firewall is enabled for all Azure services. 按一下此頁面上的 [關閉] 即可對所有 Azure 服務停用。Click OFF on this page to disable for all Azure services.

C# 程式範例C# program example

本文的下一節會呈現使用 ADO.NET 將 TRANSACT-SQL (T-SQL) 陳述式傳送到 SQL 資料庫的 C# 程式。The next sections of this article present a C# program that uses ADO.NET to send Transact-SQL (T-SQL) statements to the SQL database. C# 程式會示範下列動作:The C# program demonstrates the following actions:

實體關聯圖 (ERD)Entity Relationship Diagram (ERD)

CREATE TABLE 陳述式包含 REFERENCES 關鍵字,可建立兩個資料表之間的「外部索引鍵」(FK) 關聯性。The CREATE TABLE statements involve the REFERENCES keyword to create a foreign key (FK) relationship between two tables. 如果您使用 tempdb,請使用一對前置破折號將 --REFERENCES 關鍵字註解化。If you're using tempdb, comment out the --REFERENCES keyword using a pair of leading dashes.

ERD 會顯示兩個資料表之間的關聯性。The ERD displays the relationship between the two tables. tabEmployee.DepartmentCode「子」資料行中的值受限於 tabDepartment.DepartmentCode「父」資料行中的值。The values in the tabEmployee.DepartmentCode child column are limited to values from the tabDepartment.DepartmentCode parent column.

顯示外部索引鍵的 ERD

注意

您可以選擇編輯 T-SQL,將前置 # 新增至資料表名稱,以將其建立為 tempdb 中的暫存資料表。You have the option of editing the T-SQL to add a leading # to the table names, which creates them as temporary tables in tempdb. 沒有測試資料庫可供使用時,這很適合用於示範。This is useful for demonstration purposes, when no test database is available. 在外部索引鍵使用期間不會強制執行任何參考,而在程式完成執行之後,暫存資料表會在連線關閉時自動刪除。Any reference to foreign keys are not enforced during their use and temporary tables are deleted automatically when the connection closes after the program finishes running.

編譯和執行To compile and run

C# 程式在邏輯上是一個 .cs 檔案,而實際上會分成數個程式碼區塊,讓您更容易了解每個區塊。The C# program is logically one .cs file, and is physically divided into several code blocks, to make each block easier to understand. 若要編譯及執行此程式,請執行下列步驟:To compile and run the program, do the following steps:

  1. 在 Visual Studio 中建立 C# 專案。Create a C# project in Visual Studio. 專案類型應該是「主控台」,可在 [範本] > [Visual C#] > [Windows 桌面] > [主控台應用程式 (.NET Framework)] 下找到。The project type should be a Console, found under Templates > Visual C# > Windows Desktop > Console App (.NET Framework).

  2. 在 Program.cs 檔案中,利用下列步驟取代程式碼的起始行:In the file Program.cs, replace the starter lines of code with the following steps:

    1. 複製並貼上下列程式碼區塊 (以其出現的相同順序),請參閱連線到資料庫產生 T-SQL,以及提交至資料庫Copy and paste the following code blocks, in the same sequence they're presented, see Connect to database, Generate T-SQL, and Submit to database.

    2. 變更 Main 方法中的下列值:Change the following values in the Main method:

      • cb.DataSourcecb.DataSource
      • cb.UserIDcb.UserID
      • cb.Passwordcb.Password
      • cb.InitialCatalogcb.InitialCatalog
  3. 確認已參照 System.Data.dll 組件。Verify the assembly System.Data.dll is referenced. 若要確認,請展開 [方案總管] 窗格中的 [參考] 節點。To verify, expand the References node in the Solution Explorer pane.

  4. 若要從 Visual Studio 建置及執行程式,請選取 [啟動] 按鈕。To build and run the program from Visual Studio, select the Start button. 報告輸出會顯示在程式視窗中,而 GUID 值會隨著測試回合有所不同。The report output is displayed in a program window, though GUID values will vary between test runs.

    =================================
    T-SQL to 2 - Create-Tables...
    -1 = rows affected.
    
    =================================
    T-SQL to 3 - Inserts...
    8 = rows affected.
    
    =================================
    T-SQL to 4 - Update-Join...
    2 = rows affected.
    
    =================================
    T-SQL to 5 - Delete-Join...
    2 = rows affected.
    
    =================================
    Now, SelectEmployees (6)...
    8ddeb8f5-9584-4afe-b7ef-d6bdca02bd35 , Alison , 20 , acct , Accounting
    9ce11981-e674-42f7-928b-6cc004079b03 , Barbara , 17 , hres , Human Resources
    315f5230-ec94-4edd-9b1c-dd45fbb61ee7 , Carol , 22 , acct , Accounting
    fcf4840a-8be3-43f7-a319-52304bf0f48d , Elle , 15 , NULL , NULL
    View the report output here, then press any key to end the program...
    

使用 ADO.NET 連線至 SQL DatabaseConnect to SQL database using ADO.NET

using System;
using System.Data.SqlClient;   // System.Data.dll
//using System.Data;           // For:  SqlDbType , ParameterDirection

namespace csharp_db_test
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var cb = new SqlConnectionStringBuilder();
                cb.DataSource = "your_server.database.windows.net";
                cb.UserID = "your_user";
                cb.Password = "your_password";
                cb.InitialCatalog = "your_database";

                using (var connection = new SqlConnection(cb.ConnectionString))
                {
                    connection.Open();

                    Submit_Tsql_NonQuery(connection, "2 - Create-Tables", Build_2_Tsql_CreateTables());

                    Submit_Tsql_NonQuery(connection, "3 - Inserts", Build_3_Tsql_Inserts());

                    Submit_Tsql_NonQuery(connection, "4 - Update-Join", Build_4_Tsql_UpdateJoin(),
                        "@csharpParmDepartmentName", "Accounting");

                    Submit_Tsql_NonQuery(connection, "5 - Delete-Join", Build_5_Tsql_DeleteJoin(),
                        "@csharpParmDepartmentName", "Legal");

                    Submit_6_Tsql_SelectEmployees(connection);
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }

            Console.WriteLine("View the report output here, then press any key to end the program...");
            Console.ReadKey();
        }

可傳回 T-SQL 陳述式的方法Methods that return T-SQL statements

static string Build_2_Tsql_CreateTables()
{
    return @"
        DROP TABLE IF EXISTS tabEmployee;
        DROP TABLE IF EXISTS tabDepartment;  -- Drop parent table last.

        CREATE TABLE tabDepartment
        (
            DepartmentCode  nchar(4)          not null    PRIMARY KEY,
            DepartmentName  nvarchar(128)     not null
        );

        CREATE TABLE tabEmployee
        (
            EmployeeGuid    uniqueIdentifier  not null  default NewId()    PRIMARY KEY,
            EmployeeName    nvarchar(128)     not null,
            EmployeeLevel   int               not null,
            DepartmentCode  nchar(4)              null
            REFERENCES tabDepartment (DepartmentCode)  -- (REFERENCES would be disallowed on temporary tables.)
        );
    ";
}

static string Build_3_Tsql_Inserts()
{
    return @"
        -- The company has these departments.
        INSERT INTO tabDepartment (DepartmentCode, DepartmentName)
        VALUES
            ('acct', 'Accounting'),
            ('hres', 'Human Resources'),
            ('legl', 'Legal');

        -- The company has these employees, each in one department.
        INSERT INTO tabEmployee (EmployeeName, EmployeeLevel, DepartmentCode)
        VALUES
            ('Alison'  , 19, 'acct'),
            ('Barbara' , 17, 'hres'),
            ('Carol'   , 21, 'acct'),
            ('Deborah' , 24, 'legl'),
            ('Elle'    , 15, null);
    ";
}

static string Build_4_Tsql_UpdateJoin()
{
    return @"
        DECLARE @DName1  nvarchar(128) = @csharpParmDepartmentName;  --'Accounting';

        -- Promote everyone in one department (see @parm...).
        UPDATE empl
        SET
            empl.EmployeeLevel += 1
        FROM
            tabEmployee   as empl
        INNER JOIN
            tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
        WHERE
            dept.DepartmentName = @DName1;
    ";
}

static string Build_5_Tsql_DeleteJoin()
{
    return @"
        DECLARE @DName2  nvarchar(128);
        SET @DName2 = @csharpParmDepartmentName;  --'Legal';

        -- Right size the Legal department.
        DELETE empl
        FROM
            tabEmployee   as empl
        INNER JOIN
            tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
        WHERE
            dept.DepartmentName = @DName2

        -- Disband the Legal department.
        DELETE tabDepartment
            WHERE DepartmentName = @DName2;
    ";
}

static string Build_6_Tsql_SelectEmployees()
{
    return @"
        -- Look at all the final Employees.
        SELECT
            empl.EmployeeGuid,
            empl.EmployeeName,
            empl.EmployeeLevel,
            empl.DepartmentCode,
            dept.DepartmentName
        FROM
            tabEmployee   as empl
        LEFT OUTER JOIN
            tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
        ORDER BY
            EmployeeName;
    ";
}

將 T-SQL 提交至資料庫Submit T-SQL to the database

static void Submit_6_Tsql_SelectEmployees(SqlConnection connection)
{
    Console.WriteLine();
    Console.WriteLine("=================================");
    Console.WriteLine("Now, SelectEmployees (6)...");

    string tsql = Build_6_Tsql_SelectEmployees();

    using (var command = new SqlCommand(tsql, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine("{0} , {1} , {2} , {3} , {4}",
                    reader.GetGuid(0),
                    reader.GetString(1),
                    reader.GetInt32(2),
                    (reader.IsDBNull(3)) ? "NULL" : reader.GetString(3),
                    (reader.IsDBNull(4)) ? "NULL" : reader.GetString(4));
            }
        }
    }
}

static void Submit_Tsql_NonQuery(
    SqlConnection connection,
    string tsqlPurpose,
    string tsqlSourceCode,
    string parameterName = null,
    string parameterValue = null
    )
{
    Console.WriteLine();
    Console.WriteLine("=================================");
    Console.WriteLine("T-SQL to {0}...", tsqlPurpose);

    using (var command = new SqlCommand(tsqlSourceCode, connection))
    {
        if (parameterName != null)
        {
            command.Parameters.AddWithValue(  // Or, use SqlParameter class.
                parameterName,
                parameterValue);
        }
        int rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine(rowsAffected + " = rows affected.");
    }
}
} // EndOfClass
}

後續步驟Next steps

在本教學課程中,您已了解基本的資料庫工作,例如建立資料庫和資料表、連線至資料庫、載入資料,以及執行查詢。In this tutorial, you learned basic database tasks such as create a database and tables, connect to the database, load data, and run queries. 您已了解如何︰You learned how to:

  • 建立資料庫Create a database
  • 設定防火牆規則Set up a firewall rule
  • 使用 Visual Studio 和 C# 連線到資料庫Connect to the database with Visual Studio and C#
  • 建立資料表Create tables
  • 插入、更新、刪除及查詢資料Insert, update, delete, and query data

請前進到下一個教學課程,以了解資料移轉。Advance to the next tutorial to learn about data migration.