Introduction

Enzo Server
  Installation
  Quick Start
  Core Features
    Async Calls
    ConnectionStrings
    Edge Cache
    HTTP Access
    Scheduling
    Views
  Advanced Capabilities
    Change Data Capture
  Administration
    Configuration Settings
    Logins & ACL
    Auditing
    Current Executions
    Linked Server
    SSL

  User Guides
     Sharding
     Sharding Overview

     SharePoint

   Adapters
    All Adapters
    Azure Bus
    Azure IoT Hub
    Azure Key Vault
    Azure Storage
    Bing Search
    Couchbase
    CSV
    DB (RDBMS)
    Dropbox
    Files
    Flight Aware
    FTP
    GoogleDrive
    GoogleSearch
    GoogleSheet
    Geonames
    HttpEndpoint (REST)
    Insteon
    Loggly
    MailChimp
    Messaging (SMS/SMTP)
    MongoDB
    Motus
    MSAD
    MSMQ
    Pipeline
    Pressero
    RabbitMQ
    SalesForce
    Shard
    SharePoint
    Slack
    SQL Server
    SQLX
    Sudoku
    Threading
    Twilio
    Twitter
    VellemanDMX
    Whiparound
    WMI
    Xls (Excel)
    ZIP


  SDK
    Overview
    Installation
    DevHost
    Create A Simple Adapter
    Handler Columns

    Advanced SDK Concepts
      Handler Decorators
      Dynamic Columns
      Table & Table Enumerators
      Virtual Tables

/sdk/advanced/dynamiccolumns




Dynamic Columns


You can create handlers that return columns dynamically. This is useful when you do not know how many columns a handler will return at design time.

It is important to note that by the very nature of Linked Server, calling handlers that return columns dynamically should not be called directly through Linked Server requests. Workarounds are provided below.


Handler Definition

In order to create a handler that returns columns dynamically, you need to include the VarCols handler options. The following example defines a handler that returns an unknown number of columns:

RegisterHandler("GetFields,Fields",
    HandlerOptions.Select | HandlerOptions.HttpGET | HandlerOptions.VarCols,
    "Returns an unknown number of columns",
    new[] { "EXEC MyAdapter.GetFields", "SELECT * FROM MyAdapter.Fields" },
    GetFields
    );
        

Sample Implementation Method

The following code gets called by the previously defined handler (GetFields); this method creates a number of DataColumn objects, and adds them to a DataTable object. The DataTable is loaded with records and returned to Enzo through the SetTableResult() method.

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

    if (e.IsArgValidation)
        return retval;

    DataTable table = new DataTable();
    table.Columns.Add("Id", typeof(Guid));      // add a column of type Guid
    table.Columns.Add("Title", typeof(string)); // add a column of type String
    table.Columns.Add("Required", typeof(bool));    // add a column of type Boolean

    // Add a single row to the data table
    row.ItemArray = new object[] { Guid.NewGuid(), "test", true };
    table.Rows.Add(row);
    
    // Set the output table to the one we just created
    retval.SetTableResult(table);

    return retval;
}

Linked Server Workarounds

Linked Server requires tables to have a very specific, constant schema (columns and data types). This represents an issue when handlers use the VarCols handler options and return a DataTable that can have different columns and/or data types. In order to access handlers that use variable columns through Linked Server, the following options are available:

  • EXEC Command: Calling a handler through its EXEC command allows you to return a variable number of columns through Linked Server. Using the previous example, calling EXEC MyAdapter.GetFields is possible through Linked Server without further work. Linked Server treats Stored Procedures differently and does not require a schema definition to be known ahead of time.

  • Enumeration Methods: You can set the TableEnumerator and ColumnsEnumerator properties of the base adapter to point to your own methods that return the list of tables and columns for a specific table. These properties are used when Linked Server attempts to determine the schema of a table.

  • Virtual Tables: You can also add support for Virtual Tables in your adapter; a Virtual Table (also known as a View) allows you define a specific schema for a specific SQL command to the handler. Accessing the Virtual Table through Linked Server ensures that a known number of columns will be returned every time, and provides additional support in Enzo Manager.

  • Direct Enzo Calls: In some cases you may be able to call Enzo Server directly instead of calling the handler through Linked Server; when this option is available, bypassing Linked Server allows you to execute a SELECT command directly.