From SQL-Server to Web Service

An other way to export data from a SQL-Server to use them to call a web service

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:

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:

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:

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:

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).

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:

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.

The article depicts an excerpt of a much bigger project on which I was working on that time.

In fact, the origin of the export is a stored procedure which is called by an ERP (SAP Business One) when a modification applies. So a bigger picture is:
- The ERP records a transaction,
- The ERP calls a dedicated stored procedure,
- The SP has to notify a software bus of the modification, which in turns has to export the modified data and transmit them to a (several) remote web-service(s) which are totally unknown by the starting process.

For many reasons (inside or outside of a transaction, etc.), it was easier to export data directly from the SP and to call an intermediary web service and this what is explained in the article.

I've spent so much time on verifying the grammar and the orthography that I've forgotten to verify the SQL code in the text

Don't know for the demand... I was alone when I did it, since I had to answer to the customer with the boss behind. It's a small excerpt of something bigger you can imagine, but it could be decently package (never thought about it); you can catch me personally if you want to speak on that subject.