高品质开源工具Chloe.ORM：支持存储过程与Oracle - 我叫So_H5之家

扯淡

这是一款高质量的.NET C#数据库访问框架(ORM)。查询接口借鉴 Linq。借助 lambda 表达式，可以完全用面向对象的方式就能轻松执行多表连接查询、分组查询、聚合查询、插入数据、批量删除和更新等操作。

其实，早在两个月前，Chloe 就已经支持 Oracle 数据库了，只是LZ个人平时用 Oracle 不多，Oracle 较其他数据库稍微特别，因此，并没有及时发布，生怕 bug 连连。经过好几个月的沉淀，除了支持 Oracle 外，对框架内部代码结构也做了不少的调整，现在，实体也支持继承父类或接口，更加可喜可贺的是也支持了存储过程，包括 output 参数以及 Oracle 的 RefCurcor 返回结果集。与此同时，方便对 Chloe 的感兴趣的同学学习框架，官网也正式上线了。

导航

Chloe.ORM 事前准备

实体：

public enum Gender { Man = 1, Woman } [Table("Users")] public class User { [Sequence("USERS_AUTOID")] public int Id { get; set; } public string Name { get; set; } public Gender? Gender { get; set; } public int? Age { get; set; } public int? CityId { get; set; } public DateTime? OpTime { get; set; } } public class City { [Column(IsPrimaryKey = true)] public int Id { get; set; } public string Name { get; set; } public int ProvinceId { get; set; } } public class Province { [Column(IsPrimaryKey = true)] public int Id { get; set; } public string Name { get; set; } }

因为框架本身需要与具体的数据库驱动解耦，所以 OracleContext 构造函数需要一个 IDbConnectionFactory 的参数，IDbConnectionFactory 接口只有一个 CreateConnection() 方法，必须先建个类，实现 CreateConnection 方法：

public class OracleConnectionFactory : IDbConnectionFactory { string _connString = null; public OracleConnectionFactory(string connString) { this._connString = connString; } public IDbConnection CreateConnection() { OracleConnection oracleConnection = new OracleConnection(this._connString); OracleConnectionDecorator conn = new OracleConnectionDecorator(oracleConnection); return conn; } }

由于我用的是 Oracle.ManagedDataAccess 数据库驱动，OracleConnection 创建的 DbCommand 默认是以顺序方式绑定参数，所以，上述例子使用了装饰者模式对 OracleConnection 封装了一遍，主要就是修改 DbCommand 参数绑定方式。OracleConnectionDecorator 的定义在官网API文档和 Github 上的 demo 中都有，在这就不贴了，不然太占篇幅。

创建一个 DbContext：

string connString = "Your connection string"; OracleContext context = new OracleContext(new OracleConnectionFactory(connString));

再创建一个 IQuery<T>：

IQuery<User> q = context.Query<User>();

查询数据基本查询

IQuery<User> q = context.Query<User>(); q.Where(a => a.Id == 1).FirstOrDefault(); /* * SELECT "USERS"."ID" AS "ID","USERS"."NAME" AS "NAME","USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME" FROM "USERS" "USERS" WHERE ("USERS"."ID" = 1 AND ROWNUM < 2) */ //可以选取指定的字段，支持返回匿名类型，也可以返回自定义类型 q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault(); /* * SELECT "USERS"."ID" AS "ID","USERS"."NAME" AS "NAME" FROM "USERS" "USERS" WHERE ("USERS"."ID" = 1 AND ROWNUM < 2) */ //分页 q.Where(a => a.Id > 0).OrderBy(a => a.Age).TakePage(1, 20).ToList(); /* * SELECT "T"."ID" AS "ID","T"."NAME" AS "NAME","T"."GENDER" AS "GENDER","T"."AGE" AS "AGE","T"."CITYID" AS "CITYID","T"."OPTIME" AS "OPTIME" FROM (SELECT "TTAKE"."ID" AS "ID","TTAKE"."NAME" AS "NAME","TTAKE"."GENDER" AS "GENDER","TTAKE"."AGE" AS "AGE","TTAKE"."CITYID" AS "CITYID","TTAKE"."OPTIME" AS "OPTIME",ROWNUM AS "ROW_NUMBER_0" FROM (SELECT "USERS"."ID" AS "ID","USERS"."NAME" AS "NAME","USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME" FROM "USERS" "USERS" WHERE "USERS"."ID" > 0 ORDER BY "USERS"."AGE" ASC) "TTAKE" WHERE ROWNUM < 21) "T" WHERE "T"."ROW_NUMBER_0" > 0 */

连接查询

IQuery<User> users = context.Query<User>(); IQuery<City> cities = context.Query<City>(); IQuery<Province> provinces = context.Query<Province>(); //建立连接 IJoiningQuery<User, City> user_city = users.InnerJoin(cities, (user, city) => user.CityId == city.Id); IJoiningQuery<User, City, Province> user_city_province = user_city.InnerJoin(provinces, (user, city, province) => city.ProvinceId == province.Id); //查出一个用户及其隶属的城市和省份的所有信息，同样支持返回匿名类型，也可以返回自定义类型 var view = user_city_province.Select((user, city, province) => new { User = user, City = city, Province = province }).Where(a => a.User.Id == 1).ToList(); /* * SELECT "USERS"."ID" AS "ID","USERS"."NAME" AS "NAME","USERS"."GENDER" AS "GENDER","USERS"."AGE" AS "AGE","USERS"."CITYID" AS "CITYID","USERS"."OPTIME" AS "OPTIME","CITY"."ID" AS "ID0","CITY"."NAME" AS "NAME0","CITY"."PROVINCEID" AS "PROVINCEID","PROVINCE"."ID" AS "ID1","PROVINCE"."NAME" AS "NAME1" FROM "USERS" "USERS" INNER JOIN "CITY" "CITY" ON "USERS"."CITYID" = "CITY"."ID" INNER JOIN "PROVINCE" "PROVINCE" ON "CITY"."PROVINCEID" = "PROVINCE"."ID" WHERE "USERS"."ID" = 1 */ //也可以只获取指定的字段信息：UserId,UserName,CityName,ProvinceName，这时，生成的 sql 只包含指定的字段 user_city_province.Select((user, city, province) => new { UserId = user.Id, UserName = user.Name, CityName = city.Name, ProvinceName = province.Name }).Where(a => a.UserId == 1).ToList(); /* * SELECT "USERS"."ID" AS "USERID","USERS"."NAME" AS "USERNAME","CITY"."NAME" AS "CITYNAME","PROVINCE"."NAME" AS "PROVINCENAME" FROM "USERS" "USERS" INNER JOIN "CITY" "CITY" ON "USERS"."CITYID" = "CITY"."ID" INNER JOIN "PROVINCE" "PROVINCE" ON "CITY"."PROVINCEID" = "PROVINCE"."ID" WHERE "USERS"."ID" = 1 */

聚合函数

Chloe 的聚合查询拥有和 linq 差不多的接口，基本是一看就明白。