There is already an open DataReader associated with this Connection which must be closed first. c#

Question

I'm getting this error on my Controller

System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> MySql.Data.MySqlClient.MySqlException: There is already an open DataReader associated with this Connection which must be closed first.
   at MySql.Data.MySqlClient.MySqlCommand.CheckState()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.Execute(MergeOption mergeOption)
   at System.Data.Objects.DataClasses.EntityReference`1.Load(MergeOption mergeOption)
   at System.Data.Objects.DataClasses.RelatedEnd.Load()
   at System.Data.Objects.DataClasses.RelatedEnd.DeferredLoad()
   at System.Data.Objects.DataClasses.EntityReference`1.get_Value()
   at Timee.fingerprint.get_employee() in C:\Users\MyNameDesktop\Time\Timee\AModel.Designer.cs:line 2234
   at Timee.BundyForm.verificationControl_OnComplete(Object Control, FeatureSet FeatureSet, EventHandlerStatus& EventHandlerStatus) in C:\Users\MyName\Desktop\Time      \Timee\BundyForm.cs:line 82
   at DPFP.Gui.Verification.VerificationControl.<>c__DisplayClass2.<relay_OnComplete>b__0()

Controller:

 void verificationControl_OnComplete(object Control, DPFP.FeatureSet FeatureSet, ref DPFP.Gui.EventHandlerStatus EventHandlerStatus)
    {
        clearInfoBoxTimer.Stop();

        DateTime entryTime = DateTime.Now;

        DPFP.Verification.Verification ver = new DPFP.Verification.Verification();
        DPFP.Verification.Verification.Result res = new DPFP.Verification.Verification.Result();

        employee employees = null;
        foreach (fingerprint fingerPrint in this.db.fingerprints)
        {
            DPFP.Template template = new DPFP.Template();
            template.DeSerialize(fingerPrint.data);
            ver.Verify(FeatureSet, template, ref res); 
            if (res.Verified)
            {
                employees = fingerPrint.employee; //Im GETTING AN ERROR HERE
                break;
            }
        }
     }

Based on some forums I read I have to add multipleactiveresultsets=True; to my webconfig. But in my case it not applicable because I'm using MYSQL which doesn't support it. Is there other way to make this work? Please help me out guys, thanks.

Model of Verification

// Summary:
//     Performs the system function of fingerprint verification, which is a one-to-one
//     comparison of a fingerprint feature set with a fingerprint template produced
//     at enrollment that returns a decision of match or non-match.
public class Verification
{
    // Summary:
    //     Use this value to specify the default FAR threshold
    public const int ProbabilityNotSet = -1;

    // Summary:
    //     Initializes a new instance of the Verification class for comparing a fingerprint
    //     feature set with a fingerprint template using the default value of the false
    //     accept rate (FAR)
    public Verification();
    //
    // Summary:
    //     Initializes a new instance of the Verification class for comparing a fingerprint
    //     feature set with a fingerprint template and assigns the value of the FAR
    //
    // Parameters:
    //   FARRequested:
    //     Value of the requested FAR
    public Verification(int FARRequested);

    // Summary:
    //     Returns or assigns the requested false accept rate (FAR)
    public int FARRequested { get; set; }

    // Summary:
    //     Performs fingerprint verification and returns the comparison decision based
    //     on the default FAR threshold
    //
    // Parameters:
    //   FeatureSet:
    //     A DPFP.FeatureSet object
    //
    //   Template:
    //     A DPFP.Template object
    //
    // Returns:
    //     Verification result object
    public static Verification.Result Verify(FeatureSet FeatureSet, Template Template);
    //
    // Summary:
    //     Performs fingerprint verification and returns the comparison decision based
    //     on the specified FAR threshold
    //
    // Parameters:
    //   FeatureSet:
    //     A DPFP.FeatureSet object
    //
    //   Template:
    //     A DPFP.Template object
    //
    //   FARRequested:
    //     False Accept probability threshold or ProbabilityNotSet to use the default
    //     threshold
    //
    // Returns:
    //     Verification result object
    public static Verification.Result Verify(FeatureSet FeatureSet, Template Template, int FARRequested);
    //
    // Summary:
    //     Performs the system function of fingerprint verification and specifies a
    //     comparison decision based on the FAR set by the FARRequested property
    //
    // Parameters:
    //   FeatureSet:
    //     A DPFP.FeatureSet object
    //
    //   Template:
    //     A DPFP.Template object
    //
    //   Result:
    //     A DPFP.Verification.Result object
    public void Verify(FeatureSet FeatureSet, Template Template, ref Verification.Result Result);

    // Summary:
    //     Represents the results of a fingerprint verification operation.
    public class Result
    {
        // Summary:
        //     Default c-tor
        public Result();

        // Summary:
        //     Returns or assigns the value of the achieved FAR for a comparison operation.
        public int FARAchieved { get; set; }
        //
        // Summary:
        //     Returns or assigns the comparison decision, which indicates whether the comparison
        //     of a fingerprint feature set and a fingerprint template resulted in a decision
        //     of match or non-match. This decision is based on the value of the FARRequested
        //     property
        public bool Verified { get; set; }
    }
}

score 1 · Answer 1 · edited Jul 19 '13 at 11:02

1

Does the below code help ?

foreach (fingerprint fingerPrint in this.db.fingerprints)
{
    using(fingerprint)
    {
        DPFP.Template template = new DPFP.Template();
        template.DeSerialize(fingerPrint.data);
        ver.Verify(FeatureSet, template, ref res); 
        if (res.Verified)
        {
            employees = fingerPrint.employee; //Im GETTING AN ERROR HERE
            break;
        }
    }
}

edited Jul 19 '13 at 11:02

Erik Schierboom

16,301
10
64
81

answered Jul 19 '13 at 10:57

Rameez Ahmed Sayad

1,300
6
16
29

I'm getting an error on this line 'using (fingerPrint)' type used in a using statement must be implicitly convertible to System.IDisposable. – bot Jul 19 '13 at 11:02
What is db object ... I presumed underlying would be EF – Rameez Ahmed Sayad Jul 19 '13 at 11:27

score 1 · Answer 2 · answered Jul 19 '13 at 11:09

You cannot open a datareader inside a datareader. (You can not open a reader when there is already an open reader, normally in a nested loop).

As a company, we ran into this issue quite a bit in the past. Upon much investigation, we decided, for our purposes, to rethink how we structure our data connections.

You first have to close the open reader, and then open the next reader. We have thus moved on to write our code more object orientated, using class objects as "storage" for the top level loop and filling that object with the data that we need.

Close the reader.

Step through your temporary object and open a reader to get more data.

Wash, rinse, repeat.

This has been working well for us.

P.S, you can also eliminate nested read loops by making better use of table joins.

thanks for the valuable information. I'll try to think about it. I have to discuss this to my team. — bot, Jul 19 '13 at 11:12

score 1 · Answer 3 · answered Jul 19 '13 at 11:16

What datatype is res? What datatype is fingerprints?

You may need to give some more context to your result, but it sounds like you should return a dataset/dataadapter rather than return the ADO.NET connection. fingerPrint.employee probably uses the same underlying connection but you have not closed the first reader, hence the error message.

If you implement this strategy it will allow you to decouple from the underlying database instance.

So your database layer would use IDataReader internally then return either a DataSet or DataAdapter that the upper layers can iterate through, without having to worry about open DB connections.

I use BLOB as datatype of fingerprint. for res I can't really tell just look at my model I included it in my question. — bot, Jul 23 '13 at 01:51

score 1 · Accepted Answer · answered Jul 23 '13 at 09:39

I was now able to find an answer to my own problem. Here is my working solution

 void verificationControl_OnComplete(object Control, DPFP.FeatureSet FeatureSet, ref DPFP.Gui.EventHandlerStatus EventHandlerStatus)
    {
        clearInfoBoxTimer.Stop();

        DateTime entryTime = DateTime.Now;

        DPFP.Verification.Verification ver = new DPFP.Verification.Verification();
        DPFP.Verification.Verification.Result res = new DPFP.Verification.Verification.Result();

        employee employees = null;
        foreach (fingerprint fingerPrint in this.db.fingerprints)
        {
            DPFP.Template template = new DPFP.Template();
            template.DeSerialize(fingerPrint.data);
            ver.Verify(FeatureSet, template, ref res); 
            if (res.Verified)
            {
                db.Connection.Close(); //I close the connection first
                db.Connection.Open(); // then I open it again

                employees = fingerPrint.employee; 
                break;
            }
        }
     }

Based on the suggestions of the people here and on some forums I read, I need to Close first the open reader then Open it again. It took me time to think how will do it and at last I was now able to find one. This is just how I solve my problem in the simplest way, I don't know if there are other solutions out there. But at least this solves my problem and still I'm very thankful to the people here who tried there very best to help me. Thank you guys. :)

score 0 · Answer 5 · answered Jul 19 '13 at 12:27

0

You need to enable multipleActive result set true in your connectionstring.

Add “MultipleActiveResultSets=True” to your connectionstring.

See the following link. http://www.dotnetjalps.com/2013/06/Entity-Framework-There-is-already-an-open-DataReader-associated-with-this-Command.html

answered Jul 19 '13 at 12:27

Jalpesh Vadgama

13,653
19
72
94

Thanks but as I stated on my above answer that multipleactiveresultsets=true is not applicable in my case because I'm using MYSQL. It only works in SQL Server. – bot Jul 20 '13 at 07:38

score 0 · Answer 6 · edited May 23 '17 at 12:30

When you use foreach to iterate through fingerprints, it keeps the connection open. One solution would be to call ToList() on fingerprints, which will get all the fingerprints and put them in a list, and then close the connection. You can then use foreach to iterate through the list and do an additional query for each item in the list. Example:

foreach (fingerprint fingerPrint in this.db.fingerprints.ToList())

This question has some other answers:

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

There is already an open DataReader associated with this Connection which must be closed first. c#

6 Answers6