高品質開源工具Chloe.ORM：支持存儲過程與Oracle

扯淡

這是一款高質量的.NET C#數據庫訪問框架(ORM)。查詢接口借鑒 Linq。借助 lambda 表達式，可以完全用面向對象的方式就能輕松執行多表連接查詢、分組查詢、聚合查詢、插入數據、批量刪除和更新等操作。

其實，早在兩個月前，Chloe 就已經支持 Oracle 數據庫了，只是LZ個人平時用 Oracle 不多，Oracle 較其他數據庫稍微特別，因此，並沒有及時發布，生怕 bug 連連。經過好幾個月的沈澱，除了支持 Oracle 外，對框架內部代碼結構也做了不少的調整，現在，實體也支持繼承父類或接口，更加可喜可賀的是也支持了存儲過程，包括 output 參數以及 Oracle 的 RefCurcor 返回結果集。與此同時，方便對 Chloe 的感興趣的同學學習框架，官網也正式上線了。
導航

    Chloe.ORM 事前準備 查詢數據 基本查詢 連接查詢 聚合函數 分組查詢 插入數據 更新數據 刪除數據 存儲過程 支持函數 坎坎坷坷 結語
    Chloe.ORM
    事前準備

    實體：
    view source
    print
    ?
    01.
    public enum Gender
    02.
    {
    03.
    Man = 1,
    04.
    Woman
    05.
    }
    06.
     
    07.
    [Table('Users')]
    08.
    public class User
    09.
    {
    10.
    [Sequence('USERS_AUTOID')]
    11.
    public int Id { get; set; }
    12.
    public string Name { get; set; }
    13.
    public Gender? Gender { get; set; }
    14.
    public int? Age { get; set; }
    15.
    public int? CityId { get; set; }
    16.
    public DateTime? OpTime { get; set; }
    17.
    }
    18.
     
    19.
    public class City
    20.
    {
    21.
    [Column(IsPrimaryKey = true)]
    22.
    public int Id { get; set; }
    23.
    public string Name { get; set; }
    24.
    public int ProvinceId { get; set; }
    25.
    }
    26.
     
    27.
    public class Province
    28.
    {
    29.
    [Column(IsPrimaryKey = true)]
    30.
    public int Id { get; set; }
    31.
    public string Name { get; set; }
    32.
    }

    因為框架本身需要與具體的數據庫驅動解耦，所以 OracleContext 構造函數需要一個 IDbConnectionFactory 的參數，IDbConnectionFactory 接口只有一個 CreateConnection() 方法，必須先建個類，實現 CreateConnection 方法：
    view source
    print
    ?
    01.
    public class OracleConnectionFactory : IDbConnectionFactory
    02.
    {
    03.
    string _connString = null;
    04.
    public OracleConnectionFactory(string connString)
    05.
    {
    06.
    this._connString = connString;
    07.
    }
    08.
    public IDbConnection CreateConnection()
    09.
    {
    10.
    OracleConnection oracleConnection = new OracleConnection(this._connString);
    11.
    OracleConnectionDecorator conn = new OracleConnectionDecorator(oracleConnection);
    12.
    return conn;
    13.
    }
    14.
    }

    由於我用的是 Oracle.ManagedDataAccess 數據庫驅動，OracleConnection 創建的 DbCommand 默認是以順序方式綁定參數，所以，上述例子使用了裝飾者模式對 OracleConnection 封裝了一遍，主要就是修改 DbCommand 參數綁定方式。OracleConnectionDecorator 的定義在官網API文檔和 Github 上的 demo 中都有，在這就不貼了，不然太占篇幅。

    創建一個 DbContext：
    view source
    print
    ?
    1.
    string connString = 'Your connection string';
    2.
    OracleContext context = new OracleContext(new OracleConnectionFactory(connString));

    再創建一個 IQuery<T>：
    view source
    print
    ?
    1.
    IQuery<User> q = context.Query<User>();
    查詢數據
    基本查詢
    view source
    print
    ?
    01.
    IQuery<User> q = context.Query<User>();
    02.
     
    03.
    q.Where(a => a.Id == 1).FirstOrDefault();
    04.
    /*
    05.
    * 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)
    06.
    */
    07.
     
    08.
    //可以選取指定的字段，支持返回匿名類型，也可以返回自定義類型
    09.
    q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
    10.
    /*
    11.
    * SELECT 'USERS'.'ID' AS 'ID','USERS'.'NAME' AS 'NAME' FROM 'USERS' 'USERS' WHERE ('USERS'.'ID' = 1 AND ROWNUM < 2)
    12.
    */
    13.
     
    14.
    //分頁
    15.
    q.Where(a => a.Id > 0).OrderBy(a => a.Age).TakePage(1, 20).ToList();
    16.
    /*
    17.
    * 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
    18.
    */
    連接查詢
    view source
    print
    ?
    01.
    IQuery<User> users = context.Query<User>();
    02.
    IQuery<City> cities = context.Query<City>();
    03.
    IQuery<Province> provinces = context.Query<Province>();
    04.
     
    05.
    //建立連接
    06.
    IJoiningQuery<User, City> user_city = users.InnerJoin(cities, (user, city) => user.CityId == city.Id);
    07.
    IJoiningQuery<User, City, Province> user_city_province = user_city.InnerJoin(provinces, (user, city, province) => city.ProvinceId == province.Id);
    08.
     
    09.
    //查出一個用戶及其隸屬的城市和省份的所有信息，同樣支持返回匿名類型，也可以返回自定義類型
    10.
    var view = user_city_province.Select((user, city, province) => new { User = user, City = city, Province = province }).Where(a => a.User.Id == 1).ToList();
    11.
    /*
    12.
    * 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
    13.
    */
    14.
     
    15.
    //也可以只獲取指定的字段信息：UserId,UserName,CityName,ProvinceName，這時，生成的 sql 只包含指定的字段
    16.
    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();
    17.
    /*
    18.
    * 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
    19.
    */
    聚合函數

    Chloe 的聚合查詢擁有和 linq 差不多的接口，基本是一看就明白。
    view source
    print
    ?
    01.
    IQuery<User> q = context.Query<User>();
    02.
     
    03.
    q.Select(a => AggregateFunctions.Count()).First();
    04.
    /*
    05.
    * SELECT COUNT(1) AS 'C' FROM 'USERS' 'USERS' WHERE ROWNUM < 2
    06.
    */
    07.
     
    08.
    q.Select(a => new { Count = AggregateFunctions.Count(), LongCount = AggregateFunctions.LongCount(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Average = AggregateFunctions.Average(a.Age) }).First();
    09.
    /*
    10.
    * SELECT COUNT(1) AS 'COUNT',COUNT(1) AS 'LONGCOUNT',SUM('USERS'.'AGE') AS 'SUM',MAX('USERS'.'AGE') AS 'MAX',MIN('USERS'.'AGE') AS 'MIN',AVG('USERS'.'AGE') AS 'AVERAGE' FROM 'USERS' 'USERS' WHERE ROWNUM < 2
    11.
    */
    12.
     
    13.
    var count = q.Count();
    14.
    /*
    15.
    * SELECT COUNT(1) AS 'C' FROM 'USERS' 'USERS'
    16.
    */
    17.
     
    18.
    var longCount = q.LongCount();
    19.
    /*
    20.
    * SELECT COUNT(1) AS 'C' FROM 'USERS' 'USERS'
    21.
    */
    22.
     
    23.
    var sum = q.Sum(a => a.Age);
    24.
    /*
    25.
    * SELECT SUM('USERS'.'AGE') AS 'C' FROM 'USERS' 'USERS'
    26.
    */
    27.
     
    28.
    var max = q.Max(a => a.Age);
    29.
    /*
    30.
    * SELECT MAX('USERS'.'AGE') AS 'C' FROM 'USERS' 'USERS'
    31.
    */
    32.
     
    33.
    var min = q.Min(a => a.Age);
    34.
    /*
    35.
    * SELECT MIN('USERS'.'AGE') AS 'C' FROM 'USERS' 'USERS'
    36.
    */
    37.
     
    38.
    var avg = q.Average(a => a.Age);
    39.
    /*
    40.
    * SELECT AVG('USERS'.'AGE') AS 'C' FROM 'USERS' 'USERS'
    41.
    */