Oracle[转]高格调开源工具Chloe.ORM:支持存储过程与Oracle

本文转自:http://www.cnblogs.com/so9527/p/6131177.html

扯淡

眼看是千篇一律慢慢悠悠大质量之.NET C#数据库访问框架(ORM)。查询接口借鉴 Linq。借助
lambda
表达式,可以了用面向对象的道就是能够轻松实施多表连接查询、分组查询、聚合查询、插入数据、批量去和创新等操作。

其实,早以点滴单月前,Chloe 就曾支撑 Oracle 数据库了,只是LZ个人平时用
Oracle 不多,Oracle 较其它数据库稍微特别,因此,并没有立刻公布,生怕 bug
连连。经过好几只月的陷落,除了支持 Oracle
外,对框架内代码结构吧做了重重之调,现在,实体也支撑继承父类或接口,更加可喜可贺的凡啊支持了仓储过程,包括
output 参数与 Oracle 的 RefCurcor 返回结果集。与此同时,方便对 Chloe
的谢兴趣的校友念框架,官网也规范上丝了。

导航

  • Chloe.ORM
    • 前准备
    • 查询数据
      • 着力查询
      • 连查询
      • 聚合函数
      • 分组查询
    • 插入数据
    • 履新数据
    • 剔除数据
    • 积存过程
    • 支持函数
  • 坎坎坷坷
  • 结语

Chloe.ORM

事先备选

实体:

Oracle 1

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; }
}

Oracle 2

因框架本身要和实际的数据库让解耦,所以 OracleContext
构造函数需要一个 IDbConnectionFactory 的参数,IDbConnectionFactory
接口就出一个 CreateConnection() 方法,必须事先打个像样,实现
CreateConnection 方法:

Oracle 3

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 4

出于我于是的是 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>();

查询数据

基本查询

Oracle 5

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
 */

Oracle 6

老是查询

Oracle 7

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
 */

Oracle 8

聚合函数

Chloe 的集结查询有与 linq 差不多的接口,基本是同样看就是了解。

Oracle 9

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

q.Select(a => AggregateFunctions.Count()).First();
/*
 * SELECT COUNT(1) AS "C" FROM "USERS" "USERS" WHERE ROWNUM < 2
 */

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();
/*
 * 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
 */

var count = q.Count();
/*
 * SELECT COUNT(1) AS "C" FROM "USERS" "USERS"
 */

var longCount = q.LongCount();
/*
 * SELECT COUNT(1) AS "C" FROM "USERS" "USERS"
 */

var sum = q.Sum(a => a.Age);
/*
 * SELECT SUM("USERS"."AGE") AS "C" FROM "USERS" "USERS"
 */

var max = q.Max(a => a.Age);
/*
 * SELECT MAX("USERS"."AGE") AS "C" FROM "USERS" "USERS"
 */

var min = q.Min(a => a.Age);
/*
 * SELECT MIN("USERS"."AGE") AS "C" FROM "USERS" "USERS"
 */

var avg = q.Average(a => a.Age);
/*
 * SELECT AVG("USERS"."AGE") AS "C" FROM "USERS" "USERS"
 */

Oracle 10

分组查询

Chloe 的分组查询功能,可以像写 sql 一样支持 Having 和 Select。

Oracle 11

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

IGroupingQuery<User> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age);

g = g.Having(a => a.Age > 1 && AggregateFunctions.Count() > 0);

g.Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList();
/*
 * SELECT "USERS"."AGE" AS "AGE",COUNT(1) AS "COUNT",SUM("USERS"."AGE") AS "SUM",MAX("USERS"."AGE") AS "MAX",MIN("USERS"."AGE") AS "MIN",AVG("USERS"."AGE") AS "AVG" FROM "USERS" "USERS" WHERE "USERS"."ID" > 0 GROUP BY "USERS"."AGE" HAVING ("USERS"."AGE" > 1 AND COUNT(1) > 0)
 */

Oracle 12

插数据

方式1

因 lambda 表达式树的措施插入:

其一种方式插入的利是,可以指定列插入,就像写 sql 一样简单。 
同时,该办法插入返回表主键值。如果实体主键是自增列(序列),返回值就会见是由增值。

Oracle 13

/* User 实体打了序列标签,会自动获取序列值。返回主键 Id */
int id = (int)context.Insert<User>(() => new User() { Name = "lu", Age = 18, Gender = Gender.Man, CityId = 1, OpTime = DateTime.Now });
/*
 * SELECT "USERS_AUTOID"."NEXTVAL" FROM "DUAL"
 * Int32 :P_0 = 14;
   INSERT INTO "USERS"("NAME","AGE","GENDER","CITYID","OPTIME","ID") VALUES(N'lu',18,1,1,SYSTIMESTAMP,:P_0)
 */

Oracle 14

方式2

盖实体的法门插入:

欠法插入,如果一个实体是自增列,会自行将由增列设置及对应的属性上。

Oracle 15

User user = new User();
user.Name = "lu";
user.Age = 18;
user.Gender = Gender.Man;
user.CityId = 1;
user.OpTime = DateTime.Now;

//会自动将自增 Id 设置到 user 的 Id 属性上
user = context.Insert(user);
/*
 * SELECT "USERS_AUTOID"."NEXTVAL" FROM "DUAL"
 * Int32 :P_0 = 15;
   String :P_1 = 'lu';
   Int32 :P_2 = 1;
   Int32 :P_3 = 18;
   DateTime :P_4 = '2016/9/5 9:16:59';
   INSERT INTO "USERS"("ID","NAME","GENDER","AGE","CITYID","OPTIME") VALUES(:P_0,:P_1,:P_2,:P_3,:P_2,:P_4)
 */

Oracle 16

创新数据

方式1

坐 lambda 表达式树的措施创新:

欠措施化解之题目是:1.指定列更新;2.批量创新;3.支持类似 Age=Age + 100
这样创新字段。

Oracle 17

context.Update<User>(a => a.Id == 1, a => new User() { Name = a.Name, Age = a.Age + 100, Gender = Gender.Man, OpTime = DateTime.Now });
/*
 * UPDATE "USERS" SET "NAME"="USERS"."NAME","AGE"=("USERS"."AGE" + 100),"GENDER"=1,"OPTIME"=SYSTIMESTAMP WHERE "USERS"."ID" = 1
 */

//批量更新
//给所有女性年轻 10 岁
context.Update<User>(a => a.Gender == Gender.Woman, a => new User() { Age = a.Age - 10, OpTime = DateTime.Now });
/*
 * UPDATE "USERS" SET "AGE"=("USERS"."AGE" - 10),"OPTIME"=SYSTIMESTAMP WHERE "USERS"."GENDER" = 2
 */

Oracle 18

方式2

以实体的法子更新:

Oracle 19

User user = new User();
user.Id = 1;
user.Name = "lu";
user.Age = 28;
user.Gender = Gender.Man;
user.OpTime = DateTime.Now;

context.Update(user); //会更新所有映射的字段
/*
 * String :P_0 = 'lu';
   Int32 :P_1 = 1;
   Int32 :P_2 = 28;
   Nullable<Int32> :P_3 = NULL;
   DateTime :P_4 = '2016/9/5 9:20:07';
   UPDATE "USERS" SET "NAME"=:P_0,"GENDER"=:P_1,"AGE"=:P_2,"CITYID"=:P_3,"OPTIME"=:P_4 WHERE "USERS"."ID" = :P_1
 */


/*
 * 支持只更新属性值已变的属性
 */

context.TrackEntity(user);//在上下文中跟踪实体
user.Name = user.Name + "1";
context.Update(user);//这时只会更新被修改的字段
/*
 * String :P_0 = 'lu1';
   Int32 :P_1 = 1;
   UPDATE "USERS" SET "NAME"=:P_0 WHERE "USERS"."ID" = :P_1
 */

Oracle 20

剔除数据

方式1

盖 lambdaOracle 表达式树的点子去:

Oracle 21

context.Delete<User>(a => a.Id == 1);
/*
 * DELETE FROM "USERS" WHERE "USERS"."ID" = 1
 */

//批量删除
//删除所有不男不女的用户
context.Delete<User>(a => a.Gender == null);
/*
 * DELETE FROM "USERS" WHERE "USERS"."GENDER" IS NULL
 */

Oracle 22

方式2

因为实体的主意去:

Oracle 23

User user = new User();
user.Id = 1;
context.Delete(user);
/*
 * Int32 :P_0 = 1;
   DELETE FROM "USERS" WHERE "USERS"."ID" = :P_0
 */

Oracle 24

 

储存过程

经过囤过程获得一个 User 信息:

Oracle 数据库被,如果一个储存过程得回到结果集,需要靠 RefCursor
output 参数特性。用法如下:

Oracle 25

/* 必须先自定义 RefCursor 参数 */
OracleParameter p_cur = new OracleParameter();
p_cur.ParameterName = "p_cur";
p_cur.OracleDbType = OracleDbType.RefCursor;
p_cur.Direction = ParameterDirection.Output;

DbParam refCursorParam = new DbParam();
/* 将自定义 RefCursor 参数设置到 DbParam 的 ExplicitParameter 属性 */
refCursorParam.ExplicitParameter = p_cur;

DbParam id = new DbParam("id", 1);
User user = context.SqlQuery<User>("Proc_GetUser", CommandType.StoredProcedure, id,refCursorParam).FirstOrDefault();

Oracle 26

经囤过程的 output 参数获取一个用户之 name:

DbParam id = new DbParam("id", 1);
DbParam outputName = new DbParam("name", null, typeof(string)) { Direction = ParamDirection.Output };
context.Session.ExecuteNonQuery("Proc_GetUserName", CommandType.StoredProcedure, id, outputName);

支撑函数

Oracle 27

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

var space = new char[] { ' ' };

DateTime startTime = DateTime.Now;
DateTime endTime = startTime.AddDays(1);
var ret = q.Select(a => new
     {
         Id = a.Id,

         String_Length = (int?)a.Name.Length,//LENGTH("USERS"."NAME")
         Substring = a.Name.Substring(0),//SUBSTR("USERS"."NAME",0 + 1,LENGTH("USERS"."NAME"))
         Substring1 = a.Name.Substring(1),//SUBSTR("USERS"."NAME",1 + 1,LENGTH("USERS"."NAME"))
         Substring1_2 = a.Name.Substring(1, 2),//SUBSTR("USERS"."NAME",1 + 1,2)
         ToLower = a.Name.ToLower(),//LOWER("USERS"."NAME")
         ToUpper = a.Name.ToUpper(),//UPPER("USERS"."NAME")
         IsNullOrEmpty = string.IsNullOrEmpty(a.Name),//too long
         Contains = (bool?)a.Name.Contains("s"),//
         Trim = a.Name.Trim(),//TRIM("USERS"."NAME")
         TrimStart = a.Name.TrimStart(space),//LTRIM("USERS"."NAME")
         TrimEnd = a.Name.TrimEnd(space),//RTRIM("USERS"."NAME")
         StartsWith = (bool?)a.Name.StartsWith("s"),//
         EndsWith = (bool?)a.Name.EndsWith("s"),//

         /* oracle is not supported DbFunctions.Diffxx. */
         //DiffYears = DbFunctions.DiffYears(startTime, endTime),//
         //DiffMonths = DbFunctions.DiffMonths(startTime, endTime),//
         //DiffDays = DbFunctions.DiffDays(startTime, endTime),//
         //DiffHours = DbFunctions.DiffHours(startTime, endTime),//
         //DiffMinutes = DbFunctions.DiffMinutes(startTime, endTime),//
         //DiffSeconds = DbFunctions.DiffSeconds(startTime, endTime),//
         //DiffMilliseconds = DbFunctions.DiffMilliseconds(startTime, endTime),//
         //DiffMicroseconds = DbFunctions.DiffMicroseconds(startTime, endTime),//

         /* ((CAST(:P_0 AS DATE)-CAST(:P_1 AS DATE)) * 86400000 + CAST(TO_CHAR(CAST(:P_0 AS TIMESTAMP),'ff3') AS NUMBER) - CAST(TO_CHAR(CAST(:P_1 AS TIMESTAMP),'ff3') AS NUMBER)) / 86400000 */
         SubtractTotalDays = endTime.Subtract(startTime).TotalDays,//
         SubtractTotalHours = endTime.Subtract(startTime).TotalHours,//...
         SubtractTotalMinutes = endTime.Subtract(startTime).TotalMinutes,//...
         SubtractTotalSeconds = endTime.Subtract(startTime).TotalSeconds,//...
         SubtractTotalMilliseconds = endTime.Subtract(startTime).TotalMilliseconds,//...

         AddYears = startTime.AddYears(1),//ADD_MONTHS(:P_0,12 * 1)
         AddMonths = startTime.AddMonths(1),//ADD_MONTHS(:P_0,1)
         AddDays = startTime.AddDays(1),//(:P_0 + 1)
         AddHours = startTime.AddHours(1),//(:P_0 + NUMTODSINTERVAL(1,'HOUR'))
         AddMinutes = startTime.AddMinutes(2),//(:P_0 + NUMTODSINTERVAL(2,'MINUTE'))
         AddSeconds = startTime.AddSeconds(120),//(:P_0 + NUMTODSINTERVAL(120,'SECOND'))
         //AddMilliseconds = startTime.AddMilliseconds(20000),//不支持

         Now = DateTime.Now,//SYSTIMESTAMP
         UtcNow = DateTime.UtcNow,//SYS_EXTRACT_UTC(SYSTIMESTAMP)
         Today = DateTime.Today,//TRUNC(SYSDATE,'DD')
         Date = DateTime.Now.Date,//TRUNC(SYSTIMESTAMP,'DD')
         Year = DateTime.Now.Year,//CAST(TO_CHAR(SYSTIMESTAMP,'yyyy') AS NUMBER)
         Month = DateTime.Now.Month,//CAST(TO_CHAR(SYSTIMESTAMP,'mm') AS NUMBER)
         Day = DateTime.Now.Day,//CAST(TO_CHAR(SYSTIMESTAMP,'dd') AS NUMBER)
         Hour = DateTime.Now.Hour,//CAST(TO_CHAR(SYSTIMESTAMP,'hh24') AS NUMBER)
         Minute = DateTime.Now.Minute,//CAST(TO_CHAR(SYSTIMESTAMP,'mi') AS NUMBER)
         Second = DateTime.Now.Second,//CAST(TO_CHAR(SYSTIMESTAMP,'ss') AS NUMBER)
         Millisecond = DateTime.Now.Millisecond,//CAST(TO_CHAR(SYSTIMESTAMP,'ff3') AS NUMBER)
         DayOfWeek = DateTime.Now.DayOfWeek,//(CAST(TO_CHAR(SYSTIMESTAMP,'D') AS NUMBER) - 1)

         Int_Parse = int.Parse("1"),//CAST(N'1' AS NUMBER)
         Int16_Parse = Int16.Parse("11"),//CAST(N'11' AS NUMBER)
         Long_Parse = long.Parse("2"),//CAST(N'2' AS NUMBER)
         Double_Parse = double.Parse("3"),//CAST(N'3' AS BINARY_DOUBLE)
         Float_Parse = float.Parse("4"),//CAST(N'4' AS BINARY_FLOAT)
         Decimal_Parse = decimal.Parse("5"),//CAST(N'5' AS NUMBER)
         //Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),//不支持

         Bool_Parse = bool.Parse("1"),//
         DateTime_Parse = DateTime.Parse("1992-1-16"),//TO_TIMESTAMP(N'1992-1-16','yyyy-mm-dd hh24:mi:ssxff')

         B = a.Age == null ? false : a.Age > 1,
     }).ToList();

Oracle 28

坎坎坷坷

支持 Oracle,一开始自是不容(畏)绝(惧)的,这卖最好奇葩了-
-。后来考虑,反正肯定都得如支持,干脆拿它们为涉嫌了吧,免得“夜长梦多”!不过
Oracle 是真奇葩,烦!比如,Oracle 不克直接以蕴藏过程里直接实施 Select
sql 返回结果集,必须得靠它那个神马 RefCurcor
参数,这个自家真正万万没悟出,后来同等号园友提醒了才留意这特性!
再一个,Oracle 不支持 bool 类型,Oracle.ManagedDataAccess 这个叫的
DataReader 也非支持 GetBoolean 方法,同时 Oracle.ManagedDataAccess
创建的 DbCommand 默认是凡以一一方式绑定参数,因此,又不得不对 DataReader
和 DbCommand 包装一样一体才会用。如果的确如细数起来,Oracle
的糟点连起来量能绕地球一环抱!

结语

管 Oracle 给支持了,心中之石头为算是落下,生活自在了成千上万。作为众多 ORM
中为数不多能支持 Oracle 的一致朵成员,感兴趣之可以关心一波。或许,Chloe
真能让您带来不同等的觉得!更多详细用法敬请参照官网API文档。

艺教程或心得我反而不是老擅长写,我光想管日常开支的部分干货分享给大家,您的推荐是本人享受的极度特别动力。如果当
Chloe 这个开源项目对,望大家叫个赞,也得上 Github
关注还是储藏(star)一下,以便能够立时收到更新通知。同时,Chloe 官网以及根据
NFine
改造的后台后续也会见放出,有想的同室可以触发单关注,也欢迎广大C#亲生入群交流,畅谈.NET复兴大计。最后,感谢大家看至此!

Chloe.ORM 完全开源,遵循 Apache2.0 协议,托管给
GitHub,地址:https://github.com/shuxinqin/Chloe。

官网:http://www.52chloe.com 
官网后台:http://www.52chloe.com:82

相关文章