Page 1 of 1

Inconsistent parameter result when ParameterCheck = true

Posted: Mon 01 Oct 2018 15:09
by wdnichols
I am creating a command object for a stored procedure like this:

Code: Select all

using (var command = new PgSqlCommand("PKG_PROCESS_UPSERT_PROCESS", dbConnection)
{
    ParameterCheck = true,
    CommandType = CommandType.StoredProcedure
})
{
    // At this point, command.Parameters.Count should be 21. 
}
After creation, I should have 21 parameters, because that's how many parameters my stored procedure has. Occasionally, the parameter count is 21, but most of the time (90% of the time or more), command.Parameters is empty.

I have not found a pattern to when this works.

To deal with this, I have modified my code like this:

Code: Select all

using (var command = new PgSqlCommand("PKG_PROCESS_UPSERT_PROCESS", dbConnection)
{
    ParameterCheck = true,
    CommandType = CommandType.StoredProcedure
})
{
    if (command.Parameters.Count <= 0)
    {
        command.ParameterCheck = false;
        PgSqlCommandBuilder.DeriveParameters(command);
    }
    // At this point, command.Parameters.Count is ALWAYS 21. 
}
This allows me to continue with the correct parameter list.

What is going on?

Is there a bug in ParameterCheck with regard to stored procedures?

Should I just skip ParameterCheck and always depend on the DeriveParameters method for stored procedures?

Regards,
Bill.

Re: Inconsistent parameter result when ParameterCheck = true

Posted: Fri 05 Oct 2018 09:56
by Pinturiccio
This is a designed behavior. For CommandType.StoredProcedure, changes occur only after the command is prepared or executed. For more information, please refer to https://www.devart.com/dotconnect/oracl ... l#autosync

You can use the following code:

Code: Select all

using (var command = new PgSqlCommand("PKG_PROCESS_UPSERT_PROCESS", dbConnection)
{
    ParameterCheck = true,
    CommandType = CommandType.StoredProcedure
})
{
command.Prepare();
}