Hi everyone,
I have a truly weird memory leak when using the CommandBuilder through System.Data.Common and the ODP.net factory. As soon as I get the UpdateCommand from the CommandBuilder a new DataReader is open and left hanging until the connection is closed. I know the reader is not closed because I eventually run into a "ORA-01000: maximum open cursors exceeded" error and because by looking at a private list in the connection object I can see the open DataReaders.
The problem can be reproduced with this simple code:
DbProviderFactory factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = "Data Source=<SID>;User Id=<USER>;Password=<PWD>";
connection.Open();
DataSet ds = new DataSet();
DbCommand cmd = factory.CreateCommand();
cmd.CommandText = "SELECT * FROM <SOME_TABLE>";
cmd.Connection = connection;
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds);
DbCommandBuilder builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.UpdateCommand = (Oracle.DataAccess.Client.OracleCommand)builder.GetUpdateCommand(); //when this line runs a new Cursor (DataReader) is opened and never closed regardless of the calls to Dispose() below
builder.Dispose();
adapter.Dispose();
cmd.Dispose();
ds.Dispose();
I can see the open cursor in the database but also in the connection object with this watch expression in Visual Studio:
((Oracle.DataAccess.Client.OracleConnection)(connection)).m_DataReaderList.Count
You should be able to see the above incremented as soon as the GetUpdateCommand() is called
To make this nicer the problem goes away if I don't use the factory class and have a reference to Oracle.DataAccess:
Oracle.DataAccess.Client.OracleConnection connection = new Oracle.DataAccess.Client.OracleConnection();
connection.ConnectionString = "Data Source=<SID>;User Id=<USER>;Password=<PWD>";
connection.Open();
DataSet ds = new DataSet();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
cmd.CommandText = "SELECT * FROM <SOME TABLE>";
cmd.Connection = connection;
Oracle.DataAccess.Client.OracleDataAdapter adapter = new Oracle.DataAccess.Client.OracleDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds);
Oracle.DataAccess.Client.OracleCommandBuilder builder = new Oracle.DataAccess.Client.OracleCommandBuilder();
builder.DataAdapter = adapter;
adapter.UpdateCommand = builder.GetUpdateCommand(true);
I'm using ODAC112021 (Oracle.DataAccess is version 4.112.2.0). I tried ODAC1120320 with the same result.
If I use the factory just to create the CommandBuilder the problem is still there.
Any ideas? Any suggestions to write the code using the factory slightly different so I can "avoid" the leak?
Any idea will be welcomed.
Thanks.