Passing IDataReader to inherited classes not closing connections ExecuteDataReaderAsync in C#

Question

I am having a serious problem and I am not sure how to fix it. I have multiple libraries pointing to a main data provider C# library. I set it up to perform asynchronous calls to ExecuteDataReaderAsync. However, the SQL connections are not closing as expected so I'm getting SQL connection pool errors.

I tried wrapping the SqlConnection in a using however the IDataReader does not get returned since it's outside the scope of the using.

Here is my main data provider method (DataProvider.cs)

public virtual async Task<SqlDataReader> ExecuteDataReaderAsync(string StoredProcedureName, params object[] Parameters)
        {
            InitDatabase();

            var connection = new SqlConnection(databaseControllers[connectionStringName].ConnectionString);


            var cmd = new SqlCommand()
            {
                Connection = connection,
                CommandType = CommandType.StoredProcedure,
                CommandText = dbPrefixName + StoredProcedureName,
            };
            await connection.OpenAsync();

            SqlCommandBuilder.DeriveParameters(cmd);
            if (cmd.Parameters.Count - 1 > Parameters.Count())
                throw new InvalidOperationException("The number of parameters provided does not match the number of parameters in the stored procedure. There are " + Parameters.Count().ToString() + " parameters provided, however the stored procedure requires " + cmd.Parameters.Count.ToString() + " parameters.");

            for (int i = 0; i < Parameters.Count(); i++)
            {
                cmd.Parameters[i + 1].Value = Parameters[i];
            }


            var reader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
            return reader;

        }

Here is an example of the implementation in an inherited class:

 internal async Task<List<Models.PermitAssociatedApplication>> RemoveAssociatedApplication(int PermitApplicationId)
        {
            List<Models.PermitAssociatedApplication> temp = new List<Models.PermitAssociatedApplication>();
            using (IDataReader reader = await ExecuteDataReaderAsync("AssociatedApplications_RemoveApplication", PermitApplicationId))
            {
                while (reader.Read())
                    temp.Add(FillAssociatedApplication(reader));
            }
            return temp;
        }

Any thoughts how I can fix this? Our application is set to go live in a matter of days.

Thank you so much!!

Answer 1

You have to call Close() method on the datareader and that will close the connection. Otherwise it will stay open. Since you have this CommandBehavior.CloseConnection, calling Close() on the datareader will close the underlying connection.