auto-generating SQL, can NHibernate help?

RF · **Joined:** Fri May 30, 2008 3:57 pm **Posts:** 26

NHibernate 1.2.1
.Net 3.5

We've got a small app that requires some flexibilty in it's data and we're looking at autogenerating some SQL. Is there any way to piggy back off of NHibernate for this? The queries will all be relatively simple except for the requirement that they support sub-queries.

What I'd like to do is create a query using ICriteria or IQuery (preferrably ICriteria), have NHibernate auto-generate the SQL, and we pluck it out and store it elsewhere (the queries themselves will be write rarely, read mostly).

Or, if NHibernate exposes hooks for it's internal SQL generation mechanism, that'd be even better.

1. Is it possible to do sub-queries with ICriteria, and
2. How would I get at the SQL generated by NHibernate without having NHibernate actually query the database?

Thanks!

wolli · **Posted:** Wed Aug 13, 2008 10:50 am

Quote:

1. Is it possible to do sub-queries with ICriteria, and

Yes, with detached criterias:

Code:

crit.Add(Subqueries.Exists(detachedCriteria))

[url]
http://www.hibernate.org/hib_docs/nhibe ... hedqueries[/url]

Quote:

2. How would I get at the SQL generated by NHibernate without having NHibernate actually query the database?

I don't think you can get the actual statements. But you can try that question on the nhusers group at google (search that group before).

MutantNinjaLoungeSinger · **Joined:** Thu Jun 28, 2007 5:02 pm **Posts:** 12

Where x is an open ISession object...

string aSQLString = x.GetSessionImplementation().GetQueries(anHQLQueryString, false)[0].SQLString

GetQueries returns an array. The example above just shows the first raw SQL statement needed to perform the HQL query you've provided. (Depending on the HQL complexity, there might be more than one query performed by the translator)