With the Client Extension, you can create custom APIs. For example, you can create an API that involves custom tables and schema to generate custom reports. To see all the features available to you, we will create a custom API that pulls data from the Database. For this example, we're going to use a query that gets the top x Associates over the last y days.

At the end of this tutorial, we'll have an Extension structure like this:

Demo\Api\GetHighestEarnersApi.cs
Demo\Api\GetHighestEarnersRequest.cs
Demo\Api\GetHighestEarnersResponse.cs
Demo\Api\HighEarner.cs

📘Note

Replace any instance of Demo with your client ID.

Building Models

There are three models we need to build:

1. GetHighestEarnersRequest

namespace Demo.Api
{
    public class GetHighestEarnersRequest
    {
        public int NumberOfAssociates { get; set; }
        public int NumberOfDays { get; set; }
    }
}

2. HighEarners

namespace Demo.Api
{
    public class HighEarner
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string BackofficeID { get; set; }
        public int HighestRank { get; set; }
        public double Amount { get; set; }
    }
}

3. GetHighestEarnersResponse

namespace Demo.Api
{
   public class GetHighestEarnersResponse
   {
       public HighEarner[] HighestEarners { get; set; }
   }
}

Building the Endpoint (Simplified)

1. Declare proper `using` directives:

using Dapper;
using DirectScale.Disco.Extension.Api;
using DirectScale.Disco.Extension.Services;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

2. Implement the Simplified Interface:

public class GetHighestEarnersApi : ApiEndpoint<GetHighestEarnersRequest>
    {

ApiEndpoint<TRequest> is a simplified interface that lets you skip a few steps and define an object. The HTML request body is automatically parsed as JSON into the specified model (GetHighestEarnersRequest).

The JSON API request, in this case, would look like this:

{ 
"NumberOfAssociates": 1, 
"NumberOfDays": 30 
}

3. Create a method to Define the API

private readonly IDataService _dataService;

public GetHighestEarnersApi(IDataService dataService, IRequestParsingService requestParsingService) : base("demo/GetHighestEarners", AuthenticationType.V1, requestParser: requestParsingService)
{
    _dataService = dataService;
}

IDataService - Provides connection strings for direct Database access.
IRequestParsingService - A helper to get an object out of an API request.

base("demo/GetHighestEarners"

This is the API pathname. Replace Demo with your client ID.

AuthenticationType.V1

AuthenticationType sets the authentication type to use for the endpoint. In this example, we've set it to V1. The alternate option is None.

❗Warning

If set to None, your API will be publically exposed and can be called by anyone. Furthermore, you will have to do authentication manually (see Headers). DirectScale's authentication procedures do not verify authorization. If your custom API is doing something for a specific Associate, make sure that only the Associate initiates the request.

3. Declare the HTTP response:

public override IApiResponse Post(GetHighestEarnersRequest request)

The interface IApiResponse provides the ability to specify the HTTP response content of a custom API call. Implement Post(ApiRequest) to create a custom API endpoint.

4. Connect to the Database

Connect to the Database to allow the SQL query to get data.

using (var _dbConnection = new SqlConnection(_dataService.ConnectionString.ConnectionString))

ConnectionString is a read-only connection string for accessing DirectScale and client custom tables. Use ClientConnectionString to access only client custom tables.

5. Build the SQL Query

string query = @"SELECT top @NumberOfAssociates d.FirstName, d.LastName, d.BackofficeID, s.HighestRank, SUM(h.Amount) AS Amount
                      FROM CRM_CommissionHistory h
                      LEFT JOIN CRM_Distributors d ON h.AssociateID = d.recordnumber
                      LEFT Join CRM_Stats s on h.AssociateID = s.AssociateID
                      WHERE h.PostDate >= DATEADD(DAY, -@NumberOfDays, GETDATE())
                      GROUP BY d.BackofficeID, d.LastName, d.FirstName, s.HighestRank
                      ORDER BY Amount DESC;";

6. Connect the Query to the HighEarners Object

var associates = _dbConnection.Query<IEnumerable<HighEarner>>(query, request);
return new Ok(associates);

The method returns Ok, which is a class that provides a default implementation for a typical response of 200 OK, with an optional JSON object in the body.

Full Endpoint Example

using Dapper;
using DirectScale.Disco.Extension.Api;
using DirectScale.Disco.Extension.Services;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Demo.Api
{
    public class GetHighestEarnersApi : ApiEndpoint<GetHighestEarnersRequest>
    {
        private readonly IDataService _dataService;

        public GetHighestEarnersApi(IDataService dataService, IRequestParsingService requestParsingService)
            : base("demo/GetHighestEarners", AuthenticationType.V1, requestParser: requestParsingService)
        {
            _dataService = dataService;
        }

        public override IApiResponse Post(GetHighestEarnersRequest request)
        {
            using (var _dbConnection = new SqlConnection(_dataService.ConnectionString.ConnectionString))
            {
                string query = @"SELECT top @NumberOfAssociates d.FirstName, d.LastName, d.BackofficeID, s.HighestRank, SUM(h.Amount) AS Amount
                      FROM CRM_CommissionHistory h
                      LEFT JOIN CRM_Distributors d ON h.AssociateID = d.recordnumber
                      LEFT Join CRM_Stats s on h.AssociateID = s.AssociateID
                      WHERE h.PostDate >= DATEADD(DAY, -@NumberOfDays, GETDATE())
                      GROUP BY d.BackofficeID, d.LastName, d.FirstName, s.HighestRank
                      ORDER BY Amount DESC;";
                var associates = _dbConnection.Query<IEnumerable<HighEarner>>(query, request);
                return new Ok(associates);
            }
        }
    }
}

Building the Endpoint (Expanded)

The expanded version of this endpoint will parse the same request the same way with the same response. It is a little bit different to set up. The main difference is that the Post method provides you with the ApiRequest object instead of the GetHighestEarnersRequest object.

The ApiRequest will have the JSON body and can be parsed into the GetHighestEarnersRequest object, but it also has way more information about the request headers. If you need access to any of that other information or receive a request that is not JSON, you have to use the expanded IApiEndpoint interface. Otherwise, the simplified interface is easier and will work most of the time.

Full Expanded Endpoint Example

using Dapper;
using DirectScale.Disco.Extension.Api;
using DirectScale.Disco.Extension.Services;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Demo.Api
{
    public class GetHighestEarnersApi : IApiEndpoint
    {
        private readonly IDataService _dataService;
        private readonly IRequestParsingService _requestParsingService;

        public GetHighestEarnersApi(IDataService dataService, IRequestParsingService requestParsingService)
        {
            _dataService = dataService;
            _requestParsingService = requestParsingService;
        }

        public ApiDefinition GetDefinition()
        {
            return new ApiDefinition
            { 
                Route = "demo/GetHighestEarners",
                Authentication = AuthenticationType.V1
            };
        }

        public IApiResponse Post(ApiRequest request)
        {
            GetHighestEarnersRequest getHighestEarnersRequest = _requestParsingService.ParseBody<GetHighestEarnersRequest>(request);
            using (var _dbConnection = new SqlConnection(_dataService.ConnectionString.ConnectionString))
            {
                string query = @"SELECT top @NumberOfAssociates d.FirstName, d.LastName, d.BackofficeID, s.HighestRank, SUM(h.Amount) AS Amount
                      FROM CRM_CommissionHistory h
                      LEFT JOIN CRM_Distributors d ON h.AssociateID = d.recordnumber
                      LEFT Join CRM_Stats s on h.AssociateID = s.AssociateID
                      WHERE h.PostDate >= DATEADD(DAY, -@NumberOfDays, GETDATE())
                      GROUP BY d.BackofficeID, d.LastName, d.FirstName, s.HighestRank
                      ORDER BY Amount DESC;";
                var associates = _dbConnection.Query<IEnumerable<HighEarner>>(query, getHighestEarnersRequest);
                return new Ok(associates);
            }
        }
    }
}

You'll see that we declared an ApiDefinition class:

        public ApiDefinition GetDefinition()
        {
            return new ApiDefinition
            { 
                Route = "demo/GetHighestEarners",
                Authentication = AuthenticationType.V1
            };
        }

We declare the Route that will trigger the custom API to be called. The URL path will be prefixed with /Command/ClientAPI/ to call this custom endpoint. Do not set Route to a value starting with /.

For example, setting Route to demo/GetHighestEarners can be called from your Corporate Admin domain + /Command/ClientAPI/demo/GetHighestEarners.

Register the endpoint in `ExtentionEntry.cs`:

namespace Demo
{
    public class ExtensionEntry : IExtension
    {
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddSingleton<IApiEndpoint, GetHighestEarnersApi>();

DirectScale unusually consumes client registrations. Because of that, the order of the dependency registrations matters. When you register an API, it resolves to an instance. If the API has dependencies that you didn't register yet, you'll get an error when trying to deploy.