习惯原生SQL的同学刚搞NH可能觉得坐点复杂的查询NH就无能为力了,其实不然,NH提供了使用原生SQL的功能,就是有些地方要注意下。
原生的SQL如图,查询每天的售票情况。。下面我们来看看NH里怎么写
public IList<object[]> GetDaysOrderTotal() { string timeUnit = "100"; string sql = "select CONVERT(VARCHAR(" + timeUnit + "), od.BuyTime, 102) timeUnit,count(*) count " + "from TicketAssign ta, OrderDetail detail,[Order] od ,DJ_TourEnterprise dj,TicketPrice tp,Ticket t "+ "where ta.OrderDetail_id =detail.Id and detail.Order_id=od.Id "+ "and detail.TicketPrice_id=tp.Id and tp.Ticket_id=t.Id and t.Scenic_id=dj.Id group by CONVERT(VARCHAR(" + timeUnit + "), od.BuyTime, 102)"; var query = session.CreateSQLQuery(sql) .AddScalar("count",NHibernateUtil.Int32) .AddScalar("timeUnit",NHibernateUtil.String); return query.List<object[]>(); }
同时,前台绑定object也要这样
<asp:Repeater runat="server" ID="rptdate"> <HeaderTemplate> <table> </HeaderTemplate> <ItemTemplate> <tr> <td> <a href='/manager/quzhouspring/TicketStatistics3.aspx?date=<%# ((object[])Container.DataItem)[1].ToString() %>'> <%# ((object[])Container.DataItem)[1].ToString() %></a> </td> <td> <%# ((object[])Container.DataItem)[0].ToString() %> </td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater>