01-04-01【Nhibernate (版本3.3.1.4000) 出入江湖】原生的SQL查询
Nhibernate 支持原生的SQL查询 ：
1 /// <summary> 2 /// 使用原生的SQL查询 3 /// </summary> 4 /// <param name="datetime"></param> 5 /// <returns></returns> 6 public IList<Customer> GetCustomersByDateTimeUsingSql(string datetime) 7 { 8 IList<Customer> resuCustomers = null; 9 10 ISession session = _sessionManager.GetSession(); 11 ITransaction transaction = session.BeginTransaction(); 12 13 ; 14 try 15 { 16 //方式一：直接拼装SQL语句 17 //String sql = String.Format("select distinct CustomerAlias.* " + 18 // " from Customer CustomerAlias inner join [Order] " + 19 // " on CustomerAlias.CustomerId = [Order].CustomerId" + 20 // " where [Order].OrderDate >='{0}';", datetime); 21 ////用原生的SQL语句查询时，用AddEntity拼装实体类 22 //resuCustomers = session.CreateSQLQuery(sql).AddEntity("CustomerAlias", typeof(Customer)).List<Customer>(); 23 24 25 //方式二：使用查询参数 26 String sqlusingParameter = String.Format("select distinct CustomerAlias.* " + 27 " from Customer CustomerAlias inner join [Order] " + 28 " on CustomerAlias.CustomerId = [Order].CustomerId" + 29 " where [Order].OrderDate >=:paraDatetime And CustomerAlias.Age=:paraAge;"); 30 //用原生的SQL语句查询时，用AddEntity拼装实体类 31 resuCustomers = session.CreateSQLQuery(sqlusingParameter).AddEntity("CustomerAlias", typeof(Customer)) 32 .SetString("paraDatetime", datetime) 33 .SetInt32("paraAge", 10) 34 .List<Customer>(); 35 36 transaction.Commit(); 37 } 38 catch (Exception) 39 { 40 transaction.Rollback(); 41 throw; 42 } 43 finally 44 { 45 //如果已经打开了支持延迟，当外部用到延迟加载属性， 46 //session早已关闭，所以会抛出异常。 47 session.Close(); 48 } 49 50 return resuCustomers; 51 }
