/sdk/advanced/dynamiccolumns
Dynamic Columns
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.