教程:在 Azure SQL 数据库 C# 和 ADO.NET 的单一数据库中设计关系数据库

Azure SQL 数据库是 Azure 中的关系数据库即服务 (DBaaS)。 本教程介绍如何将 Azure 门户、ADO.NET 与 Visual Studio 结合使用来完成以下操作:

  • 使用 Azure 门户创建单一数据库
  • 通过 Azure 门户设置服务器级 IP 防火墙规则
  • 使用 ADO.NET 和 Visual Studi 连接至数据库
  • 使用 ADO.NET 创建表
  • 使用 ADO.NET 插入、更新和删除数据
  • 查询数据 ADO.NET

如果没有 Azure 订阅,可在开始前创建一个 1 元人民币试用帐户

先决条件

安装 Visual Studio 2017

创建空的单一数据库

创建 Azure SQL 数据库中的单一数据库时,会使用定义好的一组计算和存储资源。 数据库在 Azure 资源组中创建,使用数据库服务器进行托管。

遵循以下步骤创建空白的单一数据库。

  1. 在 Azure 门户的左上角单击“创建资源”。

  2. 在“新建”页上的“Azure 市场”部分中选择“数据库”,然后在“特别推荐”部分中单击“SQL 数据库”。

    创建空数据库

  3. 如上图所示,在“SQL 数据库”表单中填写以下信息:

    设置       建议的值 说明 
    数据库名称 yourDatabase 如需有效的数据库名称,请参阅数据库标识符
    订阅 yourSubscription 有关订阅的详细信息,请参阅订阅
    资源组 yourResourceGroup 有关有效的资源组名称,请参阅 命名规则和限制
    选择源 空白数据库 指定应创建空白数据库。
  4. 单击“服务器”以使用现有的数据库服务器,或者创建并配置新的数据库服务器。 选择现有服务器或单击“创建新服务器”,然后在“新建服务器”窗体中填写以下信息:

    设置       建议的值 说明 
    服务器名称 任何全局唯一名称 如需有效的服务器名称,请参阅 命名规则和限制
    服务器管理员登录名 任何有效的名称 如需有效的登录名,请参阅数据库标识符
    密码 任何有效的密码 密码必须至少有八个字符,且必须使用以下类别中的三个类别的字符:大写字符、小写字符、数字以及非字母数字字符。
    位置 任何有效的位置 中国东部、中国东部 2、中国北部、中国北部 2

    创建数据库 - 服务器

  5. 单击“选择”。

  6. 单击“定价层”,指定服务层、DTU 或 vCore 数,以及存储量。 可以浏览相关选项,了解每个服务层可提供的 DTU/vCore 数和存储。

    选择服务层、DTU 数或 vCore 数以及存储量后,然后单击“应用”。

  7. 输入空白数据库的“排序规则”(就本教程来说,请使用默认值)。 有关排序规则的详细信息,请参阅 排序规则

  8. 填写“SQL 数据库”窗体后,单击“创建”以预配单一数据库。 这个步骤可能需要几分钟的时间。

  9. 在工具栏上,单击“通知”可监视部署过程。

    通知

创建服务器级 IP 防火墙规则

SQL 数据库服务在服务器级别创建 IP 防火墙。 此防火墙阻止外部应用程序和工具连接到服务器和服务器上的任何数据库,除非防火墙规则允许其 IP 通过防火墙。 若要启用与单一数据库的外部连接,必须首先为 IP 地址(或 IP 地址范围)添加 IP 防火墙规则。 遵循这些步骤创建 SQL 数据库服务器级 IP 防火墙规则

Important

SQL 数据库服务通过端口 1433 进行通信。 如果尝试从企业网络内部连接到此服务,则该网络的防火墙可能不允许经端口 1433 的出站流量。 如果是这样,则无法连接到单一数据库,除非管理员打开端口 1433。

  1. 部署完成后,在左侧菜单中单击“SQL 数据库”,然后在“SQL 数据库”页上单击“yourDatabase”。 此时会打开数据库的概览页,显示完全限定的服务器名称(例如 yourserver.database.chinacloudapi.cn),并且会提供进行进一步配置所需的选项。

  2. 复制此完全限定的服务器名称,将其用于从 SQL Server Management Studio 连接到服务器和数据库。

    服务器名称

  3. 单击工具栏上的“设置服务器防火墙”。 此时会打开 SQL 数据库服务器的“防火墙设置”页。

    服务器级别 IP 防火墙规则

  4. 在工具栏上单击“添加客户端 IP”,将当前的 IP 地址添加到新的 IP 防火墙规则。 IP 防火墙规则可以针对单个 IP 地址或一系列 IP 地址打开端口 1433。

  5. 单击“保存” 。 此时会针对当前的 IP 地址创建服务器级 IP 防火墙规则,在 SQL 数据库服务器上打开端口 1433。

  6. 单击“确定”,并关闭“防火墙设置”页。

你的 IP 地址现在可以通过 IP 防火墙。 现在可以使用 SQL Server Management Studio 或其他所选工具连接到单一数据库。 确保使用之前创建的服务器管理员帐户。

Important

默认情况下,所有 Azure 服务都允许通过 SQL 数据库 IP 防火墙进行访问。 在此页上单击“关”即可对所有 Azure 服务执行禁用操作。

C# 程序示例

本文的后续部分介绍了一个 C# 程序,该程序使用 ADO.NET 将 Transact-SQL (T-SQL) 语句发送到 SQL 数据库。 该 C# 程序演示以下操作:

实体关系图 (ERD)

CREATE TABLE 语句涉及 REFERENCES 关键字,该关键字用于在两个表之间创建外键 (FK) 关系。 如果使用 tempdb,请通过一对前导短划线注释掉 --REFERENCES 关键字。

ERD 显示两个表之间的关系。 tabEmployee.DepartmentCode 子列中的值仅限 tabDepartment.DepartmentCode 父列中的值。

ERD,显示外键

Note

可以选择编辑 T-SQL,以便向表名添加前导 #,从而在 tempdb 中以临时表的形式创建这些表。 在没有测试数据库可用时,可以通过这种方法进行演示。 使用外键期间不强制要求对外键进行任何引用,程序完成运行后关闭连接时,将自动删除临时表。

编译和运行

该 C# 程序在逻辑上是一个 .cs 文件,在物理上划分成多个代码块,使每个块更易于理解。 若要编译和运行该程序,请执行以下步骤:

  1. 在 Visual Studio 中创建 C# 项目。 项目类型应该是“控制台”,可在“模板” > “Visual C#” > “Windows 桌面” > “控制台应用(.NET Framework)”下找到。

  2. Program.cs 文件中,通过以下步骤替换起始代码行:

    1. 按相同的显示顺序复制并粘贴以下代码块;请参阅连接到数据库生成 T-SQL提交到数据库

    2. 更改 Main 方法中的以下值:

      • cb.DataSource
      • cb.UserID
      • cb.Password
      • cb.InitialCatalog
  3. 验证是否已引用程序集 System.Data.dll。 若要进行验证,请在“解决方案资源管理器”窗格中展开“引用”节点。

  4. 若要在 Visual Studio 中生成并运行该程序,请选择“启动”按钮。 报告输出将显示在程序窗口中,不过,GUID 值根据测试运行的不同而异。

    =================================
    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 数据库

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.chinacloudapi.cn";
                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 语句的方法

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 提交到数据库

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
}

后续步骤

本教程介绍了基本数据库任务,例如创建数据库和表、连接到数据库、加载数据和运行查询。 你已了解如何:

  • 创建数据库
  • 设置防火墙规则
  • 使用 Visual Studio 和 C# 连接至数据库
  • 创建表
  • 插入、更新、删除和查询数据