There Is Already an Open Datareader Associated With This Command 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).

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.

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

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.

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

An SqlDataReader should be closed after its use, otherwise it keeps the connection locked and you cannot use that connection until you close it.

However you don't need an SqlDataReader to discover if you have already registered an email address

public bool good(string emailadress)
{
    string query = @"IF EXISTS(SELECT 1 FROM Clients WHERE Email = @email) 
                     SELECT 1 ELSE SELECT 0";
    SqlCommand cmd = new SqlCommand(query, con);
    cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = emailadress;
    int result = (int)cmd.ExecuteScalar();
    return (result == 1);
}

Keep in mind that disposable objects like the SqlCommand, the SqlDataReader but of uttermost importance, the SqlConnection should be created at the moment you need them and disposed immediately to free valuable resouces.

The code above with a connection created on the spot would be:

public bool good(string emailadress)
{

    string query = @"IF EXISTS(SELECT 1 FROM Clients WHERE Email = @email) 
                     SELECT 1 ELSE SELECT 0";
    using(SqlConnection con = new SqlConnection(....here put your connection string...))
    using(SqlCommand cmd = new SqlCommand(query, con))
    {
        con.Open();
        cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = emailadress;
        int result = (int)cmd.ExecuteScalar();
        return (result == 1);
   }
}

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

I think your issue might be that when you return from inside the if statement, the reader will not be closed.

You should change your code to use using statements. That will close your reader even if you return. The same is true for the connection.

Your code has another flaw. I believe readers require an open connection. The first pass around the loop will close the connection if the condition is false, making the second pass fail.

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