ORM Nhibernet 框架的 CRUD 操作

 private ISession _session;
        public ISession Session
        {
            set
            {
                _session = value;
            }
        }
        public QueryCriteriaAPI(ISession session)
        {
            _session = session;
        }


        #region 语法学习
        /// <summary>
        /// 创建ICriteria实例
        /// </summary>
        /// <returns></returns>
        public IList<Customer> CreateCriteria()
        {
            //NHibernate.ICriteria这个接口代表对一个特定的持久化类的查询。
            //ISession是用来制造Criteria实例的工厂。
            var crit = _session.CreateCriteria(typeof(Customer));
            crit.SetMaxResults(50);
            var customers = crit.List<Customer>();
            return customers;
        }
        /// <summary>
        /// 缩小结果集范围
        /// </summary>
        /// <returns></returns>
        public IList<Customer> Narrowing()
        {
            //一个查询条件(Criterion)是NHibernate.ICriterion接口的一个实例。
            //类NHibernate.Expression.Expression定义了获得一些内置的ICriterion类型的工厂方法。

            var customers = _session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Like("Firstname", "YJing%"))
                .Add(Restrictions.Between("Lastname", "A%", "Y%"))
                .List<Customer>();

            //表达式（Expressions）可以按照逻辑分组
            //IList<Customer> customers = _session.CreateCriteria(typeof(Customer))
            //    .Add(Restrictions.Like("Firstname", "YJ%"))
            //    .Add(Restrictions.Or(
            //    Restrictions.Eq("Lastname", "L%"),
            //    Restrictions.IsNull("Lastname")
            //    ))
            //    .List<Customer>();

            //IList<Customer> customers = _session.CreateCriteria(typeof(Customer))
            //    .Add(Restrictions.In("Firstname", new string[] { "YJing", "YJingLee", "Y" }))
            //    .Add(Restrictions.Disjunction()
            //    .Add(Restrictions.IsNull("Lastname"))
            //    .Add(Restrictions.Eq("Lastname", "Lee"))
            //    .Add(Restrictions.Eq("Lastname", "xyz"))
            //    ).List<Customer>();

            //预制的条件类型（Expression的子类）。可以直接嵌入SQL。
            //{alias}是一个占位符，它将会被所查询实体的行别名所替代
            //Parameter paramName = new Parameter("someName", new StringSqlType());
            //IList<Customer> customers = _session.CreateCriteria(typeof(Customer))
            //    .Add(Expression.Sql(
            //      new SqlString(new object[]{
            //          "lower({alias}.Lastname) like lower(","Lastname",")"}), "YJing%", NHibernateUtil.String))
            //          .List<Customer>();
            return customers;
        }
        /// <summary>
        /// 排序Order by
        /// </summary>
        /// <returns></returns>
        public IList<Customer> Order()
        {
            //使用ICriteria.Order对结果集排序,true=asc,false=desc
            return _session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Like("Firstname", "Y%"))
                .AddOrder(new NHibernate.Criterion.Order("Lastname", true))
                .AddOrder(new NHibernate.Criterion.Order("Firstname", false))
                .List<Customer>();
        }
        #endregion

        #region 实例学习
        /// <summary>
        /// 利用CriteriaAPI按Firstname查询顾客
        /// </summary>
        /// <param name="firstname"></param>
        /// <returns>顾客列表</returns>
        public IList<Customer> UseCriteriaAPI_GetCustomersByFirstname(string firstname)
        {
            //NHibernate1.2写法
            //return _session.CreateCriteria(typeof(Customer))
            //    .Add(new NHibernate.Expression.EqExpression("Firstname", firstname))
            //    .List<Customer>();

            //NHibernate2.0写法
            //return _session.CreateCriteria(typeof(Customer))
            //    .Add(Expression.Eq("Firstname", firstname))
            //    .List<Customer>();
            //使用Name封装：.Add(Restrictions.Eq("Name.Firstname", firstname))
            return _session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Eq("Firstname", firstname))
                .List<Customer>();
        }
        /// <summary>
        /// 利用CriteriaAPI按Firstname和Lastname查询顾客
        /// </summary>
        /// <param name="firstname"></param>
        /// <param name="lastname"></param>
        /// <returns></returns>
        public IList<Customer> UseCriteriaAPI_GetCustomersByFirstnameAndLastname(string firstname, string lastname)
        {
            //NHibernate1.2写法
            //return _session.CreateCriteria(typeof(Customer))
            //    .Add(new NHibernate.Expression.EqExpression("Firstname", firstname))
            //    .Add(new NHibernate.Expression.EqExpression("Lastname", lastname))
            //    .List<Customer>();

            //NHibernate2.0写法
            //使用Name封装：.Add(Restrictions.Eq("Firstname", firstname))
            return _session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Eq("Firstname", firstname))
                .Add(Restrictions.Eq("Lastname", lastname))
                .List<Customer>();
        }
        /// <summary>
        /// 利用CriteriaAPI获取顾客ID大于CustomerId的顾客
        /// </summary>
        /// <param name="customerId">顾客ID</param>
        /// <returns>顾客列表</returns>
        public IList<Customer> UseCriteriaAPI_GetCutomersWithIdGreaterThan(int customerId)
        {
            //NHibernate1.2写法
            //return _session.CreateCriteria(typeof(Customer))
            //    .Add(new NHibernate.Expression.GtExpression("CustomerId", customerId))
            //    .List<Customer>();

            //NHibernate2.0写法
            return _session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Gt("CustomerId", customerId))
                .List<Customer>();
        }
        #endregion


        #region 事务
        public IList<Customer> GetAll()
        {
            // 开启事物
            using (ITransaction tx = _session.BeginTransaction())
            {
                try
                {
                    //提交事务
                    tx.Commit();
                    return null;
                }
                catch (HibernateException ex)
                {
                　　// 回滚事务
                    tx.Rollback();
                   throw ex ;
                }
            }
        }


        #endregion

        #region  添加、 删除、更新对象

        public int Add(Customer model)
        {
         int id =(int) _session.Save(model);
         _session.Flush();
         return id;
        }

        public void Delete ( Customer model)
        {
             _session.Delete(model);
             _session.Flush();
        }


        public void Update( Customer model)
        {
          // 更新
           _session.Update(model);

           // 更新或保存 •检查这个对象是否已经存在Session中。如果对象不在，调用Save(object)来保存。如果对象存在，检查这个对象是否改变了。 如果对象改变，调用Update(object)来更新。

           //_session.SaveOrUpdate(model);
           _session.Flush();
        }

        #endregion


        #region 条件查询 (Criteria Query)

        // 创建 Criteria 实例 使用ISession接口的CreateCriteria方法创建了NHibernate.ICriteria接口一个特定的持久化类的查询实例，也可以说ISession是用来制造Criteria实例的工厂
        public IList<Customer> Creater()
        {  
           ICriteria cria = _session.CreateCriteria(typeof(Customer));
           cria.SetMaxResults(50);
           IList<Customer> list = cria.List<Customer>();
           return list;     
        }


        // 结果集限制数据 
        //使用ICriteria接口提供的Add方法添加Restrictions类中约束表达式可以限制一些结果集的作用。
        public IList<Customer> Norrawing()
        {

         IList<Customer> customers = _session.CreateCriteria(typeof(Customer))
                                       .Add(Restrictions.Like("FirstName","y%"))// 查询 FirstName like  y%
                                       .Add(Restrictions.Eq("FirstName", "yang "))  // 查询 FirstName=Yang
                                       .Add(Restrictions.Between("LastName","%m","_n")) // 查询 LastName between %m _n
                                       .List<Customer>();

           return customers;

        }


        // 结果集排序
        // 使用ICriteria.Order对结果集排序，第二个参数true代表asc，false代表desc。例如下面例子查询Customer对象按FirstName降序、Lastname升序。
         public IList<Customer> Orderss()
        {
           IList<Customer> customers = _session.CreateCriteria(typeof(Customer))
                                       .Add(Restrictions.Like("FristName","yangming"))
                                       .AddOrder(new Order("FirstName",false))  /// FirstName  降序排序
                                       .AddOrder(new Order("LastName",true))  // LastName 升序排序
                                       .List<Customer>();

          return customers;
        }

        /*
         * 条件查询同样支持关联查询、动态关联抓取(在介绍一对多，多对多关系中阐述)，投影、聚合和分组，离线(detached)查询和子查询是2.0版新增加的内容
         * */

         // 根据 示例查询(Query By Example) 

         //根据示例查询(QBE，Query By Example)是条件查询的一种特殊情况，NHibernate.Criterion.Example类根据你指定的实例创造查询条件。其典型的用法：创建一个Example实例；在Example实例上设置值；根据Example和设置NHibernate返回其对象集合。

         public IList<Customer> Query()
         {
            Customer customer = new Customer { Firstname="yangyang", Lastname="wang"};

            return _session.CreateCriteria(typeof(Customer))
             .Add(Example.Create(customer))
             .List<Customer>();
         }


         //可以自行调整Example使之更实用：
         public IList<Customer> Query(Customer customer)
         {
            Example exa = Example.Create(customer)
             .IgnoreCase()
             .EnableLike()
             .SetEscapeCharacter(‘&‘);

             return _session.CreateCriteria(typeof(Customer))
             .Add(exa)
             .List<Customer>();
         }

        #endregion

        #region 查询  NHibernate查询语言(HQL)

        public IList<Customer> ListAll()
         {
           return _session.CreateCriteria(" from Customer ")
           .List<Customer>();
         }


         public IList<Customer> ListAll2()
        {

          return _session.CreateCriteria(" From Customer as c ")
          .List<Customer>();

          // group by 子句
          //return _session.CreateCriteria("select * from Customer group by  firstname ")
          //.List<Customer>();


         // distinct 子句
   //       return _session.CreateQuery("select distinct c.Firstname from Customer c")
   //.List<Customer>();

           // where 子句

          //return _session.CreateQuery("from Customer c where c.Firstname=‘YJing‘")
          // .List<Customer>();


           // 带参数的 where 子句
    //       // return _session.CreateQuery("from Customer c where c.CustomerId > :cid")
    //.SetInt32("cid", 1)
    //.List<Customer>();

        }

        #endregion
        

        #region  分页查询

         ISessionFactory SessionFactory;

         // 分页功能
         public IList<Customer> GetAll(int currentPage, int pageSize)
         {
             // 获取当前 session
             ISession curSession = SessionFactory.GetCurrentSession();
             ICriteria cria = _session.CreateCriteria(typeof(Customer));
             // 分页
             var model = cria.SetFetchSize(pageSize).SetFirstResult(currentPage).List<Customer>();

             return model;
         }



          // 分页查询 
         //在Nhibernate里实现分页用 SetFirstResult 和SetMaxResults实现，SetFirstResult 简单的理解就是从第几条记录开始,SetMaxResults是取几条记录
         private IList<Customer> GetRecord(IList<ICriterion> queryConditions, int pageIndex, int pageSize, string orderField, bool isAscending)
         {

             ICriteria criteria = _session.CreateCriteria(typeof(Customer));
             foreach (ICriterion cri in queryConditions)
             {
                 criteria.Add(cri);
             }
           
             //记录总数
             int skipCount = (pageIndex - 1) * pageSize;
             criteria.AddOrder(new Order(orderField, isAscending));
             criteria.SetFirstResult(skipCount).SetMaxResults(pageSize);
             return criteria.List<Customer>();

             //SQL查询
             //IList<Customer> customerList = Session.CreateSQLQuery("SELECT * FROM Customer")
             //    .SetFirstResult(pageStart*pageLimit)
             //    .SetMaxResults(pageLimit)
             //    .SetResultTransformer(Transformers.AliasToBean<Customer>()).List<Customer>(); 
             //return customerList;

             IList<Customer> customers = _session.CreateSQLQuery("")
             .SetFirstResult(pageIndex * pageSize)
             .SetMaxResults(pageSize)
             .SetResultTransformer (NHibernate.Transform.Transformers.AliasToBean<Customer>()).List<Customer>();

             return customers;
         } 




        #endregion