On numerous occasions I’ve wanted to download and analyse Dynamics table data directly in Excel, only to be thwarted by the Dynamics Office Connector. It seems that you simply can’t download any-old-table from the AOT, it has to be the right type of table otherwise you might get one of the following:
 
 
Or this one…?
 
 
Irritating to say the least…!
 
The official (time-consuming) solution, off course, is to define/create/deploy a document or query service; however, that in itself requires you to create and add new AOT objects and potentially invoke a full recompile of the CIL (which is sometimes not feasible within a production environment).
 
There is a “cheat” … and that is simply to use “Passthru X++”. The AOS is capable of runtime compilation of X++ (if you have the right priveleges enabled). The following routine accepts an “X++ text string” and attempts to compile and execute it:
 
X++ routine to run dynamic script
public client server static str runScript(str _script)
{
    ExecutePermission perm;
    XPPCompiler comp;
    str result;
    ;
    try
    {
        perm = new ExecutePermission();
        if (perm != null)
        {
            perm.assert();
            comp = new XppCompiler();
            if (comp.compile(_script))
            {
                result = runBuf(_script);
                return result;
            }
            else
            {
                return "Error: compilation failure, please check script.";
            }
            CodeAccessPermission::revertAssert();
        }
        else
        {
            return "Error: no permission to execute scripts.";
        }
    }
    catch (Exception::Error)
    {
        return "Error: execution failure, please check script.";
    }
}
 
 

·         More information on this API can be found here: http://msdn.microsoft.com/en-gb/library/xppcompiler.execute.aspx

·         Note the use of this API is *not* approved for ISV solutions that require software certification.

 
If you pass an X++ function into the “_script” parameter, then an attempt is made to run that function and pass the results back into an object (i.e. the results of the “runBuf” operation).
 
You can test the class method directly from an X++ job. The following code sample will dynamically compile the X++ script string (in quotes) below and then return the result back into the string “str1”. Stepping through it will reveal what is going on (you won’t be able to “step-into” the dynamically compiled element).
 
static void Job1(Args _args)
{
    str str1 = DynScript::runScript("str test1() { return 'Hello world'; }");
    info(str1);
}
 
 

·         Ideally, all return types from these types of calls should be defined as “Object” as almost anything could be returned from such a function. In this case, however, my intention is to use this in a .net proxy class where everything must be “strongly-typed”.

 
Theoretically, any X++ code should work, so long as its “well-formed”, compiles, doesn’t break any server permissions and returns a string-object (not asking much heh!)
 
In order to use this from Visual Studio, a simple class-library project is required as a “wrapper” to invoke the X++ class method. Drag the X++ class into your Visual Studio project in order to create the proxy. The following C# sample will logon and invoke the method:
 
Sample wrapper for X++ method
namespace DynScriptClassLibrary
{
    public class DynScriptClass
    {
        Session session;
        public string runScript(string _script)
        {
            if (session == null)
            {
                session = new Session();
                session.Logon(null, null, null, null);
            }
            return DynScript.runScript(_script);
        }
    }
}
 
 
Once compiled, the class-library assembly needs to be included in any project that requires direct X++ scripting access (there is no need to deploy the assembly to the AOT).
 
Voila… you now have the ability to execute dynamic X++ from your .Net applications. The possibilities for this are endless… however, with great power comes great responsibility and “Passthru X++” should be filtered through a “sanitisation-layer” before allowing any AOS to execute it (very similar to the concept of checking for SQL-injection in web applications before actually running any SQL).
 
In my next blog post article I will go through a couple of examples of how this can be used from Excel to transmit and receive data without using the (slightly restrictive) Office Connector. This will be done without making any changes to the AOT or any changes to a live production environment.
 
 
REGARDS