Using(UniDataReader) does not properly dispose of connection after termination.

[mmoon]

Devart’s UniDataReader has been used for a while and its functioned to its purpose however recently Connection Pooling, initially disabled, has been enabled to improve performance and efficient when it comes to managing connections. The issue is after it was turned on it was found that not all of the connections were getting disposed of correctly, in time no connections were available, this lead to a unhandled UniException about the max pool size has been reached so it timed out. Details included below

The exception message:

“An unhandled exception of type 'Devart.Data.Universal.UniException' occurred in Devart.Data.Universal.dll
Additional information: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.”

Call Stack:
{Devart.Data.Universal.UniException (0x80004005): Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at Devart.Common.DbConnectionBase.Open()
at Devart.Data.Universal.UniConnection.Open()
at Devart.Data.Universal.UniConnection.Open()
at UniDBConnection_FB21089.FrmUniDBConnection.btnBeginUniConnections_Click(Object sender, EventArgs e) in c:\Users\Scott James\Documents\Visual Studio 2013\Projects\UniDBConnection_FB21089\UniDBConnection_FB21089\frmUniDBConnection.cs:line 30
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at UniDBConnection_FB21089.Program.Main() in c:\Users\Scott James\Documents\Visual Studio 2013\Projects\UniDBConnection_FB21089\UniDBConnection_FB21089\Program.cs:line 19
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()}

The UniDataReader is used inside of a Using statement and should be disposing of its self and its connection once the Using statement terminates but this does not seem to be happening.

This issue has been investigated and recreated in a separate project in order to try isolating the issue. The problem has been found and recreated in the function “beginUniConnections”, included below is the source code of the issue. In the source code there is also the function “beginSqlConnections” identical to the previously mentioned function but instead of Devart’s libraries it uses Microsoft’s libraries. The problem does not seem to be happening with the Microsoft equivalent SqlDataReader as it is disposing of the underlying connection correctly so it can be returned to the pool.

An attempt was made to use the CommandBehavior.CloseConnection with the UniCommands however it didn’t resolve the issue. We then attempted another method at forcing the reader to close its connection with the function “SetConnectionCloseInDataReaderUsingReflection” however this had no affect either.

Code: Select all

private void btnBeginUniConnections_Click(object sender, EventArgs e)
        {
            string connectionString = "Provider=SQL Server;Data Source=SJ-OPT9010\\SRJSQLServer;Initial Catalog=AutomatedRegression;Integrated Security=True";
            for (int i = 0; i < 200; i++)
            {
                UniConnection t;
                UniCommand myCommand = new UniCommand("SELECT * FROM SRJTestTable");
                t = new UniConnection(connectionString);

                myCommand.Connection = t;
                t.Open();
                UniDataReader myReader;
                using (myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    myReader = SetConnectionCloseInDataReaderUsingReflection(myReader);
                    while (myReader.Read())
                    {
                        string name = (string) myReader["name"];
                    }
                }
            }
        }

        private void btnBeginSqlConnections_Click(object sender, EventArgs e)
        {
            string connectionString = "Data Source=SJ-OPT9010\\SRJSQLServer;Initial Catalog=AutomatedRegression;Integrated Security=True";
            for (int i = 0; i < 200; i++)
            {
                SqlConnection t;
                SqlCommand myCommand = new SqlCommand("SELECT * FROM SRJTestTable");
                t = new SqlConnection(connectionString);

                myCommand.Connection = t;
                t.Open();
                SqlDataReader myReader;
                using (myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (myReader.Read())
                    {
                        string name = (string)myReader["name"];
                    }
                }
            }
        }

        private UniDataReader SetConnectionCloseInDataReaderUsingReflection(UniDataReader reader)
        {
            Type readerType = reader.GetType();
            // 'b' is the IDataReader field in UniDb's UniDataReader object. 
            FieldInfo iDataReaderFieldInfo = readerType.GetField("b", BindingFlags.NonPublic | BindingFlags.Instance);
            Object iDataReaderField = iDataReaderFieldInfo.GetValue(reader);
            Type iDataReaderType = iDataReaderField.GetType();
            FieldInfo commandBehaviorFieldInfo = iDataReaderType.GetField("_commandBehavior", BindingFlags.NonPublic | BindingFlags.Instance);
            commandBehaviorFieldInfo.SetValue(iDataReaderField, CommandBehavior.CloseConnection);

            return reader;
        }

[Shalex]

Thank you for your report. We have reproduced the bug with leaving connection in the Open state by UniCommand.ExecuteReader(CommandBehavior.CloseConnection). We will notify you when the issue is fixed.

As a workaround, please close connection manually or employ the using block for the connection:

Code: Select all

private void Workaround_1()
        {
            string connectionString = "Provider=SQL Server;Data Source=SJ-OPT9010\\SRJSQLServer;Initial Catalog=AutomatedRegression;Integrated Security=True";
            for (int i = 0; i < 200; i++)
            {
                UniConnection t;
                UniCommand myCommand = new UniCommand("SELECT * FROM SRJTestTable");
                t = new UniConnection(connectionString);

                myCommand.Connection = t;
                t.Open();
                UniDataReader myReader;
                using (myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    // myReader = SetConnectionCloseInDataReaderUsingReflection(myReader);
                    while (myReader.Read())
                    {
                        string name = (string) myReader["name"];
                    }
                }
                t.Close(); // added
            }
        }

private void Workaround_2()
        {
            string connectionString = "Provider=SQL Server;Data Source=SJ-OPT9010\\SRJSQLServer;Initial Catalog=AutomatedRegression;Integrated Security=True";
            for (int i = 0; i < 200; i++)
            {
                using (var t = new UniConnection(connectionString)) { // added
                    UniCommand myCommand = new UniCommand("SELECT * FROM SRJTestTable");

                    myCommand.Connection = t;
                    t.Open();
                    UniDataReader myReader;
                    using (myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)) {
                        // myReader = SetConnectionCloseInDataReaderUsingReflection(myReader);
                        while (myReader.Read()) {
                            string name = (string) myReader["name"];
                        }
                    }
                }
            }
        }

[mmoon]

Thank you for your reply. We will use the work around and look forward to hearing from you once the issue is fixed.

[Shalex]

The bug with leaving connection in the Open state by UniCommand.ExecuteReader(CommandBehavior.CloseConnection) is fixed: viewtopic.php?f=4&t=34377.