ORM Nhibernet 架構的 CRUD 操作

標籤：

  Nhibernet 的基本文法：

 

 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類型的Factory 方法。            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

 

ORM Nhibernet 架構的 CRUD 操作