Extension: SqlService - quandis/qbo3-Documentation GitHub Wiki

The SqlService is an IService plugin enables QBO to query data from non-QBO databases. To use this plugin:

  • Create a named connection to the remote database in /Config/ConnectionStrings.config
  • Create a Service entry that leverages the named connection

Connection String configuration

Assume you want to connect to a database called 'DataWarehouse' on an Azure SqlServer with the following connection string:

<connectionStrings>
  ...
  <add name="DataWarehouse" connectionString="Server=sqlDataWarehouse.database.windows.net;Database=DataWarehouse;uid=someSqlAccount;pwd=SuperSecret!" providerName="System.Data.SqlClient" />-->
</connectionStrings>

Service configuration for a stored procedure

For this example, assume we've created a stored procedure pMyFancyStoredProcedure.

From Design > Configuration > Modules, choose a module to extend with a Service entry, such as Message. From the Services tab, add a service:

  • Name: MyNewService
  • Return Type: DataSet
  • Plugin Type: qbo.Application.Services.SqlService, qbo.Application
  • Endpoint: DataWarehouse:pMyFancyStoredProcedure

Once configured, you may call Message/MyNewService, and receive the results of the pMyFancyStoredProcedure. Of course, the new method signature can be used from other QBO components, including workflow steps, batching, document generation and more.

If pMyFancyStoredProcedure contains parameters, you can simply pass those parameter to the method signature. Assuming pMyFancyStoredProcedure includes the parameters @State and @Age, invoke the method signature like this:

Message/MyNewService?State=TX&Age=21

will ultimately call:

EXEC pMyFancyStoredProcedure @State='Tx', @Age=21

Sample Configuration XML

Rather than configure the MyNewService manually, one may do so programmatically by importing the following:

<ConfigurationEntryCollection>
  <ConfigurationEntryItem>
    <ConfigurationEntry>Message/MyNewService</ConfigurationEntry>
    <Source>Message.config</Source>
    <ConfigurationType>qbo.Application.Configuration.ServiceCollection</ConfigurationType>
    <ConfigurationKey>MyNewService</ConfigurationKey>
    <ConfigurationXml>
        <Service Name="MyNewService" Type="qbo.Application.Services.SqlService, qbo.Application" ReturnType="DataSet" EndPoint="DataWarehouse:pMyFancyStoredProcedure"/>
    </ConfigurationXml>
  </ConfigurationEntryItem>
</ConfigurationEntryCollection>

Service configuration for a custom statement

Instead of targeting a stored procedure, you can have QBO generate a full SQL statement to be passed to the DataWarehouse. For this example, let's extend the Contact module. From Design > Configuration > Modules > Contact, select the Statements tab and add statement:

  • Name: MyCoolStatement
  • Return Type: DataSet
  • Query: SELECT * FROM MyCoolDataWarehouseTable WHERE CreatedDate BETWEEN @StartDate and @EndDate
  • Parameters: add StartDate and EndDate, each of type DateTime

Next, from Design > Configuration > Modules > Contact, select the Services tab and add a service:

  • Name: MyRemoteService
  • Return Type: DataSet
  • Plugin Type: qbo.Application.Services.SqlService, qbo.Application
  • Endpoint: DataWarehouse:MyCoolStatement

When you invoke Contact/MyRemoteService?StartDate=1/1/2018&EndDate=2/1/2018, QBO will pass the following SQL to DataWarehouse:

exec sp_executesql N'
  SELECT * FROM MyCoolDataWarehouseTable WHERE CreatedDate BETWEEN @StartDate and @EndDate
',N'@StartDate datetime', @StartDate=N'1/1/2018', N'@EndDate datetime', @EndDate=N'2/1/2018'

QBO will generate sp_executesql to mitigation SQL injection risks.

Request.Body as Stored Procedure Parameters

Most requests to stored procedures involve a small number of parameters, typically triggered by a web form post or simple CustomRoute configuration. If you encounter a more complicated use case, you can have QBO pass an entire request body into a single stored procedure parameter.

To have a stored procedure handle Json payloads:

  • create a stored procedure parameter @JsonPayload [n]varchar(max), and
  • specifying RequireStream = true in the Service configuration

For XML payloads:

  • create a stored procedure parameter @XmlPayload xml, and
  • specifying RequireStream = true in the Service configuration

These special parameter names are determined by application settings, and may be overridden:

  • qbo.Application.Properties.Settings.SqlServiceJsonPayload
  • qbo.Application.Properties.Settings.SqlServiceXmlPayload

Exception Handling

If the SQL query raises an exception, the SqlService can map the exception to one of the following .NET exceptions:

Exception Default Code Formatting Excepted
ArgumentException 59999 {parameter}|[{message}]
ArgumentOutOfRangeException 59998 {parameter}|[{value}]|[{message}]
InvalidOperationException 59997 {message}
ArgumentNullException 59996 {parameter}|{message}
IndexOutOfRangeException 59995 {message}
ItemNotFound 59994 {message}

This is useful because HttpAsyncHandler will abide by RESTful error conventions when encountering these common .NET errors.

If you wish map different SQL error codes to these .NET exceptions, you can override the qbo.Application.Properties.Settings.SqlExceptionTranslation from Design > Configuration > Modules > Matrix. This setting is formatted as:

{SQL error code}={.NET Exception},...

defaulting to:

59999=ArgumentException&59998=ArgumentOutOfRangeException&59997=InvalidOperationException&59996=ArgumentNullException&59995=IndexOutOfRangeException&59994=ItemNotFound

To add a custom error message to MSSQL:

EXEC sp_addmessage {number}, 59999, 'MyArgument|This will raise an ArgumentException.'
⚠️ **GitHub.com Fallback** ⚠️