Inconsistent parameter result when ParameterCheck = true

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
wdnichols
Posts: 8
Joined: Thu 24 May 2018 12:49

Inconsistent parameter result when ParameterCheck = true

Post by wdnichols » Mon 01 Oct 2018 15:09

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.

Pinturiccio
Devart Team
Posts: 2314
Joined: Wed 02 Nov 2011 09:44

Re: Inconsistent parameter result when ParameterCheck = true

Post by Pinturiccio » Fri 05 Oct 2018 09:56

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();
}

Post Reply