Using SQL string in Fluent NHibernate

Sample code only shows how to execute a simple SQL string using Fluent NHibernate. Basically the method here will also work with Hibernate except that mapping will be on a .xml file.

First we have to create a the Entity and mapping class.

/// <summary>
/// 
/// </summary>
public class Planet
{
    private int _Id;
    /// <summary>
    /// 
    /// </summary>
    public virtual int Id
    {
        get { return _Id; }
        set { _Id = value; }
    }
    private string _Name;
    /// <summary>
    /// 
    /// </summary>
    public virtual string Name
    {
        get { return _Name; }
        set { _Name = value; }
    }
}

/// <summary>
/// 
/// </summary>
public class PlanetMap : ClassMap<Planet>
{
    /// <summary>
    /// 
    /// </summary>
    public PlanetMap()
    {
        Id(x => x.Id).Column("id");
        Map(x => x.Name).Column("name");
    }
}

Now lets go to the actual method.

/// <summary>
/// 
/// </summary>
/// <param name="args"></param>
public static void Main(string[] args)
{
    ISessionFactory sessionFactory = Fluently
                .Configure()
                .Database(MySQLConfiguration.Standard.ConnectionString(GetConnectionString()))
                .Mappings(m => m.FluentMappings.AddFromAssemblyOf<Program>())
                .BuildSessionFactory();

    using (ISession session = sessionFactory.OpenSession())
    {
        // Executing sql string query
        ISQLQuery query = session.CreateSQLQuery("SELECT id, name FROM planet");

        // Getting result in a form of nested arrays
        Collection<Planet> planets = new Collection<Planet>();
        IList result = query.List();
        foreach(object[] item in result)
        {
            planets.Add(new Planet() { Id = (int) item[0], Name = (string) item[1] });
        }
    }

    Console.WriteLine("Press enter key to exit.");
    Console.ReadLine();
}

From the example above we used the ISQLQuery object to define and execute the query, please note that the result is in the form of a nested array of objects.

Advertisement