C# ADO.NET IBM DB2 named parameters with same name throws Not enough parameters specified Exception

Question

I have a fairly agnostic ADO.NET application that connects to a number of databases and is able to extract the necessary information to run. I have hit a snag with DB2 and how it handles named parameters, particularly when I reuse a named parameter in the same query. I know of a couple of ways to get around this by simply adding more parameters, but in theory it should work as it does on other databases that I connect to as the parameter name is the same.

What I'm doing is a bit more complicated and involves subqueries etc, but to demonstrate, take the following query:

select value from test.table where cola=@key1 and colb=@key1;

The named parameter @key1 is used twice.

My code is as follows:

       try
        {
            DbProviderFactory dbfFactory = DbProviderFactories.GetFactory("IBM.Data.DB2.iSeries");
            using (DbConnection dbConnection = dbfFactory.CreateConnection())
            {
                dbConnection.ConnectionString = "DataSource=xxx.xxx.xxx.xxx;UserID=xxxxxxxx;password=xxxxxxxxx";

                using (DbCommand dbCommand = dbConnection.CreateCommand())
                {
                    IDbDataParameter iddpParameter1 = dbCommand.CreateParameter();
                    iddpParameter1.ParameterName = "@key1";
                    iddpParameter1.DbType = DbType.String;
                    iddpParameter1.Value = "1";

                    dbCommand.Parameters.Add(iddpParameter1);
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.CommandText = "select value from test.table where cola=@key1 and colb=@key1";
                    dbConnection.Open();

                    using (IDataReader idrReader = dbCommand.ExecuteReader())
                    {
                        while (idrReader.Read())
                        {
                                   ...
                        }
                    }
                }

            } // end dbConnection
        } // end try

        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }

When I run this I get an exception that tells me:

System.InvalidOperationException: Not enough parameters specified.  The command requires 2 parameter(s), but only 1 parameter(s) exist in the parameter collection.

I get what it is telling me, but I'm looking for help in figuring out how I can have the provider use the named parameter for both parameters as they are the same. It seems that it is doing a blind count of named parameters and not realizing that they are the same named parameters. SQL Server seems to allow me to do this with the same code above. I'm guessing it's just one of those differences in the providers, but hoping someone has run into this and has a solution for DB2 that doesn't get into specific DB2 code.

Thanks, appreciate the assistance.

Answer 1

well I did a little more digging, and I wonder if it might be the connector that you are using. So I'm doing the following (which is very similar to what you are doing)

in my app config file I have

<connectionStrings>
    <add name="AWOLNATION" providerName="Ibm.Data.DB2" connectionString="Server=sail:50000;Database=Remix;" />
</connectionStrings>

in my Databasemanager class I would initialize it like so

    public static DatabaseManager Instance(string connectionStringName)
    {
        var connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionStringName];
        if (connectionStringSettings == null) throw new MissingMemberException("[app.config]", string.Format("ConnectionStrings[{0}]", connectionStringName));
        return new DatabaseManager(connectionStringSettings);
    }

    private DatabaseManager(ConnectionStringSettings connectionInformation)
    {
        _connectionInformation = connectionInformation;
        _parameters = new Dictionary<string, object>();
    }
    private void Initialize()
    {
        _connection = DbProviderFactories.GetFactory(_connectionInformation.ProviderName).CreateConnection();
        _connection.ConnectionString = _connectionInformation.ConnectionString;

        _command = _connection.CreateCommand();
    }

I add parameters a little different though. I have a Dictionary<string,object> that I add too when setting up my query. To use your example I would have had this

    public IEnumerable<object> GetSomething(string key)
    {
        var sql = "select value from test.table where cola = @key1 and colb = @key1";
        _manager.AddParameter("@key1", key);

        return _manager.ExecuteReader<object>(sql, ToSomethignUseful);
    }

    private object ToSomethignUseful(DatabaseManager arg)
    {
        return new { Value = arg.GetArgument<object>("value") };
    }

then reading is where the OP and I have similar code

    public IEnumerable<T> ExecuteReader<T>(string sql, Func<DatabaseManager, T> conversionBlock)
    {
        Initialize();
        using (_connection)
        {
            _connection.Open();
            _command.CommandText = sql;
            _command.CommandType = CommandType.Text;

            if (_parameters.Count > 0)
                AddParameters(_command, _parameters);

            _parameters.Clear();

            using (_reader = _command.ExecuteReader())
            {
                while (_reader.Read())
                {
                    yield return conversionBlock(this);
                }
            }
        }
    }
    private static void AddParameters(DbCommand command, Dictionary<string, object> parameters)
    {
        foreach (var param in parameters)
        {
            command.Parameters.Add(CreateParameter(command, param.Key, param.Value));
        }
    }

    private static DbParameter CreateParameter(DbCommand command, string key, object value)
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = key;
        parameter.Value = value;

        return parameter;
    }

running said code is working for me, so I wonder if the difference is in the provider that we are using. I'm using named parameters in production and have been for atleast a year now, possibly closer to 2 years.

I will say that I did get the same error when essentially running the same code twice, as shown in this code

    public static void IndendedPrintForEach<T>(this IEnumerable<T> array, string header, Func<T, string> consoleStringConverterMethod)
    {
        var list = array.ToList();
        var color = Console.ForegroundColor;
        Console.ForegroundColor = ConsoleColor.Magenta;
        Console.WriteLine($"<<<{header}>>>");
        Console.ForegroundColor = color;

        if (!list.Any())
        {
            Console.ForegroundColor = ConsoleColor.DarkRed;
            Console.WriteLine("    ************NoItemsFound************");
            Console.ForegroundColor = color;
        }
        else
        {
            foreach (var item in list)
                Console.WriteLine($"    {consoleStringConverterMethod(item)}");
        }
    }

on line 3 var list = array.ToList() was the fix to the problem that you were seeing for me. before I had if (!array.Any()) which would run the query and use the parameters (which I clear out before I execture the query) then when I go to enumerate through and print each item in the array I was then getting the error. For me the problem was that it was re-running the query which I had no more parameters. The fix was to enumerate the query with the ToList() and then do my checking and printing on the list.

Answer 2

You answered you own question: "Unfortunately, I have not found a solution. I had to create another named parameter and just assign it the same value"

Oracle/DB2/Sybase especially are difficult with SQL queries and parameters.

1. Parameters in the SQL query should be in the same order they are added to the C# parameters are added to the C# SQL command (Oracle, Sybase)
2. Put parenthesis around the SQL query where clause parts using parameters (all)
3. Make sure the SQL data types are matching the C# parameter data types (all)
4. Check for overflow/underflow of parameter data so that the SQL query does not error
5. Pass in null/empty string in the appropriate format for the database. Ideally, create C# SQLParameter create methods to create the parameter in the correct format for the database

Oracle is particularly finicky about this. Take the time to build a C# wrapper library to construct a C# query object correctly, construct C# parameters correctly and add the C# SQL parameters to the query.

Put in notes that the query parameter add order should match the order of "@" parameters in the SQL query.

This wrapper library is you documentation for you and the next developer to avoid the problems you've encountered.