[转]高格调开源工具Chloe.O劲客M:协助存款和储蓄进程与Oracle

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

扯淡

那是一款高品质的.NET C#数据库访问框架(O安德拉M)。查询接口借鉴 Linq。借助
lambda
表达式,可以完全用面向对象的法门就能轻轻松松实施多表连接查询、分组查询、聚合查询、插入数据、批量刨除和创新等操作。

实质上,早在五个月前,Chloe 就早已帮衬 Oracle 数据库了,只是LZ个人平日用
Oracle 不多,Oracle 较别的数据库稍微特别,由此,并不曾当即揭露,生怕 bug
连连。经过好多少个月的沉淀,除了帮助 Oracle
外,对框架之中代码结构也做了不少的调整,以往,实体也支撑继承父类或接口,尤其可喜可贺的是也协助了蕴藏进度,包涵output 参数以及 Oracle 的 RefCurcor 重临结果集。与此同时,方便对 Chloe的感兴趣的校友学习框架,官网也规范上线了。

导航

Chloe.ORM

前面备选

实体:

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

图片 2

因为框架自己必要与具象的数据库驱动解耦,所以 OracleContext
构造函数必要一个 IDbConnectionFactory 的参数,IDbConnectionFactory
接口唯有2个 CreateConnection() 方法,必须先建个类,完成CreateConnection 方法:

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

图片 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>();

查询数据

主干查询

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

图片 6

老是查询

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

图片 8

聚合函数

Chloe 的汇聚查询全体和 linq 差不离的接口,基本是一看就知道。

图片 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"
 */

图片 10

分组查询

Chloe 的分组查询作用,能够像写 sql 一样扶助 Having 和 Select。

图片 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)
 */

图片 12

插入数据

方式1

以 lambda 表达式树的办法插入:

此种情势插入的便宜是,能够内定列插入,就像写 sql 一样简单。 
并且,该形式插入再次来到表主键值。假若实体主键是自增列(连串),重返值就会是自增值。

图片 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)
 */

图片 14

方式2

以实体的办法插入:

该措施插入,尽管四个实体存在自增列,会自动将自增列设置到对应的本性上。

图片 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)
 */

图片 16

履新数据

方式1

以 lambda 表明式树的情势立异:

该措施缓解的难点是:1.钦赐列更新;2.批量更新;3.辅助类似 Age=Age + 100
那样立异字段。

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

图片 18

方式2

以实体的措施立异:

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

图片 20

剔除数据

方式1

以 lambda 表明式树的方法删除:

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

图片 22

方式2

以实体的办法删除:

图片 23

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

图片 24

 

仓储进程

通过存储进度获得三个 User 音讯:

Oracle 数据库中,倘若2个储存进程必要再次回到结果集,须求借助 RefCursor
output 参数性情。用法如下:

图片 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();

图片 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);

支撑函数

图片 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();

图片 28

坎坎坷坷

援救 Oracle,一开首作者是拒(畏)绝(惧)的,那货太奇葩了-
-。后来想想,反正肯定都得要协助,干脆把它给干了啊,免得“朝令暮改”!然而Oracle 是真奇葩,烦!比如,Oracle 无法一贯在仓库储存进程里平素实施 Select
sql 再次来到结果集,必须得凭借它丰硕神马 RefCurcor
参数,这几个本人实在万万没悟出,后来一位园友提示了才留意这一个特点!
再3个,Oracle 不帮忙 bool 类型,Oracle.ManagedDataAccess 那一个驱动的
DataReader 也不支持 GetBoolean 方法,同时 Oracle.ManagedDataAccess
成立的 DbCommand 私下认可是是以一一情势绑定参数,因而,又不得不对 DataReader
和 DbCommand 包装三回才能用。倘使确实要细数起来,Oracle
的糟点连起来测度能绕地球一圈!

结语

把 Oracle 给帮助了,心中的石块也终究落下,生活自在了许多。作为广大 O逍客M
中为数不多能支撑 Oracle 的一枚成员,感兴趣的能够关切一波。只怕,Chloe真能给您带来不均等的感到!越多详细用法敬请参照官网API文档。

技能教程或心得我倒不是很善于写,我只想把一般支出的有个别干货分享给我们,您的引进是本人享受的最大引力。借使觉得
Chloe 这些开源项目不错,望大家给个赞,也可以上 Github
关心或储藏(star)一下,以便能立即收到更新布告。同时,Chloe 官网以及基于
NFine
改造的后台后续也会释放,有期待的同桌能够点个关注,也欢迎广大C#亲生入群沟通,畅谈.NET复兴大计。最终,多谢大家阅读至此!

Chloe.O揽胜极光M 完全开源,遵循 Apache2.0 协议,托管于
GitHub,地址:https://github.com/shuxinqin/Chloe

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

相关文章