Exception: There Is Already an Open Datareader Associated with This Connection Which Must Be Closed First

There is already an open DataReader associated with this Command which must be closed first

This can happen if you execute a query while iterating over the results from another query. It is not clear from your example where this happens because the example is not complete.

One thing that can cause this is lazy loading triggered when iterating over the results of some query.

This can be easily solved by allowing MARS in your connection string. Add MultipleActiveResultSets=true to the provider part of your connection string (where Data Source, Initial Catalog, etc. are specified).

There is already an open DataReader associated with this Command which must be closed first.

Your problem is that you are not disposing the objects you are using. For that purpose is better to always use using structure, since it will guarantee you that everithing is gonna be disposed. Try the code below:

SqlConnection sqlConnection1 = new SqlConnection("Data Source=ComputerOne; Initial Catalog=TestDatabase;Integrated Security=False; User ID=test; Password=test123;");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
string cardID = "";
string quantity="";

using(sqlConnection1 = new SqlConnection("Data Source=ComputerOne; Initial Catalog=TestDatabase;Integrated Security=False; User ID=test; Password=test123;"))
{
    sqlConnection1.Open();

    using(cmd = new SqlCommand())
    {
        cmd.CommandText = "Select * From Users Where CardID=" + "'" + user.CardID + "'";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = sqlConnection1;

        using(reader = cmd.ExecuteReader())
        {

            while (reader.Read())
            {
                cardID = reader["CardID"].ToString();
            }
        } //reader gets disposed right here
    } //cmd gets disposed right here

    using(cmd = new SqlCommand())
    {
        cmd.CommandText = "Select T1.CardID, T2.Title, Sum(T1.Quantity) as Quantity From CardTransactions as T1 JOIN Adds as T2 ON T1.AddsID = T2.AddsID Where T1.CardID =" + cardID + "AND T1.Type = 1 Group By T1.CardID, T2.Title";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = sqlConnection1;

        using(reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                quantity = reader["Quantity"].ToString();
            }
        } //reader gets disposed right here
    } //cmd gets disposed right here
    sqlConnection1.Close();
} //sqlConnection1 gets disposed right here

Entity Framework error: There is already an open DataReader associated with this Command

In this code there is only 1 database connection, so when you do this

foreach (var cat in db.BookCategories.Include("Books").Where(c => c.Id == CategoryId))

It uses the connection to start reading the 'Books'. It starts reading them, then reads one at a time.

You get that error message if you try to do another DB operation inside the loop. The reason is that the connection is still in the middle of reading the Books, or in other words, the reader is still open.

If you do this:

...... => c.Id == CategoryId).ToList())

Then it forces all the Books to be read into the list, and closes the reader, so now you can do something else with the connection.

Error There is already an open DataReader associated with this Command which must be closed first when using 2 distinct commands

I suggest creating an additional connection for the second command, would solve it. Try to combine both queries in one query. Create a subquery for the count.

while (dr3.Read())
{
    dados_historico[4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verificação
}

Why override the same value again and again?

if (dr3.Read())
{
    dados_historico[4] = dr3["QT"].ToString(); //quantidade de emails lidos naquela verificação
}

Would be enough.

C# : there is already an open DataReader associated with this command

As the error message reads, you already have an open DataReader associated with that Command object.

You need to create a new SqlCommand object for each (nested) command that you want to execute.

SqlCommand locationCommand = connection.CreateCommand();
SqlCommand networkCommand = connection.CreateCommand();
SqlCommand ipAddrCommand = connection.CreateCommand();

Assign your CommandText as needed to each of those command objects, then you can call ExecuteReader on each one and process as needed.

Entity Framework,There is already an open DataReader associated with this Connection which must be closed first

When you say

place.Each(m =>

You're using a reader to iterate the items from the database

Then

  var pu = users.FirstOrDefault(u => u.LoginName == m.Key);

The second reader enters while the first one is still executing and the error occurs

Call ToList() to get the data into memory and close the reader

var place = db.Place.Include(m => m.User).Where(m => dIds.Contains(m.Id)).ToList();

You can also enable MARS (Multiple Active Result Sets)

https://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx

MySqlException: There is already an open DataReader associated with this Connection which must be closed first

Never send an IQueryable<T> or non-materialized IEnumerable<T> outside a controller action!: Notice how in the example you linked to on Microsoft's website, the code calls ToList() prior to returning View(model).

You need to materialize your query to a List<T> in your Action so that the DbContext can be disposed-of safely and not leave dangling connections open:

viewModel.Cinemas = await db.cinemas
    .Include(c => c.responsable)
    .ToListAsync();

Entity Framework There is already an open DataReader associated with this Command which must be closed first

The problem is using a singleton in the first place. A DbContext is neither thread-safe nor meant to be long lived. It's a Unit-of-Work that caches any pending changes and either commits them all atomically when SaveChanges is called, or discards them when disposed. This means a DbContext should always be disposed once the unit of work is done.

Nothing is gained by using a singleton DbContext either. It's not a connection, so no time is saved by keeping it around for long. A DbContext opens and closes connections itself when it has to read data or persist changes. It doesn't keep them alive.

By making IAuthorizationHandler you're also keeping a DbContext instance as a singleton and end up using it concurrently from multiple requests. You may not get any exceptions until two requests try to read from the DbContext instance simultaneously.

Solution

Make UserAuthorizationHandler scoped

To fix this problem, the easiest solution seems to be to make UserAuthorizationHandler a scoped instance. The code posted here doesn't seem to use any other singleton services so it doesn't need to be a singleton itself.

Use scopes explicitly

If it has to remain a singleton, the DbContext and by extension, the UserRepository will have to be created inside a scope as needed. In this case, inside HandleAsync. To do that you need to inject IServiceProvider instead of IUserRepository. This is explained in Consuming a scoped service in a background task :

public class UserAuthorizationHandler : IAuthorizationHandler
{
    private readonly IServiceProvider _services;
    public UserAuthorizationHandler(IServiceProvider services)
    {
        _services = services;
    }
    public Task HandleAsync(AuthorizationHandlerContext context)
    {
        using (var scope = Services.CreateScope())
        {
            using(var userRepository=scope.GetRequiredService<IUserRepository>())
            {
                var pendingRequirements = context.PendingRequirements.ToList();
                ...
            }
        }
    }

BTW don't use blocking IO/database calls in an async method. This blocks a thread that could be used to serve other requests and increasing CPU usage due to spinwaits. A lot of the performance gains in ASP.NET Core come from avoiding unnecessary blocks

This means that either IsPremium and IsFeatureActivated should become asynchronous, or, if the synchronous methods are really needed, that async versions should be added. In this particular case both methods are used, so a good idea would be to use a single query to return both values :

public async Task<(bool isPremium,isFeatureActivated)> FeaturesForAsync(int userId)
{
    var results=await _context.Users
                        .Where(u=>u.Id==userId)
                        .Select(u=>new {u.IsPremium,u.IsFeatureActivated})
                        .SingleOrDefaultAsync();

    if (results==null) return default;
    return (results.IsPremium,results.IsFeatureActivated);
}

Or, using records instead of tuples :

record UserFeatures(bool IsPremium,bool IsFeatureActivated);

public async Task<(bool isPremium,isFeatureActivated)> FeaturesForAsync(int userId)
{
    var results=await _context.Users
                        .Where(u=>u.Id==userId)
                        .Select(u=>new UserFeatures( 
                                         u.IsPremium,
                                         u.IsFeatureActivated))
                        .SingleOrDefaultAsync();

    return results ?? new UserFeatures(false,false);
}

---

Related Topics