在上一篇，我們完成交友任務管理的API雛形開發。
在本篇，我們要＂玩真的＂，將資料庫（Azure Table Storage)串接到API上！

在進行開發之前，我們先來快速地介紹一下Azure Table Storage!

Azure Table Storage簡介

他是表格式的儲存體，適合較為結構化的資料，例如會員資料，圖書館書籍資料，購物車。

大家可能會聯想到SQL Server，但實際上它不是關聯式資料庫RDBMS)。比較類似Redis的Key-Value儲存。

如果你有時可能會彈性的加欄位，那Azure Table Storage是你很好的選擇!

Azure Table Storage的好處

接下來我們直接進入正題，準備來串接Azure Table Storage來開發程式囉！

完整程式碼下載：https://github.com/hatsukiotowa/AzureFunctionsRestAPI_TableStorage

1. 從Azure Portal手動新增Table

選擇Storage Account > Overview > Tables

新增Table > 輸入Table名稱

看到Table已經建立完成

完成後，我們無法直接在Portal上新增資料，必須使用Microsoft Azure Storage Explorer，或是Visual Studio 2017的Cloud Explorer進行操作。

在這裡我們使用Visual Studio 2017的Cloud

2. 開啟Visual，開啟Cloud Explorer

這邊快速的介紹一下，Partition Key和Row Key：

在Key-Value儲存的世界中,Partition Key有點像資料放置的位置，是一個Hash

而Row Key和Partition一同搭配使用，用來作為做排序的依據

3. 接下來我們要先修改之前寫的Models.cs程式碼，加上串接Table Storage需要的Entity和Mappings

針對Azure Table Storage的Entity與ToDo進行轉換

public static class Mappings
{
    public static TodoTableEntity ToTableEntity(this Todo todo)
    {
        return new TodoTableEntity()
        {
            PartitionKey = "TODO",
            RowKey = todo.Id,
            CreatedTime = todo.CreatedTime,
            IsCompleted = todo.IsCompleted,
            MissionDescription = todo.MissionDescription
        };
    }

    public static Todo ToTodo(this TodoTableEntity todo)
    {
        return new Todo()
        {
            Id = todo.RowKey,
            CreatedTime = todo.CreatedTime,
            IsCompleted = todo.IsCompleted,
            MissionDescription = todo.MissionDescription
        };
    }

}

4. 修改AzureWebJobsStorage及Dashboard連接值(local.settings.json)

相關資料可以在Account Storage的Access Keys查看

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "DefaultEndpointsProtocol=https;AccountName={YourAccountName};AccountKey={YourAccountKey};BlobEndpoint=https://{YourAccountName}.blob.core.windows.net/;TableEndpoint=https://{YourAccountName}.table.core.windows.net/;QueueEndpoint=https://{YourAccountName}.queue.core.windows.net/;FileEndpoint=https://{YourAccountName}.file.core.windows.net/",
    "AzureWebJobsDashboard": "DefaultEndpointsProtocol=https;AccountName={YourAccountName};AccountKey={YourAccountKey};BlobEndpoint=https://{YourAccountName}.blob.core.windows.net/;TableEndpoint=https://{YourAccountName}.table.core.windows.net/;QueueEndpoint=https://{YourAccountName}.queue.core.windows.net/;FileEndpoint=https://{YourAccountName}.file.core.windows.net/",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet"
  },
  "Host": {
    "CORS": "*"
  }
}

5. 新建交友任務(POST /api/missions)串接Azure Table Storage

完整程式碼：

[FunctionName("CreateTodo")]
public static async Task<IActionResult> CreateTodo(
    [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "missions")]HttpRequest req, 
    [Table("Todos", Connection = "AzureWebJobsStorage" )] IAsyncCollector<TodoTableEntity> todoTable,
    TraceWriter log)
{
    log.Info("Creating a new todo list item");

    string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    var input = JsonConvert.DeserializeObject<TodoCreateModel>(requestBody);

    var todo = new Todo() { MissionDescription = input.MissionDescription };
    await todoTable.AddAsync(todo.ToTableEntity());
    return new OkObjectResult(todo);
}

在修改的程式碼裡，我們在參數增加下行，串接Azure Table Storage，Connection裡的AzureWebJobsStorage，即是我們在local.settings.json定義的連線資訊

 [Table("Todos", Connection = "AzureWebJobsStorage" )] IAsyncCollector<TodoTableEntity> todoTable,

我們將todo的item轉換成Table Storage能讀取的TableEntity

await todoTable.AddAsync(todo.ToTableEntity());

6. 取得交友任務(GET /api/missions)串接Azure Table Storage

完整程式碼：

[FunctionName("GetTodos")]
public static async Task<IActionResult> GetTodos(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "missions")]HttpRequest req,
    [Table("Todos", Connection = "AzureWebJobsStorage")] CloudTable todoTable,
    TraceWriter log)
{
    log.Info("Getting todo list items");
    var query = new TableQuery<TodoTableEntity>();
    var segment = await todoTable.ExecuteQuerySegmentedAsync(query, null);
    return new OkObjectResult(segment.Select(Mappings.ToTodo));
}

CloudTable提供我們Query Table的功能

[Table("Todos", Connection = "AzureWebJobsStorage")] CloudTable todoTable

我們Query Table內的所有資料

var query = new TableQuery<TodoTableEntity>();
var segment = await todoTable.ExecuteQuerySegmentedAsync(query, null);

回傳轉成Todo的Objects

return new OkObjectResult(segment.Select(Mappings.ToTodo));

7.取得交友任務(GET /api/mission/{id})串接Azure Table Storage

[FunctionName("GetTodoById")]
public static async Task<IActionResult> GetTodoById(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "mission/{id}")]HttpRequest req,
    [Table("Todos", "TODO", "{id}", Connection = "AzureWebJobsStorage")] TodoTableEntity todo,
    TraceWriter log, string id)
{
    log.Info("Getting todo item by id");
    if (todo == null)
    {
        log.Info($"Item {id} not found");
        return new NotFoundResult();
    }
    return new OkObjectResult(todo.ToTodo());
}

Table的第一個參數是Table Name, TODO是我們指定的Partition Key， {id}是Row key

 [Table("Todos", "TODO", "{id}", Connection = "AzureWebJobsStorage")] TodoTableEntity todo,

8. 修改交友任務(PUT /api/mission/{id})串接Azure Table Storage

完整程式碼：

[FunctionName("UpdateTodo")]
public static async Task<IActionResult> UpdateTodo(
    [HttpTrigger(AuthorizationLevel.Anonymous, "put", Route = "mission/{id}")]HttpRequest req,
    [Table("Todos", Connection = "AzureWebJobsStorage")] CloudTable todoTable,
    TraceWriter log, string id)
{


    string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    var updated = JsonConvert.DeserializeObject<TodoUpdateModel>(requestBody);

    var findOperation = TableOperation.Retrieve<TodoTableEntity>("TODO", id);
    var findResult = await todoTable.ExecuteAsync(findOperation);
    if (findResult.Result == null)
    {
        return new NotFoundResult();
    }

    var existingRow = (TodoTableEntity)findResult.Result;
    existingRow.IsCompleted = updated.IsCompleted;
    if (!string.IsNullOrEmpty(updated.MissionDescription))
    {
        existingRow.MissionDescription = updated.MissionDescription;
    }
    var replaceOperation = TableOperation.Replace(existingRow);
    await todoTable.ExecuteAsync(replaceOperation);

    return new OkObjectResult(existingRow.ToTodo());
}

使用ID找到對應的item進行修改

var findOperation = TableOperation.Retrieve<TodoTableEntity>("TODO", id);
var findResult = await todoTable.ExecuteAsync(findOperation);
if (findResult.Result == null)
{
    return new NotFoundResult();
}

修改並覆蓋已有的資料

var existingRow = (TodoTableEntity)findResult.Result;
existingRow.IsCompleted = updated.IsCompleted;
if (!string.IsNullOrEmpty(updated.MissionDescription))
{
    existingRow.MissionDescription = updated.MissionDescription;
}
var replaceOperation = TableOperation.Replace(existingRow);
await todoTable.ExecuteAsync(replaceOperation);

9. 刪除交友任務(DELETE /api/mission/{id})串接Azure Table Storage

完整程式碼：

[FunctionName("DeleteTodo")]
public static async Task<IActionResult> DeleteTodo(
    [HttpTrigger(AuthorizationLevel.Anonymous, "delete", Route = "mission/{id}")]HttpRequest req,
    [Table("Todos", Connection = "AzureWebJobsStorage")] CloudTable todoTable,
    TraceWriter log, string id)
{
    var deleteOperation = TableOperation.Delete(new TableEntity()
    { PartitionKey = "TODO", RowKey = id, ETag = "*" });
    try
    {
        var deleteResult = await todoTable.ExecuteAsync(deleteOperation);
    }catch(StorageException e) when (e.RequestInformation.HttpStatusCode == 404)
    {
        return new NotFoundResult();
    }
    return new OkResult();
}

這邊比較特別的是etag，是針對Concurrency進行優化。
設定成ETag = "*" 之後，實作"last-write-wins"的機制

var deleteOperation = TableOperation.Delete(new TableEntity()
    { PartitionKey = "TODO", RowKey = id, ETag = "*" });

10.實際測試之後，可以到Cloud Explorer查看資料是否成功寫入Azure Table Storage上

完整程式碼下載：https://github.com/hatsukiotowa/AzureFunctionsRestAPI_TableStorage

完成Table Storage的實作後，大家會不會也想了解其他的儲存方式呢？

在下一篇我們將介紹Azure Queue Storage，是另一種儲存形式，快速連結在此：Azure Queue Storage介紹

參考資料及圖片來源

• http://dog0416.blogspot.com/2018/05/azure-azure-tables-storage.html
• https://app.pluralsight.com/library/courses/microsoft-azure-table-storage-implementing
• https://anthonychu.ca/post/azure-functions-update-delete-table-storage/