学习simple.data之高级篇

一、调用存储过程

1、不带参数

CREATE PROCEDURE ProcedureWithoutParamsAS SELECT * FROM ORDER;

调用db.ProcedureWithoutParams();

2、带参数

CREATE PROCEDURE ProcedureWithoutParams@ORDERCODE VARCHAR(MAX)AS SELECT * FROM ORDER WHERE ORDERCODE=@ORDERCODE;

调用db.ProcedureWithoutParams(1);

或者db.ProcedureWithoutParams(ORDERCODE:1);

二、事务

using (var transaction = db.BeginTransaction()){   transaction.Users.Insert(Name: "张三", Age: 20);   transaction.Commit();}

三、分页

1、获取多条数据,没有跳过数据

IEnumerable<dynamic> q = db.Order.All().Take(10).ToList<dynamic>();

2、跳过多条记录返回多条数据

IEnumerable<dynamic> q = db.Order.All().Skip(10).Take(10).ToList<dynamic>();

四、Simple.Data.Oracle使用心得

1、Sequence使用

db.Customer.Insert(CustomerId:Sequence.Next("Customers_SEQ"));

加入CustomerId作为Order的外键，新增时需要获取到生成的Sequence赋值给Order

using (var tx = db.BeginTransaction()){db.Customer.Insert(CustomerId:Sequence.Next("Customers_SEQ"));db.Order.Insert(CustomerId:Sequence.Current("Customers_SEQ"));tx.Commit();}

2、存储过程调用

目前是不支持游标返回

create or replace PROCEDURE ORDERCOUNTBYFACTORYNAME(V_FACTORYNAME  IN VARCHAR,V_COUNT OUT NUMBER) ISbeginselect COUNT(*) INTO V_COUNT FROM ORDER OINNER JOIN ORDERDETAIL OD on O.PKID= OD.ORDERIDWHERE O.FACTORYNAME = V_FACTORYNAME ;end ORDERCOUNTBYFACTORYNAME;

调用方法是由：包头名称+__+存储过程名称组成

var result = db.PKG__ORDERCOUNTBYFACTORYNAME("SO20140917");var count = result.OutputValues["V_COUNT "];