I am starting a project and am struggling with the architecture for our data access layer. Basically it will need to interface with multiple backends with different database designs.
I would like a common DAL, which then executes a common function in any backend. The backends have unique code for inserting, updating, etc. So adding an Employee in 1 backend will have different code in another.
I tried the Repository pattern but that just doesn't apply to the situation. I've ended up with just a Factory pattern method, but I will end up creating a Factory for each object. I could maybe only create 1 factory but then the Backend object would have hundred of functions like "SaveEmployee", "SavePlan", etc.
Right now I have the following:
DAL
--> DAL.Backend1
--> Employee.Save(employee)
--> Plan.Save(plan)
--> DAL.Backend2
--> Employee.Save(employee)
--> Plan.Save(plan)
In the DAL project I have a Factory pattern for each Object, Employee, Plan, to decide which DAL's Object to return and execute against.
I am pretty sure this is not the best architecture for this, so I am wondering if there's a better pattern to use to solve my problem.
We've implemented this functionality on one of our projects. I'm not sure it's the best solution, but so far it's working for us. However, we have a custom DAO layer based on CodeSmith templates that generate the CRUD classes for us. Essentially, we created a singleton which represents a connection broker for all users. When the user logs in, they select the database the want to connect to (though some IP filtering narrows down their options, ideally to 1). The login stores a Connection Token with the associated user and the base class for the generated DAO layer calls the connection broker to retrieve the appropriate connection string. That way anytime a DAO object is called the connection string is gathered before the DAO object attempts to connect to the DB.