Stonefield Query Enterprise has a rich API that makes it easy to run and even create reports programmatically from other applications. A couple of sample projects in the Samples folder, ReportCreation and Sample WinForms Application, show how to do this. ReportCreation is a C# console project that programmatically creates a report and outputs it to Microsoft Excel. As its name suggests, Sample WinForms Application is a C# Windows Forms project that displays a list of reports in a TreeView control and allows you to preview or output them to file.

Here are the steps to add these features to your .NET applications:

  • Add the following Stonefield Query assemblies to your project (they're all located in the SQWeb\bin folder):

    • Application.dll

    • Collections.dll

    • Enums.dll

    • Interfaces.dll

    • Library.dll

    • Reports.dll

    You may need to add other assemblies as well; for example, add Filtering.dll if you want to programmatically add filter conditions to a report and DataDictionary.dll if you want to programmatically add tables or fields to the data dictionary.

  • Right-click the project in Solution Explorer and choose Manage Nuget Packages from the shortcut menu. Search in the Online section for "SQLite." The top result should be "System.Data.SQLite(x86/x64)". Click the Install button for this package.

  • If your application needs to display a preview window for reports, add the following assemblies from SQWeb\bin (in all of these, VERSION means the version number of the assembly; this version may change with updates to Stonefield Query):

    • DevExpress.Data.vVERSION.dll

    • DevExpress.Printing.vVERSION.Core.dll

    • DevExpress.Utils.vVERSION.dll

    • DevExpress.XtraPrinting.vVERSION.dll

    • DevExpress.XtraReports.vVERSION.dll

    For a console application, you may see a message about log4net configuration settings missing in the application's config file. This isn't required because the correct configuration settings are in SQWeb\Logs\Logger.Config. However, to make the message go away, add this to the configSections section of App.Config:

      <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
    

    Add this to the configuration section:

      <log4net>
        <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
         <file type="log4net.Util.PatternString" value="%property{LogFileName}" />
          <appendToFile value="true" />
          <rollingStyle value="Size" />
          <maxSizeRollBackups value="5" />
          <maximumFileSize value="10MB" />
          <staticLogFileName value="true" />
          <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%date (%timestamp ms since app started) [%thread] %level %logger - %message%newline" />
          </layout>
        </appender>
        <root>
          <level value="INFO"/>
          <appender-ref ref="RollingFileAppender"/>
        </root>
      </log4net>
    

Instantiating the Stonefield Query Application object

In the following code, the SetupQuery method instantiates the Stonefield Query Application object into _app. Change the assignment to the appRoot and projectFolder variables as necessary.

/// <<summary>>
/// A reference to the Application object.
/// <</summary>>
private Stonefield.Query.Application.Application _app;
 
/// <<summary>>
/// Instantiate the Stonefield Query Application object.
/// <</summary>>
private bool SetupQuery()
{
   bool result = true;
 
   // Get the paths we need. appRoot contains the location of the SQWeb folder where
   // resources, licenses, and other files are found.
   string appRoot = @"C:\Program Files (x86)\Stonefield Query Enterprise SDK\SQWeb";
   string projectFolder = @"C:\MyProject";
   string configFile = projectFolder + @"\Project_Data\settings.xml";
   string settingsFile = projectFolder + @"\App_Data\ApplicationSettings.xml";

   // Instantiate and set up a Stonefield Query Application object.
   string[] arguments = new string[] { "approot=" + appRoot,
                                       "project=" + configFile, 
                                       "appsettings=" + settingsFile };
   _app = new Stonefield.Query.Application.Application(arguments,
        null, null);
   _app.Setup();
 
   // See if we have a license problem. If not, login as ADMIN.
   if (! HaveLicenseProblem())
   {
      LoginResult login = _app.Security.Login("admin", "admin");
      if (login != LoginResult.Success)
      {
         MessageBox.Show("Login failed");
         result = false;
      }
   }
   else
   {
      result = false;
   }
   return result;
}
 
/// <<summary>>
/// Display a message if we have a license problem.
/// <</summary>>
private bool HaveLicenseProblem()
{
   bool terminate = true;
   switch (_app.LicenseManager.LicenseProblem)
   {
      case LicenseValidation.Valid:
      case LicenseValidation.None:
         terminate = false;
         break;
      case LicenseValidation.ExpiredUnactivated:
         MessageBox.Show("The trial period has expired.");
         break;
      case LicenseValidation.RegisteredRegistrationNumberMismatch:
         MessageBox.Show("The registration number doesn't match the value on our license server.");
         break;
      case LicenseValidation.RegistrationNumberMismatch:
         MessageBox.Show("The license file is invalid.");
         break;
   }
   return terminate;
}

Application parameters

There are several parameters you can pass to the Stonefield Query Application object when you instantiate it. These parameters specify things such as which folder contains the project files and what log file to use. They are passed as an array of name-value pairs as you can see in the code example above.

  • approot: specifies the root of the application path. This should be the path of the parent of the bin folder containing the Stonefield Query DLLs.

  • appsettings: the path to the App_Data\ApplicationSettings.xml file containing application settings.

  • datasource: the name of the data source to connect to.

  • logfile: the name of the log file to use. If this isn't specified, AppLog.txt is used.

  • password: the password to use to automatically log a user in. As discussed in the Using Stonefield Query from Other Web Applications topic, it probably better to use a token instead.

  • project: the path to the Project_Data\Settings.xml file containing project settings.

  • reporttorun: the name of a report to run; see the Using Stonefield Query from Other Web Applications topic for details.

  • supportiframes: specify true for this parameter if you want Stonefield Query to support iframes.

  • token: the security token to use; see the Using Stonefield Query from Other Web Applications topic for details.

  • useloupe: specify true for this parameter if you want Stonefield Query to use Loupe for logging. Loupe, a utility from Gibraltar Software, (http://www.gibraltarsoftware.com) provides advanced logging features and an easy-to-use (and free) log viewer.

  • username: the user name to use to automatically log a user in. As discussed in the Using Stonefield Query from Other Web Applications topic, it probably better to use a token instead.

Running a report

The ExportToExcel method in the following code outputs the specified report to a Microsoft Excel spreadsheet. Note the "TODO" comment; you have to set the values of any ask-at-runtime filter conditions to appropriate values.

/// <summary>
/// Output a report to an Excel file.
/// </summary>
private void ExportToExcel(IReport report)
{
   // Get the report we want.
   IReport report = _app.ReportEngine.Reports["My report"];
 
   // See if the report has any ask-at-runtime conditions. If so,
   // we need to supply values.
   var conditions = report.FilterConditions.Where(f => f.AskAtRuntime);
   if (conditions.Count() > 0)
   {
      foreach (FilterCondition condition in conditions)
      {
         // TODO: put the value to use in the following variable:
         object value = null;
         condition.Values.Clear();
         condition.Values.Add(value);
      }
   }
 
   // Get the result set for the report, then create the layout
   // and do the export if we succeeded or display a message that
   // it failed.
   if (_app.ReportEngine.RetrieveDataForReport(report))
   {
      ReportResult result = report.CreateLayout();
      if (result == ReportResult.Success)
      {
         report.ExportOptions.Type = FileTypes.XLSX;
         report.ExportOptions.FileName = "My Excel File.xlsx";
         report.Export();
      }
      else
      {
         MessageBox.Show("CreateLayout failed: " + result.ToString());
      }
   }
 
   // We couldn't create the result set, so display a message accordingly.
   else
   {
      MessageBox.Show("The data retrieval failed. The error message is:\n\n" +
         _app.ReportEngine.Error);
   }
}

Creating a report

The following code creates a report named "My test report." Note that while this code adds the report to the collection and saves it to an SFX file, there's actually no need to do that if you intend to create the report programmatically each time.

/// <summary>
/// Create a report and fill it with the desired fields.
/// </summary>
private static IReport CreateReport()
{
   // Create a quick report and add it to the collection.
   QuickReport report = new QuickReport(_app);
   report.Name = "My test report";
   _app.ReportEngine.Reports.Add(report);
   report.Save();
 
   // Add Country and group on it. This is one way to add it:
   // create a QuickReportField, set its Field property, and
   // add it to the report's Field collection.
   QuickReportField groupField = new QuickReportField();
   groupField.Heading = "Country";
   groupField.Field = _app.DataDictionary.Fields["Customers.Country"];
   groupField.Group = 1;
   report.Fields.Add(groupField);
 
   // Add City and put in the group header for country. This is
   // another way to add it:  pass the New method of the report's
   // Fields collection a reference to the field.
   QuickReportField field = report.Fields.New(
        _app.DataDictionary.Fields["Customers.City"]);
   field.Heading = "City";
   field.InGroupHeader = groupField;
   report.Fields.Add(field);
 
   // Add CompanyName.
   QuickReportField company = new QuickReportField();
   company.Heading = "Company";
   company.Field = _app.DataDictionary.Fields["Customers.CompanyName"];
   report.Fields.Add(company);
 
   // Add ContactName and right-justify it.
   QuickReportField contactField = new QuickReportField();
   contactField.Heading = "Contact";
   contactField.Field = _app.DataDictionary.Fields["Customers.ContactName"];
   contactField.HorizontalAlignment =
        Stonefield.Query.ReportEngine.TextAlignment.Right;
   report.Fields.Add(contactField);
 
   // Add ContactTitle below ContactName.
   field = report.Fields.New(_app.DataDictionary.Fields["Customers.ContactTitle"]);
   field.Heading = "Title";
   field.HorizontalPosition = contactField;
   field.VerticalPosition = 1;
 
   // Add Address and Phone.
   field = report.Fields.New(_app.DataDictionary.Fields["Customers.Address"]);
   field.Heading = "Address";
   field = report.Fields.New(_app.DataDictionary.Fields["Customers.Phone"]);
   field.Heading = "Phone";
 
   // Filter on Country = "Germany" and ContactTitle = "Sales"
   IFilterCondition filtCondition = report.FilterConditions.New(
        _app.DataDictionary.Fields["Customers.Country"]);
   filtCondition.Values.Add("Germany");
   filtCondition = report.FilterConditions.New(
        _app.DataDictionary.Fields["Customers.ContactTitle"]);
   filtCondition.Connection = new Stonefield.Query.Filtering.AndConnection();
   filtCondition.Values.Add("Sales Representative");
 
   return report;
}

Reloading the data dictionary, security, and schedules

If your application makes changes to the data dictionary or security settings after Stonefield Query has been started, or if you want changes made to scheduled tasks through the Windows Task Scheduler to be visible in Stonefield Query, normally the web application has to be restarted by recycling the application pool for those changes to take effect since they're loaded at application startup. However, you can send a message to Stonefield Query telling it to refresh these settings without restarting using code something like (this assumes _app contains an instance of the Stonefield Query application object as shown earlier):

_app.MessageManager.SendMessage(message)

where message is an enumeration of Stonefield.Messaging.Messages indicating what should be refreshed: RefreshDataDictionary, RefreshSchedules, or RefreshSecurity.

Running Stonefield Query from another web application

See the Using Stonefield Query from Other Web Applications topic for details.