Broken Clear() Method in ODP.Net OracleParameterCollection

Due to the Broken Clone in ODP.Net OracleParameter Class, I was thinking to reuse the parameters instead cloning. My effort was unfortunately fruitless with another piece of broken OPD.Net code.

Ideally, I should be able to reuse my parameters as longer as I don't use them concurrently. I should be able to bind the parameter to a command, execute it, and some how release the parameter from that command for reuse, right? Naturally, you would think IDbCommand.Dispose() should release bound parameters. Wrong! None of three major ADO clients that I tested does this.

To trace if a parameter was already added to a collection, all three major clients, System.Data.SqlClient, System.Data.OracleClient and Oracle.DataAccess.Client, stores the collection back to each parameter added to it. So while the parameter collection holds references to all the added parameters, all parameters also hold a reference back to the collection. Thus, as long as you hold on one of those, none of them is GC'able. A good contributor to potential memory leakage.

The last hope is calling IDbCommand.Parameters.Clear(). That must releases all the parameters from the collection made them eligible for reuse. If that works, we can always call Clear() before dispose the IDbCommand. It indeed works for both System.Data clients. But ODP.Net's Clear() is again broken it doesn't remove the reference from the parameters when the collection itself is cleared. Even more friendly to memory leakage.

Here is the code that I used to do the test.

        static void SqlParameterTest()
        {
            Console.WriteLine("== Syste.Data.SqlClient ==");
            ParameterTest(
                new System.Data.SqlClient.SqlParameter(),
                new System.Data.SqlClient.SqlCommand(),
                new System.Data.SqlClient.SqlCommand());
            Console.WriteLine("== System.Data.OracleClient ==");
            ParameterTest(
                new System.Data.OracleClient.OracleParameter(),
                new System.Data.OracleClient.OracleCommand(),
                new System.Data.OracleClient.OracleCommand());
            Console.WriteLine("== Oracle.DataAccess.Client ==");
            ParameterTest(
                new Oracle.DataAccess.Client.OracleParameter(),
                new Oracle.DataAccess.Client.OracleCommand(),
                new Oracle.DataAccess.Client.OracleCommand());
        }

        private static void ParameterTest(IDbDataParameter parameter, IDbCommand command1, IDbCommand command2)
        {
            command1.Parameters.Add(parameter);
            try
            {
                Console.WriteLine("1. Add again to same command.");
                command1.Parameters.Add(parameter);
                Console.WriteLine("\tSucess");
            }
            catch (Exception e)
            {
                Console.WriteLine("\tFailure: " + e.Message);
            }
            command1.Dispose();
            try
            {
                Console.WriteLine("2. Add to 2nd command after disposes the 1st one.");
                command2.Parameters.Add(parameter);
                Console.WriteLine("\tSucess");
            }
            catch (Exception e)
            {
                Console.WriteLine("\tFailure: " + e.Message);
                command1.Parameters.Clear();
                try
                {
                    Console.WriteLine("3. Add to 2nd command after clear parameters of 1st one.");
                    command2.Parameters.Add(parameter);
                    Console.WriteLine("\tSucess");
                }
                catch (Exception e2)
                {
                    Console.WriteLine("\tFailure: " + e2.Message);
                }
            }
        }

And the result is below. You can see that ODP.Net failed in all three use cases while others all get success in the 3rd test case.

== Syste.Data.SqlClient ==
1. Add again to same command.
	Failure: The SqlParameter is already contained by another SqlParameterCollection.
2. Add to 2nd command after disposes the 1st one.
	Failure: The SqlParameter is already contained by another SqlParameterCollection.
3. Add to 2nd command after clear parameters of 1st one.
	Sucess
== System.Data.OracleClient ==
1. Add again to same command.
	Failure: The OracleParameter is already contained by another OracleParameterCollection.
2. Add to 2nd command after disposes the 1st one.
	Failure: The OracleParameter is already contained by another OracleParameterCollection.
3. Add to 2nd command after clear parameters of 1st one.
	Sucess
== Oracle.DataAccess.Client ==
1. Add again to same command.
	Failure: OracleParameter object is already contained in a collection
2. Add to 2nd command after disposes the 1st one.
	Failure: OracleParameter object is already contained in a collection
3. Add to 2nd command after clear parameters of 1st one.
	Failure: OracleParameter object is already contained in a collection