Keyword not supported MultipleActiveResultSets

Question

We are using a custom Membership Provider in an ASP.NET MVC application. We have been experiencing intermittent 'System.InvalidOperationException - There is already an open DataReader associated with this Command which must be closed first.' problems with the class so I decided to enabled MARS.

This is the connection string in the web config...

<add name="CustomMembershipServices" connectionString="User ID=CUSTOM_USER;Password=pwd;Database=OUR_DB;Server=.\SQLEXPRESS;MultipleActiveResultSets=true;" providerName="System.Data.SqlClient" />

Our code now throws an ArgumentException: Keyword not supported: 'multipleactiverecordsets' at the following line..

protected void CreateAndOpenConnection()
{
    // Exception thrown here...
    _connection = new SqlConnection(_connectionString);
    _connection.Open();
}

The same exception is seen when targeting SQL2005 Express and SQL2008 Standard editions. Any suggestions?

In response to AdaTheDev here is the method that uses the data reader...

public bool CheckUserPassword(long userID, string password)
{
    bool success = false;

    if (!string.IsNullOrEmpty(password))
    {
        try
        {
            CreateAndOpenConnection();

            using (SqlCommand cmd = CreateSqlCommandForStoredProcedure("CheckPassword"))
            {
                IPasswordUtility generator = new PasswordUtility();

                cmd.Parameters.AddWithValue("UserID", userID);
                cmd.Parameters.AddWithValue("Password", generator.HashPassword(password));

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    success = reader.HasRows;

                    reader.Close();
                }
            }
        }
        finally
        {
            CloseConnection();
        }
    }

    return success;
}

I cannot see any reason why this would be creating multiple data readers on the same connection.

Answer 1

Unless you actually need MARS for a functional reason, my recommendation would be to focus on the underlying issue - it sounds like (and I could be wrong!) the expected/intended behaviour of the class is to not open multiple datareaders against a single connection at the same time.

If that is the case, then you should make sure datareaders are closed properly before another is opened on the same connection - it sounds like there is a situation where this is not being done. Using MARS as a workaround would still leave the fact that there is something not being closed properly.

Edit 1: That code does look OK to me in terms of clearing up the datareaders etc. Unless there's some multi-threading going on, causing a problem through the use of the shared _connection object? (I'm doubting this is the case). Are there any other methods in the class that aren't closing a datareader?

Answer 2

The problem is due to the ASP.NET Provider framework treating my class as a static singleton ...

"Question: When is a non-static class static?"