DataConnector | ComponentOne
ADO.NET provider for JSON / Configuration
In This Topic
    Configuration
    In This Topic

    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.

    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.

    XML
    Copy Code 
     <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>
    JSON
    Copy Code 
    {
  "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:

    XML
    Copy Code 
    <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:

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