Unfortunately it is not so easy to directly modify service schema. Unlike Delphi, in .NET Schema is shared between service instances, so modifying it without side effects is possible yet can have performance issues.
There is a different approach that might work.
Here is the full source code of a DataService and explanations below:
using RemObjects.DataAbstract.Server;
using RemObjects.SDK.Types;
namespace WinFormsApplication35
{
[RemObjects.SDK.Server.Service]
public class DataService : RemObjects.DataAbstract.Server.DataAbstractService
{
public DataService()
{
this.InitializeComponent();
// For logging purposes only
this.BeforeExecutingGetDataReader += DataService_BeforeExecutingGetDataReader;
}
private void DataService_BeforeExecutingGetDataReader(RemObjects.DataAbstract.Schema.ServiceSchema sender, RemObjects.DataAbstract.Schema.BeforeExecutingGetDataReaderEventArgs e)
{
System.Diagnostics.Debug.WriteLine(e.Command.CommandText);
}
// Required for designer support
private void InitializeComponent()
{
RemObjects.DataAbstract.Bin2DataStreamer dataStreamer;
dataStreamer = new RemObjects.DataAbstract.Bin2DataStreamer();
this.AcquireConnection = true;
this.ServiceDataStreamer = dataStreamer;
this.ServiceSchemaName = "WinFormsApplication35Dataset";
}
public override Binary GetDataFiltered(string[] tableNames, TableRequestInfo[] requestInfo)
{
this.AllowCustomSqlMacros = true;
this.UnknownSqlMacroIdentifier += (IChannelSender, e) =>
{
if (e.Identifier == "ADDITIONAL_WHERE")
{
e.Value = "(Id in (1000, 1100, 1048))";
e.Processed = true;
}
};
return this.GetData(tableNames, requestInfo);
}
}
}
Here I will assume that we have a table Orders in the Schema with statement type set to AutoSQL
The handler for the BeforeExecutingGetDataReader is used to log the SQL statements sent to the server.
Step 1.
Change Orders table Statement Type from AutoSQL to SQL and let Schema Modeler generate the statement
It will be
SELECT
"Id", "OrderDate", "OrderStatus",
"CustomerId"
FROM
"Orders"
WHERE
{WHERE}
Step 2.
Amend statement as
SELECT
"Id", "OrderDate", "OrderStatus",
"CustomerId"
FROM
"Orders"
WHERE
{WHERE}
AND {ADDITIONAL_WHERE}
Note that DASM will think that the statement is broken - that is expected. Also the table won’t be accessible by client apps at this point
Step 3.
Check the GetDataFiltered method code
Enabling AllowCustomSqlMacros for all remote calls by default is very dangerous. This event allows to modify the underlying SQL statements directly and can be used for SQL injection. That’s why it is disabled by default.
In the code above it is enabled and a special event handler is attached. This event handler will take the ADDITIONAL_WHERE placeholder and replace it with the required SQL
