C# NPOCO 轻量级ORM框架入门

NPoco 是一个简单的微ORM框架，用于映射POCO 对象的查询结果，基于PetaPoco ，可在.NET和Mono环境运行。

官方介绍：http://www.toptensoftware.com/petapoco/ 
源码地址：https://github.com/CollaboratingPlatypus/PetaPoco 
PetaPoco有如下特点： 
1.PetaPoco不支持Linq语法，开发者需要手动编写SQL语句，调用PetaPoco封装的方法生成在数据库中可执行的SQL。 
2.性能方面比手动编写DAL（Data Access Layer）稍差，也仅次于功能强大速度快的Dapper。 
3.使用简单，不需要配置，将PetaPoco.cs文件加入项目中即可使用。 
4.支持强类型POCO（Plain Old CLR Object），支持用T4模板生成类。 
5.支持多种数据库： SQL Server，SQL Server CE, MySQL, PostgreSQL and Oracle。

下载

visual studio通过Nugut下载PetaPoco相关文件，你可以在Nugut官网查看PetaPoco版本信息：http://www.nuget.org/packages/petapoco 
安装命令：Install-Package PetaPoco，安装完成会下载如下文件： 
134d59bc-fd0d-4624-b39b-a74dfbcef4d5.png 
Generated文件夹是T4模板使用，核心文件只有PetaPoco.cs，因此也可以直接拷贝该文件到项目中使用。 
初次使用配置好sql连接，本次使用MySQL演示： 

查询

本次新增一个Agency实体类作为演示： 
public class Agency 
{ 
public int id { get; set; } 
public string agencyCode { get; set; } 
public string agencyName { get; set; } 
public DateTime createDate { get; set; } 
public int status { get; set; } 
public decimal originalDeposit { get; set; } 
public decimal leftDeposit { get; set; } 
} 
创建数据库表SQL语句： 
CREATE TABLE agency ( 
id int(11) NOT NULL, 
agencyCode varchar(20) NOT NULL, 
agencyName varchar(50) DEFAULT NULL, 
createDate datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 
status int(11) NOT NULL DEFAULT ‘0’, 
originalDeposit decimal(10,4) NOT NULL DEFAULT ‘0.0000’, 
leftDeposit decimal(10,4) NOT NULL DEFAULT ‘0.0000’, 
PRIMARY KEY (id), 
UNIQUE KEY code_uq (agencyCode) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’机构表’; 
使用时首先创建一个PetaPoco.Database对象：var db = new PetaPoco.Database(“SqlConnectionString”);

查询多条记录： 
StringBuilder sb = new StringBuilder(); 
var list = db.Query(“SELECT * FROM agency”); 
foreach (var a in list) 
{ 
sb.AppendFormat(“{0},{1},{2};”, a.id, a.agencyCode, a.agencyName); 
}

此外还可以调用Fetch方法查询数据，使用和Query类似，但是Fetch返回的而是POCO类的List，而Query使用yield返回数据是IEnumerable，但这些数据并未加载到内存中。 
//An enumerable collection of result records 
IEnumerable listQuery = db.Query(“SELECT * FROM agency”); 
//A List holding the results of the query 
List listFetch=db.Fetch(“SELECT * FROM agency”)); 
查询一个值，如下查询记录数： 
//query a scalar 
long count = db.ExecuteScalar(“SELECT Count(*) FROM agency”); 
查询单条记录：

//query a single record 
var oneRow = db.SingleOrDefault(“SELECT * FROM agency WHERE id=@0”, 5); 
还可以进行简写，PetaPoco会自动补全select关键字：var oneRow = db.SingleOrDefault(“WHERE id=@0”, 5);

支持分页查询， 获取分页数据： 
//Paged 分页 
Sql sql = new Sql(“SELECT * FROM agency where status=@0 ORDER BY id DESC”, -1); 
var result = db.Page(1, 5, sql);//第一个参数是页码，第二个参数是页容量，第三个参数是SQL语句 
返回的是Page对象，Page类如下： 
/// 
/// Holds the results of a paged request. 
/// 
/// The type of Poco in the returned result set 
public class Page 
{ 
/// 
/// The current page number contained in this page of result set 
/// 
public long CurrentPage { get; set; } 
/// 
/// The total number of pages in the full result set 
/// 
public long TotalPages { get; set; } 
/// 
/// The total number of records in the full result set 
/// 
public long TotalItems { get; set; } 
/// 
/// The number of items per page 
/// 
public long ItemsPerPage { get; set; } 
/// 
/// The actual records on this page 
/// 
public List Items { get; set; } 
/// 
/// User property to hold anything. 
/// 
public object Context { get; set; } 
}

分页的相关信息到封装到了该类中，此外还提供了上Context属性，通过这个属性可以方便的传递一些上下文数据。

不带查询的命令

使用Execute执行不带查询（a non-query command）的命令： 
int rtn=db.Execute(“DELETE FROM agency WHERE id=@0”,5);

新增

插入一条数据，需要指定表名和主键，第一个参数是表名，第二个参数是表主键，第三个参数是要新增的实体： 
//insert 
Agency agency = new Agency(); 
agency.agencyCode = “Kungge001”; 
agency.agencyName = “坤哥网001”; 
var b = db.Insert(“agency”, “id”, agency);

修改

修改一条数据，也需要指定表名和主键， 第一个参数是表名，第二个参数是表主键，第三个参数是要修改的实体： 
//update 
var updAgency = db.SingleOrDefault(“SELECT * FROM agency WHERE id=@0”, 4); 
updAgency.agencyName = “坤哥金融”; 
int u = db.Update(“agency”, “id”, updAgency);

或者还可以传递一个匿名类参数来更新部分字段，如下更新agencyCode字段： 
db.Update(“agency”, “id”, new { agencyCode=”WKTech001” },updAgency); 
如果无法确定poco对象是新增还是修改，可以使用IsNew方法判断，使用Save方法，PetaPoco自动判断是插入还是更新记录： 
if (db.IsNew(agency)) 
{ 
//insert 
} 
// Save a new or existing record 
db.Save(agency);

删除

删除一条数据：

//delete 
var delAgency = db.SingleOrDefault(“SELECT * FROM agency WHERE id=@0”, 6); 
int d = db.Delete(“agency”, “id”, delAgency);//第一个参数是表名，第二个参数是主键，第三个参数是实体 
int d2 = db.Delete(“agency”, “id”, null, 24);//第一个参数是表名，第二个参数是主键，第三个参数是POCO 
实体若是第四个参数有值传null即可，第四个参数主键值

以上增删改操作有点麻烦，每次都要指定表名和主键。现对实体做修改，指定表名和主键： 
[TableName(“agency”)] 
[PrimaryKey(“id”)] 
public class Agency 
{ 
public int id { get; set; } 
public string agencyCode { get; set; } 
public string agencyName { get; set; } 
public DateTime createDate { get; set; } 
public int status { get; set; } 
public decimal originalDeposit { get; set; } 
public decimal leftDeposit { get; set; } 
} 
简化后的新增： 
//insert 
Agency agency = new Agency(); 
agency.agencyCode = “Kungge0048”; 
agency.agencyName = “坤哥网0048”; 
var b = db.Insert(agency);

支持无标识的主键列：PrimaryKey属性类有AutoIncrement成员，表示主键列是否是自动增长，该成员默认设置为true， 
[PrimaryKey(“id”,AutoIncrement =true)]，当Insert时返回的是主键的值。 
如果没有设置这个成员值，可以通过调用Insert重载方法指定是否生成主键： 
public object Insert(string tableName, string primaryKeyName, bool autoIncrement, object poco) 
简化后的修改： 
var updAgency = db.SingleOrDefault(“SELECT * FROM agency WHERE id=@0”, 4); 
updAgency.agencyName = “坤哥金融2”; 
int u = db.Update(updAgency); 
var u2 = db.Update(“SET agencyName=@0 WHERE id=@1”, “坤哥金融2”, 4);

可用使用save方法来进行保存结果，不用关心是新增还是修改，db.Save(agency)。 
简化后的删除： 
//delete 
var delAgency = db.SingleOrDefault(“SELECT * FROM agency WHERE id=@0”, 6); 
int d = db.Delete(delAgency); 
int d2 = db.Delete(“WHERE id=@0”, 6); 
PetaPoco支持[Column]属性指定需要映射的列来自动匹配列，也可以使用属性[Ignore]属性忽略特定的列，[ExplicitColumns]属性表示只有明确标出的列才进行映射: 
[TableName(“agency”)] 
[PrimaryKey(“id”)] 
public class Agency 
{ 
[Column] 
public int id { get; set; } 
[Column] 
public string agencyCode { get; set; } 
[Column] 
public string agencyName { get; set; } 
[Column] 
public DateTime createDate { get; set; } 
[Column] 
public int status { get; set; } 
[Column] 
public decimal originalDeposit { get; set; } 
[Ignore] 
public decimal leftDeposit { get; set; } 
}

复杂查询

查询不仅是表中的列，还会有需要计算或连接的列，在查询结果时就要将这些列填充，添加[ResultColumn]属性后会被自动填充。在使用 Update和Insert时会自动忽略，使用方法不变。 
修改Agency实体类： 
[TableName(“agency”)] 
[PrimaryKey(“id”)] 
public class Agency 
{ 
[Column] 
public int id { get; set; } 
[Column] 
public string agencyCode { get; set; } 
[Column] 
public string agencyName { get; set; } 
[Column] 
public DateTime createDate { get; set; } 
[Column] 
public int status { get; set; } 
[Column] 
public decimal originalDeposit { get; set; } 
[Ignore] 
public decimal leftDeposit { get; set; } 
[ResultColumn] 
public long OrderCount { get; set; } 
} 
使用ResultColumn属性的列要在select中显示引用： 
//复杂查询 
var sql = new Sql(@”select agency.*,count(agency.agencyCode) as OrderCount 
from agency 
join order on order.agencyCode=agency.agencyCode 
group by order.agencyCode”); 
var list=db.Query(sql); 
查询结果如下： 
1c2208d8-554d-4de7-8301-f044e210bee1.png 
也可以分开来写，上面的可以用下面来代替： 
var sql2 = new Sql().Select(“agency.*,count(agency.agencyCode) as OrderCount”).From(“agency“).InnerJoin(“order“).On(“order.agencyCode=agency.agencyCode”).GroupBy(“order.agencyCode”); 
var list2 = db.Query(sql2);

SQL Builder

使用SQL Builder使得格式化SQL语句变得简单，使用参数化SQL能防止SQL注入，而且SQL Builder消耗资源少。 
//SQL Builder 
decimal leftDeposit = 2; 
decimal originalDeposit = 10; 
var sql = Sql.Builder.Append(“SELECT * FROM agency”).Append(“WHERE status=@0”, 0); 
//sql = new Sql(“SELECT * FROM agency WHERE id=@0 “,25);//还可以直接new Sql() 
//根据要求对sql拼接参数 
if (leftDeposit > 0) 
{ 
sql.Append(“and leftDeposit=@0”, leftDeposit); 
} 
if (originalDeposit > 0) 
{ 
sql.Append(“and originalDeposit=@0”, originalDeposit); 
} 
var agency = db.Query(sql); 
每个拼接参数都使用@0， PetaPoco最终会生成完整的参数列表，正确的更新参数。还可以使用命名的参数： 
sql.Append(“and createDate>=@minCreateDate and createDate<=@maxCreateDate”,new { 
minCreateDate=DateTime.Now.AddDays(-7), 
maxCreateDate=DateTime.Now 
}); 
设置排序：sql.OrderBy(“createDate DESC”); 
当构建Sql对象时没有where条件，在添加刷选where条件时，PetaPoco自动处理成相应的连接符where为and，所以在添加连续where子句时，直接用where即可： 
decimal leftDeposit = 2; 
decimal originalDeposit = 10; 
var sql = Sql.Builder.Append(“SELECT * FROM agency”); 
//sql = new Sql(“SELECT * FROM agency WHERE id=@0 “,25);//还可以直接new Sql() 
//根据要求对sql拼接参数 
if (leftDeposit > 0) 
{ 
sql.Append(“where leftDeposit=@0”, leftDeposit); 
} 
if (originalDeposit > 0) 
{ 
sql.Append(“where originalDeposit=@0”, originalDeposit); 
} 
var agency = db.Query(sql); 
但是要注意的是where子句必须是Sql片段的第一部分，如下将会出错： 
sql.Append(“where originalDeposit=@0 where originalDeposit=@1 “, leftDeposit,originalDeposit); 
但是两个Append连接是可以的： 
sql.Append(“where leftDeposit =@0 “,leftDeposit).Append(“where originalDeposit=@0”, originalDeposit) ; 
若是多个Append相连，where也必须是连续的，下面的就不行： 
sql.Append(“where leftDeposit =@0 “,leftDeposit).Append(“and status=0”).Append(“where originalDeposit=@0”, originalDeposit) ; 
order by也可以多个append： 
sql.Append(“ORDER BY leftDeposit”).Append(“ORDER BY originalDeposit”) 
会生成：ORDER BY leftDeposit,originalDeposit

SQL命令跟踪

PetaPoco提供了三个属性用于查看最终执行的SQL语句： 
LastSQL：string类型，最终执行的SQL语句 
LastArgs：object[]类型，参数数组 
LastCommand：string类型，最终执行的Command 
如下分页获取数据例子： 
16e1f886-32ed-459f-a352-4d20e2b81dc5.png

特点分析

原始版本的PetaPoco使用的是反射设置来从数据库中去读取pcoo对象的属性，反射虽然使用简单，但是性能较慢。后来PetaPoco用动态生成的的方法取代了反射，PetaPoco比典型的Linq性能要好。

T4模板

PetaPoco支持T4模板，可以自动生成PetaPoco对象，也可以生成一些常用方法，如Save(),IsNew(),Update()等。 
使用T4模板很简单，总共包含三个文件，使用NuGet包下载会放在目录/Models/Generated下： 
PetaPoco.Core.ttinclude：包括所有读取数据库的常规方法 
PetaPoco.Generator.ttinclude：定义生成所需内容的实际模板 
Database.tt：模板本身，包括各种设置和上面的两个ttinclude文件 
典型的 Database.tt文件如下： 
<#@ include file=”PetaPoco.Core.ttinclude” #> 
<# 
// Settings 
ConnectionStringName = “jab”; 
Namespace = ConnectionStringName; 
DatabaseName = ConnectionStringName; 
string RepoName = DatabaseName + “DB”; 
bool GenerateOperations = true; 
// Load tables 
var tables = LoadTables();

<#@ include file=”PetaPoco.Generator.ttinclude” #> 
如何使用模板： 
1.将上面介绍的三个文件放在项目中 
2.在app.config和web.config文件中设置connection和provider 
3.修改Database.tt文件中的ConnectionStringName为实际值

以上内容字体没有统一，是因为这篇文章是之前记录在 Evernote 上的，由于没有使用插入代码功能，每次从 IDE 中拷贝代码都会改变文章原有字体，就不一一调整了，哈哈，凑合着看吧