I am currently working on converting a "legacy" web-based (Coldfusion) application from single data source (MSSQL database) to multi-tier OOP.

In my current system there is a read/write database with all the usual stuff and additional "read-only" databases that are exported daily/hourly from an Enterprise Resource Planning (ERP) system by SSIS jobs with business product/item and manufacturing/SCM planning data.

The reason I have the opportunity and need to convert to multi-tier OOP is a newer more modern ERP system is being implemented business wide that will be a complete replacement.

This newer ERP system offers several interfaces for third party applications like mine, from direct SQL access to either a dotNet web-service or a SOAP-like web-service.

I have found several suitable frameworks I would be happy to use (Coldspring, FW/1) but I am not sure what design patterns apply to my data access object/component and how to manage the connection/session tokens, with this background, my question has the following three parts:

  1. Firstly I have concerns with moving from the relative safety of a SSIS job that protects me from downtime and speed of the ERP system to directly connecting with one of the web services which I note seem significantly slower than I expected (simple/small requests often take up to a whole second). Are there any design patterns I can investigate/use to cache/protect my data tier?

  2. It is my understanding data access objects (the component that connects directly with the web services and convert them into the data types I can then work with in my Domain Objects) should be singletons (and will act as an Adapter/Facade), am I correct?

  3. As part of the data access object I have to setup a connection by username/password (I could set up multiple users and/or connect multiple times with this) which responds with a session token that needs to be provided on every subsequent request.

    Do I do this once and share it across the whole application, do I setup a new "connection" for every user of my application and keep the token in their session scope (might quickly hit licensing limits), do I set the "connection" up per page request, or is there a design pattern I am missing that can manage multiple "connections" where a requests/access uses the first free "connection"?

    It is worth noting if the ERP system dies I will need to reset/invalidate all the connections and start from scratch, and depending on which web-service I use might need manually close the "connection/session"

  • Knowing this question is rather old, you may want to supply an answer providing the details of how you solved the problem as well. Could help others in the future. Commented Oct 3, 2012 at 12:06

1 Answer 1


The options you've provided lead me to think that continuing to use the SSIS jobs to import data into a database you control for your application looks to be the best option for both managing the data flow and performance for the end-user.

If you've got requirements that need more up to date data than that supplied via SSIS then I would go for using the web services at that point for those specific parts of the application.

There is nothing in the DAO pattern that requires the implementation to be a singleton, you'll have a much easier time testing the DAO classes if they are implemented as normal classes and then only use a single instances as a dependency when in production.

Finally as session maintenance is required i would use short lived instances given you need to maintain per-request and if connection/resource limits are required to be managed a resource pool pattern implementation could be used to manage checking out/in access to instances that maintain the session tokens (eg get a new token on check out, release token on check in, ensure use is short lived). There should be a number of existing resource pool implementations available, use one as there are many gotchas to implementing that type of pattern.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.