Nhibernate CreateSQLQuery存储过程结果到非映射类 [英] Nhibernate CreateSQLQuery Stored Procedure result to non mapped class

问题描述

在我的应用程序中，我想从未在应用程序中映射表的存储过程中获取数据.在此存储过程中，我为列名添加了别名，以响应类中的属性.

In my application I want to get data from a stored procedured where the table is not mapped in the application. In this stored procedure I added aliases to the column names which respond to the properties in my class.

HQL:

return Session.CreateSQLQuery("exec PER_PrikklokSaldi :IDPers :jaar :maand")
            .AddScalar("Description", NHibernateUtil.String)
            .AddScalar("StartSaldo", NHibernateUtil.Int32)
            .AddScalar("Plus", NHibernateUtil.Int32)
            .AddScalar("Minus", NHibernateUtil.Int32)
            .AddScalar("EndSaldo", NHibernateUtil.Int32)
            .SetParameter("IDPers", _employeeId)
            .SetParameter("jaar", _year)
            .SetParameter("maand", _month)
            .SetResultTransformer(new AliasToBeanResultTransformer(typeof(ClockInfoSaldi)))
            .List<ClockInfoSaldi>()
            .ToList();

班级:

public class ClockInfoSaldi
{
    public string Description { get; set; }
    public int StartSaldo { get; set; }
    public int Plus { get; set; }
    public int Minus { get; set; }
    public int EndSaldo { get; set; }
}

MS SQL Management Studio中的存储过程结果:

我可以运行:

var test = Session.CreateSQLQuery("exec PER_PrikklokSaldi :IDPers :jaar :maand")
            .SetParameter("IDPers", _employeeId)
            .SetParameter("jaar", _year)
            .SetParameter("maand", _month)

但是当我运行第一个提到的HQL代码时，出现此错误:{无法执行查询\ r \ n [exec PER_PrikklokSaldi @ p0 @ p1 @ p2] \ r \ n名称:IDPers-值:827名称: jaar-值:2014名称:maand-值:1 \ r \ n [SQL:exec PER_PrikklokSaldi @ p0 @ p1 @ p2]}

But when I run the first mentioned HQL code I get this error: {"could not execute query\r\n[ exec PER_PrikklokSaldi @p0 @p1 @p2 ]\r\n Name:IDPers - Value:827 Name:jaar - Value:2014 Name:maand - Value:1\r\n[SQL: exec PER_PrikklokSaldi @p0 @p1 @p2]"}

InnerException:{'@ p1'附近的语法不正确."}

InnerException: {"Incorrect syntax near '@p1'."}

推荐答案

答案被隐藏在例外中(并且大部分在此部分:'@ p1'附近的语法不正确."):

{无法执行查询\ r \ n [exec PER_PrikklokSaldi @ p0 @ p1 @ p2] \ r \ n名称:IDPers-值:827名称:jaar-值:2014名称: maand-值:1 \ r \ n [SQL:exec PER_PrikklokSaldi @ p0 @ p1 @ p2]}

{"could not execute query\r\n[ exec PER_PrikklokSaldi @p0 @p1 @p2 ]\r\n Name:IDPers - Value:827 Name:jaar - Value:2014 Name:maand - Value:1\r\n[SQL: exec PER_PrikklokSaldi @p0 @p1 @p2]"}

参数应以逗号分隔:

var test = Session.CreateSQLQuery("exec PER_PrikklokSaldi :IDPers, :jaar, :maand")
        .SetParameter("IDPers", _employeeId)
        .SetParameter("jaar", _year)
        .SetParameter("maand", _month)

或者也许是(对于sql server)

"exec PER_PrikklokSaldi @IDPers=:IDPers, @jaar=:jaar, @maand=:maand"

改为执行此操作

exec PER_PrikklokSaldi @ p0，@ p1，@ p2

exec PER_PrikklokSaldi @p0, @p1, @p2

这篇关于Nhibernate CreateSQLQuery存储过程结果到非映射类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！