Update method without HQL mode in NHibernate

0
votes

I would like to make an Update method in NHibernate with restrictions (where clause), but I don't want to do HQL mode.

Example, my SQL query:

"update NFE set SEFAZ_STATUS_DATA=@SEFAZ_STATUS_DATA,MENSAGEM_ERRO=@MENSAGEM_ERRO"

How I can do without use HQL?

This works?

public virtual T Alterar(List<ICriterion> lstCriterios, T entity)
{
    try
    {
        using (ISession session = SessionFactory.OpenSession())
        {
            ICriteria criterio = session.CreateCriteria(typeof(T));

            foreach (ICriterion cri in lstCriterios)
            {
                criterio.Add(cri);
            }

            session.Update(entity);

            return entity;
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}
1
c#nhibernatefluent-nhibernatehql

1 Answers

1
votes

In general, there are two ways how to execute Update. One is session based, the second is represented by DML.

9.4.1. Updating in the same ISession

Transactional persistent instances (ie. objects loaded, saved, created or queried by the ISession) may be manipulated by the application and any changes to persistent state will be persisted when the ISession is flushed (discussed later in this chapter). So the most straightforward way to update the state of an object is to Load() it, and then manipulate it directly, while the ISession is open...

So, in our case, we can use any kind of query to load entities (Criteria, QueryOver, Query) into session. This means, no HQL is needed.

All objects will be then updated on a server/application tier (C#) and then once session is flushed.. all changes will be persisted.

This approach requires lot of background handling (create entities from SQL selects... create UPDATE statements...) but is very native for NHiberante/ORM

using (ISession session = SessionFactory.OpenSession())
{
    var criteria = session.CreateCriteria<T>();

    // I. use criteria to find what should be changed    
    foreach (var cri in lstCriterios)
    {
        criterio.Add(cri);
    }

    // II. load the searched
    var list = session.List<T>();

    // III. update entities in runtime
    foreach(var entity in list)
    {
       entity.Property1 = newValue1;
       entity.Property2 = newValue2;
       ...
    }

    // IV. UPDATE == flush
    session.Flush();
}

13.3. DML-style operations

As already discussed, automatic and transparent object/relational mapping is concerned with the management of object state. This implies that the object state is available in memory, hence manipulating (using the SQL Data Manipulation Language (DML) statements: INSERT, UPDATE, DELETE) data directly in the database will not affect in-memory state. However, NHibernate provides methods for bulk SQL-style DML statement execution which are performed through the Hibernate Query Language (HQL)...

In this case, we do have access to the most effective UPDATE - executed directly on DB server, without any entity loading. See example:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction();

string hqlUpdate = "update Customer c set c.name = :newName where c.name = :oldName";
// or string hqlUpdate = "update Customer set name = :newName where name = :oldName";
int updatedEntities = s.CreateQuery( hqlUpdate )
        .SetString( "newName", newName )
        .SetString( "oldName", oldName )
        .ExecuteUpdate();
tx.Commit();
session.Close();

While this approach is giving us great performance, it is based on HQL (opposite to above requirement)...

SUMMARY: With NHibernate we can use powerful querying without HQL to load data into session, manipulate them in C#, persist changes one by one... Or we can use HQL to create a DML statement, and execute on UPDATE directly on the server

See also: NHibernate – Executable DML by Ayende