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

Hi.

Just faced absolutely stupid and very strange problem in .NET 2.0 application (cannot use higher version because of requirements to have it working also on Windows 2000).

See the enclosed code.
It is a method loading data for couple of grids in application.
The problem is following - after a 3rd (sometimes after 2nd) click on a [Refresh] button it throws the System.InvalidOperationException with message = "There is already an open DataReader associated with this Command which must be closed first."

BUT(!!!) as you can see in the code it dispose all the db objects - DataAdapter and SelectCommand. So, I assume it must not have any readers opened!

Also as you can see there is a "lock (this.GetType())" statement, so I assume only one caller should enter that code at the moment.

The this.owner.Connector.AltConnection is used only for loading data (only in this method). So I think there should not be any other consumers for this connection object.

That is why question is - WHY IT THROWS THAT EXCEPTION without any obvious reasons?!
What could be the solution for this problem?

Target database is SQL 2000sp3 on Windows 2000, I'm testing it on Windows 7 x64.

Regards,
Dmitry.

PS. Connection string defined in *.config file:

<add name="SiteDatabase" providerName="System.Data.SqlClient" connectionString="data source=xxWsBuild;database=xxxBuild;User ID=$(DatabaseUser);Password=$(DatabasePassword)"/>

Have tried to add a "MultipleActiveResultSets=False" but it did not help.
public void Reload()
{
    lock (this.GetType())
    {
        using (DbDataAdapter da = this.owner.Connector.DbFactory.CreateDataAdapter())
        {
            using (da.SelectCommand = this.owner.Connector.DbFactory.CreateCommand())
            {
                da.SelectCommand.Connection = this.owner.Connector.AltConnection;

                if (!string.IsNullOrEmpty(this.sql))
                {
                    da.SelectCommand.CommandText = this.sql;
                    ds.Clear();

                    Trace.WriteLineIf(CommonDL.trcLvl.TraceInfo, CommonDL.trcLvl.TraceInfo ? string.Format(
                        "* OpenSQL: {0}", this.sql) : "");
                    
                    da.Fill(ds);

                    Trace.WriteLineIf(CommonDL.trcLvl.TraceInfo, CommonDL.trcLvl.TraceInfo ? string.Format(
                        " * OpenSQL: {0} rows loaded", this.Table.Rows.Count) : "");

                    loaded = DateTime.Now;
                }
            }
        }
    }

    RebuildIndexes();
}

Open in new window

Dmitry_BondAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

strickddCommented:
I have a feeling that you're not showing all the pertinent code. You have an object that isn't declared in this method and, therefore, must be a globally declared object.

ds.Clear();

You also have the "loaded" variable, but that won't affect what you're seeing.

I would say, look at what the ds object is doing and also make sure that you are NOT looping when you call "Reload". If you have any sort of loop (for, foreach, while, etc.) that calls Reload(), then you should definitely change it around so your connection object is declared outside the loop and passed into the function. Looping connections are BAD
0
Dmitry_BondAuthor Commented:
There are no loops at all!
It is just a direct call from a GUI.

But for a reference here is the rest of related code (see enclosed).

Suspect the gridTickets_SelectionChanged may cause a problem... but(!).
If it is really gridTickets_SelectionChanged - why it not solved by a "lock (this.GetType())" statement (see code of Reload() method in original question)?!

Why "lock" does not work then?!
And if "lock" does not work - what also can I use instead?!


// this is a data contaner class
public class DataPortion
{
    // [...]
    
    private CommonDL owner;
    private string sql;
    private DataSet ds;
    private DateTime loaded = DateTime.MinValue;
    
    internal DataPortion(CommonDL pOwner, string pId, string pSql)
    {
      owner = pOwner;
      sql = pSql;
      ds = new DataSet(pId);
    }
    
    // <-- the "public void Reload()" was already shown in original question
}

// this is a data layer class
public class CommonDL
{
    // [...]

    public DataPortion GetData(string pDataSetId, string pSql)
    {
        DataPortion dp = null;
        if (this.datasets.ContainsKey(pDataSetId))
            dp = this.datasets[pDataSetId];
        else
        {
            dp = new DataPortion(this, pDataSetId, pSql);
            this.datasets.Add(pDataSetId, dp);
            dp.Reload();
        }
        return dp;
    }

    private Dictionary<string, DataPortion> datasets = new Dictionary<string, DataPortion>();
}

// this a GUI class
public partial class FormMain : Form
{
    // this is auto-generated code by VS2008
    private System.Windows.Forms.DataGridView gridTickets;
    private System.Windows.Forms.TabControl tabsMain;

    private DataPortion dpTickets = null;

    private void DisplayTickets()
    {
        if (this.dpTickets == null)
        {
            this.dpTickets = engine.GetData("Tickets", 
              "SELECT * FROM [Ticket] ORDER BY id");
            gridTickets.AutoGenerateColumns = true;
            gridTickets.DataSource = dpTickets.Table;
        }

        gridTickets.Refresh();
    }

    private void tabsMain_SelectedIndexChanged(object sender, EventArgs e)
    {
        switch (tabsMain.SelectedIndex)
        {
            case 0: /* [...] */ break;
            case 1: /* [...] */ break;
            case 2: 
                DisplayTickets();
                tbNew.Enabled = engine.EnsureRole("super;manager");
                tbEdit.Enabled = engine.EnsureRole("super;manager");
                tbDelete.Enabled = engine.EnsureRole("super;manager");
                tbRunTicket.Enabled = engine.EnsureRole("super;manager;developer");
                break;
        }
    }

    private void tbRefresh_Click(object sender, EventArgs e)
    {
        tbRefresh.Enabled = false;
        mainToolbar.Refresh();  
        try
        {
            switch (tabsMain.SelectedIndex)
            {
                case 0: /* [...]*/ break;
                case 1: /* [...]*/ break;
                case 2:
                    dpTickets.Reload(); // <-- in my case this is called when [Refresh] is clicked
                    break;
            }
        }
        finally { tbRefresh.Enabled = true; }
    }

    private DataGridViewRow rwTicket = null;
    private void gridTickets_SelectionChanged(object sender, EventArgs e)
    {
        if (rwTicket != gridTickets.CurrentRow && gridTickets.CurrentRow != null)
        {
            rwTicket = gridTickets.CurrentRow;
            DataRow rTicket = ((DataRowView)rwTicket.DataBoundItem).Row;

            dpBuilds = engine.GetTicketBuilds((int)rTicket["Id"]);
            gridTicketBuilds.AutoGenerateColumns = true;
            gridTicketBuilds.DataSource = dpBuilds.Table;
            gridTicketBuilds.Refresh();
        }
    }
}

Open in new window

0
mastooCommented:
That lock won't prevent a reentrant call.  When you get the exception and it breaks on the offending line, look at your call stack.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dmitry_BondAuthor Commented:
Btw, what is "reentrant call"?
I tried to google and found that this term is used in questions but had not found explanation of this term.
Is it a common/widely used term?
Where I can see what exactly does it means?
0
mastooCommented:
"A function is reentrant if, while it is being executed, it can be re-invoked by itself, or by any other routine, by interrupting the present execution for a while."  If it is the same thread re-invoking itself, then we call it recursive.  If it can be the same or other threads, it is reentrant.  Recursive code is also technically reentrant, but that is a little confusing so single threaded reentrancy would usually be called recursive.

So, having said that, let me admit my mistake - I should have said recursive, not reentrant.  That lock statement will prevent other threads from coming through but if the same thread makes a recursive call I'm pretty sure the .Net lock statement will let it on through and you could run into the symptom you are asking about.  In a windows gui, it is fairly easy to run into this without explicitly coding recursive calls.

It is quite easy to diagnose this.  Break on the error and look at your call stack.  If you see the current function has invoked itself somewhere higher up on the callstack, then you are getting recursion.  If not, disregard everything I've posted :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dmitry_BondAuthor Commented:
Yes, true. There was a subordinate DataGridView which was reloading if data in the main DataGridView changed. So, it did entered Reload() method once again.
Strange that I have not noticed this. :-\
Anyway - big thanks.

I have to use this approach to make it working:

gridTickets.SelectionChanged -= this.gridTickets_SelectionChanged;
try { dpTickets.Reload(); }
finally { gridTickets.SelectionChanged += this.gridTickets_SelectionChanged; }

In other words - turn off event handler while loading data, then turn on back after data loaded.
If you know any better way to do it - welcome to recommend.
0
mastooCommented:
Yep, that works.  Glad to help.
   
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.