We are about change our multi-year database application and would like to eliminate a simple step. The winApp is a membership and billing database. Each year of the membership and billing is in a different database all hosted on a single SQL server.
Right now each time the application launches, the user is asked what year they want to look at. If they need to look at two years at the same time, they need to open a second instance of the winApp, and choose the other year. We store this selection in the UserObject corrently, and have been able to modify the DataAccess and Fetch to use what is in the UserObject.
ThHe users of the system, no longer want to open up a seperate instance each time. So we would like to place this year selection on each search screen. Then pass the connection string to each object before it tries to connect.
Well,
I guess you would have to set that as a "context" property on each "root" object (like "year") and let the serverside code control which database to use.
The "Best Approach" would likely be re-writing the database to accomodate a multiyear design in a single database. However, since that would likely take a lot of effort, you can change the context as Johnny suggested or you could union your queries across the databases. Reference each database with double dot notation. The user of course would need access to all databases referenced this way.
Example
Select Name, Data, '2010' year
From SCHEMAYEAR2010..MyTable
union
Select Name, Data, '2011' year
From SCHEMAYEAR2011..MyTable
Agreed. If the connection string varies based on the state of the object, then the object obviously needs to have a property with that state, so the DAL code can use that property value to find the right connection string for the object.
I wish that we would planned better 10 years ago for the multi years Curelow, but such is life.
ok, i'm feeling really dumb right now, but how can i set the property of the object so that the DAL code is aware? outside of passing the "key" to each factory method?
Here's the thing. You need to decide on some specific criteria by which the data access code selects the right connection string.
You have told us that this is per-object. Therefore the criteria must be a property of every object, or the DAL must be able to infer the criteria based on existing properties of the object.
For fetch operations, it sounds like you now always have a compound key that includes the actual key value, plus a Year value (or whatever this other criteria might be). I discuss compound keys in the Using CSLA 4 ebook series. The short version is that you can inherit from CriteriaBase to create a compound criteria class that you use to do fetch (and immediate delete) operations.
If all your objects have a common primary key type (like int or long or Guid) then you can create one compound criteria type and use it all over. If your objects have different primary key types, then you'll have to create different criteria types.
Copyright (c) Marimer LLC