使用 SQL 开发你的 REST API

• 下载 SqlWebApi.zip - 21.2 MB

引言

演示如何使用 TSQL 存储过程为 WebApi 创建 Visual Studio 2015 解决方案

背景

如果您有一个 SQL Server 数据库，并且希望移动应用程序能够处理您的数据，那么您就需要一个 API。

使用 Microsoft 工具，这意味着需要编写一个 WebApi 应用程序。通常，您会使用 C# 编写 API，并使用 Linq 和 Entity Framework 进行编码。您会用数据模型和存储库来构建应用程序。

我想向您展示一种不同的方法。使用存储过程开发 WebApi 后端。

我将使用我自己的开源 nuget 包 apirIO

REST、资源和 HTTP

REST 关注的是资源。资源是指一个对象，例如产品或产品列表。

HTTP 用于通过 GET、PUT、POST、DELETE 等词来操作资源。

动词	URI	描述
GET	http://myserver/products	检索产品数组
GET	http://myserver/products/1	检索 ID 为 1 的产品
PUT	http://myserver/products/1	使用正文中的值更新产品 1
POST	http://myserver/products	添加产品
删除	http://myserver/products/99	删除产品

资源和 SQL

使用 SQL，我们开发存储过程来创建、读取、更新和删除产品。

所需的 HTTP 动词是 POST、GET、PUT、DELETE。

按照惯例，存储过程应以 API、资源名称和 HTTP 动词作为前缀。对于示例中的产品资源，我们需要实现以下存储过程：

    CREATE PROCEDURE API_Products_POST(@Name VARCHAR(100))
    CREATE PROCEDURE API_Products_GET(@ID int = NULL)
    CREATE PROCEDURE API_Products_PUT(@ID,@Name VARCHAR(100))
    CREATE PROCEDURE API_Products_DELETE(@ID int)

示例表

首先创建一个数据库，然后是示例产品表。

CREATE TABLE Products
(
    ProductID int IDENTITY(1,1) PRIMARY KEY,
    ProductName varchar(100) NOT NULL
)
GO
INSERT INTO Products(ProductName) VALUES ('Widget 1')
GO
INSERT INTO Products(ProductName) VALUES ('Widget 2')
GO

读取和定义资源。

在 GET 存储过程中，我们返回一个或多个资源。此过程还定义了资源的结构。

CREATE PROCEDURE API_Products_Get (@ID int = NULL) 
AS
    SELECT ProductId, ProductName 
        FROM Products 
        WHERE ProductID = @ID OR  @ID IS NULL

更新资源

更新存储过程将响应 Put HTTP 动词。在此示例中，我们希望能够更改产品的名称。

CREATE PROCEDURE API_Products_Put(@ID int, @ProductName VARCHAR(100))
AS
    UPDATE Products SET ProductName = @ProductName 
    WHERE ProductID = @ID

这可能是一个引入一些错误处理的好地方。我们看到两个参数没有默认值。如果没有设置值，运行时将发生错误。

我们可以检查 @ID 是否有效

ALTER PROCEDURE API_Products_Put(@ID int, @ProductName VARCHAR(100))
AS
    IF NOT EXISTS(SELECT ProductID FROM Products 
    WHERE ProductID = @ID) 
    BEGIN
            RAISERROR('Unknown Product',1,1)
            RETURN 400
    END
    UPDATE Products SET ProductName = @ProductName 
    WHERE ProductID = @ID
    RETURN 200 –- OK

RAISERROR 使用严重性级别 1，这意味着 TSQL 中的警告。执行会继续并返回 400，这将是 HTTP 返回代码。消息“未知产品”将作为消息返回给调用者。如果 UPDATE 成功，则返回 200。

注意：如果您省略 RETURN 200，将返回零，在返回给调用者之前，它将被转换为 200。

创建资源

POST 存储过程可能很简单

CREATE PROCEDURE API_Products_Post(@ProductName VARCHAR(100))
AS
    INSERT INTO Products(ProductName) VALUES(@ProductName)

能够返回新创建行的 ID 会很有用。我们可以这样做：

CREATE PROCEDURE API_Products_Post(
@ProductName VARCHAR(100), @NewId int OUTPUT)
AS
    INSERT INTO Products(ProductName) VALUES(@ProductName)
    SET @NewId  = @@IDENTITY
    RETURN 200

Apir 会构建新产品的 URI，并在 HTTP 头部将其返回给客户端。

删除资源

最后，delete 存储过程可能很简单

CREATE PROCEDURE API_Products_Delete(@ID int)
AS
    DELETE FROM Products WHERE ProductID = @ID

创建 WebApi 站点

有了这四个存储过程，我们就拥有了产品资源所需的所有代码。

让我们使用 Visual Studio 2015 来创建站点。

使用标准的 ASP.NET Web 应用程序模板，我们创建 SqlWebApi

启动一个简单的 WebApi 项目

项目准备就绪后，从工具菜单启动程序包管理器控制台

Install-Package ApirIO

在 web.config 中添加与数据库的连接字符串

<configuration>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=localhost\sqlexpress;Initial Catalog=myDatabase;Integrated Security=True" />
  </connectionStrings>

启动您的 WebApi

我们编写了多少行 C# 代码？没有，并且我们已经准备好测试 API 了

只需按 F5 即可启动项目的调试运行。

您将看到标准的首页。如果您单击标题中的 API，将显示 ApiControllers。示例 ValuesController 就在那里，还有新的产品。

尝试通过在浏览器中输入 URL 来运行 API_Products_Get 存储过程

https://:50608/api/products

您将根据浏览器以 XML 或 JSON 格式获取产品列表。

Swagger 测试和文档工具

Swashbuckle 是一个很棒的 nuget 包，它将为您提供一个用于测试 API 的 GUI。

从工具菜单启动程序包管理器控制台

Install-Package Swashbuckle -Version 5.5.3

重新启动您的项目并打开 URL

https://:50608/swagger

您已经为 API 添加了 Swagger GUI。

ApirIO 的工作原理

应用程序启动时，ApirIO 会分析您的 API* 存储过程并生成 C# 代码。生成的控制器继承自 ASP.NET ApiControllers。

代码会在运行时编译并加载到项目中。默认情况下，C# 源文件位于 App_Data 文件夹中。

如果 ApirIO 无法编译生成的 C# 代码，则会将错误日志 swaError.txt 写入同一文件夹。

ApirIO 使用 ADO.NET 进行数据库访问。使用名为“DefaultConnection”的连接。

每次启动项目时都会生成代码。如果添加存储过程或更改参数，则需要重新启动应用程序。

记录 API

多年来，C# 开发人员一直使用 XML 注释来记录代码。这些注释被 Visual Studio 和 Swashbuckle 等工具使用。ApirIO 允许您注释存储过程并将任何注释移动到生成的 C# 代码中。这对 API 文档来说非常棒，因为它可以在 Swagger 中使用，并且与数据库一起存在。

让我们修改用于插入新产品的存储过程，使其更具生产环境的适用性。

--- <summary> 
--- Add a new product to the database
--- </summary>  
--- <remarks> A link to the new product is returned  </remarks> 
--- <response code="201">OK</response>
--- <response code="521">Bad productname</response>
--- <response code="522">Product with name exists</response>
CREATE PROCEDURE API_Products_Post(
        @ProductName VARCHAR(100), 
        @NewId int OUTPUT)
AS
    IF (@ProductName IS NULL OR LEN(@ProductName) < 2) 
    BEGIN
            RAISERROR('Bad product name',1,1)
            RETURN 521
    END
    IF EXISTS (SELECT ProductId FROM Products WHERE ProductName = @ProductName)
    BEGIN
            RAISERROR('Product with that name already exists',1,1)
            RETURN 522
    END

    INSERT INTO Products(ProductName) VALUES(@ProductName)
    SET @NewId  = @@IDENTITY
    RETURN 201

要在 Swagger 中添加 XML 注释，需要在 App_Start\SwaggerConfig.cs 文件中的第 100 行插入以下行：

    //c.IncludeXmlComments(GetXmlCommentsPath());
    c.IncludeXmlComments(AppDomain.CurrentDomain.GetData("DataDirectory").ToString() + "\\xmlComments.xml");

您还需要 using "System" 来使用 AppDomain。

尝试添加产品。成功添加产品后，您将获得 201。请注意响应头部。它将包含

    "location": "https://:50608/api/products/2", 

这是已创建资源的 URI。

摘要

通过几个简单的存储过程，我们为产品表创建了一个 REST API。

在生产环境中，您应该创建一个 API 用户并限制访问权限，使其只能执行 API* 存储过程。

生成的 API 获得了 ASP.NET ApiController 类提供的所有功能。

您不必编写一行 C# 代码。

创建的 Asp.Net 站点将适用于具有 API_* 存储过程的任何数据库。对于另一个 API 项目，只需复制网站并更改连接字符串即可。

下载中包含的示例项目带有可加载的数据库。App_Data\ApirTest.sql 包含用于构建表和存储过程的脚本。尝试运行它，然后访问 https://:60361/swagger 

关注点

我发现使用 TSQL 编写和维护后端逻辑比 C# 更容易。即使在我进行了多个 Entity Framework 项目之后，我仍然觉得 TSQL 更简单快捷。TSQL 存储过程可以进行单元测试。它们与数据库一起存在，并且不容易与数据库不同步。

存储过程在 SQL Server 上已经存在了很长时间。它们几乎没有改变。Microsoft 的数据库访问工具会频繁更新。

很长一段时间以来，我一直认为新开发者应该学习 C#。现在，对于移动和 Web 开发来说，javascript 和 SQL 可能就是您所需要的全部。