chunter.me

Probably the #1 downside with the eBiz Soa is it only returns a single object or single array of objects. It’s not a huge problem and more annoying that anything. It’s easy enough for your consumer to make multiple endpoint calls and build the complex object it needs. But… wouldn’t it just be nice to have one endpoint and only hit the API and database once? I thought so, and finally found time to build the component that should just be the OTB output option. From a single stored procedure it can return:

single object
single array
multiple single objects with property names defaulted to: Table##
multiple single objects with named property names
multiple arrays with property names defaulted to: Table##
multiple arrays with named property namespace
full mix of all above
direct JSON object from SQL Server JSON query utilities like FOR JSON PATH (this is magical fun)

But How Can We Do This?

I don’t know if I missed the announcement for this or Aptify just never announce it. I lean more to the latter. But, a magical property to the JSON of endpoints. The property allows you to output your own HttpResponseMessage and ignore the outputEntityDefinition! Yes! Meaning you can now return anything your heart desires now from the endpoint, woot, woot!

  "outputEntityDefinition": null,
  "options": {
    "customOutput": true
  }

Moving Responsibility

eBiz Soa’s endpoint introduction of use having to define the OutputDefinition meant we put more thought into our endpoints and the data they were returning. It allows you to to re-use business logic that returned say the standard profile data, but each endpoint ONLY returned what was defined in the OutputDefinition.

But the new magic property removes our Output Definition? Yep, we move the responsibility of controlling the data outputted to your stored procedure. It’s just SQL everyone, create a view or table function and only select the fields you need. It’s on us to be good developers not lazy developers.

Data Query Component

using Aptify.Framework.DataServices;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using PB.Rexies.AptifyBits;
using PB.Rexies.Data;
using SimplifyAptify.AptifyConfigs.Soa.EndpointComponents.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace SimplifyAptify.AptifyConfigs.Soa.EndpointComponents
{
    public class DataQueryEndpointComponent : BaseEndpointComponent
    {
        public string DataObjectName
        {
            get => Properties.GetString(nameof(DataObjectName));
            set => Properties[nameof(DataObjectName)] = value;
        }

        protected override string RunCore()
        {
            Logger.LogInformation("DataQueryEndpointComponent");

            // Ensure DataObject
            if (string.IsNullOrWhiteSpace(DataObjectName))
            {
                throw new ArgumentException("DataObjectName is required.");
            }

            var dsEndpointDetails = DataAction.GetDataSetParameterized(
                "spEndpointComponent_DataQueryEndpointComponent_sa",
                CommandType.StoredProcedure,
                DataAction.GetDataParameter("@DataObjectName", SqlDbType.NVarChar, DataObjectName)
            );

            if (dsEndpointDetails.Tables.Count != 2)
            {
                throw new DataException("Unable to retrieved component dataset.");
            }

            if (dsEndpointDetails.Tables[0].Rows.Count != 1
                || dsEndpointDetails.Tables[0].Rows[0][0] == null
                || !dsEndpointDetails.Tables[0].Rows[0][0].ToString().Equals("Stored Procedure", StringComparison.OrdinalIgnoreCase))
            {
                throw new ArgumentException("Only Stored Procedures are supported.");
            }

            Logger.LogInformation("DataQueryEndpointComponent - Sproc: {DataObjectName}", DataObjectName);

            // Build Data Object Params
            var sprocParams = new List<IDataParameter>();
            foreach (DataRow drParam in dsEndpointDetails.Tables[1].Rows)
            {
                var propName = drParam["Name"].ToString();
                var prop = Properties.GetProperty(propName.Replace("@", string.Empty).Trim());
                if (prop != null)
                {
                    if (Enum.TryParse(drParam["Type"].ToString(), out SqlDbType sqlDbType))
                    {
                        sprocParams.Add(DataAction.GetDataParameter(propName, sqlDbType, prop));
                    }
                    else
                    {
                        sprocParams.Add(DataAction.GetDataParameter(propName, prop));
                    }
                }
            }

            Logger.LogInformation("DataQueryEndpointComponent - Params: {SprocParams}", JsonConvert.SerializeObject(sprocParams));

            // Execute Data Object
            var dsDataObject = DataAction.GetDataSetParameterized(
                DataObjectName,
                CommandType.StoredProcedure,
                sprocParams.ToArray()
            );

            // Generate Output Response Object
            var output = new EndpointComponentResponse();

            Logger.LogInformation("DataQueryEndpointComponent - Tbl Count: {Count}", dsDataObject.Tables.Count);
            Logger.LogDebug("DataQueryEndpointComponent - Tbl Data: {Tbl}", JsonConvert.SerializeObject(dsDataObject.Tables));

            // Configured Data Set Output
            if (dsDataObject.Tables.Count > 0 && dsDataObject.Tables[0].Columns[0].ColumnName.Equals("TableName", StringComparison.OrdinalIgnoreCase))
            {
                if (dsDataObject.Tables[0].Rows.Count == 1)
                {
                    var isCollection = (bool)dsDataObject.Tables[0].Rows[0]["IsCollection"];
                    Logger.LogInformation("DataQueryEndpointComponent - Table Name Configuration - Single Table - {IsCollection}", isCollection);

                    output.Data = OutputDataObject(isCollection, dsDataObject.Tables[1]);
                }
                else
                {
                    Logger.LogInformation("DataQueryEndpointComponent - Table Name Configuration - Multiple Table");

                    var outputData = new Dictionary<string, object>();
                    var tableIdx = 1;

                    foreach (DataRow dr in dsDataObject.Tables[0].Rows)
                    {
                        var name = dr["TableName"].ToString();
                        var isCollection = (bool)dr["IsCollection"];

                        Logger.LogInformation("DataQueryEndpointComponent - Table Name Configuration - Multiple Table - {Name}: {IsCollection}", name, isCollection);

                        object data = OutputDataObject(isCollection, dsDataObject.Tables[tableIdx]);
                        outputData.Add(name, data);

                        // Increment Table Index
                        tableIdx += 1;
                    }

                    output.Data = outputData;
                }
            }

            // FOR JSON PATH
            else if (dsDataObject.Tables.Count > 0 && dsDataObject.Tables[0].Columns[0].ColumnName.Equals("JsonOutput", StringComparison.OrdinalIgnoreCase))
            {
                Logger.LogInformation("DataQueryEndpointComponent - Json Output");

                var json = dsDataObject.Tables[0].Rows[0]["JsonOutput"];
                if (json != null)
                {
                    var jsonString = json.ToString();

                    if (!string.IsNullOrWhiteSpace(jsonString) && (jsonString.StartsWith("{") || jsonString.StartsWith("[")))
                    {
                        output.Data = JsonConvert.DeserializeObject<dynamic>(json.ToString());
                    }
                }
            }

            else if (dsDataObject.Tables.Count == 1)
            {
                if (dsDataObject.Tables[0].Rows.Count == 1)
                {
                    Logger.LogInformation("DataQueryEndpointComponent - No Config - Single Row");

                    output.Data = dsDataObject.Tables[0].Columns
                        .Cast<DataColumn>()
                        .ToDictionary(
                            column => column.ColumnName,
                            column => dsDataObject.Tables[0].Rows[0][column]
                        );
                }
                else if (dsDataObject.Tables[0].Rows.Count > 1)
                {
                    Logger.LogInformation("DataQueryEndpointComponent - No Config - Single Table");
                    output.Data = dsDataObject.Tables[0];
                }
            }

            // Raw Data Set Output
            else
            {
                Logger.LogInformation("DataQueryEndpointComponent - No Config - Multiple Tables");
                output.Data = dsDataObject;
            }

            // Set Output Response Message
            EndpointComponentResponseSet(output);

            return SuccessResult;
        }

        private static object OutputDataObject(bool isCollection, DataTable dtResult)
        {
            if (isCollection)
            {
                return dtResult;
            }
            else if (!isCollection && dtResult.Rows.Count > 0)
            {
                return dtResult.Columns.Cast<DataColumn>().ToDictionary(column => column.ColumnName, column => dtResult.Rows[0][column]);
            }

            return null;
        }
    }
}

/*
NAME: spEndpointComponent_DataQueryEndpointComponent_sa
DESC: Gets the details needed for the setup and validation of the Data Query Endpoint Component.
TYPE: Stored Procedure
GRANT: GRANT EXECUTE ON spEndpointComponent_DataQueryEndpointComponent_sa TO EndUsers
*/

CREATE PROCEDURE spEndpointComponent_DataQueryEndpointComponent_sa (
  @DataObjectName NVARCHAR(250)
) AS BEGIN

  -- DB Object Type
  SELECT RTRIM(d.Type) [Type]
  FROM DBObject d
  WHERE d.Name = @DataObjectName


  -- Sproc Parameters
  SELECT
    pa.parameter_id [Sequence],
    pa.name [Name],
    UPPER(t.name) [Type],
    t.is_nullable [Nullable],
    t.max_length [Length]

  FROM
    sys.parameters pa

    INNER JOIN sys.procedures p on
      pa.object_id = p.object_id

    INNER JOIN sys.types t on
      pa.system_type_id = t.system_type_id
      AND pa.user_type_id = t.user_type_id

  WHERE
    p.name = @DataObjectName

  ORDER BY
    pa.parameter_id

END

Setup

The spEndpointComponent_DataQueryEndpointComponent_sa returns if the DB Object name is a stored procedure and it’s input properties. If the DB Object is not a stored procedure we thought an error.

Execute DB Object with Params

I did clone Aptify’s Execute Data Object components method here. Except, I removed the requirement that your stored procedure properties needed to start with “Request_”. We just match input property name to the parma names.

I did keep the @AuthenticatedPrincipalRecordId the same as the BaseEndpointComponent adds the AuthenticatedPrincipalRecordId to the components Properties.

Table Configuration

When you serialize a DataSet each result will have a property name of TableIndex. I added a little bit in there so you can add a Table Configuration as the first result.

You just need you first result sets first column name to be TableName. You also include a second bit column IsCollection.

The IsCollection is mostly for enforcing a single object instead of an array with single object.

Finally, you need to return the Table Configuration ordered sequentially to match all your other results within the sproc.

JSON Endpoint

We still need the JSON Endpoint to define our endpoint but little utility magic to make this simpler.

Utilities.json

{
  "BusinessLogic": {
    "EndpointDataQuery": {
      "executionType": "processFlow",
      "processFlowProperties": {
        "processFlowName": "<Name of your process flow that implements your Data Query Component>",
        "processFlowParameters": {
          "AuthenticatedPrincipalRecordId": "@AuthenticatedAttributes.AuthenticatedPrincipalRecordId"
        }
      }
    }
  }
}

This utilities saves you some lines of code and guarantees you never forget to add the AuthenticatedPrincipalRecordId. Then in your endpoint you just specify the DataObjectName and any additional properties from your Input Definition.

Endpoint.json

{
  "endpoints": {
    "Profile": {
      "route": {
        "httpMethod": "GET",
        "segments": {
          "me": {
            "isLiteral": true,
            "type": "string"
          }
        }
      },
      "inputEntityDefinition": null,
      "outputEntityDefinition": null,
      "businessLogic": {
        "ProfileBusinessLogic": {
          "$ref": "<Path to folder as needed>/utilities.json#/BusinessLogic/EndpointDataQuery",
          "processFlowProperties": {
            "processFlowParameters": {
              "DataObjectName": "<Stored Procedure Name>"
            }
          }
        }
      },
      "options": {
        "customOutput": true
      }
    }
  }
}

Part 3 - Post Content

Input Definitions do not support complex objects. Or do they?

Aptify Clients

Continue the conversation on the Community Forums.

eBiz Soa Generic Components - Part 2 - Data Query