How to add Table Type as a Out Parameter to Dapper Stored Procedure in C#

Question

I'm using dapper to connect to Oracle Database for my .Net Core Microservices application.

In a stored procedure, I have one out parameter like this.

PROCEDURE INSERT_PLAN_TEST
(
   ... other parameters goes here....
   P_PARENT_PLAN_ID           MFP_PLAN.PLAN_HEADER.PARENT_PLAN_ID%TYPE DEFAULT NULL,
   OUT_PLAN_ID                OUT NUMBER,  
   O_ERR_CODE                 OUT ERR_CODE    
)
IS

Here, the O_ERR_CODE is of type TABLE OF varchar2(200)

Now, In my C# method, How I can pass such parameter?

public async Task<int> CreatePlanAsync(PlanHeader planHeader)
{
    using (IDbConnection connection = _context.Database.GetOracleConnection())
    {
        connection.Open();
        var _params = new DynamicParameters();
        _params.Add("P_ORG_ID", planHeader.OrgId, DbType.Int32, ParameterDirection.Input);
        ..... other parameters goes here.....
       _params.Add("O_ERR_CODE", null, <here i need to specify the type>, ParameterDirection.Output);
    }
}

Please help me on this.

Do You use return dynamic object on return c#? – CelzioBR Dec 20 '19 at 13:11 — CelzioBR, Dec 20 '19 at 13:11

score 1 · Answer 1 · answered Dec 13 '19 at 13:22

1

I assume, You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported.

answered Dec 13 '19 at 13:22

Ravi Kumar Kasim

184
9

How to add Table Type as a Out Parameter to Dapper Stored Procedure in C#

1 Answers1