Macros can be used in Reports and SQL Scripts to dynamically create and execute SQL queries at runtime without using SQL string concatenation to construct them and sp_executesql for execution. Macros are written in C#, which offers great flexibility for creating both simple and very complex macros.
The query below shows an example of how macros can be combined with standard SQL. The {{ and }} tokens specifies the presence of a macro. The macro itself is called “MyRowCount”, and it takes 1 numeric argument.
select {{MyRowCount(100)}} * from [Store_WebAppBasicStore_05052015115318]
The macro implementation itself looks like this:
namespace Local
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Profitbase.Invision.Scripting.Dynamic;
public class MyMacros : MacroExpansionProvider
{
public string MyRowCount(int num)
{
return $"TOP {num}";
}
}
}
So in this simple case, the resulting SQL will be
select TOP 100 * from [Store_WebAppBasicStore_05052015115318]
Obviously, you would probably not need a macro for a simple TOP n statement, but this simple example shows the basic concepts or writing and using macros.
Using macros in SQL statements
You can put macros into any part of a SQL Script or Report query. Macros are not constrained to any particular part of SQL queries, for example WHERE clauses. You can even define your entire SQL query using just macros.
The “{{” and “}}” tokens marks be beginning and end of a macro expression. As an example, {{MyWhereClause(@ProductID)}} specifies a macro expression where MyWhereClause is a macro that takes one argument.
The example below shows how to dynamically construct a SQL query using macros. As you can see, we’re using both Function and Non-Function macros (macros that can take arguments, and those that can’t).
SELECT {{ColumnNames}}
FROM @Object[WebApp Basic Store,DataStore].DbObjectName
WHERE {{WhereClause}} AND {{FilterOnQuantity(10)}}
{{OrderByAsync()}}
This query has 4 macros – ColumnNames, WhereClause, FilterOnQuantity and OrderByAsync.
ColumnNames and WhereClause are Non-Function macros (they do not have a parameter list), while FilterOnQuantity and OrderByAsync are function macros (they do have a parameter list). Non-function and Function macros does not differ in where they can be used, but how they can be used and how they are implemented.
The code below shows the core concepts of how these macros can be implemented. Notice how the method names of the Function macros matches the names of macros in the SQL expression, and that they return a string. Also notice that the expansions for the Non-Function macros are resolved in the RunAsync method.
namespace Local
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Profitbase.Invision.Scripting.Dynamic;
public class MacroRuntime : MacroExpansionProvider
{
public override async Task<IEnumerable<Expansion>> RunAsync()
{
var list = new List<Expansion>();
list.Add(new Expansion("WhereClause", "Qty > 2")); // Expansion of the WhereClause macro
var columnNames = await ObjectModel.ForObject("WebApp Basic Store").Storage().GetColumnNamesAsync();
list.Add(new Expansion("ColumnNames", string.Join(",", columnNames))); // Expansion of the ColumnNames macro
return list;
}
// Expansion of the FilterOnQuery macro
public string FilterOnQuantity(int num)
{
return $"Qty < {num}";
}
// Expansion of the OrderByAsync macro
public Task<string> OrderByAsync()
{
return Task.FromResult("ORDER BY AccountID");
}
}
}
Writing macros
Macros are written in C# and must be implemented in a class that inherits from the MacroExpansionProvider base class.
To implement Non-Function macros, you need to override the RunAsync() method in the base class and return one expansion for each macro.
To implement Function macros, you must implement your own methods which must return a string.
Function macros
Function macros have the following characteristics
- They must be implemented as methods in a class that inherits from the MacroExpansionProvider base class
- They must return a string or a Task<string>
- They can have 0 or n parameters
Because a Function macro can accept arguments, you can use it multiple times in the same query by providing different arguments to produce different expansions.
Example
This example shows how to create a function macro which takes one argument. You can use it in a SQL query like this: SELECT {{MyRowCount(30}} … which will produc SELECT TOP 30 …
namespace Local
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Profitbase.Invision.Scripting.Dynamic;
public class MyMacros : MacroExpansionProvider
{
public string MyRowCount(int num)
{
return $"TOP {num}";
}
}
}
Non-function macros
Non-function macros have the following characteristics
- They must be resolved in the RunAsync override method of a class that inherits from the MacroExpansionProvider base class
- The RunAsync method returns a collection of Expansion objects. An Expansion object must contain the macro name and the expansion itself (the value that the macro produces)
Because Non-Function macros cannot accept arguments, they will always produce the same result, so you cannot use them multiple places in the same query unless you want the same SQL expression to be inserted at the different locations.
Example
This example shows how to produce the expansions Non-function macros. Non-function macros cannot take any arguments, and they will always produce the same value for the same execution.
namespace Local
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Profitbase.Invision.Scripting.Dynamic;
public class MacroRuntime : MacroExpansionProvider
{
public override async Task<IEnumerable<Expansion>> RunAsync()
{
var list = new List<Expansion>();
list.Add(new Expansion("WhereClause", "Qty > 2")); // Expansion of the WhereClause macro
var columnNames = await ObjectModel.ForObject("WebApp Basic Store").Storage().GetColumnNamesAsync();
list.Add(new Expansion("ColumnNames", string.Join(",", columnNames))); // Expansion of the ColumnNames macro
return list;
}
}
}
The MacroExpansionProvider API
The MacroExpansionProvier has an API which can help you to implement your macros. The API provides services and properties for getting information about the current solution, metadata about objects in the solution, the arguments passed to the current execution context (typically originating from SetParamValue(…) in Workbooks, and so on.
MacroExpansionProvider.Context
The Context API provides information about the current execution context of the macros. For example, for macros in a SQL Script, the current execution context of the macros is the invocation of the SQL Script. You can read about the details of the Context API in the docs.
The following example shows how the Context API can be used to create a ConditionalWhere macro.
namespace Local
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Profitbase.Invision.Scripting.Dynamic;
public class MyMacros : MacroExpansionProvider
{
public string ConditionalWhere(string tableAlias)
{
if(this.Context.IsDevMode)
{
return "WHERE 1 = 1";
}
else
{
if(this.Context.Arguments.TryGetValue<int>("@Level", out int level))
{
// @SelectedProductId is passed as argument to the execution context
// for example by SetParamValue(...). If it is not, provide a default value
if(!this.Context.Arguments.Contains("@SelectedProductId"))
{
this.Context.CustomArguments.Add("@SelectedProductId", "myDummyValue");
}
switch(level)
{
case 1:
return $"WHERE {tableAlias}.L1 = @SelectedProductId";
case 2:
return $"WHERE {tableAlias}.L2 = @SelectedProductId";
default:
return $"WHERE {tableAlias}.LEAF = @SelectedProductId";
}
}
else
{
return "WHERE 1 = 2";
}
}
}
}
}
You could for example use the macro like this:
SELECT ProductID, ProductName, SUM(SalesAmt) AS SalesAmt
FROM ProductSales sales
{{ConditionalWhere("sales")}}
GROUP BY ProductID, ProductName
MacroExpansionProvider.SqlCommandService
The SqlCommandService provides an API to execute arbitrary sql commands. (Examples further down)
MacroExpansionProvider.ObjectModel
The ObjectModel API provides access to metatadata information and data for the objects in the solution. For example connection strings, database table names for materialized objects, column names and transactions (Examples further down)
Examples
The following example shows how the Context, SqlCommandService and ObjectModel API can be used to create a macro (SelectList) that returns a collection of columns that can be used as the select list of a query.
namespace Local
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Linq;
using Profitbase.Invision.Scripting.Dynamic;
public class MyMacros : MacroExpansionProvider
{
public async Task<string> SelectList()
{
var customerMetadataStorage = this.ObjectModel.ForObject("Customer Metadata").Storage();
var connectionString = await customerMetadataStorage.GetConnectionStringAsync();
var customerMetadataTableName = await customerMetadataStorage.GetDbObjectNameAsync();
var customerCategory = await this.SqlCommandService.ExecuteScalarAsync<int>(connectionString, $"SELECT CustomerCategory FROM {customerMetadataTableName} WHERE CustomerID = @customerId", new SqlParameter("@customerId", this.Context.Arguments.GetValue("@CustomerID")));
if(customerCategory == 1)
{
return string.Join(",", (await this.ObjectModel.ForObject("Customer Sales").Storage().GetColumnNamesAsync()).Where(c => c.Contains("Cat1")));
}
else
{
return string.Join(",", (await this.ObjectModel.ForObject("Customer Sales").Storage().GetColumnNamesAsync()).Where(c => !c.Contains("Cat1")));
}
}
}
}
You could for example use the SelectList macro like this:
SELECT {{SelectList()}} FROM Customer_Sales
What about arguments?
If you need to check the arguments passed to the object being executed (SQL Script, Report query, etc) – and you probably will – you can use the Context.Arguments API.
public string CheckArguments()
{
// will throw invalid cast exception if @EmployeeID cannot be cast to int
if(this.Context.Arguments.TryGetValue<int>("@EmployeeID", out int employeeId))
{
// do something with employeeId
}
if(this.Context.Arguments.TryGetValue("@ManagerID", out object managerId))
{
// do something with Manager ID
}
// Check if the @CustomerID was passed
bool customerIdExists = this.Context.Arguments.Contains("@CustomerID");
// will throw if KeyNotFoundException if @SalesRepID does not exist.
// will throw invalid cast exception if @SalesRepID cannot be cast to string
var salesRepId = this.Context.Arguments.GetValue<string>("@SalesRepID");
object objValue = this.Context.Arguments.GetValue("@SomeObjectID");
return string.Empty;
}
Conclusion
That was a long read! Hopefully, you have gained some understanding of how you can use macros to produce dynamically generated SQL queries at runtime.
-The Cloud and Applications Team