ADO.NET 架构师中级开发 SQL Server SQL ASP.NET C#

使用 ASP.NET Core Web API 和 Azure SQL Database 构建 REST 服务

Jovan Popovic(MSFT)

4.93/5 (34投票s)

2016年6月14日

CPOL

16分钟阅读

170838

831

如何使用 ASP.NET Core Web API 和 Azure SQL Database 构建 REST 服务

简介

在本文中，我们将了解如何使用 ASP.NET Core Web API 以及 Azure SQL Database 和 SQL Server 2016 中的新 JSON 功能来创建 REST API。

Azure SQL Database 和 SQL Server 2016 提供了内置 JSON 支持，让您可以轻松地从数据库以 JSON 格式获取数据，或者将 JSON 数据加载到表中。这对于返回数据库数据为 JSON 格式的 Web 服务，或者接受 JSON 文本作为参数并将其插入数据库的 Web 服务来说是一个不错的选择。借助 Azure SQL Database 中新增的内置 JSON 支持，表和 JSON 文本之间的转换变得极其容易。
在本文中，我们将了解如何轻松地使用 Azure SQL Database 和新的 JSON 功能来构建 ASP.NET Core Web API。

我们将构建哪种类型的 REST 服务？

此 REST 服务是使用新的 ASP.NET Core 框架构建的。在最新发布的 ASP.NET 版本中，您可以使用 .NET Framework 或 .NET Core 来创建 ASP.NET Core Web 应用程序。如果需要最小化的环境，并且可以在任何平台上使用，请选择 .NET Core。

更新

.NET Core 仍在开发中，在 RTM 之前可能会有一些重大更改，因此这里我使用的是 .NET Framework。但是，核心原则同样适用于使用 .NET Framework 和 .NET Core 创建的 ASP.NET Core 应用程序。

在撰写本文时，我曾使用 ASP.NET Core RC1，然后升级到 RC2，后者包含一些小的重大更改，导致我不得不重写应用程序。因此，我选择使用 .NET Framework 而不是 RC2 Core，因为它在撰写本文时更加稳定。在此期间，.NET Core v1 已发布，应该会更稳定。

但是，核心原则同样适用于使用 .NET Framework 和 .NET Core 创建的 ASP.NET Core 应用程序。由于我使用了 Azure SQL Database 中的 JSON 功能，我的应用程序非常轻量级，我可以对应用程序进行一些小的更改以适应新框架，并且 JSON/SQL 转换的主要逻辑**将保持不变**。如果您愿意，可以轻松地将此应用程序重写为 node.js 或任何其他框架。

更新结束 :)

我将不使用复杂的数据库结构。我们将围绕一个简单的Todo表构建 REST 服务，该表有四列：Title、Description、Completed和TargetDate。

我们的 REST Web API 服务将具有以下 HTTP 方法：

GET，用于返回表中的所有行
GET，用于返回具有指定 ID 的单个行
POST，用于创建/插入表中的新行
PUT，用于更新由 ID 指定的行中的所有字段/列
PATCH，用于更新表中的某些字段
DELETE，用于按 ID 删除行

无模型服务

创建 Web API 时，最初您只会得到一个Controllers 文件夹，您需要在其中放置服务代码。Web API 不强制您添加模型、视图或其他任何架构概念。

然而，人们通常做的第一件事是添加Model文件夹，其中包含映射表到 C# 对象所需的所有域类，并定义序列化这些对象时生成的 JSON 的架构。在某些情况下，他们会添加整个EntityFramework模型来访问数据。

在很多情况下，这些模型仅用作普通的 DTO（数据传输对象），它们只是用于其他框架从 SQL 数据库读取数据或序列化 JSON 响应的模式输入。有时，这些类甚至没有显着的域模型特征或类之间的关系。

Azure SQL Database 中的 JSON 函数使您可以保持服务轻量级和“无模型”。如果创建模型的主要目的是为了从数据库到 JSON 的序列化“模板”，那么如果您不需要它，就没有必要这样做。SQL/JSON 函数可以处理 JSON 和表数据之间的所有转换。

这样，JSON 函数使您可以轻松地将数据库数据暴露给 Web 客户端，而无需额外的转换层。在此示例中，我将创建一个 REST 服务，该服务仅在 SQL 数据库和 Web 客户端之间传递 JSON。

注意：我并不是说模型或 ORM 是坏的。然而，如果您的 REST API 的主要目的是仅仅将数据作为 REST 端点公开，那么您可能不会充分利用 ORM/EF 的全部功能。在这种情况下，最好尝试使用原始形式的 JSON，然后将其传递给数据库。

设置

要运行此示例，我们需要一个包含Todo表的数据库和一个 Web 应用程序。本节将介绍如何设置项目和数据库。

数据库设置

首先，您需要在 Azure SQL Database 或 SQL Server 2016 中创建一个新数据库，并执行以下脚本来创建和填充Todo表。

Setup.sql

DROP TABLE IF EXISTS Todo

GO

CREATE TABLE Todo (
    Id int IDENTITY PRIMARY KEY,
    Title nvarchar(30) NOT NULL,
    Description nvarchar(4000),
    Completed bit,
    TargetDate datetime2
)

GO

INSERT INTO Todo (Title, Description, Completed, TargetDate)
VALUES
('Install SQL Server 2016','Install RTM version of SQL Server 2016', 0, '2016-06-01'),
('Get new samples','Go to github and download new samples', 0, '2016-06-02'),
('Try new samples','Install new Management Studio to try samples', 0, '2016-06-02')

此 SQL 脚本将创建一个简单的Todo表，并用三行数据填充它。
作为替代方案，您可以使用 SQL Server Management Studio/Import Data-tier application 导入 .bacpac 文件，这将恢复数据库并填充表。

您还需要做的另一件事是将兼容性级别设置为130，如果尚未设置的话。

ALTER DATABASE TodoDb SET COMPATIBILITY_LEVEL = 130

最新的兼容性级别启用了我们将用于这些示例的OPENJSON函数。完成此步骤后，您将拥有一个包含一个Todo表的准备好的数据库。

应用程序设置

我使用 Visual Studio 2015 Community Edition 来创建 Web API REST 服务。您可以从此文章下载示例，或者创建一个新项目并选择 ASP.NET Core Web Application (.NET Framework)。

然后，您可以选择项目的 Web API 类型，如果想在 Azure 中托管，还可以选择性地勾选“host in the cloud”复选框。

现在您已经有了 ASP.NET Core Web API 项目，我们可以创建一个新的 REST 服务。

数据库访问组件

我们需要一些类来从数据库读取数据。在本文中，我将不使用 Entity Framework 或类似的工具。由于 Azure SQL Database 将格式化和解析我的 JSON 数据，我可以使用任何简单的 ADO.NET 库来执行纯粹的SqlCommand。

在本文中，我使用了一个轻量级的数据访问库，它封装了基本的数据访问函数。该库名为CLR-Belgrade-SqlClient，可以从 GitHub 下载 - https://github.com/JocaPC/CLR-Belgrade-SqlClient。这是一个小型、轻量级的数据访问库，仅封装了基本的 ADO.NET 类和方法。

Belgrade SQL Client 库遵循类似 CQRS 的模式，其中命令和查询类是分开的。在此库中，我们有两个主要类：

QueryPipe，它执行带有FOR JSON子句的 SQL 查询，并将结果流式传输到某个输出流。在我们的例子中，这个输出流将是 Web API 控制器的Response.Body。
Command，用于执行非读取器查询，这些查询将用于INSERT、UPDATE和DELETE查询。

这个库的一个优点是它完全是async的。它在底层使用 ADO.NET 类的async方法，这可能会提高代码的可伸缩性。

要下载此库，您可以通过 Visual Studio 中的包管理器安装Belgrade.Sql.Client，或者在包管理器控制台中键入以下命令：

Install-Package Belgrade.Sql.Client

如果这不起作用，您可以从 GitHub 下载源代码并将其编译到您的项目中：https://github.com/JocaPC/CLR-Belgrade-SqlClient。

Nuget 包是使用 .NET Framework 4.6 编译的，但源代码是通用的，可以在任何框架（例如 .NET Core）下编译。

请注意，此库不是使用新 SQL Server/Azure SQL Database 中的 JSON 的先决条件。它只是一个帮助我编写更简单代码的辅助库，但您可以使用任何其他可以执行标准 SQL 命令的库。

Azure SQL Database 中的 JSON

Azure SQL Database 提供了以下处理 JSON 的功能：

JSON in Azure SQL database

我们在此图中看到的的主要功能是：

JSON 函数，可以获取 JSON 文本中的值、更新 JSON 文本、检查 JSON 是否有效等。
FOR JSON，将 SQL 查询的结果格式化为 JSON 文本
OPENJSON，打开并解析 JSON 文本，将其转换为表

如果您想了解更多关于 Azure SQL Database 中的 JSON 的信息，我推荐以下文章 - 13 日星期五 - JSON 即将登陆 SQL Server - CodeProject。
现在，我们将了解如何使用这些功能在 Azure SQL Database 中实现基本的 CRUD 操作。

从表中选择行

如果您想从表中选择数据并以 JSON 格式读取它们，只需在 SQL SELECT查询的末尾添加FOR JSON PATH子句即可。

select * from Todo
FOR JSON PATH

结果将是 JSON，而不是表，如下所示。

[
{"Id":1,"Title":"Install SQL Server 2016",
 "Description":"Install RTM version of SQL Server 2016","Completed":false,
 "TargetDate":"2016-06-01T00:00:00"},
{"Id":2,"Title":"Check what's new",
 "Description":"Go to MSDN to see what is new in SQL Server 2016",
 "Completed":false,"TargetDate":"2016-06-01T00:00:00"},
{"Id":3,"Title":"Get new samples",
 "Description":"Go to github and download new samples",
 "Completed":false,"TargetDate":"2016-06-01T00:00:00"},
{"Id":4,"Title":"Try new samples",
 "Description":"Install new Management studio to try samples",
 "Completed":false,"TargetDate":"2016-06-02T00:00:00"}
]

如果从 REST Web API 执行此查询，您可以直接将此结果流式传输到客户端，因为这很可能是客户端期望的输出。

选择单行

Azure SQL Database 使您可以选择单行（通过指定行的 ID）并将其作为单个 JSON 对象返回。

select *
from Todo 
where Id = 3
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

如果添加WITHOUT_ARRAY_WRAPPER选项，Azure SQL Database 将删除包围 JSON 结果的[和]，并返回一个您可以返回给客户端的单个 JSON 对象 - 类似于

{"Id":3,"Title":"Get new samples","Description":"Go to github and download new samples",
 "Completed":false,"TargetDate":"2016-06-01T00:00:00"}

与前一种情况一样，您可以直接通过 REST 服务将此文本返回给客户端。

插入新行

OPENJSON函数解析您发送到数据库的 JSON 文本，并将其转换为table结构。然后，您可以简单地将解析后的结果插入table。

Set @todo = '{"Title":"Get new samples",
              "Description":"Go to github and download new samples",
              "Completed":false,"TargetDate":"2016-06-01T00:00:00"}'

insert into Todo
select *
from OPENJSON(@todo)
WITH( Title nvarchar(30), Description nvarchar(4000), Completed bit, TargetDate datetime2)

OPENJSON将解析@todo变量中的 JSON 文本。在WITH子句中，您可以定义您想从 JSON 文本中读取哪些键，这些键将作为列返回。然后，您只需从OPENJSON中选择结果并将其插入Todo表中。

更新现有行

OPENJSON函数（用于解析 JSON 文本）可用于更新现有行。

set @todo = '{"Title":"Get new samples",
              "Description":"Go to github and download new samples",
              "Completed":false,"TargetDate":"2016-06-01T00:00:00"}'

update Todo
set Title = json.Title, Description = json.Description,
       Completed = json.completed, TargetDate = json.TargetDate
from OPENJSON( @todo )
        WITH(   Title nvarchar(30), Description nvarchar(4000),
                Completed bit, TargetDate datetime2) AS json
where Id = @id

OPENJSON将解析@todo变量中的 JSON 文本，并像在前一个示例中一样，您可以在WITH子句中定义您想从 JSON 文本中读取哪些键。与INSERT不同，我们需要使用来自OPENJSON的结果来UPDATETodo表中的行。

删除

为了删除行，您不需要 JSON，因为行是按id删除的。

DELETE Todo WHERE Id = 3

现在我们知道 SQL 查询会是什么样子，我们只需要添加使用这些查询的 C# 代码，我们就可以拥有 REST 服务。

实现 REST Web API

好的，现在我们有了数据库、项目，并且知道如何在 Azure SQL Database 中使用 JSON，因此我们可以创建一个访问Todo表的 REST 服务。

首先，使用新建/控制器添加一个新控制器，并将其命名为TodoController。TodoController必须引用能够执行 SQL 查询并返回 JSON 的某些类/服务。由于我使用的是CLR-Belgrade-SqlClient，我需要对将执行 SQL 命令的Command和Query对象进行引用。

public class TodoController : Controller
{
        private readonly IQueryPipe SqlPipe;
        private readonly ICommand SqlCommand;

        public TodoController(ICommand sqlCommand, IQueryPipe sqlPipe)
        {
            this.SqlCommand = sqlCommand;
            this.SqlPipe = sqlPipe;
        }
}

注意：出于简化原因，我添加了对这些服务的引用，但在您的应用程序中，您可能会使用某种类型的存储库而不是服务。

在此示例项目中，我使用了内置的 ASP.NET 5 依赖注入，通过构造函数注入。在 ASP.NET 5 中，您有一个新的Startup.cs类，您可以在其中添加应用程序中的控制器和其他组件将使用的所有服务。服务在Startup类中的ConfigureServices方法中添加。

public void ConfigureServices(IServiceCollection services)
{
    const string ConnString = "Server=db.database.windows.net;Database=TodoDb;
                               User Id=usr;Password=pwd";

    services.AddTransient<IQueryPipe>( _=> new QueryPipe(new SqlConnection(ConnString)));
    services.AddTransient<ICommand>( _=> new Command(new SqlConnection(ConnString)));

    // Add framework services.
    services.AddMvc();
}

这里，我添加了带有接口IQueryPipe和ICommand的瞬时服务，这些服务使用 lambda 表达式进行初始化。您只需在连接字符串中设置您的服务器、数据库和用户名。
这不是一个强制性的方法，您可以使用任何其他依赖注入框架（ninject 或 autofac），或者使用任何其他方法初始化这些对象。您甚至可以直接初始化Pipe/Command对象。

IQueryPipe SqlPipe = new QueryPipe(new SqlConnection( "Connection string ")));
ICommand SqlCommand = new Command(new SqlConnection("Connection string ")));

注意：如果您使用的是 .NET Core，您将需要一个包含 SqlConnection 类的程序包或程序集。

现在我们可以开始实现 REST Web API 服务的 CRUD 方法。QueryPipe有一个名为Stream的方法，它将 SQL 查询的结果流式传输到输出流。它有两个参数：

SQL 命令文本（string）或 SQL 命令对象（SqlCommand），将要执行
OutputStream，将执行的 SQL 查询的结果刷新到该流

以下代码展示了SqlPipe类的用法示例：

SqlPipe.Stream("SELECT * FROM sys.tables FOR JSON PATH", Response.Body);

SqlPipe将执行带有FOR JSON子句的 SQL 查询，并将结果直接流式传输到输出流，在本例中是Response.Body，因为我们将结果作为响应体返回给客户端。
现在，我们可以向TodoController添加方法来实现在基本 CRUD 操作。

返回 Todo 表中的所有记录（GET）

首先，我们将添加一个方法，当用户调用GET API/Todo URL 时会调用该方法。此 URL 将返回Todo表中的所有对象。

// GET api/Todo
[HttpGet]
public async Task Get()
{
    await SqlPipe.Stream("select * from Todo FOR JSON PATH", Response.Body, "[]");
}

首先要注意的是，这是一个async方法（async Task），它会将 SQL 查询的结果流式传输到Response.Body。由于Belgrade.SqlClient是一个async库，您可以使用await关键字调用Stream方法。
调用/api/Todo的某个 Web 客户端将看到格式化为 JSON 的 SQL 查询结果。如果您使用 https://:15194/api/Todo 调用Todo Get方法，您将获得类似如下的结果：

如您所见，我们需要一行代码才能在此 REST 服务中返回数据。Stream方法的第三个参数定义了在没有返回数据时应返回什么 - 在我们的例子中是空数组。

返回 Todo 表中的单个记录（GET）

现在我们有了所有Todo项目的列表，我们需要一个方法来返回按id查询的Todo。

// GET api/Todo/5
[HttpGet("{id}")]
public async Task Get(int id)
{
    var cmd = new SqlCommand("select * from Todo where Id = @id FOR JSON PATH, 
                              WITHOUT_ARRAY_WRAPPER");
    cmd.Parameters.AddWithValue("id", id);
    await SqlPipe.Stream(cmd, Response.Body, "{}");
}

这也是一个异步方法，它会将 SQL 查询的结果流式传输到Response.Body。调用/api/Todo/1的客户端将看到带有FOR JSON子句的 SQL 查询结果。

第三个参数定义了在没有返回数据时应返回什么 - 在这种情况下是空对象。响应的示例显示在下图

好的，现在我们已经实现了所需的GET方法，接下来我们将继续实现更新数据的方法。

向 Todo 表插入新记录（POST）

为了添加新的Todo项，我需要一个响应POST请求的方法。

// POST api/Todo
[HttpPost]
public async Task Post()
{
    string todo = new StreamReader(Request.Body).ReadToEnd();
    var cmd = new SqlCommand(
@"insert into Todo
select *
from OPENJSON(@todo)
WITH( Title nvarchar(30), Description nvarchar(4000), Completed bit, TargetDate datetime2)");
    cmd.Parameters.AddWithValue("todo", todo);
    await SqlCommand.ExecuteNonQuery(cmd);
}

您可以注意到我只是复制了上一节中的OPENJSON查询，并将其包装在 C# 代码中。这是一个async方法，它将从request正文中读取 JSON，定义SqlCommand并将输入 JSON 作为参数提供。JSON 将在OPENJSON命令中解析并插入表中。

注意：如果您使用的是 .NET Core，您将需要一个包含 StreamReader 类的程序包或程序集。

如果您打开一个可以向服务器发送 HTTP 请求的工具，例如 Chrome Poster，您可能会得到以下结果：

更新 Todo 表中的现有记录（PUT）

现在我们需要实现PUT方法，该方法更新由id指定的行的值。您可以添加类似如下的代码：

// PUT api/Todo/5
[HttpPut("{id}")]
public async Task Put(int id)
{
    string todo = new StreamReader(Request.Body).ReadToEnd();
    var cmd = new SqlCommand(
@"update Todo
set Title = json.Title,
    Description = json.Description,
    Completed = json.completed,
    TargetDate = json.TargetDate
from OPENJSON( @todo )
WITH(   Title     nvarchar(30), Description nvarchar(4000),
        Completed bit,          TargetDate  datetime2) AS json
where Id = @id");
    cmd.Parameters.AddWithValue("id", id);
    cmd.Parameters.AddWithValue("todo", todo);
    await SqlCommand.ExecuteNonQuery(cmd);
}

这是一个async方法，它将从request正文中读取 JSON，定义SqlCommand并将输入 JSON 和 id 作为参数提供。JSON 将在OPENJSON命令中解析，并且指定 id 的行将被更新。

注意：如果您使用的是 .NET Core，您将需要一个包含 StreamReader 类的程序包或程序集。
注意：我发现有些人对 ReadToEnd() 方法有问题（在我机器上可以工作 :)）。您可以使用其他方法来读取请求的正文，例如使用 MemoryStream。

部分更新 Todo 表中的现有记录（PATCH）

许多 REST 服务都支持PUT和PATCH方法。PATCH方法与PUT类似，但PUT会覆盖所有内容，如果输入 JSON 中缺少某些字段，则会插入null值，而PATCH只会更新 JSON 中提供的那些字段。PATCH的代码可能如下所示：

// PATCH api/Todo
[HttpPatch]
public async Task Patch(int id)
{
    string todo = new StreamReader(Request.Body).ReadToEnd();
    var cmd = new SqlCommand(
@"
update Todo
set Title = ISNULL(json.Title, Title),
    Description = ISNULL(json.Description, Description),
    Completed = ISNULL(json.Completed, Completed),
    TargetDate = ISNULL(json.TargetDate, TargetDate)
from OPENJSON(@todo)
WITH(   Title nvarchar(30), Description nvarchar(4000),
        Completed bit, TargetDate datetime2) AS json
where Id = @id
");
    cmd.Parameters.AddWithValue("id", id);
    cmd.Parameters.AddWithValue("todo", todo);
    await SqlCommand.ExecuteNonQuery(cmd);
}

您可能会注意到PATCH与PUT非常相似。两种方法都使用相似的代码，并通过 id 更新表中的行。关键区别在于ISNULL（json.COLUMN, COLUMN）部分。
PUT代码将更新行中的所有单元格。如果 JSON 中未提供某个key:value，它将插入NULL值，因为如果WITH子句中指定的某个键找不到，OPENJSON会返回NULL。

然而，此代码将检查 JSON 中的值是否为NULL，如果不是NULL，则会将此值写入列。如果值为NULL，则会写入现有列，并且单元格不会被更改。通过这种简单的逻辑，您可以只发送一个应该更新的字段，而其他字段将不会被更改。

从表中删除行（DELETE）

最后，我们需要一个DELETE操作来按id删除行。DELETE操作不需要 Azure SQL JSON 函数，这些函数在 Azure SQL Database 中可用，因此我们只需要简单的代码。

// DELETE api/Todo/5
[HttpDelete("{id}")]
public async Task Delete(int id)
{
    var cmd = new SqlCommand(@"delete Todo where Id = @id");
    cmd.Parameters.AddWithValue("id", id);
    await SqlCommand.ExecuteNonQuery(cmd);
}

此方法将仅从请求中获取提供的id，并通过Id删除Todo表中的行。现在，我们有了一个完整的 REST 服务，每个方法只有几行代码。

结论

借助 Azure SQL Database 中的 JSON 支持，创建接受或返回 JSON 的 REST Web 服务变得非常容易。在本文中，您可能会看到每个 REST 方法都只有几行代码。您甚至不需要 ORM、类模型等。

如果您需要快速创建小型微服务来公开数据库中的几个表，这可能是一个不错的解决方案。只需稍加努力，您甚至可以为控制器生成代码。

建议

在本文中，我将数据访问逻辑放在了控制器的正文中，因为我想要一个简单的示例。在实践中，您会将此代码移到一个单独的数据访问或存储库类中，然后从控制器调用它。

在此代码中，我将 SQL 查询放在了 C# 代码中。我的建议是为所有这些查询创建存储过程，并仅从代码中调用存储过程。过程将很简单，只有一两个参数（id和/或 JSON 文本）。

CREATE PROCEDURE dbo.InsertTodo(@TodoJson NVARCHAR(MAX))
AS BEGIN
insert into Todo
select *
from OPENJSON(@todo)
    WITH(    Title nvarchar(30), Description nvarchar(4000),
        Completed bit, TargetDate datetime2)
END

使用存储过程，您将获得更快的查询和更简单的数据访问逻辑，因为您只需在 C# SqlCommand中放置存储过程名称。

最后，在此项目中，连接字符串内嵌在Startup.cs代码中，但您应该将其移到某个配置文件中。

历史

2016 年 6 月 14 日：初始版本