教程:在 Azure SQL 数据库 C# 和 ADO.NET 中设计关系数据库Tutorial: Design a relational database in Azure SQL Database C# and ADO.NET
Azure SQL 数据库
Azure SQL 数据库是云 (Azure) 中的关系数据库即服务 (DBaaS)。Azure SQL Database is a relational database-as-a-service (DBaaS) in the Cloud (Azure). 本教程介绍如何将 Azure 门户、ADO.NET 与 Visual Studio 结合使用来完成以下操作:In this tutorial, you learn how to use the Azure portal and ADO.NET with Visual Studio to:
- 使用 Azure 门户创建数据库Create a database using the Azure portal
- 通过 Azure 门户设置服务器级 IP 防火墙规则Set up a server-level IP firewall rule using the Azure portal
- 使用 ADO.NET 和 Visual Studi 连接至数据库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.NETQuery data ADO.NET
*如果没有 Azure 订阅,请在开始前创建一个试用版订阅。*If you don't have an Azure subscription, create a Trial Subscription before you begin.
提示
以下 Microsoft Learn 模块可帮助你免费学习如何开发和配置可查询 Azure SQL 数据库的 ASP.NET 应用程序,包括创建简单数据库。The following Microsoft Learn module helps you learn for free how to Develop and configure an ASP.NET application that queries an Azure SQL Database, including the creation of a simple database.
先决条件Prerequisites
安装 Visual Studio 2019 或更高版本。An installation of Visual Studio 2019 or later.
在 Azure SQL 数据库中创建空数据库Create a blank database in Azure SQL Database
在 Azure SQL 数据库中创建数据库时,会使用定义好的一组计算和存储资源。A database in Azure SQL Database is created with a defined set of compute and storage resources. 数据库在 Azure 资源组中创建,使用逻辑 SQL 服务器进行托管。The database is created within an Azure resource group and is managed using an logical SQL server.
按照以下步骤创建空数据库。Follow these steps to create a blank database.
在 Azure 门户的左上角单击“创建资源”。Click Create a resource in the upper left-hand corner of the Azure portal.
在“新建”页上的“Azure 市场”部分中选择“数据库”,然后在“特别推荐”部分中单击“SQL 数据库” 。On the New page, select Databases in the Azure Marketplace section, and then click SQL Database in the Featured section.
如上图所示,在“SQL 数据库”表单中填写以下信息: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 有关有效的资源组名称,请参阅 Naming rules and restrictions(命名规则和限制)。For valid resource group names, see Naming rules and restrictions. 选择源Select source 空白数据库Blank database 指定应创建空白数据库。Specifies that a blank database should be created. 单击“服务器”,使用现有服务器或创建和配置新服务器。Click Server to use an existing server or create and configure a new 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 有关有效的服务器名称,请参阅 Naming rules and restrictions(命名规则和限制)。For valid server names, see Naming rules and restrictions. 服务器管理员登录名Server admin login 任何有效的名称Any valid name 如需有效的登录名,请参阅Database Identifiers(数据库标识符)。For valid login names, see Database identifiers. 密码Password 任何有效的密码Any valid password 密码必须至少有八个字符,且必须使用以下类别中的三个类别的字符:大写字符、小写字符、数字以及非字母数字字符。Your password must have at least eight characters and must use characters from three of the following categories: uppercase characters, lowercase characters, numbers, and non-alphanumeric characters. 位置Location 任何有效的位置Any valid location 中国东部、中国东部 2、中国北部、中国北部 2China East, China East 2, China North, China North 2 单击“选择”。Click Select.
单击“定价层”,指定服务层级、DTU 或 vCore 数,以及存储量。Click Pricing tier to specify the service tier, the number of DTUs or vCores, and the amount of storage. 可以浏览相关选项,了解每个服务层级可提供的 DTU/vCore 数和存储。You may explore the options for the number of DTUs/vCores and storage that is available to you for each service tier.
选择服务层、DTU 数或 vCore 数以及存储量后,然后单击“应用”。After selecting the service tier, the number of DTUs or vCores, and the amount of storage, click Apply.
输入空白数据库的“排序规则”(就本教程来说,请使用默认值)。Enter a Collation for the blank database (for this tutorial, use the default value). 有关排序规则的详细信息,请参阅 Collations(排序规则)For more information about collations, see Collations
完成 SQL 数据库表单后,即可单击“创建”对数据库进行预配 。Now that you've completed the SQL Database form, click Create to provision the database. 这个步骤可能需要几分钟的时间。This step may take a few minutes.
在工具栏上,单击“通知”可监视部署过程。On the toolbar, click Notifications to monitor the deployment process.
创建服务器级 IP 防火墙规则Create a server-level IP firewall rule
SQL 数据库在服务器级别创建 IP 防火墙。SQL Database 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 database, you must first add an IP firewall rule for your IP address (or IP address range). 遵循这些步骤创建服务器级 IP 防火墙规则。Follow these steps to create a server-level IP firewall rule.
重要
通过端口 1433 进行的 SQL 数据库通信。SQL Database 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 database unless your administrator opens port 1433.
部署完成后,在左侧菜单中单击“SQL 数据库”,然后在“SQL 数据库”页上单击“yourDatabase” 。After the deployment is complete, click SQL databases from the left-hand menu and then click yourDatabase on the SQL databases page. 此时会打开数据库的概览页,显示完全限定的 服务器名称(例如 yourserver.database.chinacloudapi.cn),并且会提供进行进一步配置所需的选项。The overview page for your database opens, showing you the fully qualified Server name (such as yourserver.database.chinacloudapi.cn) and provides options for further configuration.
复制此完全限定的服务器名称,将其用于从 SQL Server Management Studio 连接到服务器和数据库。Copy this fully qualified server name for use to connect to your server and databases from SQL Server Management Studio.
单击工具栏上的“设置服务器防火墙”。Click Set server firewall on the toolbar. 此时会打开服务器的“防火墙设置”页面。The Firewall settings page for the server opens.
在工具栏上单击“添加客户端 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.
单击“保存” 。Click Save. 此时会针对当前的 IP 地址创建服务器级 IP 防火墙规则,在服务器上打开端口 1433。A server-level IP firewall rule is created for your current IP address opening port 1433 on the server.
单击“确定”,并关闭“防火墙设置”页。 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 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 数据库 IP 防火墙进行访问。By default, access through the SQL Database IP firewall is enabled for all Azure services. 在此页上单击“关”即可禁用对所有 Azure 服务的访问。Click OFF on this page to disable access for all Azure services.
C# 程序示例C# program example
本文的后续部分介绍了一个 C# 程序,该程序使用 ADO.NET 将 Transact-SQL (T-SQL) 语句发送到 SQL 数据库。The next sections of this article present a C# program that uses ADO.NET to send Transact-SQL (T-SQL) statements to SQL Database. 该 C# 程序演示以下操作:The C# program demonstrates the following actions:
- 使用 ADO.NET 连接到 SQL 数据库Connect to SQL Database using ADO.NET
- 返回 T-SQL 语句的方法Methods that return T-SQL statements
- 创建表Create tables
- 在表中填充数据Populate tables with data
- 更新、删除和选择数据Update, delete, and select data
- 将 T-SQL 提交到数据库Submit T-SQL to the database
实体关系图 (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.
备注
可以选择编辑 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:
在 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) .
在 Program.cs 文件中,通过以下步骤替换起始代码行:In the file Program.cs , replace the starter lines of code with the following steps:
按相同的显示顺序复制并粘贴以下代码块;请参阅连接到数据库、生成 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.
更改
Main
方法中的以下值:Change the following values in theMain
method:- cb.DataSourcecb.DataSource
- cb.UserIDcb.UserID
- cb.Passwordcb.Password
- cb.InitialCatalogcb.InitialCatalog
验证是否已引用程序集 System.Data.dll 。Verify the assembly System.Data.dll is referenced. 若要进行验证,请在“解决方案资源管理器”窗格中展开“引用”节点。 To verify, expand the References node in the Solution Explorer pane.
若要在 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 数据库Connect 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.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 语句的方法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:
- 使用 Azure 门户创建数据库Create a database using the Azure portal
- 通过 Azure 门户设置服务器级 IP 防火墙规则Set up a server-level IP firewall rule using the Azure portal
- 使用 ADO.NET 和 Visual Studi 连接至数据库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.NETQuery data ADO.NET
请转到下一教程,了解数据迁移。Advance to the next tutorial to learn about data migration.