CODEWITHSUNDEEP
c#nhibernate
Luke101
Luke101

Reputation: 65238

How to use NHibernate with several databases and tables

I am a newbie at NHibernate and I apologize if this is a simple question. Basically, I have one database that contains access to other databases. Here is the layout of my base database. This is a table that contains the database info of other databases

--Databases--
Id
DatabaseName
DatabaseType
ConnectionString

As you can see this table contains all the databases that is maintained. Now here is a table that contains all the columns that is linked to the databases.

--Tables--
Id
Name
ColumnNames
DatabaseId

I have many different types of databases that include (Oracle, mysql, mssql, or postgresql). They all play different roles in my application. It will be very time consuming to create the mapping xml files for each table in each database.

I would like to pass nhibernate the connection string and execute a simple query against the table. Here is an example of a query.

select * from table90182763 where id IS not NULL and id <> 0

Can I pass this SQL query straight to NHibernate? Can I efficiently pass the connection string to nhibernate on every request?

Upvotes: 1

Views: 786

Answers (1)

David C
David C

Reputation: 3810

You can use session.CreateSQLQuery(), but it has a drawback in your scenario.

In order to use CreateSQLQuery, you will need to already have a session manager for each database, mapping files and classes for the domain entities to be built from the results, etc...

NHibernate maps to entities so if you want to write raw SQL and get a datatable of results back, just use standard ODBC drivers and do things the old way.

NHibernate makes development and maintenance quicker and easier, and should be used correctly if you will want to maintain and add new functionality quickly in the future.

As the mighty Craig Quillen says 'If it's legitimate infrastructure and it's going to save you time and sleepless nights down the road, it's a good ROI'.

You can set up NHibernate to access any number of databases, with any number of database engine types. You can also abstract your database session using an IoC Container to make it nearly transparent.

You should use Code Configuration (example here) to create several Session Factories, each with it's own ConnectionString, Dialect, and Driver. These would be created as Static or 'On Demand IoC' Singletons. Then depending upon which Entity you need to load, your IoC factory could request a session from the correct NHibernate Session Factory.

In order to generate mapping files and entities in an automated fashion directly from the database tables, use a tool similar to : http://nmg.codeplex.com/

Upvotes: 2

Related Questions