This is probably a re-hash of an old topic, but most of what i found searching the forum is quite old so I thought a refresher might be in order.
I have a project that requires me to integrate data from three separate databases. Two are SQL Server while the third is DB2. The DB2 database is closed to changes as it houses the company's financial data from their ERP system. I cannot add tables, views, etc. - I have to use it as-is. One of the SQL Server databases contains metadata that is used to help work with the data in the DB2 database. The other hold application-specific data used by the client app.
I am using LINQ-to-SQL to access the SQL Server databases and standard ADO.NET for DB2. There is no LINQ-to-DB2 that I know of and the company (despite my urging) is not willing to use Entity Framework because they have several outdated developers who can't support the app if we jump into EF. (I know!)
Oh, and we are following an SOA approach with an ASP.NET web-client front-end running on a web server, a WCF service application running on an app server behind the firewall and the databases running on their own servers internally. The WCF service app is a standalone application and not the data portal as we envision having multiple clients using the service in the future and the company doesn't want to lock the service interface into CSLA.
The problem at-hand is a call from the web application into the service application that requires data from more than one database. In the service application, I need to query a table in the SQL Server metadata database then use that information to query records in the DB2 database.
Long story short, I have a set of rules that determine what fiscal periods may be edited based on today's date. The list of rules for each fiscal period is contained in a SQL Server database. I apply those rules as part of the filter criteria when I query the DB2 database for financial information. The work is currently done by my service class by creating a new FiscalPeriodRuleList business object which hits the appropriate SQL Server database and retrieves the business rules. Once I have this, I'm kinda stumped as the best approach to obtain the final result which is a list of financial statements from the DB2 database. Right now I have another business object, Financials, that I am passing the FiscalPeriodRuleList to in the factory method and it generates the necessary query code.
I can provide more details as needed but any insights or suggestions are appreciated.
Copyright (c) Marimer LLC