Introduction
One of my daily tasks is to integrate SAP's Business One ERP inside our customer's IT system. Integration is an important part of the job, since the companies for which we implement the ERP have more often than never, other systems to do complementary tasks and all these systems have to communicate together.
For one of the last projects I worked on, the need was to "deeply" integrate the ERP in the IT environment of the customer. For reasons which belong to this customer, this bi-directional integration (send modification done in the ERP, integrate modifications done in other systems) was done thru web services calls... So it means SOAP calls with xml data transfers. I'm not here to discuss the pros and cons of this choice but to present a specific problem (and the solution given): some of the outgoing calls from the ERP had to be synchronal from the modifications done by the user up to the return of the web service call. Meaning that the effectiveness of the user's modifications depends upon the result of a distant program, creating a large distributed transaction.
Easily said not so easy to implement (at least the first time 
). Synchronal from the ERP means that data have to be extracted within a SQL-Server transaction (so from inside the user's process, else modifications won't be visible), do some transformations on these data, call the distant web service and integrate the result of the call inside the user's process.
For this article, our interests will go to a small excerpt of the global process:
- Extracting data from the SQL-Server,
- Transform it in an "understandable" form to call a web service.
This seems small, but it will give enough to deal with for an article!
Background
The problem presented here (and the implemented solution) has to be viewed as a general one, but the solution describes is specific to a given customer: so full source code cannot be provided and a demo project isn't really relevant. Beside this, what is interesting here is the overall process and how problems have been solved.
It's assumed that the reader has enough knowledge of SQL (in particular of T-SQL from SQL-Server), can read a bit of C# source-code and mainly, has a good understanding of a global system to adapt all or parts of the materials provided here to its own implementation(s).
Global architecture
Before doing anything, it is necessary to understand how the solution is organized, so we have to look first at the full picture:
- User modifies specific data inside the ERP (customer, item, specific data, invoice, and so on).
- For each of these modifications, the ERP starts a SQL-Server transaction and, from inside it, calls a user stored procedure where data is controlled; it's where to recognize that some work has to be done.
- If some work has to be done, by whatever way a web-service has to be called with the modified (and technically transformed) data.
From here, we have several problems:
- All calls aren't necessarily synchronal: some of them can be asynchronous. Respecting the "Do It Once" rule; it has been choose to implement both the same way and to differentiate only how to call this single implementation.
- Some mechanism to deal with errors has to exist: so logging that a request has been started (or planned to be), and its results. The bad news is that the logging as to stay if the global process fails (for synchronal calls, transaction is rolled back),
- It has to go fast: process can be fired from with a transaction and users aren't usually patient, so no time to spend on structures transforming and so on.
- It has to be extensible: so we need to minimize the work to do in case of adding new objects to deal with.
- It has to be reproducible, at least for debugging. The overall system should give the opportunity to reprocess a failed message, in whichever way.
Besides the fact that we won't discuss in detail every point, the given answers have been the following ones:
A specific database has been introduced, database which contains configuration tables. These tables give the objects we can deal with, for which kind of transfer (who is the originator, who is the destination, synchronal or not, kind of movement (Add - Update) and so on). Together with this configuration, "live tables" exist: they archive the transfer demands, what happened, log error messages and so on.
So, from our five previous points:
- Transfer demands are all logged to be processed by a specific stored-procedure. For those which have to be synchronal, the procedure is called directly; else, a timer will call it periodically for all pending calls. So easy answer.
- Logging is an integral part of the architecture. The only part which concerns us for this article is "good" SQL writing, and we'll see this later.
- It starts to be tricky here, and it's the main subject of this article. At the end we have to call a web service (defined by a WSDL), and to transfer really complex objects not just a couple of strings. From here, a good and a bad news: the good is that in the SQL-Server we can add stored-procedures programmed in .NET; the bad is that from all the limitations (mainly because of security) introduced by Microsoft, even if we're able to call a web service from inside the extended stored procedure we aren't allowed to reference a WSDL.
So the easiest solution is to introduce an intermediate web-service, which will receive data from the SQL-Server in a really simply form (single string), reformats and transforms it then call the real web service. - This is one of the trickiest parts: because of the preceding point, if adding new objects the intermediary web service has to be modified, so it becomes necessary to limit the modifications to this. The choice made was to modify one of the configuration tables to include the SQL query to execute when to extract data.
- This point is achieved by the global architecture: since the system histories’ all the demands of interface, to reproduce a call we only have to change its status into the historical database.
So, where are we now?
- A modification is done on data by the user, modification which is archived by the system.
- If this modification corresponds to one of our interfaces, the demand is logged. If the interface is synchronal it is immediately executed, else it's "only" stored and will wait until a periodic timer detects the pending demand and executes the interface.
- The process then use dynamic queries to extract data to a single string with the results and call an intermediary web service.
- The intermediary web-service will transform the received data in an understandable form, transforms it and calls the final web service.
- The result of the call is stored at the end in the archive tables and if the call was synchronal, notifies the user.
Here the picture is nearly full, only one point is missing: data transformation inside the intermediary web service. The final web service will be called by using SOAP protocol, so it uses only xml data for its transfer. The DotNet framework will do something interesting here: it will natively transform .NET objects into xml by serialization. So the result of our transformation needs to be a C# objects (which is generated during the integration of the wsdl).
The easiest way to do our controls - transformations and processes, is to deal only with C# objects and to avoid as the plague string/xml manipulations. Doing so has another advantage: we're going to be sure of data types, and won't have to do any conversions, only mapping and transformations. So: we're coming from the SQL-Server with data as a single string and we need an object? Looks like a deserialization process And from what kind of string to deserialize? From xml. So the string sent by the SQL-Server has to be formatted as xml: pretty simple
So at the end of this description:
- We have configuration tables with SQL queries inside,
- These queries are executed as dynamic SQL and returns an xml formatted string,
- The xml formatted string is sent as a parameter to the intermediary web service, which will deserialize it,
- From here, this web service does its job and this doesn't interests us for this article.
Just easy.
Extracting data
This process has to be thought globally and not only from the SQL-Server side: the extracted data have to be processed by a web service; which needs to have strongly typed data (it needs to know the column names, data types and the organization of the received data) to be able to transform them.
The easiest here, is to define the XML from XSD definitions, and thru the xsd.exe tool to generate C# objects. This process will give us the following benefits:
- With the generated objects, we'll have only C# objects manipulations and no string-xml conversions,
- The xsd will define the format of the xml strings to extract from the SQL-Server.
Since the SQL queries to extract data can be complex, the easiest is to do stored procedures from them, stored procedures which are going to be called dynamically. As an example, here is one of these stored procedures:
IF EXISTS (select * from sysobjects where type = 'P'
and name = 'ESB_Frontend_Create_ProductInformations')
DROP PROCEDURE ESB_Frontend_Create_ProductInformations
GO
CREATE PROCEDURE ESB_Frontend_Create_ProductInformations
@parm varchar(20),
@xml nvarchar(MAX) out
AS
SET @xml = (SELECT DISTINCT
I0.ItemCode
, I0.CodeBars
, G0.ItmsGrpNam ItemGroupName
, I0.ItemName
, I0.U_Color Color
, I0.U_Brand Brand
, ESB_CS.U_ESB ContributionStatus
, ESB_LS.U_ESB LogisticStatus
, I1.Price DistributorPurchasePrice
, I0.AvgPrice AvgStdPrice
, ESB_NF.U_ESB NetworkFlag
FROM DB_PRODUCTION..OITM I0 WITH (NOLOCK)
INNER JOIN DB_PRODUCTION..OITB G0 WITH (NOLOCK) ON I0.ItmsGrpCod = G0.ItmsGrpCod
INNER JOIN DB_PRODUCTION..ITM1 I1 WITH (NOLOCK)
INNER JOIN DB_PRODUCTION..OPLN PL0 WITH (NOLOCK) ON I1.PriceList = PL0.ListNum AND PL0.U_Usage = 'PCD'
ON I0.ItemCode = I1.ItemCode
INNER JOIN DB_PRODUCTION..[@ESB_LOGISTICSTATUS] ESB_LS WITH (NOLOCK) ON I0.U_LogStatus = ESB_LS.Code
LEFT OUTER JOIN DB_PRODUCTION..[@ESB_NETWORKFLAG] ESB_NF WITH (NOLOCK) ON I0.U_NetFlag = ESB_NF.Code
INNER JOIN DB_PRODUCTION..[@ESB_CONTSTAT] ESB_CS WITH (NOLOCK) ON I0.U_ContStat = ESB_CS.Code
WHERE I0.ItemCode = @parm
FOR XML PATH)
Two things interesting here:
- First, the use of the WITH (NOLOCK) clause after each of our table names. This is mandatory, since the procedure can be called from inside a transaction and if this was not added, we'll get deadlocks inside the stored procedure.
- Second, the global call: SET @xml = ( ... FOR XML PATH). The result of this is that the procedure will return a single string with all the data. This string will be xml format based and tag names will be the names of the columns.
Remind that we have to deserialize this into a C# object? Here is the xsd definition for this data:
<xs:complexType name="SboEsbProductItem">
<xs:all>
<xs:element name="ItemCode" type="tns:ItemCode" />
<xs:element name="CodeBars" type="tns:BarCode" />
<xs:element name="ItemGroupName" type="tns:GroupName" />
<xs:element name="ItemName" type="tns:ItemName" />
<xs:element name="Color" type="tns:Color" />
<xs:element name="Brand" type="tns:Brand" />
<xs:element name="ContributionStatus" type="tns:ContributionStatus" />
<xs:element name="LogisticStatus" type="tns:LogisticStatus" />
<xs:element name="DistributorPurchasePrice" type="tns:Price" />
<xs:element name="AvgStdPrice" type="tns:Price" />
<xs:element name="NetworkFlag" type="tns:NetworkFlag" />
</xs:all>
</xs:complexType>
So, the names of columns inside the SQL query have to be the names of tags inside the xsd: this is this simple thing which will make the global process working.
This xsd definition is included in a more global schema (excerpt):
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:tns="http://www.elconsulting.fr/"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.elconsulting.fr/"
elementFormDefault="qualified" version="1.0">
<xs:include schemaLocation="InterfacesTypes.xsd" />
<xs:include schemaLocation="GpObjects.xsd" />
<xs:include schemaLocation="EsbObjects.xsd" />
<!
<!
<xs:complexType name="AdminInfo">
<xs:all>
<xs:element name="archiveId" type="tns:archiveId" minOccurs="1" default="0" />
<xs:element name="interfaceId" type="tns:interfaceId" minOccurs="1" default="0" />
<xs:element name="RetryInstanceId" type="tns:RetryInstanceId" minOccurs="1" default="0" />
<xs:element name="Source" type="tns:SubSystem" minOccurs="1" />
<xs:element name="Destination" type="tns:SubSystem" minOccurs="1" />
<xs:element name="Entity" type="tns:Entity" minOccurs="1" />
<xs:element name="Method" type="tns:Method" minOccurs="1" />
<xs:element name="Database" type="tns:Database" minOccurs="1" />
<xs:element name="ObjectType" type="tns:ObjectType" />
<xs:element name="ObjectTransactionType" type="tns:ObjectTransactionType" />
<xs:element name="ObjectKey" type="tns:ObjectKey" />
<xs:element name="ObjectKeyValue" type="tns:ObjectKeyValue" />
<xs:element name="Created" type="tns:Timestamp" minOccurs="1" />
</xs:all>
</xs:complexType>
<xs:complexType name="BusinessObject">
<xs:choice>
<xs:element name="SboEsbProductItem" type="tns:SboEsbProductItem" maxOccurs="1" />
</xs:choice>
</xs:complexType>
<!
<xs:element name="WS_SBO">
<xs:complexType>
<xs:sequence>
<xs:element name="AdminInfo" type="tns:AdminInfo" minOccurs="1" />
<xs:element name="BusinessObject" type="tns:BusinessObject" minOccurs="1" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Our stored procedure, is called from inside another one, here are now two excerpts of this last one.
First, how to extract data and get our xml string:
IF @query <> ''
BEGIN
SELECT @parameters = <a href="mailto:'@parm">'@parm</a> varchar(20), @xml nvarchar(MAX) output'
EXECUTE sp_executesql @query, @parameters, @list_of_cols_val_tab_del, @xml = @xml out
END
Nothing fancy, just plain dynamic SQL. The received variable @query contains (for our example):
EXEC ArchiveDB..ESB_Frontend_Update_ProductInformations @parm, @xml out
Second, calling the intermediary web service (detailed later). To do it, a control object is created with technical informations about the current process (the AdminInfo object from the schema), then the external stored procedure is executed:
SELECT
@AdminInfo =
'<![CDATA[<AdminInfo>' +
CASE ISNULL (@interface, 0) WHEN 0 THEN '' ELSE '<interfaceId>' +
CAST (@interface AS VARCHAR) + '</interfaceId>' END +
'<archiveId>' + CAST (@archive AS varchar) + '</archiveId>' +
'<RetryInstanceId>' + CAST (@RetryInstance AS varchar) + '</RetryInstanceId>' +
'<Source>SBO</Source>' +
'<Destination>' + ISNULL (@destination, '') + '</Destination>' +
'<Database>' + ISNULL (@syndb, '') + '</Database>' +
'<Entity>' +
CASE ISNULL (@destination, '')
WHEN 'ESB' THEN
CASE @object_type
WHEN '2' THEN 'BusinessPartner'
WHEN '4' THEN 'ProductItem'
WHEN 'SOFI_DEVICE' THEN 'Device'
WHEN 'SOFI_INSIM' THEN 'Sim'
WHEN '17' THEN 'SalesOrder'
WHEN '22' THEN 'PurchaseOrder'
ELSE ''
END
WHEN 'GP' THEN
CASE @object_type
WHEN '4' THEN 'ProductItem'
WHEN '13' THEN 'CustomerInvoice'
WHEN '14' THEN 'CustomerCreditNote'
WHEN '15' THEN 'InventoryTransactionDelivery'
WHEN '20' THEN 'InventoryTransactionReception'
WHEN '67' THEN 'InventoryTransfert'
ELSE ''
END
ELSE ''
END +
'</Entity>' +
'<Method>' + CASE ISNULL (@transaction_type, '')
WHEN 'A' THEN 'Create'
WHEN 'U' THEN 'Update'
ELSE '' END +
'</Method>' +
'<ObjectType>' + ISNULL (@object_type, '') + '</ObjectType>' +
'<TransactionType>' + ISNULL (@transaction_type, '') + '</TransactionType>' +
'<ObjectKey>' + ISNULL (@list_of_key_cols_tab_del, '') + '</ObjectKey>' +
'<ObjectKeyValue>' + ISNUll (@list_of_cols_val_tab_del, '') + '</ObjectKeyValue>' +
'<Created>' + CONVERT (varchar(64), getdate(), 126) + '</Created>' +
'</AdminInfo>]]>'
, @BusinessObject = '<![CDATA[<BusinessObject>' + @xml + '</BusinessObject>]]>'
SELECT @WebService = prmValue
FROM dbo.Parameters
WHERE prmName = 'SBO-' + @destination
BEGIN TRY
exec @error = dbo.SboWebService @WebService, @AdminInfo, @BusinessObject,
@errorClass OUT, @errorCode OUT, @errorDetails OUT
IF @error <> 0
BEGIN
SELECT @error = @errorCode, @error_message = @errorDetails
END
END TRY
BEGIN CATCH
SELECT @Error = 1, @error_message = ERROR_MESSAGE()
END CATCH
The only tricky part here is that we are sending xml data to a web service which will resend it (without any transformation) to another one. But, the external stored procedure (SboWebService) to be able to send this data must not interprets it; and the only way to achieve this in xml is to include the string inside a [CDATA[ .. ]] block.
Something else interesting here is the block of T-SQL where the @WebService variable is affected: in the final solution, it exists in fact six web-services and four can be called from the SQL-Server. So, here we are getting from a configuration table the real address of the web service to call. A (good) side effect of this is that with this no differences exists between the misc. environments (development, test and production).
External stored procedure
To call the intermediary web service from SQL server, it exists several methods, which won't be discussed here. During the implementation, for the sake of performance, evolution and consistency of the development, it has been choosed to do something like http://blogs.msdn.com/b/spike/archive/2010/11/25/how-to-consume-a-web-service-from-within-sql-server-using-sql-clr.aspx, which is one of the many available resources on this subject, so no need to emphasis on it.
The only tricky thing for this is that because of the SQL-Server (2008R2) can only use the CLR 2.0 the project has to target the Framework 2.0.
The body of the external stored-procedure is pretty simple, just parameters conversion (both ways) and the call to our intermediary web service:
public class StoredProcedures
{
[SqlProcedure]
public static SqlInt32 SboWebService(SqlString url, SqlString xmlAdminInfo,
SqlString xmlBusinessObject, out SqlString errorClass,
out SqlInt32 errorCode, out SqlString errorDetails)
{
string error;
int code;
string details;
errorClass = (SqlString)string.Empty;
errorCode = 0;
errorDetails = (SqlString)string.Empty;
var ws = new CallWebService.CallWebService.Frontend
{
Url = url.ToString()
};
int returnValue = ws.SboWebService(xmlAdminInfo.ToString(),
xmlBusinessObject.ToString(), out error, out code, out details);
if (0 != returnValue)
{
errorClass = error;
errorDetails = details;
}
return (SqlInt32)returnValue;
}
};
At the line var ws = new CallWebService.CallWebService.Frontend, the first CallWebService is the namespace given to the Web Reference when it was imported. We are dynamically changing the destination url, with the parameter discussed previously.
Intermediary web service
Finally, it is where the work has to be done. The only thing interesting us here, is that the web service is receiving an xml string which when deserialized provides the objects to process. Respecting the "Do It Once" rule, the corresponding code resides in an ancestor abstract class:
public int SboWebService(string xmlAdminInfo, string xmlBusinessObject,
out string errorClass, out int errorCode, out string errorDetails)
{
var returnValue = 0;
using (_sLogger.VerboseCall())
{
var watch = new StopwatchExt();
string xmlWsObject;
_sLogger.Debug("SboWebService Parameters:");
_sLogger.Debug(xmlAdminInfo);
_sLogger.Debug(xmlBusinessObject);
if (true == ReformatData(xmlAdminInfo, xmlBusinessObject,
"WS_SBO", out errorClass, out errorCode, out errorDetails, out xmlWsObject))
{
Data.Ws.AdminInfo adminInfo = null;
Data.Ws.BusinessObject businessObject = null;
try
{
var sboBase = XmlHelpers.Deserialize<Data.Ws.WS_SBO>(xmlWsObject);
if (null != sboBase)
{
adminInfo = sboBase.AdminInfo;
businessObject = sboBase.BusinessObject;
}
}
catch (Exception e)
{
_sLogger.Debug(e);
errorDetails = "Error deserializing WsSboBase object";
returnValue = 1;
_sLogger.Error(errorDetails + ": " + xmlWsObject);
adminInfo = null;
businessObject = null;
}
if (null != adminInfo)
{
if (null != businessObject)
{
returnValue = SboWebServiceEx(adminInfo, businessObject,
out errorClass, out errorCode, out errorDetails);
}
else
{
var contextManager = ContextManager.CreateNew(
WebConfigurationManager.ConnectionStrings["Interfaces"]);
var errorMessage = "Nothing to process. Execution stopped [ArchiveId: " +
adminInfo.archiveId + "][Entity: " + adminInfo.Entity +
"][Method: " + adminInfo.Method + "][ObjectKey: " +
adminInfo.ObjectKey + "][ObjectKeyValue: " +
adminInfo.ObjectKeyValue + "]";
_sLogger.Error(errorMessage);
returnValue = 1;
contextManager.Initialize(adminInfo.Source.ToString(),
adminInfo.Destination.ToString(), adminInfo.Entity.ToString(),
adminInfo.Method.ToString());
contextManager.Archive = adminInfo.archiveId;
contextManager.RetryInstance = adminInfo.RetryInstanceId;
InterfacesHelpers.AddRetryInstanceError(contextManager, "Error",
"SAPBusinessOne", "-2052", errorMessage, null, null);
InterfacesHelpers.SetArchiveStatus(contextManager, "E");
}
}
}
else
{
_sLogger.Error("Error Reformating Data");
returnValue = 1;
}
watch.Stop();
_sLogger.Info("Time Elapsed: {0} - Return: {1}",
watch.ElapsedTime, returnValue);
}
return returnValue;
}
[WebMethod]
public abstract int SboWebServiceEx(Data.Ws.AdminInfo adminInfo,
Data.Ws.BusinessObject businessObject, out string errorClass,
out int errorCode, out string errorDetails);
And the ReformatData function, without wich nothing can work:
protected bool ReformatData(string xmlAdminInfo, string xmlBusinessObject,
string rootNode, out string errorClass, out int errorCode,
out string errorDetails, out string xmlWsObject)
{
bool returnValue = false;
using (_logger.VerboseCall())
{
errorCode = 0;
errorClass = string.Empty;
errorDetails = string.Empty;
xmlWsObject = string.Empty;
if ((false == string.IsNullOrEmpty(xmlAdminInfo)) &&
("Null" != xmlAdminInfo))
{
if ((false == string.IsNullOrEmpty(xmlBusinessObject)) &&
("Null" != xmlBusinessObject))
{
try
{
if (true == xmlAdminInfo.StartsWith("<![CDATA["))
{
xmlAdminInfo = xmlAdminInfo.Substring(9, xmlAdminInfo.Length - 12);
}
if (true == xmlBusinessObject.StartsWith("<![CDATA["))
{
xmlBusinessObject =
xmlBusinessObject.Substring(9, xmlBusinessObject.Length - 12);
}
xmlWsObject =
"<?xml version=\"1.0\"?>\r\n<" + rootNode +
" xmlns:xsi=\"http://www.w3.org/2001/xmlschema-instance\" xmlns" +
":tns=\"http://elconsulting.fr/\" xmlns=\"http:" +
"//www.elconsulting.fr/\">\r\n";
xmlWsObject += xmlAdminInfo;
xmlWsObject += xmlBusinessObject;
xmlWsObject += "</" + rootNode + ">";
returnValue = true;
}
catch (Exception e)
{
_logger.Debug(e);
errorDetails = "Error reformating xml data: wrong format";
_logger.Error(errorDetails);
}
}
else
{
errorDetails = "BusinessObject not provided: nothing to process";
_logger.Error(errorDetails);
}
}
else
{
errorDetails = "AdminInfo not provided: don't know what and how to process";
_logger.Error(errorDetails);
}
}
return returnValue;
}
Mainly removing the <![CDATA[ ]]> declarations and adding namespaces to be able to deserialize the objects.
What comes next is the functional part of the process, and isn't really interesting for this article.
Conclusion
From a simple demand (having synchronal process between a user's data entry and the validation from a remote web service), we get a complex solution to implement, because of all the existing limitations. But, by dividing it in small parts we have been able to give a descent answer to the initial demand.
What have we seen in this overall process?
- First, we can call a web-service from inside a transaction, and call it with the currently modified data. This removes all limitations to what it's possible to do to validate / transform data.
- Second, it is possible to extract data from a SQL-Server (I suppose this can work with other RDBMS, but I hadn't the need of it so haven't tried) in such a way that we're able to deserialize it and have native DotNet objects to deal with.
- Third and finally, with a bit of organization and by looking at the system globally we have been able to minimize the work to do: SQL queries written in such a way that they follow an XSD definition and a bit of deserialization.
Personally, this will stay in my toolbox
History
- 2013-05-20 - Initial version.
