01-04-01【Nhibernate (版本3.3.1.4000) 出入江湖】原生的SQL查询

Nhibernate 支持原生的SQL查询 ：

        /// <summary>
        /// 使用原生的SQL查询
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public IList<Customer> GetCustomersByDateTimeUsingSql(string datetime)
        {
            IList<Customer> resuCustomers = null;

            ISession session = _sessionManager.GetSession();
            ITransaction transaction = session.BeginTransaction();

            ;
            try
            {
                //方式一：直接拼装SQL语句
                //String sql = String.Format("select distinct CustomerAlias.* " +
                //                           " from Customer CustomerAlias inner join [Order] " +
                //                           " on CustomerAlias.CustomerId = [Order].CustomerId" +
                //                           " where [Order].OrderDate >='{0}';", datetime);
                ////用原生的SQL语句查询时，用AddEntity拼装实体类
                //resuCustomers = session.CreateSQLQuery(sql).AddEntity("CustomerAlias", typeof(Customer)).List<Customer>();


                //方式二：使用查询参数
                String sqlusingParameter = String.Format("select distinct CustomerAlias.* " +
                                                         " from Customer CustomerAlias inner join [Order] " +
                                                         " on CustomerAlias.CustomerId = [Order].CustomerId" +
                                                         " where [Order].OrderDate >=:paraDatetime And CustomerAlias.Age=:paraAge;");
                //用原生的SQL语句查询时，用AddEntity拼装实体类
                resuCustomers = session.CreateSQLQuery(sqlusingParameter).AddEntity("CustomerAlias", typeof(Customer))
                    .SetString("paraDatetime", datetime)
                    .SetInt32("paraAge", 10)
                    .List<Customer>();

                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw;
            }
            finally
            {
                //如果已经打开了支持延迟，当外部用到延迟加载属性，
                //session早已关闭，所以会抛出异常。
                session.Close();
            }

            return resuCustomers;
        }