How can i get NHibernate to give me the SQL it wou

Question

I'm using NHibernate to map some very simple entities to three tables.

My program needs to have a mode where it outputs the SQL it would execute to insert or update these tables, so that the SQL can be saved to a script file, to be later executed manually.

This is similar to this question (which has a very nice answer): How can I have NHibernate only generate the SQL without executing it?

Only that question is about SELECT, I need kind of the same but for INSERT / UPDATE. This also needs to be dynamic because it depends on a user option. I mean, when my program does:

        session.Save(entity);

sometimes I need that to hit the db and sometimes I need it to output the SQL it would execute.

Is this at all possible?

Answer 1

This is not possible unfortunately.

Answer 2

If you want hard to do this take a look in the code below.

public IDbCommand CreateInsertCommand(object entity)
{
    var entityTuplizer = GetTuplizer(_sessionMetadata.SessionImplementor);
    var values = entityTuplizer.GetPropertyValuesToInsert(entity, new Dictionary<object, object>(), _sessionMetadata.SessionImplementor);

    var notNull = GetPropertiesToInsert(values);
    var sql = GenerateInsertString(true, notNull);
    var insertCommand = _sessionMetadata.Batcher.Generate(sql.CommandType, sql.Text, sql.ParameterTypes);

    Dehydrate(null, values, notNull, _propertyColumnInsertable, 0, insertCommand, _sessionMetadata.SessionImplementor);
    InfraUtil.FixupGuessedType(insertCommand);

    return insertCommand;
}

More code to guide you to a working solution

public class SessionMetadata
{
    private static readonly Type KeyType = typeof(ActiveRecordBase);

    private ISessionFactoryHolder _sessionFactoryHolder;
    private ISessionFactory _sessionFactory;
    private ISessionFactoryImplementor _sessionFactoryImplementor;
    private Configuration _configuration;
    private ISession _currentSession;
    private ISessionImplementor _sessionImplementor;
    private DriverBase _driver;
    private NonBatchingBatcher _batcher;
    private IMapping _mapping;

    public ISessionFactoryHolder SessionFactoryHolder
    {
        get { return _sessionFactoryHolder ?? (_sessionFactoryHolder = ActiveRecordMediator.GetSessionFactoryHolder()); }
    }

    public ISessionFactory SessionFactory
    {
        get { return _sessionFactory ?? (_sessionFactory = SessionFactoryHolder.GetSessionFactory(KeyType)); }
    }

    public ISessionFactoryImplementor SessionFactoryImplementor
    {
        get { return _sessionFactoryImplementor ?? (_sessionFactoryImplementor = (ISessionFactoryImplementor)SessionFactory); }
    }

    public DriverBase Driver
    {
        get { return _driver ?? (_driver = (DriverBase)SessionFactoryImplementor.ConnectionProvider.Driver); }
    }

    public NonBatchingBatcher Batcher
    {
        get { return _batcher ?? (_batcher = (NonBatchingBatcher)SessionImplementor.Batcher); }
    }

    public Configuration Configuration
    {
        get { return _configuration ?? (_configuration = SessionFactoryHolder.GetConfiguration(KeyType)); }
    }

    public ISession CurrentSession
    {
        get { return _currentSession ?? (_currentSession = SessionScope.Current.GetSession(SessionFactory)); }
    }

    public ISessionImplementor SessionImplementor
    {
        get { return _sessionImplementor ?? (_sessionImplementor = CurrentSession.GetSessionImplementation()); }
    }

    public IMapping Mapping
    {
        get { return _mapping ?? (_mapping = SessionFactoryImplementor); }
    }
}

public class EntityMetadata
{
    private readonly SessionMetadata _sessionMetadata;
    private readonly Type _entityType;
    private PersistentClass _persistentClass;
    private IEntityPersister _entityPersister;

    public EntityMetadata(Type entityType, SessionMetadata sessionMetadata)
    {
        _sessionMetadata = sessionMetadata;
        _entityType = entityType;
    }

    public SessionMetadata SessionMetadata
    {
        get { return _sessionMetadata; }
    }

    public PersistentClass PersistentClass
    {
        get { return _persistentClass ?? (_persistentClass = SessionMetadata.Configuration.GetClassMapping(_entityType)); }
    }

    public IEntityPersister EntityPersister
    {
        get { return _entityPersister ?? (_entityPersister = SessionMetadata.SessionFactoryImplementor.GetEntityPersister(PersistentClass.EntityName)); }
    }
}

public class ExtractSql: SingleTableEntityPersister
{
    private readonly SessionMetadata _sessionMetadata;
    private readonly EntityMetadata _entityMetadata;
    private readonly IEntityPersister _entityPersister;
    private readonly bool[][] _propertyColumnInsertable;
    private readonly bool[][] _propertyColumnUpdateable;

    public ExtractSql(EntityMetadata entityMetadata)
        : base(entityMetadata.PersistentClass, null, entityMetadata.SessionMetadata.SessionFactoryImplementor, entityMetadata.SessionMetadata.Mapping)
    {
        if (entityMetadata == null) throw new ArgumentNullException("entityMetadata");
        _sessionMetadata = entityMetadata.SessionMetadata;
        _entityMetadata = entityMetadata;

        _entityPersister = _entityMetadata.EntityPersister;

        var hydrateSpan = _entityPersister.EntityMetamodel.PropertySpan;
        _propertyColumnUpdateable = new bool[hydrateSpan][];
        _propertyColumnInsertable = new bool[hydrateSpan][];

        var i = 0;
        foreach (var prop in _entityMetadata.PersistentClass.PropertyClosureIterator)
        {
            _propertyColumnInsertable[i] = prop.Value.ColumnInsertability;
            _propertyColumnUpdateable[i] = prop.Value.ColumnUpdateability;

            i++;
        }
    }

    protected override bool UseDynamicUpdate
    {
        get { return true; }
    }

    protected override bool UseDynamicInsert
    {
        get { return true; }
    }

    public IDbCommand CreateInsertCommand(object entity)
    {
        var entityTuplizer = GetTuplizer(_sessionMetadata.SessionImplementor);
        var values = entityTuplizer.GetPropertyValuesToInsert(entity, new Dictionary<object, object>(), _sessionMetadata.SessionImplementor);

        var notNull = GetPropertiesToInsert(values);
        var sql = GenerateInsertString(true, notNull);
        var insertCommand = _sessionMetadata.Batcher.Generate(sql.CommandType, sql.Text, sql.ParameterTypes);

        Dehydrate(null, values, notNull, _propertyColumnInsertable, 0, insertCommand, _sessionMetadata.SessionImplementor);
        InfraUtil.FixupGuessedType(insertCommand);

        return insertCommand;
    }
}

Answer 3

It's not possible as that's the main purpose of having ORM like NHibernate but for testing purposes if you want to check the queried generated by Nhibernate then use this logger in app.config , <logger name="NHibernate.SQL"> <level value="ALL" /> <appender-ref ref="NHibernateRollingLogFileAppender" /> </logger>

In then, when you stary your project it should generate logfile_nhibernate.txt and you will see all the generated queries.