Asp. Net large project practice (4)-Save and query the paging and sorting of our business objects with nhib.pdf (with source code)

The dictionary maintenance function mentioned in the previous article. In this article, we will implement the paging and sorting functions. After the implementation of the UI Layer, we will discuss that this article only implements the data access layer.

1. In the data access interface of the dictionary item, add a method to obtain the dictionary item list through the dictionary category Id, and code with the paging and sorting functions

      Namespace Demo. HIS. Infrastructure. Core. Repositories
   {
   // Database access interface for dictionary items
   Public interface IDictionaryRepository: IRepository <Dictionary>
   {
   // Obtain the dictionary item list through the dictionary category Id
   IList <Dictionary> GetPlistByCategoryId (string id, int start, int limit, string sort, string dir, out long total );
   }
   }

   Parameter description:
   Id: Id of the dictionary category;
   Start: indicates the number of data records that are queried by page;
   Limit: the number of data records displayed by page;
   Total: returns the total number of data records. Pay attention to the out usage;
   Sort: String Of the attribute name to be sorted. Note that the attribute name of the object is passed in, not the field name of the table.
   Dir: "desc" and "asc" are used to represent the reverse and forward orders respectively.

2. Interface Implementation Method

   Code

      namespace Demo.HIS.Infrastructure.Repositories.Data
   {
   public class DictionaryRepositoryImpl : RepositoryNhbImpl<Dictionary>, IDictionaryRepository
       {
   public IList<Dictionary> GetPlistByCategoryId(string id, int start, int limit, string sort, string dir, out long total)
           {
               sort = "d." + sort;
   
               var query = Session.CreateQuery(@"select d from Dictionary as d where d.Category.Id=:Id"
   + " order by " + sort + " " + dir)
                                 .SetString("Id", id)
                                 .SetFirstResult(start)
                                 .SetMaxResults(limit);
   
               total = Session.CreateQuery(@"select count(*) from Dictionary as d where d.Category.Id=:Id")
                   .SetString("Id", id)
                   .UniqueResult<long>();
   
   return query.List<Dictionary>();
           }
       }
   }

   Here we use HQL statements for query and sorting, and use the. SetFirstResult and. SetMaxResults methods of nhib.pdf to implement paging.
   For the total number of data returned for query, you can only query the database once again. If you have a better solution, let me know.

3. Use the NHProfiler tool to view the SQL statements generated by nhib.pdf
   NHProfiler tool to download (cracked)
   In the Demo. HIS. FrameWork project, add HibernatingRhinos. nhibinos. Profiler. Appender. dll that references NHProfiler.
   Do you still remember to add code to the SessionBuilder class:

   Code

      Private static void CreateConfiguration ()
   {
   HibernatingRhinos. nhib.pdf. Profiler. Appender. NHibernateProfiler. Initialize (); // view the SQL statement generated by HQL
   // Configuration = new Configuration (). Configure (System. Web. HttpContext. Current. Request. PhysicalApplicationPath + "Configuration \ hibernate. cfg. xml ");
   Configuration = new Configuration (). Configure ();
   }

   With the NHProfiler tool enabled, we can view the SQL statements generated by each access to the database of nhib.pdf.
   It is very important to check the real SQL statements generated by the ORM when you are not familiar with the usage of the ORM!
   

4. Test
   I wrote a test in the DemoHisSite project:

   Code

      // Test it
   Public ActionResult test ()
   {
   Demo. HIS. Infrastructure. Core. Repositories. IDictionaryRepository r = new Demo. HIS. Infrastructure. Repositories. Data. DictionaryRepositoryImpl ();
   Long total;
   R. GetPlistByCategoryId ("48391bb4-471b-4499-899b-cea9748e1a7b", 0, 15, "Index", "desc", out total );
   Return Content ("");
   }

   Through viewing NHProfiler, we can see that nhib.pdf generates two SQL statements:

      select count(* ) as col_0_0_
   from   INFRA_DICTIONARY dictionary0_
   where  (dictionary0_.IsDelete = 0)
   and dictionary0_.DICCATEGORY_ID = '48391bb4-471b-4499-899b-cea9748e1a7b' /* :p0 */

    

   Code

      select *
   from   (select   dictionary0_.DICTIONARY_ID  as DICTIONARY1_0_,
                    dictionary0_.VERSION        as VERSION0_,
                    dictionary0_.NAME           as NAME0_,
                    dictionary0_.CODE           as CODE0_,
                    dictionary0_.INPUT_CODE1    as INPUT5_0_,
                    dictionary0_.INPUT_CODE2    as INPUT6_0_,
                    dictionary0_.INPUT_CODE3    as INPUT7_0_,
                    dictionary0_.INDEX_FIELD    as INDEX8_0_,
                    dictionary0_.DESCRIPTION    as DESCRIPT9_0_,
                    dictionary0_.CREATETIME     as CREATETIME0_,
                    dictionary0_.ISDELETE       as ISDELETE0_,
                    dictionary0_.DICCATEGORY_ID as DICCATE12_0_
   from     INFRA_DICTIONARY dictionary0_
   where    (dictionary0_.IsDelete = 0)
   and dictionary0_.DICCATEGORY_ID = '48391bb4-471b-4499-899b-cea9748e1a7b' /* :p0 */
   order by dictionary0_.INDEX_FIELD desc)
   where  rownum <= 15 /* :p1 */

    

NOTE: If your database is configured, the following source code can be used to run the test.

Source code: HISDemo-4.rar