C#.NET调用达梦数据库的存储过程返回多张表-开发框架文库

C#.NET调用达梦数据库的存储过程返回多张表

要点

存储过程定义 OUT SYS_REFCURSOR 参数，每个OUT参数对应一个结果集；
.NET调用存储过程，需要创建 DmDbType.Cursor 参数用于接收结果集；

创建结果集参数：

C# 全选

var p = base.CreateParameter(item.ParamName, null, DbType.Object, ParameterDirection.Output) as DmParameter;
p.DmSqlType = DmDbType.Cursor;//OUT 游标参数

测试用的存储过程

单表 _Test_Product_Search - 产品资料

C# 全选

CREATE OR REPLACE PROCEDURE "CSFrameworkV6_Normal"."_Test_Product_Search" 
(
	productCode IN VARCHAR(50)=null,
	productName IN VARCHAR(50)=null,
	category IN VARCHAR(50)=null,
	barcode IN VARCHAR(50)=null,
    result1 OUT SYS_REFCURSOR
)
AS
BEGIN
	-- 打开第一个结果集
	OPEN result1 FOR SELECT top 10 * FROM "dt_Product" WHERE 1=1;

	-- 打开第二个结果集
    --	OPEN result2 FOR SELECT top 10 * FROM 表;
END;

多表 _Test_INV_Search - 销售发票

C# 全选

CREATE OR REPLACE PROCEDURE "CSFrameworkV6_Normal"."_Test_INV_Search" 
(
	INVNO IN VARCHAR(50)=null,
	CustomerCode IN VARCHAR(50)=null,
	PayType IN VARCHAR(50)=null,
	barcode IN VARCHAR(50)=null,
    result1 OUT SYS_REFCURSOR,
    result2 OUT SYS_REFCURSOR  
)
AS
BEGIN
	-- 打开第一个结果集
	OPEN result1 FOR SELECT top 10 * FROM "tb_IV" WHERE 1=1;

	-- 打开第二个结果集
    OPEN result2 FOR SELECT top 100 * FROM "tb_IVs" WHERE "IVNO" IN (SELECT TOP 10 "IVNO" FROM "tb_IV");
    
END;

测试存储过程

单表 _Test_Product_Search

C# 全选

            schema = "CSFrameworkV6_Normal";
            var db2 = DatabaseFactory.GetDatabase(DatabaseType.DaMeng, connStr, schema);
            var p1 = db2.CreateParameter("PRODUCTCODE", "A01");
            var p2 = db2.CreateParameter("PRODUCTNAME", "ABC");
            var ds = db2.GetDataSet("_Test_Product_Search", CommandType.StoredProcedure, p1, p2);
            ShowGrid(ds.Tables[0]);

C#.NET调用达梦数据库的存储过程返回多张表

多表 _Test_INV_Search

C# 全选

            var p1 = db2.CreateParameter("INVNO", "A01");
            var p2 = db2.CreateParameter("CUSTOMERCODE", "ABC");
            var ds = db2.GetDataSet("_Test_INV_Search", CommandType.StoredProcedure, p1, p2);

C#.NET调用达梦数据库的存储过程返回多张表

达梦存储过程返回多个结果集 - 员工档案

C# 全选

CREATE OR REPLACE PROCEDURE "CSFrameworkV6_Normal"."usp_EmployeeQueryReportData" 
(
	InfoID IN VARCHAR(100),
    result1 OUT SYS_REFCURSOR,
    result2 OUT SYS_REFCURSOR  
)
AS
BEGIN
	
	OPEN result1 FOR 
	SELECT e.*,
	    ec."ContractNo",
	    ec."ValidateDateBegin",
	    ec."ValidateDateEnd",
		epa."DeptAfter",--最新部门			
		DATEDIFF(DAY,e."Birthday",SYSDATE) AS Ages, --年龄
		DATEDIFF(DAY,e."EntryFactoryDate",SYSDATE) AS WorkYears --连续工龄
    FROM "dt_Employee" as e
    left join "dt_EmployeeContract" as ec on e."InfoID"=ec."InfoID"
	left join "dt_EmployeePositionAdjustment" as epa on e."InfoID"=epa."InfoID"
	WHERE e."InfoID"=InfoID
	ORDER BY ec."CreationDate",epa."CreationDate" DESC;
		
	OPEN result2 FOR SELECT * FROM "dt_EmployeeContract" WHERE "InfoID"=InfoID;		

END;