1

Sorry if this seems a little crazy but ive been messing around with NHibernate for a while and have come accross a scenario that may not be possible to solve with NHibernate...

I have 1 database that contains a load of static data, imagine it like a huge product lookup database, just to point out this is an example scenario, my actual one is a bit more complex but similar principle to this... It is hosted on a completely different box so i cant do "database2.table1.somecolumn" which i noticed as a possible way round the issue if the 2 DBs were in the same box and server.

Anyway i also have another DB which contains data relating to users, so imagine a user has bought a load of stuff from Generic Website A, you have a list of IDs pertaining to what they have bought and an amount of how many they bought as well as some other information, but the actual data relating to the product is stored in the other database...

So if you imagine you want to combine this data into a PreviousPurchasedProduct model which contained all the information from the 1st database and the additional data from the 2nd db you would have to do a query similar to this: (if they were all on one box)

SELECT db1.products., db2.purchases. FROM db2.purchases INNER JOIN db1.products ON db2.purchases.product_id = db1.products.id WHERE db2.purchases.user_id = XXX;

Now first of all is it possible to map this sort of thing up even though they are in seperate DB hosts, im guessing not and if thats the case can you achieve this flexibility via a child class. So having a product class that purely works off db1 and a derived class that takes the purchases info that only works from db2.

Also is it possible to restrict the db1 portion of data from INSERT/UPDATE/DELETE statements, im pretty sure you can in the default mappings but as this would be outside of the per class scope im not sure what flexibility i have...

Thanks for reading my waffle :D

Improve this question
2
  • Sorry, i thought i had already implied the only way round it was to make 2 seperate DB connections and run 2 seperate SQL statements... Currently i am using MSEnterprise Library DB connectors to hook up to DB1 and DB2 then i would first run something along the lines of: SELECT * FROM purchases WHERE user_id = ?; Then i would partially fill out the object with the data returned from that, while also building up an ID list of products. SELECT * FROM products WHERE id IN (?); Then i would loop round them applying the data to the relevent object, hardly brilliant but it is not run often. Commented Oct 14, 2009 at 10:48
  • As far as the DB providers go its abstracted so it could be anything, its safe to presume MSSQL or MySQL. Currently its all hooked up to use stored procedures but im thinking of steering away from it. The reason the DBs are split along multiple hosts is because one of them gets hit ALOT more than the other, and there can be multiple instances of the purchases DB, while there would only be one instance of the products DB. So as there are multiple instances off the server app running each one has its own purchases DB, but all share the same product DB as its only on initial login. Commented Oct 14, 2009 at 10:53

1 Answer 1

Reset to default
1

I would recommend you first understand how to solve this problem without NHibernate. Once you have one or more clean solutions that work without NHibernate, come back and update your question to say something like "how do I represent this SQL in NHibernate?".

Querying across databases can quickly bring database vendor specific quirks into play and you never mention which database vendor(s) you are dealing with. If both databases are the same vendor, you may be able to link the databases somehow using database vendor specific techniques (but linking isn't necessarily a good solution, so you'll want to try to uncover alternatives as well).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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