Evolving Architecture for Legacy Applications

I started developing database applications before the idea of using data access layers or ORMs became mainstream. If you happened to develop under Delphi or Visual Studio in the early days, your application architecture may look something like this:

There is a database (or any kind of data store, such as files or XML or whatever), and an application that operates on the data.

Any change to the database structure cause a mess in the application code:

• where was a now-deleted table or field accessed or processed

• which views and procedures are accessed by the code

• some business logic implemented as stored procedures, some as application code

Time to separate implicit database access from application code (I’m looking at you, DataGrid, GridView and FormView with your magic DataSources!)

Enter the data access layer, which provides the application code (in my case, C#) a type-safe interface to the database:

This gets rid of the data usage and access problem: changes to the database structure are mirrored into the data access layer, and will cause compiler errors if a table or field has been removed or its data type has changed.

Still, the problem of implementing business functions in the database or in code remains, and the application needs to be aware or where the function is located.

The business layer encapsulated business logic wherever it is implemented, calling either business function in C# (object-oriented) or in the database (taking advantage of set-based operations or database-specific functionality). The application exchanges data with the business layer using the data contract classes.

As I mainly develop web applications, I notice that the object-oriented model occasionally breaks down in the front-end, since HTML is basically all-strings, and you need to parse and re-create your data access objects based on HTML data. Further, if you need to let your users edit hierarchically-structured data, you’ll soon get lost retrieving all your hierarchical business objects.

A user interface functions layer should handle the complexity of translating business objects to and from user interface objects:

Once you use a UI functions layer, it’s up to your aesthetic preferences whether you let the application still access the business data contracts, or pass everything through the UI data contracts. It is one additional step of mapping data, and you have been warned

Notice that, although I have a strong background in MS development tools (Visual Studio, SQL Server), I tried to keep the ideas and diagrams as technology-agnostic as possible. It does not matter whether your business functions reside in a web service (of any kind) or just in a separate library (assembly), or what database (if any) you use.

Checking VAT identification numbers using JavaScript

The EU Commission provides the checkVat web service to check the validity of EU VAT Identification Numbers (wsdl, redirected target).

To perform the web service query in the browser, I used the JavaScript SOAP Client on CodePlex which worked after a couple of tweaks.

First, IE9 seems to be unable to concatenate an XML document and an empty string, resulting in a Script Error. (see this post on the Discussion page). Edit the _loadWsdl function and modify the line checking the wsdl to:

if(typeof(wsdl) != "object" && wsdl + "" != "" && wsdl + "" != "undefined")

Next, the version of MSXML installed on my current work PC does not support the local-name() XPath function required in the function _getElementsByTagName. Since this function is used to retrieve the SOAP result only, and the result node is always on the same level in XML, I added to the function:

try
{
  var nd = document.selectNodes("//*/*/*/" + tagName );
  if (nd.length > 0) {
   return nd;
  }
}
catch (ex) {}
try
{
  return document.selectNodes("//*/*/urn:" + tagName );
}
catch (ex) {}

This handles both the SOAP *Result and *Response nodes (the checkVat service adds a urn: namespace to the response attributes).

The checkVat web service is somewhat “special” (at least according to my experience) in that it defines the parameters types in a sub-namespace (xsd:schema targetNamespace=”urn:ec.europa.eu:taxud:vies:services:checkVat:types”) rather than the web service’s namespace (wsdl:definitions targetNamespace=”urn:ec.europa.eu:taxud:vies:services:checkVat”). The parameter types namespace is also used in the Response (xmlns:urn=”urn:ec.europa.eu:taxud:vies:services:checkVat:types”).

I finally gave up trying to retrieve the correct namespace with XPath and simply added the line to _sendSoapRequest:

ns = ns + ":types";

The mismatch in namespaces causes Visual Studio to issue a comment in the generated Reference.cs file:

// CODEGEN: Generating message contract since the wrapper namespace (urn:ec.europa.eu:taxud:vies:services:checkVat:types) of message “checkVatRequest” does not match the default value (urn:ec.europa.eu:taxud:vies:services:checkVat).

Further, the checkVat service does not return a Result node, but only a Response message (probably the cause for the out parameters generated in C#).

So I had to fix the _onSendSoapRequest function to parse the Response node:

var nd = SOAPClient._getElementsByTagName(req.responseXML, method + "Result");
if(nd.length == 0)
  nd = SOAPClient._getElementsByTagName(req.responseXML, method + "Response");
if(nd.length == 0)
  nd = SOAPClient._getElementsByTagName(req.responseXML, "return"); // PHP web Service?

and remove the urn: namespace in the result elements (_node2object):

obj[node.childNodes[i].nodeName.replace("urn:", "")] = p;

Finally, my test page containing the web service call for both a valid and an invalid VATID was able to call the script:

var url = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService";

function CheckVATOk()
{
  var pl = new SOAPClientParameters();
  pl.add("countryCode", "AT");
  pl.add("vatNumber", "U40600000");
  SOAPClient.invoke(url, "checkVat", pl, true, CheckVAT_callBack);
}
function CheckVATInvalid()
{
  var pl = new SOAPClientParameters();
  pl.add("countryCode", "XX");
  pl.add("vatNumber", "U40600000");
  SOAPClient.invoke(url, "checkVat", pl, true, CheckVAT_callBack);
}
function CheckVAT_callBack(r, rawXml)
{
  if (typeof(r.valid) != "undefined")
  {
    alert(r.countryCode + " " + r.vatNumber + "\r\n" + r.name + ": " +
      r.address + "\r\n" + r.valid);
  }
  else if (typeof(r.number) != "undefined")
  {
    alert(r.name + " " + r.number + ": " +
      r.message.replace(/[{}\s']/g, ""));
  }
  else
  {
    alert(r);
  }
}

In case of an error, the error message needs to be stripped of a couple of “special” characters to get a character-only error string.

The code worked from a local html page, but not when executed inside a web application. This is due to the Same Origin Policy and raises the message “This Page Accesses Data on Another Domain” in IE9 unless you make the browser “trust” the web service’s host.

To work around this problem, you’d need to implement a local webservice calling the remote checkVat web service.

More Sample Databases and Free Datasets

As I prepared my previous post on SQL Server sample databases, I also came across sample databases for other SQL databases.

PgFoundry provides a couple of database samples for PostgreSql. Real-world applications such as OpenNMS also use PostgreSql.

For MySql, I found the AdventureWorks database for MySql on SourceForge. This question on SO also covers sample databases for MySql.

Of course, a lot of real-world applications use MySql as their datastore, such as Bugzilla, MediaWiki, WordPress, or Joomla.

One question on SO asked for freely available datasets, and one answer points to an amazing list of datasets available on the web.

Makes me wonder whether there exists a comprehensive list of freely available data, all tagged with hierarchical categories?

Microsoft SQL Server Sample Databases

A recurring question on StackOverflow is whether there are other sample databases for SQL Server beside the well-known Northwind, pubs (SQL Server 2000) and AdventureWorks (SQL Server 2005 and later).

Microsoft samples on CodePlex

• Microsoft SQL Server Community Projects & Samples (2005 and later)

• Microsoft SQL Server Product Samples: Databases (2005 and later)

Database-based applications available on CodePlex using SQL Server

• Kigg (2005 and later)

• Umbraco

• DotNetNuke Community Edition

Database-based applications using SQL Server

• ScrewTurn Wiki

Other sample databases

• StackOverflow Data Dump (Torrent)

• SQL Server 2005 Express Starter Data Models (2005; 10 models)

• Chinook Database (various database servers)

• EVE Online Data Export

• Geospatial sample data

CrmService.Retrieve throws SoapException if Record does not exist

If you call the CrmService.Retrieve method with a non-existing id parameter, a SoapException is raised instead of simply returning null.

This exception can be handled by specifically catching SoapException and analyzing the exception’s Detail property (source):

BusinessEntity result = null;
try
{
  result = crmService.Retrieve(entityName, entityId, new AllColumns());
}
catch (SoapException sex)
{
  XmlNode n = sex.Detail.SelectSingleNode("//error//code");
  if (n.InnerText == "0x80040217")
    result = null;
  else throw;            
}

The Detail property is really an XmlNode which can be queried in XPath using the SelectSingleNode method. Its string representation in case of failed data retrieval looks like this:

<detail>
  <error>
    <code>0x80040217</code>   
    <description>contact With Id = [some guid] Does Not Exist</description>
    <type>Platform</type> 
  </error>
</detail>

I tried to verify the “magic” error code 80040217 and found this list of Dynamics CRM 4 Error Codes on MSDN, which states that the error codes can also be found in the SDK file SDK\Server\Helpers\CS\CrmHelpers\ErrorCodes.cs.