Nhibernate - Getting Exception executing SQL Query

Question

I am executing a 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 receive 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 issue? ---- Thanks

Chris Marisic · Accepted Answer · 2010-04-20 13:44:40Z

You are fundamentally misunderstanding NHibernate. NHibernate is not like the TypeDataSource classes that return you DataSets/DataTables that aren't real business objects.

NHibernate is meant to work with fully owned objects so you would 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 a mapping file either manually or by code generation for raw HBM mappings or use a tool on top of NH to build mappings programmatically with FluentNHibernate or ConfORM.

You need to learn the basics of NHibernate before attempting to query this is a decent introductory post: http://www.fincher.org/tips/Languages/NHibernate.shtml

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

Maxim Eliseev · Accepted Answer · 2013-10-17 14:14:01Z

In most cases you should use entity objects instead of custom 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; }
    }

marc_s · Accepted Answer · 2016-02-06 16:52:56Z

0

The secret is to use:

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

In AddScalar you have to define your NH types for output.

See ref here

edited Feb 6, 2016 at 16:52

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 17, 2013 at 13:09

nicbua

1

Collectives™ on Stack Overflow

Nhibernate - Getting Exception executing SQL Query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related