轻量ORM-SqlRepoEx (五) 存储过程操作
.net平台下兼容.net standard 2.0,一个实现以lambda表达式转转换标准sql语句,使用强类型操作数据的轻量级orm工具,在减少魔法字串同时,通过灵活的lambda表达式组合,实现业务数据查询的多样性。
一、存储过程数据获取
1、存储过程
create procedure [dbo].[custorderhist] @customerid nchar(5)
as
select productname, total=sum(quantity)
from products p, [order details] od, orders o, customers c
where c.customerid = @customerid
and c.customerid = o.customerid and o.orderid = od.orderid and od.productid = p.productid
group by productname
2、实例一个执行器
string cnstr = "data source=(local);initial catalog=northwind;user id=test;password=test";
connectionstringconnectionprovider connectionprovider = new connectionstringconnectionprovider(cnstr);
istatementexecutor target = new statementexecutor(new sqllogger(new list<isqllogwriter>() { new noopsqllogger() }), connectionprovider);
3、参数定义
var paramdef = new parameterdefinition[]
{
new parameterdefinition
{
name = "customerid",
value = "alfki"
}
};
4、调用executestoredprocedure获取一个idatareader
idatareader datareader = target.executestoredprocedure("custorderhist", paramdef);
while (datareader.read())
{
console.writeline($"productname: {datareader["productname"]},total: {datareader["total"]}");
}
二、inputoutput、output参数
1、存储过程
alter procedure [dbo].[mytestoutparam]
( @testint int output,@teststr nvarchar(50) output )
as
begin
select @testint=100+@testint, @teststr='test out put 测试'
end
2、参数定义
var paramdef2 = new parameterdefinition[]
{ new parameterdefinition
{ name = "@testint",
dbtype=dbtype.int32,
direction=parameterdirection.inputoutput,
value=99, },
new parameterdefinition
{ name = "@teststr",
dbtype=dbtype.string,
direction=parameterdirection.output,
size=100 }
};
3、调用executestoredprocedure
var datareader2 = target.executestoredprocedure("mytestoutparam", paramdef2);
4、调用getparametercollection方法获取返回值
datareader2.getparametercollection(paramdef2);