Friday, July 4, 2008

Calling Stonefield Query from .Net Applications

As described in our online documentation, Stonefield Query includes a lightweight COM object called SQProxy. This allows other applications to run reports, get the names of folders and the reports in them, and so on. Even though SQProxy is a COM object, it can be used in .Net applications such as ASP.Net.

You can easily add a reference to the SQProxy Type Library to a .Net project and use the interop class Visual Studio creates for you in your application. Simply right-click references in the Solution Explorer, choose Add Reference, select the COM page, and choose the SQProxy Type Library.


However, there's a complication: object members of SQProxy, such as SQApplication, aren't included in the type library so they don't have the proper type information that .Net needs. As a result, these members are cast as Object and you therefore cannot access their properties or call their methods.

To get around this, use reflection. This demo class has two helper methods, GetProperty and CallMethod, that get the value of a property or call the method of a Stonefield Query object. Thanks to C# MVP Rick Strahl for providing this technique and code.

using System;
using System.Collections.Generic;
using System.Text;
using sqproxy;
using System.Reflection;
namespace SQProxyTest
  class SQProxyTest
    static void Main(string[] args)
      SQProxyTest loTest = new SQProxyTest();
    private void TestSQProxy()
      //Create the SQProxy object.
      sqproxyClass SQP = 
        new sqproxy.sqproxyClass();
      //Load a project.
      SQP.LoadProject("C:\\Test", "admin",
"admin", "Northwind");
      if (SQP.ProjectLoaded)
        System.Console.WriteLine("Project Loaded");
        //Get references to some objects.
        object loApp = 
          this.GetProperty(SQP, "SQApplication");
        object loRE = 
          this.GetProperty(loApp, "ReportEngine");
        //Find out how many folders there are.
        object loFolders = 
          this.CallMethod(loRE, "GetFolders");
        int lnFolders = (int)
this.GetProperty(loFolders, "Count");
        System.Console.WriteLine("There are " +
lnFolders.ToString() + " folders");
        //Display the folder names.
        for (int i = 1; i <= lnFolders; i++)
          object loFolder = 
            this.CallMethod(loFolders, "Item",
new object[] { i });
          string lcFolder = (string)
        //Run a report.
        bool llOK =
(bool) this.CallMethod(loRE,
"RunReportToFile", new object[]
{"Customers", "C:\\Temp\\Test.pdf"} );
        if (llOK)
("The Customers report was output " +
to PDF."
("The Customers report was not " +
              run successfully.");
    //This method uses reflection on the specified
//object to call the desired method,
//it any necessary parameters.
    private object CallMethod(object toObject,
string tcMethod, params object[] toParams)
      return toObject.GetType().InvokeMember(
tcMethod, BindingFlags.InvokeMethod,
null, toObject, toParams);
    //This method uses reflection on the specified
//object to read the value of a property.
    private object GetProperty(object toObject,
string tcProperty)
      return toObject.GetType().InvokeMember(
tcProperty, BindingFlags.GetProperty,
null, toObject, null);