NPoco学习篇（2）：NPoco简介

NPoco官方主页：https://github.com/schotime/NPoco

NPoco官方简介：Simple microORM that maps the results of a query onto a POCO object. Project based on Schotime's branch of PetaPoco。

 

很奇怪，Google不到任何关于NPoco的中文介绍。而我的第一篇“开局篇”竟然在前列，MY GOD！不过也不算太奇怪，因为NPoco的源起PetaPoco的中文资料就很少，这给大叔我的学习增加了好大的难度。不过好在NPoco的GitHub主页很活跃，提问都有回复，更新也挺频繁。所以，学习NPoco的最大障碍不是资料，只是英语了。英语啊！怨念！

 

以下简介基于NPoco的官方wiki：https://github.com/schotime/NPoco/wiki

 

一、Standard Features

1. Mapping
   By default no mapping is required. It will be assumed that the table name will be the class name and the primary key will be 'Id' if its not specified with the attributes below.
   这应该是ORM标准功能吧。结合T4模板，自动生成Class，很舒服。
   示例：
   

   [TableName("Users")]
   [PrimaryKey("UserId")]
   public class User
   {
       public int UserId { get;set; }
       [Column("emailAddress")]
       public string Email { get;set; }
       [Result]
       public string ExtraInfo { get;set; }
       [Ignore]
       public int Temp { get;set; }
   }

   除了PetaPoco的 [TableName] [PrimaryKey] [Column] [Result] [Ignore] 属性外，NPoco还增加 [Result]：
   [Result] Properties marked with the Result column can be mapped into, however there properties will not be included in inserts or updates.
   Note: These columns will need to be explicitly specified in the SQL. It will not be included in the auto generated SQL.

   是介于普通Column与Ignore之间的一种列，不会自动被Update/Insert，但是可以手动的用SQL进行操作。
   
   
2. Query Single Object
   Selecting an object from the database can be done in a few different ways.
   从数据库检索数据并转换为Object。依然是标准的ORM功能。有以下几种方法：
   

   //The easiest way to load an object from the database is by passing the primary key to the SingleById<T>() method.
   IDatabase db = new Database("connStringName");
   User u = db.SingleById<User>(3);

   //Below you can see that only the where is specified. 
   //If you don't explicitly supply the select clause it will be automatically generated for you and the where will then be appended.
   User u = db.Single<User>("where emailaddress = @0", "email@domain.com");
   //or
   User u = db.Single<User>("select u.* from users u where emailaddress = @0", "email@domain.com");
   //Both these methods have a 'OrDefault' method if you are unsure that the object will exist. 
   //If it doesn't exist and you don't use the 'OrDefault' override it will throw an exception.

   //There are also First<T> and FirstOfDefault<T> which will not throw an exception if more than 1 record is returned.

3. Create Read Update Delete
   继续。。。
   

   //Inserting a new record
   IDatabase db = new Database("connStringName");
   User u = new User() 
   {
       Email = "name@domain.com",
       LastLoggedIn = DateTime.UtcNow
   };
   
   db.Insert(u);

   //Reading the new record
   var user = db.SingleById(u.UserId);
   Assert.AreEqual(u.Email, user.Email);

   //Updating the record
   //Once I have the object, I can update its properties. After calling the Update method, those changes will be persisted to the database.
   var user = db.SingleById(1);
   user.Email = "new@domain.com";
   db.Update(user);

   //Deleting the record
   //If I decide I no longer need the record I can delete it in a very similar fashion to Insert and Update. 
   //That is by passing the object to the Delete method. Just the primary key value can also be passed to the Delete method, however the generic type parameter will need to be specified.
   var user = db.SingleById(1);
   db.Delete(user);
   //or
   db.Delete<User>(1);

    

4. Query List
   Here are some of the ways to fetch multiple rows from the database.
   即检索多条数据库记录到List<T>中。
   

   // Eager Loading
   
   //1: Fetch all
   List<User> users = db.Fetch<User>();
   
   //2: Fetch with criteria
   List<User> users = db.Fetch<User>("where isActive = 1");
   
   //3: Fetch with raw SQL
   List<User> users = db.Fetch<User>("select u.* from users where u.isActive = 1");

   // Lazy Loading
   
   //Warning: The following method Query<T> uses the yield keyword. It will only run the query when the results are being iterated over. 
   //Please use the Fetch<T> method if you don't fully understand this concept.
   
   List<User> users = db.Query<User>("select u.* from users where u.isActive = 1");

    

5. Paging
   There are two main methods used for paging.
   分页帮助函数，比PetaPoco多一种。
   

   //Page<T> is defined by:
   public class Page<T> 
   {
       public long CurrentPage { get; set; }
       public long TotalPages { get; set; }
       public long TotalItems { get; set; }
       public long ItemsPerPage { get; set; }
       public List<T> Items { get; set; }
   }
   
   //Paging
   //You must provide an order by statement in your SQL statement so that the query knows in which order you want your data to be paged. 
   IDatabase db = new Database("connStringName");
   Page<T> pagedUsers = db.Page<User>(2, 10, "select u.* from users u order by userid");

   第二种方式：

   // SkipTake<T>
   //The SkipTake<T> method is very similar to the Skip and Take methods in LINQ. 
   //It has the same number of parameters as the Page<T> method, but instead of the first parameter being the page number, it is the number of records to skip. 
   //The second parameter is the number of records to return after x number of records have been skipped. 
   //To return the same results as the Page<T> method, the query would be as follows:
   List<User> users = db.SkipTake<User>(10, 10, "select u.* from users u order by userid");

    大意是以类Paging的方法，直接返回List<T>。目前尚未用到此函数。
   
   官方主页上只列出了以上五条标准功能，其他PetaPoco的功能未列出，如果需要可直接查看PetaPoco的帮助。这里推荐一篇中文版的：http://www.cnblogs.com/youring2/archive/2012/06/04/2532130.html
   
   

二、Extras

以下是NPoco的扩展功能，一共13条，貌似很多啊！可惜有三条没介绍，不知道有啥用。

目前对我很重要的就是第6条和第13条，但在使用中我遇到了一些问题，有时间的话我会单独写文章详细记述。

1. Query onto an existing object
   
2. One-to-Many query helpers
3. Mapping to Nested Objects query helpers
4. Dictionary<string, object> and object[] queries for dynamic results
5. Change tracking for updates
6. Composite Primary Key support
   

   //Composite keys can be specified by placing a comma between the two column names in the [PrimaryKey] attribute.
   [TableName("Users")]
   [PrimaryKey("UserId,UserName")]
   public class User
   {
       public int UserId { get; set; }
       public string UserName { get;set; }
   }
   
   //If you want to get one of these objects from the database using the SingleById group of methods then you can use an anonymous type.
   IDatabase db = new Database("connStringName");
   var user = db.SingleById<User>(new {UserId = 1, UserName = "user"});

   
   
7. Queries that returns Multiple Result Sets
8. Fluent Mappings including Conventional based mappings
9. Simple LINQ Queries v2+
10. Version column support
11. IDatabase interface
12. Sql Templating
13. Debugging/Profiling (MiniProfiler/Glimpse)
    There are a few ways to debug/profile NPoco. They are listed below, and are commonly done by inheriting from Database and overriding a specific method. Note: Make sure you instantiate your new class (MyDb as below) when creating a Database from then on.