/sdk/bestpractices

Best Practices for Building an Adapter

Handler Declaration

General Considerations

Handler Name

Handlers are methods in an adapter that are exposed as Stored Procedures. As a result, each handler needs to be given a unique name; handlers can usually be called using EXEC commands in SQL, and are used as part of the URI when making REST calls. In addition, some handlers can also be exposed as database tables so that a SELECT operation can be used in addition to the EXEC operation.

To support the use of SELECT/INSERT/DELETE/UPDATE operations, a Table Name must also be provided as part of the defintion of the handler. In this example, ArchiveMember is the Stored Procedure name, and Members is the table name.

RegisterHandler("ArchiveMember", ...   // example of a Stored Proc name (ArchiveMember): EXEC only

The above registration means that the handler can only be called using an EXEC command; no table operator (such as SELECT) will be permitted.

RegisterHandler("ArchiveMember,members", ...   // example of a Stored Proc name and Table name (members)

The above registration means that the handler can be called using an EXEC command, and a table operator (such as SELECT) depending on the HandlerOption provided as part of the RegisterHandler method call.

The following rules should be observed:

• The primary name (stored procedure name) of a handler must be unique in the adapter and should contain a verb (Create, Add, Archive, Get, List…) as part of the name
• The table name must be unique per table HandlerOption (ex: Select); up to 4 handlers can have the same Table Name (one for Select, Update, Delete, Insert)
• Whenever possible, the Table Name should be the same for all table operations supported
• The procedure name and table name must be different
• When a table Handler Option is selected, a Table Name must be specified

Handler Options

Ensure that the proper HandlerOptions are selected when calling RegisterHandler.

• When a table option is specified, make sure a Table Name is also specified as part of the Handler Name (ex: HandlerOptions.Delete)
• If HTTP support is needed for a handler, make sure the Table Option matches the HTTP Option
  • HandlerOptions.HttpGET | HandlerOptions.Select
  • HandlerOptions.HttpDELETE | HandlerOptions.Delete
  • HandlerOptions.HttpPOST | HandlerOptions.Insert
  • HandlerOptions.HttpPUT | HandlerOptions.Update

Input Arguments

Input arguments are equivalent to parameters of an EXEC command. They allow the adapter to handle specific scenarios without having to inspect the “WHERE” clause of the SQL command.

• All input arguments must have a data type and by part of the output columns, except for arguments that contain the “z” option.
• Make sure to use the correct data type for the argument (datetime, int…) - use a C# or VB.NET data type
  • If no data type is specified, it is assumed to be a string
• Mark required arguments accordingly; when an argument is required, the adapter function will not be called if the argument is missing from the call - as a result there is no need to check that required arguments have a value in the adapter code
• Specify a default value if possible to make things easier for the caller; for better support with Excel and PowerBI, provide a default value for required arguments
• Make sure every input argument has a description so the 'EXEC adapter.myMethod help' command returns meaningful information

Generally speaking, a “GET” operation should allow a TOP N optional input argument and should not have a default value (or set to 0). The following declares such optional argument:

"int topn|Top N parameter for EXEC operations||tz"

Output Arguments

Output arguments represent the columns returned by a SQL command.

• Specifically for Linked Server support, when a Select HandlerOption is specified, all input arguments should be present in the output columns using the same data type
• Ensure the proper data type is specified
• Do not use a space for the column name

Input/Output Columns for Table Names

In addition to the previous information, a special case exists for SELECT, UPDATE, DELETE and INSERT operations: the input arguments and output column names must match as much as possible across all handlers to improve the experience of the end user. For example, if the 'string firstName' column exists in the Select handler, the Update handler should also have a 'string firstName' column the column can be updated.

Examples

In the example below, the 'ListMemberTags' handler is defined as a stored procedure name (and 'tags' as a table name). To support SELECT operations on Linked Server, the input arguments should all be listed as output columns too with the same data types (string is the default data type if not specified).

RegisterHandler(
    "ListMemberTags,tags",
    HandlerOptions.HttpGET | HandlerOptions.Select,
    "List of member tags",
    new[]
        {
        "..."
        },
    ListMemberTags,
        new[] {
            "listId|The listId||r",
            "subscriberHash|The hash||r"
            },
        new[] {
            "name",
            "status",
            "datetime dateAdded"
            }
    );

RegisterHandler(
    "ListMemberTags,tags",
    HandlerOptions.HttpGET | HandlerOptions.Select,
    "List of member tags",
    new[]
        {
        "..."
        },
   ListMemberTags,
    new[] {
        "listId|The listId||r",
        "subscriberHash|The hash||r"
        },
    new[] {
        "name",
        "status",
        "datetime dateAdded",
        "listId",
        "subscriberHash"
      }
);

In the example below, two issues exist: the Update Table Option is missing (since the Table Name campaigns was provided) and some of the output columns do not have a description.

RegisterHandler(
   "UpdateCampaign,campaigns",
    HandlerOptions.HttpPUT,
    "Update a campaign",
    new[]
    {"..."},
    UpdateCampaign,
    new[] {
      "campaignId",
      "listId|the list id||r",
      "subjectLine",
      "previewText",
      "title",
      "fromName",
      "replyTo|reply email||r",
      "bool useConversation|The OOO replies|false",
      "toName",
      },
      null
);

RegisterHandler(
   "UpdateCampaign,campaigns",
    HandlerOptions.HttpPUT | HandlerOptions.HttpSelect,
    "Update a campaign",
    new[]
    {"..."},
    UpdateCampaign,
    new[] {
      "campaignId|Campaign id",
      "listId|the list id||r",
      "subjectLine|subject",
      "previewText|preview",
      "title|Title of the campaign",
      "fromName|The From name",
      "replyTo|reply email||r",
      "bool useConversation|The OOO replies|false",
      "toName|The email TO name",
      },
      null
);

In this example multiple mistakes exist: the SQL Sample code does not show the required arguments, the Table Name is inconsistent between operations (audience/audiences), the HttpPOST option should be HttpPUT for Update operations, the SELECT Id field conflicts with the UPDATE listId field (both should be either id or listId), and some of the input arguments do not have a description.

RegisterHandler(
   "ListAudience,audiences",
   HandlerOptions.HttpGET | HandlerOptions.Select,
   "Gets existing audiences",
   new[]
   {
   "exec MailChimp.ListAudience",
   "select * from MailChimp.audiences"
   },
   ListAudience,
   new[] {
     "string email|Filter on email address",
     "int topn|Top N parameter||tz"
   },
   new[] {
   "id",
   "name",
   "company"
   }
);



RegisterHandler(
   "UpdateAudience,audience",
   HandlerOptions.HttpPOST | HandlerOptions.Update,
   "Update an audience",
   new[]
   {
   ...
   },
   UpdateAudience,
   new[] {
     "listId|List id",
     "name",
     "company"
   }
  }
);

RegisterHandler(
   "ListAudience,audiences",
   HandlerOptions.HttpGET | HandlerOptions.Select,
   "Gets existing audiences",
   new[]
   {
    "exec MailChimp.ListAudience ‘email’...",
    "select * from MailChimp.audiences WHERE email=’...’ AND ..."
   },
   ListAudience,
   new[] {
     "string email|Filter on email address",
     "int topn|Top N parameter||tz"
   },
   new[] {
   "id",
   "name",
   "company"
   }
);



RegisterHandler(
   "UpdateAudience,audiences",
   HandlerOptions.HttpPUT | HandlerOptions.Update,
   "Update an audience",
   new[]
   {
   ...
   },
   UpdateAudience,
   new[] {
     "id|List id||r",
     "name|User name",
     "company|Company name"
   }
  }
);

Method Implementation

Generally speaking implementing a handler involves the following:

• Extract input arguments
• Call the remote object operation
• Loop until all records have been extracted for SELECT/GET/LIST operations
• Return a status, or a DataTable with the expected columns and data types

General Considerations

Response Object

The response object must always be created first; it contains all the necessary properties needed by Enzo to return results.

EventResult retval = new EventResult(e);

Input Arguments

The following returns the input argument for the email input argument:

string email = e.GetArg(“email”);

If the email input parameter has the ‘r’ option, it is not necessary to check that it has a value; this is guaranteed by Enzo. And if a default value is specified by the handler declaration, it will be returned.

If no default value is provided by the handler, the method can also provide one.

string email = e.GetArg(“email”, “admin@test.com”);

Casting is possible as well, assuming the argument specifies a data type:

        int userId = e.GetArg<int>(“userId”);
        

Argument Validation

Some clients can send an initial request to simply validate input arguments; in order to support this feature, check the IsArgValidation property and return immediately after validating the arguments in the implementation method. This is required only when the HandlerOptions.ValidationSupported handler option is used (see the Examples section).

It is highly recommended to support the HandlerOptions.ValidationSupported option.

private EventResult ListRoles(object sender, ExecEventArgs e)
{
    EventResult retval = new EventResult(e);

    // check for a condition
    int userId = e.Get<int>(“id”, 0);

    if (userId <= 0)
    retval.SetResultCode(500, “Invalid userId; must be positive”);

    if (e.IsArgValidation)
        return retval;  // we are done with basic validation; return now

    // Do some real work and set retval as needed
    return retval;
}

Long Running Operations

When the implementation method is expected to return many records, or take a long time, it should observe the CancellationRequested property; when it is true, the method should exit as soon as possible; failing to do so could prevent proper shutdown of Enzo and consume more resources than needed from the remote system. This is particularly important during loop operations that need to continue fetching from a remote system.

while (true)
{
    if (e.IsCancellationRequested) break;

    // go get next data set
    dynamic result =  wrapper.GetAsync<dynamic>(url, e.Settings).Result;

    // add result…
}

Handling TOP N

The TOP N operator is handled automatically when using the AddResultRow method; the method returns false if one of the following conditions is met:

• TopN has been reached
• IsCancellationRequested is true

The following code automatically handles the TOP N and CancellationRequested checks:

foreach (var res in result.lists)
{
    if (!retval.AddResultRow(res.id, res.name))
    {
        break;     // get out - we are done (or cancel was requested)
    }
}

The implementation code should always check the value returned by AddResultRow to find out if processing should stop.

You can check the e.IsTopNSet, and e.TopN to determine the TOP N value requested by the caller. If the implementation method already implements the TOP N condition and doesn't need Enzo to manage it, use the HandlerOptions.ImplementsTopN handler option as part of the handler registration.

Returning Errors

When an error is detected, the retval.SetResultCode method should be called. This will return an actual SQL Error code to the client. For example if the remote system throws an error, your implementation method should call this method to return the error rather than throwing an exception. In addition, keep in mind that the error message will be visible to the caller; no sensitive information should be returned. Use the LogError method to log the complete exception in Enzo.

EventResult retval = new EventResult(e);

// get input parameters

try
{
    // Call a service
}
catch (Exception ex)
{
    retval.SetResultCode(500, "Failed: " + ex.Message);
    LogError(ex);
}

return retval;

Returning 0 Records

In some cases, the SELECT operation may return 0 records. It is important to return nothing when no records are found in order to match the behavior of an empty table rather than returning an error message.

Returning 0 records is specifically important for some client applications, such as Excel.

EventResult retval = new EventResult(e);

// get input parameters

try
{
    // Call a service

    // No record? Do nothing - retval will be returned with 0 records

    // For each record... call AddResultRow
}
catch (Exception ex)
{
    // Set the error if an exception was raised
    retval.SetResultCode(500, "Failed: " + ex.Message);
    LogError(ex);
}

return retval;

Paging for Additional Records

Enzo is expected to work as a database; as a result, when a SELECT * FROM … is executed, the normal behavior is expected to return all available records. As a result, since many SaaS systems return just a few records for every call, the implementation method should continue fetching additional records until no more are available or a cancellation has been requested.

Implementing this paging logic varies for every system; most remote systems will provide a “Next” token or URL to use to fetch the next few records. Others do not provide a next token, and simply return 0 records if no more data is available.

In the following example the logic reads the outcome of the Fetch operation, and continues to fetch the next batch of records as long as items are returned.

while (true)
{
    query = orgQuery + " LIMIT " + start + " OFFSET " + offset;
    var result = bk.Query<dynamic>(query); // get next records

    if (result.Status == QueryStatus.Success)
    {
        if (result.Rows.Count > 0)
        {
            foreach (var rw in result.Rows.ToList())
            {
                // Add row to result
            }
        }
        else
        {
            break;  // no more records -- exit
        }
    }
    else
    {
        // Log error?  This may depend on the HTTP error received 
    }
}

Complete Handler Example

In the example below, the code should check for the IsArgValidation option and does not need to check for the existence of a value for the subSite parameter; setting the required flag instead (the ‘r’ option) in the handler definition is usually preferred (*). Last but not least, checking the returned value of AddResultRow ensures proper handling of TOP N and Cancellation requests.

(*) As mentioned previously, if supporting Excel is important in your design, using the 'r' argument option is not desired; Excel usually sends a totally empty SELECT command without a WHERE clause to obtain the schema of a table. As a result, for Excel support, removing the 'r' option and returning 0 records when required arguments are missing is the preferred behavior.

RegisterHandler("GetRoles,Roles",
   HandlerOptions.Select | HandlerOptions.HttpGET,
   "Gets site or subsite permissions",
   new[] { "EXEC SharePoint.GetRoles '/site/subsite/' " },
   ListRoles,
   new[] {
      "subSite|subSite"
    },
    new[]
    {
       "int id|The role Id",
       "name|The role name",
       "description|The role description"
    }
);


private EventResult ListRoles(object sender, ExecEventArgs e)
{
  EventResult retval = new EventResult(e);

  string subSite = e.GetArg("subSite");
  
  if (string.IsNullOrEmpty("subSite"))
    retval.SetResultCode("subSite required.");

  using (ClientContext context = BuildContext(subSite, e))
  {
     Web web = context.Web;
     context.Load(web.RoleDefinitions);           
     context.ExecuteQuery();

     foreach(var roleDef in web.RoleDefinitions)
     {
        retval.AddResultRow(
           roleDef.Id,
           roleDef.Name,
           roleDef.Description
           );
     }

  }
  return retval;
}

RegisterHandler("GetRoles,Roles",
   HandlerOptions.Select | HandlerOptions.HttpGET |
HandlerOptions.ValidationSupported,
   "Gets site or subsite permissions",
   new[] { "EXEC SharePoint.GetRoles '/site/subsite/' " },
   ListRoles,
   new[] {
      "subSite|subSite||r"
    },
    new[]
    {
       "int id|The role Id",
       "name|The role name",
       "description|The role description"
    }
);

private EventResult ListRoles(object sender, ExecEventArgs e)
{
  EventResult retval = new EventResult(e);

  string subSite = e.GetArg("subSite");
            
  if (e.IsArgValidation)
     return retval;

  using (ClientContext context = BuildContext(subSite, e))
  {
     Web web = context.Web;
     context.Load(web.RoleDefinitions);           
     context.ExecuteQuery();

     foreach(var roleDef in web.RoleDefinitions)
     {
        if (!retval.AddResultRow(
           roleDef.Id,
           roleDef.Name,
           roleDef.Description
           ))
         {
           break;
         }
     }

  }
  return retval;
}