使用NHibernate执行多个SQL语句 [英] Execute multiple SQL statements with NHibernate

问题描述

尝试在NHibernate中执行以下SQL语句时收到错误.它们在TOAD中可以正常工作.环境是Oracle，C#，.NET 4和NHibernate

I get an error when trying to execute the following SQL statements in NHibernate. They work fine in TOAD. The environment is Oracle, C#, .NET 4 and NHibernate

StringBuilder sb = new StringBuilder();
//some logic to select data from table1 in the where 
//clause of the following statement
sb.Append(" insert into temp_table select * from table1 where ....; ");     
sb.Append(" select t1.col1, t2.col2 from temp_table t1 join table2 t2 on t1.col1 = t2.col2 ");

IQuery query = Session.GetISession().CreateSQLQuery(sb.ToString()).SetResultTransformer(Transformers.AliasToBean(typeof(Class1)));

return query.List<Class1>();

我得到的错误.

ORA-00911: invalid character 
[GenericADOException: could not execute query....

如果我在toad中复制NHibernate生成的sql，则同样的查询有效.

If I copy the sql generated by NHibernate in toad the same query works.

推荐答案

AFAIK，您无法在nhibernate中做到这一点:

AFAIK you cannot do this in nhibernate:

您应该先进行插入:

Session.GetISession().CreateSQLQuery("insert ....").ExecuteUpdate();

，然后执行选择:

IQuery query = Session.GetISession().CreateSQLQuery("select ...".SetResultTransformer(Transformers.AliasToBean(typeof(Class1)));    
query.List<Class1>();

或者更好地尝试使用存储过程

or better try to use a storedprocedure

这篇关于使用NHibernate执行多个SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！