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]);
多表 _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# 全选
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;
版权声明：本文为开发框架文库发布内容,转载请附上原文出处连接
NewDoc C/S框架网
