轻量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);