How to build a web service method that accepts a table of records?

[timl]

I’m trying to build a web service that accepts a table of records from PowerApps.

My intention is to write some generic services to carry out auditing and to improve the performance of multi-record updates.

I’m struggling to work out the correct C# method signature and the corresponding OpenAPI syntax. Can someone see what I might be doing wrong, or provide me with a code sample?

The web method I want to build will accept input records via an HTTP POST.

[HttpPost]
public HttpResponseMessage Post([FromBody]DBRecord[] records)
{
    string responseMsg;
    if (records == null)
    {
        responseMsg = "record argument is null";
    }
    else
    {
        responseMsg = records.ToString();
    }

    //TODO
    //C# code to carry out database work /do business logic will be here        

    var response = new HttpResponseMessage(HttpStatusCode.Created);
    response.Content = new JsonContent(new { Result = "DEBUG - " + responseMsg });

    return response;

}

This method accepts an array of DBRecords. Here's the definition of DBRecord.

public class DBRecord
{
    public string id { get; set; }
    public string desc { get; set; }
}

JsonContent is an object from the Newtonsoft JSON.NET library.

Here's my OpenAPI file. I've highlighted the parts where I attempted to define the input parameter as an array.

 

When I use this to create a custom connector, the designer recognizes the POST parameter as a scalar record, instead of an array of records. I can't find any way in the UI to configure this as an array.

Can someone see what might be wrong here, or provide me with a C# web service method definition and matching OpenAPI syntax that accepts a table of records from PowerApps?

Many thanks!

[v-micsh-msft]

Hi @timl,

You may take a look at the following thread regarding how to pass arrays through Http post action in C#:

Sending an Array in an HTTP POST

Your example here for the 

DBRecord

Seems only defined the Record elements, not a list (Array).

Regards,

Michael 

[CarlosFigueira · Accepted solution]

I created a similar method (using Azure Functions for simplicity) that takes an array of records (and returns another array of records)

using System.Collections.Generic;
using System.Net;
using System.Text;

// https://powerusers.microsoft.com/t5/Connectors/How-to-build-a-web-service-method-that-accepts-a-table-of/m-p/82061
public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    log.Info("C# HTTP trigger function processed a request.");

    // Get request body
    DBRecord[] data = await req.Content.ReadAsAsync<DBRecord[]>();

    List<DBRecord> result = new List<DBRecord>();
    result.Add(new DBRecord { id = "0", desc = "Total of " + (data == null ? "<<NULL>>" : data.Length.ToString()) + " records."});
    foreach (var record in data) {
        result.Add(record);
    }

    return req.CreateResponse(HttpStatusCode.OK, result);
}

public class DBRecord
{
    public string id { get; set; }
    public string desc { get; set; }
}

I then created an Open API file for this operation as follows:

{
  "swagger": "2.0",
  "info": {
    "description": "API to test forum post https://powerusers.microsoft.com/t5/Connectors/How-to-build-a-web-service-method-that-accepts-a-table-of/m-p/82061",
    "version": "1.0.0",
    "title": "ForumThread82061",
    "license": {
      "name": "Apache 2.0",
      "url": "http://www.apache.org/licenses/LICENSE-2.0.html"
    }
  },
  "host": "MYAZUREFUNCTIONSNAME.azurewebsites.net",
  "basePath": "/api",
  "schemes": [
    "https"
  ],
  "paths": {
    "/ForumThread82061": {
      "post": {
        "summary": "Post many records",
        "description": "",
        "operationId": "UpdateRecords",
        "consumes": [
          "application/json"
        ],
        "produces": [
          "application/json"
        ],
        "parameters": [
          {
            "in": "body",
            "name": "body",
            "description": "Records to be updated",
            "required": true,
            "schema": {
              "type": "array",
              "items": {
                "$ref": "#/definitions/RecordItem"
              }
            }
          }
        ],
        "responses": {
          "200": {
            "description": "successful operation",
            "schema": {
              "type": "array",
              "items": {
                "$ref": "#/definitions/RecordItem"
              }
            }
          }
        }
      }
    }
  },
  "definitions": {
    "RecordItem": {
      "type": "object",
      "properties": {
        "id": {
          "type": "string"
        },
        "desc": {
          "type": "string"
        }
      },
      "example": {
        "id": "1",
        "desc": "The description"
      }
    }
  }
}

After creating the custom connector and creating a connection (and waiting for a few minutes for it to be ready), I was able to test the connection. Remember that you'll need to set the "raw body" option to true to enter the array (table) of records.

You can try this example, and see how it compares with yours. I was able to call it using a collection as the parameter from PowerApps.

[timl]

@v-micsh-msft  - Thanks for looking into this for me.

@CarlosFigueira- I really appreciated your effort in putting this example together. It helped me enormously.

It turns out that my original code worked, but I got confused into thinking that it didn't. Wish I could give you more kudos. Thanks once again!