.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);