CODEWITHSUNDEEP
c#nhibernatefluent-nhibernate
Marc Rasmussen
Marc Rasmussen

Reputation: 20555

Nhibernate C# making XML files for every query

I am trying to learn how to use the power of Nhibernate and after watching a turtorial i am starting to geta good grip on it.

However there is something that is bothering me.

Take for instance the following query:

    var query = "SELECT * " +
            "from DAGE_I_KS WHERE DATO in (:orderYear));";

var session = mySessionFactory.OpenSession();
var result = session.CreateSQLQuery(query)
                    .AddEntity(typeof(DAGE_I_KS))
                    .SetString("orderYear", "2012")
                    .List<DAGE_I_KS>();

Now for this to work i have to do the following steps:

Now i have to do this for every single query i want to do. In a program you will most likely do 10+ query (if not more) which means that i have to do these steps 10 times.

For me it seems ilogical. And i am having a hard time understanding how this can save me some time and trouble.

My question is, is this correct that i have to do this every time or is there something ive missed and is there a better way to do use Nhibernate when you have costum querys.

Upvotes: 0

Views: 524

Answers (1)

h.alex
h.alex

Reputation: 902

Runtime NH revolves around the concept of a session (ISession). As you know, you get the session from the session factory.

The session is the central object through which you interact with NH to execute queries and save data. The session has a flush mode property.

It is important to know that all the entities that you get from the session by using a query are linked to that session. So, if you modify an entity which is fetched, even if you do not explicitly call session.Update(obj), when the session is flushed these changes will be persisted. Thus it follows that the only methods of the session which you actually must call are session.saveOrUpdate(obj) and session.delete(obj), because you must register newly created entities with the session, and you must register entities for deletion. When the session is flushed, these changes will be persisted.

Try to read about the session flush modes, and also the ITransaction interface.

Now, how is this related to queries?

Well, I have learned that it is best to encapsulate queries into query objects. Is not my idea, but something I picked up from the blog of Fabio Maulo, or Ayende not sure but searching for the term query object in conjuncture with NH should be informative, and if you find out who to credit with the idea I will update the answer :)

It is clear that the query object needs the session to work. So, you must write the plumbing to give it one. In my implementation, a query object would be simply:

public class ShiftByNameWithOccurences : AbstractQueryObject<IList<Shift>>
{
    private string Name;

    public ShiftByNameWithOccurences(string name)
    {
        this.Name = name;
    }

    public override IList<Shift> GetResult()
    {
        var list =
        (
            from shift in this.Session.Query<Shift>()
            where shift.Name == this.Name
            select shift

        )
        .Fetch(p => p.Occurrences)
        .ToList();

        return list;
    }
}

Obviously, it is easy to implement a generic by name query for all your db entities. In this example, the occurrences of the shift are eagerly loaded, to avoid the N+1 select problem.

It is easy to see how having encapsulated queries can benefit your OO driven app design.

Also, since you have the ISession instance, you can use what ever query method of NH that you choose.

For reference:

public abstract class AbstractQueryObject<TResult> : IQueryObject<TResult>
{
    protected ISession Session;

    public void Configure(object parameter)
    {
        if (!(parameter is ISession))
            throw new ArgumentException(String.Format("Argument of wrong type. Expecting {0} but got {1}.", typeof(ISession), parameter != null ? parameter.GetType().ToString() : "null"), "parameter");

        this.Session = parameter as ISession;
    }

    public abstract TResult GetResult();
}

public interface IQueryObject<TResult>
{
    void Configure(object parameter);

    TResult GetResult();
}

Even, this interface is imagined to not be linked to NH, but also so that you can use it with EF, or some other future ORM if needed. The abstract query object is in the x.NH namespace :)

Edit:

For the task of logging SQL of each query I can suggest to use interceptors to log sql.

Register a per session interceptor when you create a session. For this task you should register only one interceptor object with the session. Then, when you instantiate the query object pass in the interceptor object as a constructor argument, or in the configure method whichever you like. When the query is being executed (in get result method) - at the start tell to the interceptor object that you want to start listening. In the implementation of your interceptor create code which will then forward all the sql to the listening query object. In the query object create a custom xml using the sql. When the query is finished, unregister the query object from the interceptor object.

But keep in mind that if additional lazy loading sql statements are executed after the query object exits the get result method will not be logged.

E.g.:

public class UseCase
{
    public void Method()
    {
        //when instantiating a session pass the interceptor to it.
        //then, also pass this sniffer to the query objects you create.
        //make the query objects listeners.
        //when the query object is to be executed (start of the get result method)
        //call the set active listener method on the sniff notifier given to the q.o.
        //in the on prepare statement method of the q.o. do whatever with the sql.
        SqlSniffer myInterceptor = new SqlSniffer();

        var session = this.SessionFactory.OpenSession(myInterceptor);
    }
}


public interface ISqlSniffListener
{
    void OnPrepareStatement(string sql);
}

public interface ISqlSniffNotifier
{
    void SetActiveListener(ISqlSniffListener listener);
}

public class SqlSniffer : EmptyInterceptor, ISqlSniffNotifier
{
    private ISqlSniffListener ActiveListener;

    public void SetActiveListener(ISqlSniffListener listener)
    {
        this.ActiveListener = listener;
    }

    public override NHibernate.SqlCommand.SqlString OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
    {
        if (this.ActiveListener != null)
            this.ActiveListener.OnPrepareStatement(sql.ToString());

        return base.OnPrepareStatement(sql);
    }
}

Or even better, it is probably better to implement a listener which is not integrated in the query object (to honor SRP) .. but you would have to signal to it which query is being executed then...which can be easy if you had an object responsible for executing the query objects, which provides them the session, and also possibly creates it ;)

P.S. It was Ayende who I read discussing the q.o., but the idea seems older.

Upvotes: 1

Related Questions