Nhibernate - getting an exception from SQL Query

Question

I am executing an SQL query using Nhibernate, below is the code in which I use for this:

public ArrayList getDocumentsForApproval(string ReleaseId) { string query = string.Format("SELECT distinct doc.Id, doc.Name as Doc, doc.url as url, suser.Name as Author, ds.name, CONVERT(VARCHAR(11), doc.DateEntered, 101) as DateEntered FROM dbo.Documents doc INNER JOIN DevelopmentSteps ds ON doc.TypeId = ds.Id INNER JOIN DocumentTrackingItems dti ON doc.Id = dti.DocumentId INNER JOIN TrackingItems ti ON dti.ItemStepId = ti.Id INNER JOIN dbo.Releases rl ON ti.ReleaseId = rl.BugTrackerName left outer join (select * from users) as suser on doc.AuthorUserid = suser.Id WHERE doc.DateEntered IS NOT NULL AND doc.DateApproved IS NULL AND rl.ID = '{0}'", ReleaseId); ISession session = NHibernateHelper.GetCurrentSession(); ArrayList document =(ArrayList) session.CreateSQLQuery(query).List(); return document; } 

The error information I get is as follows:

 **Exception Details:** NHibernate.QueryException: Return types of SQL query were not specified [SELECT distinct doc.Id, doc.Name as Doc, doc.url as url, suser.Name as Author, ds.name, CONVERT(VARCHAR(11), doc.DateEntered, 101) 

What could be the problem? ---- Thanks

Answer 1

You fundamentally misunderstand NHibernate. NHibernate is not like TypeDataSource classes that return DataSets / DataTables that are not real business objects.

NHibernate is designed to work with fully owned objects, so you will have something similar to

 Public Class Document { public virtual decimal Id { get; set; } public virtual string Name { get; set; } public virtual DateTime DateEntered { get; set; } ... so forth } 

Then you need to create the mapping file either manually or by generating code for raw HBM mappings, or use the tool on top of NH to programmatically develop programs with FluentNHibernate or ConfORM.

You need to learn the basics of NHibernate before trying to request this decent introductory post: http://www.fincher.org/tips/Languages/NHibernate.shtml

And then for the query, you can use http://www.castleproject.org/ActiveRecord/documentation/v1rc1/usersguide/hql.html for reference.

Answer 2

The secret is to use:

 CreateSQLQuery("Your query with alias").AddScalar(...) 

In AddScalar you must define your NH types for output.

See link here

Answer 3

In most cases, you should use object objects instead of user queries. If you really need a custom query, the following example might be useful

  public IEnumerable<GeoAreaIdAndCode> ReadAllGssCodes() { var query = "select GeoAreaID,Code from GeoAreaAlternativeCode where AlternativeCodeType=" + (int)GeoAreaAlternativeCodeType.GssCode; var result = Owner.Session.CreateSQLQuery(query) .AddScalar("GeoAreaID",NHibernateUtil.Int32) .AddScalar("Code",NHibernateUtil.String) .SetResultTransformer(Transformers.AliasToBean(typeof (GeoAreaIdAndCode))) .List<GeoAreaIdAndCode>(); return result; } public class GeoAreaIdAndCode { public int GeoAreaID { get; set; } public string Code { get; set; } }