Configuration

To create a configuration for JSON source, you need to configure the tables in the database, each table needs to be separated into four sections: SelectOperation, InsertOperation, UpdateOperation, and DeleteOperation.

The section SelectOperation extracts data for select statements and generates the schema of the table. In the SelectOperation, we configure the Uri, method, and list of fields in the table.
The section InsertOperation builds an insert request to send to the server. In the InsertOperation, we configure the Uri, method, and list of fields to build the insert request body.
The section UpdateOperation builds an update request to send to the server. In the UpdateOperation, we configure the Uri, method, and list of fields to build the update request body.
The section DeleteOperation builds a delete request to send to the server. In the DeleteOperation, we configure the Uri format, method, and parameters to build the request Uri.

Supported Data Types

The supported data types for Column in the Api Config file are:

Type	Description
byte	Represents an unsigned 8-bit integer value.
sbyte	Represents a signed 8-bit integer value.
short	Represents a signed 16-bit integer value.
ushort	Represents an unsigned 16-bit integer value.
int	Represents a signed 32-bit integer value.
uint	Represents an unsigned 32-bit integer value.
long	Represents a signed 64-bit integer value.
ulong	Represents an unsigned 64-bit integer value.
float	Represents a single-precision floating-point number.
double	Represents a double-precision floating-point number.
decimal	Represents a high-precision decimal floating-point number.
char	Represents a Unicode character.
bool	Represents a Boolean value indicating true or false.
string	Represents a sequence of characters.
DateTime	Represents a date and time value.
TimeSpan	Represents a time interval.
DateTimeOffset	Represents a date and time value with an offset from UTC.
Guid	Represents a globally unique identifier.

In the following example, the <Table> tag is used to configure each table in the database and divide it into the sections that were discussed earlier. When used for local files the configuration file needs to have the JSON path attribute.

<Api_Config>
   <Table name="books" jsonpath="$.bookstore.books" >
       <!-- Select Operation -->
       <SelectOperation>
           <Uri></Uri>
           <Method>GET</Method>
           <Response>
               <Column name="_id" isKey="true" type="string">_id</Column>
               <Column name="genre" type="string">genre</Column>
               <Column name="publicationdate" type="date">publicationdate</Column>
               <Column name="ISBN" type="string">ISBN</Column>
               <Column name="title" type="string">title</Column>
               <Column name="author.first-name" type="string">author.first-name</Column>
               <Column name="author.last-name" type="string">author.last-name</Column>
               <Column name="price" type="decimal">price</Column>
               <Column name="readers" type="string">readers</Column>
           </Response>
       </SelectOperation>
       <InsertOperation>
           <Uri></Uri>
           <Method>Post</Method>
           <Body>
               <Column name="_id" isKey="true" type="string">_id</Column>
               <Column name="genre" type="string">genre</Column>
               <Column name="publicationdate" type="date">publicationdate</Column>
               <Column name="ISBN" type="string">ISBN</Column>
               <Column name="title" type="string">title</Column>
               <Column name="author.first-name" type="string">author.first-name</Column>
               <Column name="author.last-name" type="string">author.last-name</Column>
               <Column name="price" type="decimal">price</Column>
               <Column name="readers" type="string">readers</Column>
           </Body>
       </InsertOperation>
       <UpdateOperation>
           <Uri></Uri>
           <Method>PUT</Method>
           <Body>
               <Column name="_id" isKey="true" type="string">_id</Column>
               <Column name="genre" type="string">genre</Column>
               <Column name="publicationdate" type="date">publicationdate</Column>
               <Column name="ISBN" type="string">ISBN</Column>
               <Column name="title" type="string">title</Column>
               <Column name="author.first-name" type="string">author.first-name</Column>
               <Column name="author.last-name" type="string">author.last-name</Column>
               <Column name="price" type="decimal">price</Column>
               <Column name="readers" type="string">readers</Column>
           </Body>
       </UpdateOperation>
       <DeleteOperation>
           <Uri></Uri>
           <Method>DELETE</Method>
           <Paramter name="Param1" type="string">_id</Paramter>
       </DeleteOperation>
   </Table>
   <Table name="readers" jsonpath="$.bookstore.books.readers">
       <!-- Select Operation -->
       <SelectOperation>
           <Uri></Uri>
           <Method>GET</Method>
           <Response>
               <Column name="name" type="string">name</Column>
               <Column name="age" type="int">age</Column>
           </Response>
       </SelectOperation>
       <InsertOperation>
           <Uri></Uri>
           <Method>Post</Method>
           <Body>
               <Column name="name" type="string">name</Column>
               <Column name="age" type="int">age</Column>
           </Body>
       </InsertOperation>
       <UpdateOperation>
           <Uri></Uri>
           <Method>PUT</Method>
           <Body>
               <Column name="name" type="string">name</Column>
               <Column name="age" type="int">age</Column>
           </Body>
       </UpdateOperation>
       <DeleteOperation>
           <Uri></Uri>
           <Method>DELETE</Method>
       </DeleteOperation>
   </Table>
</Api_Config>

{
  "bookstore": {
    "books": [
      {
        "genre": "autobiography",
        "publicationdate": "1981-03-22",
        "ISBN": "1-861003-11-0",
        "title": "The Autobiography of Benjamin Franklin",
        "author": {
          "first-name": "Benjamin",
          "last-name": "Franklin"
        },
        "price": 8.99,
        "readers": [
          {
            "name": "Anna",
            "age": 36
          },
          {
            "name": "David",
            "age": 38
          }
        ]
      },
      {
        "genre": "novel",
        "publicationdate": "1967-11-17",
        "ISBN": "0-201-63361-2",
        "title": "The Confidence Man",
        "author": {
          "first-name": "Herman",
          "last-name": "Melville"
        },
        "price": 11.99,
        "readers": [
          {
            "name": "Luis",
            "age": 33
          },
          {
            "name": "Mark",
            "age": 34
          }
        ]
      },
      {
        "genre": "Fiction - Fantasy",
        "publicationdate": "1997-06-26",
        "ISBN": "0-747532-74-5",
        "title": "Harry Potter and the Philosopher's Stone (Harry Potter, #1)",
        "author": {
          "first-name": "J.K.",
          "last-name": "Rowling"
        },
        "price": 8.99,
        "readers": [
          {
            "name": "Lisa",
            "age": 27
          },
          {
            "name": "Harry",
            "age": 19
          }
        ]
      },
      {
        "genre": "Fiction - Sifi",
        "publicationdate": "1997-06-26",
        "ISBN": "0-123456-78-9",
        "title": "Testing",
        "author": {
          "first-name": "Tester",
          "last-name": "MM"
        },
        "price": 8.99,
        "readers": [
          {
            "name": "Joe",
            "age": 27
          },
          {
            "name": "Harry",
            "age": 19
          }
        ]
      }
    ]
  }
}

To support CRUD for HTTP/HTTPS JSON source, the provider supports custom API configurations using a configuration file. The API configuration file should be an XML containing the configuration for all the CRUD operations supported by the JSON source. Below is an example of an XML API configuration file:

<Api_Config>
   <Table name="Album" >
      <SelectOperation>
         <Uri>"GET_url"/Album</Uri>
         <Method>Get</Method>
         <Response>
            <TableName name="Album" type="string"></TableName>
            <Column name="AlbumId" isKey="true" type="int">AlbumId</Column>
            <Column name="Title" type="string">Title</Column>
            <Column name="ArtistId" type="int">ArtistId</Column>
         </Response>
      </SelectOperation>
      <InsertOperation>
         <Uri>"POST_url"/Album</Uri>
         <Method>Post</Method>
         <Body>
            <TableName name="Album" type="string"/>
            <Column name="AlbumId" type="int">AlbumId</Column>
            <Column name="Title" type="string">Title</Column>
            <Column name="ArtistId" type="int">ArtistId</Column>
         </Body>
      </InsertOperation>
      <UpdateOperation>
         <Uri>"PUT_url"/Album</Uri>
         <Method>PUT</Method>
         <Body>
            <TableName name="Album" type="string"/>
            <Column name="AlbumId" type="int">AlbumId</Column>
            <Column name="Title" type="string">Title</Column>
            <Column name="ArtistId" type="int">ArtistId</Column>
         </Body>
      </UpdateOperation>
      <DeleteOperation>
         <Uri>"DELETE_url"/api/Album/{Param1}</Uri>
         <Method>DELETE</Method>
         <Paramter name="Param1" type="int">AlbumId</Paramter>
      </DeleteOperation>
   </Table>
   <Table name="Customer">
      <SelectOperation>
         <Uri>"GET_url"/Customer</Uri>
         <Method>Get</Method>
         <Response>
            <TableName name="Customer" type="string"></TableName>
            <Column name="CustomerId" isKey="true" type="int">CustomerId</Column>
            <Column name="FirstName" type="string">FirstName</Column>
         </Response>
      </SelectOperation>
      <InsertOperation>
         <Uri>"POST_url"/api/Customer</Uri>
         <Method>Post</Method>
         <Body>
            <TableName name="Customer" type="string"/>
            <Column name="CustomerId" isKey="true" type="int">CustomerId</Column>
            <Column name="FirstName" type="string">FirstName</Column>
         </Body>
      </InsertOperation>
      <UpdateOperation>
         <Uri>"PUT_url"/Customer</Uri>
         <Method>PUT</Method>
         <Body>
            <TableName name="Customer" type="string"/>
            <Column name="CustomerId" isKey="true" type="int">CustomerId</Column>
            <Column name="FirstName" type="string">FirstName</Column>
         </Body>
      </UpdateOperation>
      <DeleteOperation>
         <Uri>"DELETE_url"/Customer/{Param1}</Uri>
         <Method>DELETE</Method>
         <Paramter name="Param1" type="int">CustomerId</Paramter>
      </DeleteOperation>
   </Table>
</Api_Config>

The API configuration file can be passed to the provider through the API Config File property in the connection string:

static string csvConnectionString = $"Uri='<uri>';API Config File='api_config.xml'";