Using native SQL

At some point in the development of your application, you may find that none of the NHibernate specific querying techniques really does the trick. Maybe you need to write a query with a very specific SQL, so that it performs optimally, or maybe there's a function or stored procedure that you need to use. Thankfully, NHibernate allows you to use plain SQL queries in the native dialect of your target database and dropping out to SQL doesn't mean dropping out of NHibernate.

Getting ready

Complete the Getting Ready section at the beginning of this chapter.

How to do it…

  1. Add a new folder named QueryBySql to the project.
  2. Add a new class named SqlQueries to the folder:
    using System.Collections.Generic;
using NH4CookbookHelpers.Queries;
using NH4CookbookHelpers.Queries.Model;
using NHibernate;

namespace QueryRecipes.QueryBySql
{
  public class SqlQueries : IQueries
  {
    private readonly ISession _session;

    public SqlQueries(ISession session)
    {
      _session = session;
    }

    public IEnumerable<Movie> GetMoviesDirectedBy(
      string directorName)
    {
      var sql = @"select * from Product  
        where ProductType = 'Movie' 
        and Director = :director";
      return _session.CreateSQLQuery(sql)
        .AddEntity(typeof(Movie))
        .SetString("director", directorName)
        .List<Movie>();
    }

    public IEnumerable<Movie> GetMoviesWith(
      string actorName)
    {
      var sql = @"select m.*
        from Product m
        inner join ActorRole as ar on ar.MovieId=m.Id
        where ar.Actor = :actorName";
      return _session.CreateSQLQuery(sql)
        .AddEntity(typeof(Movie))
        .SetString("actorName", actorName)
        .List<Movie>();
    }

    public Book GetBookByISBN(string isbn)
    {
      var sql = @"select b.* from Product b
        where b.ISBN = :isbn";
      return _session.CreateSQLQuery(sql)
        .AddEntity(typeof(Book))
        .SetString("isbn", isbn)
        .UniqueResult<Book>();
    }

    public IEnumerable<Product> GetProductsByPrice(
      decimal minPrice,
      decimal maxPrice)
    {
      var sql = @"select p.* from Product p
        where p.UnitPrice between :minPrice
        and :maxPrice
        order by p.UnitPrice asc";

      return _session.CreateSQLQuery(sql)
        .AddEntity(typeof(Product))
        .SetDecimal("minPrice", minPrice)
        .SetDecimal("maxPrice", maxPrice)
        .List<Product>();
    }

  }
}
  3. Add a new class named Recipe to the folder:
    using NH4CookbookHelpers;
using NHibernate;

namespace QueryRecipes.QueryBySql
{
  public class Recipe : QueryRecipe
  {
    protected override void Run(ISession session)
    {
      var queries=new SqlQueries(session);
      ShowQueryResults(queries);
    }
  }
}
  4. Run the application and start the QueryBySql recipe.

How it works…

We won't get into the details of the SQL queries.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.12.50