none
Finding a command that is not closing RRS feed

  • Question

  • Hi guys

    I am currently working of a fairly large application that makes use of transactions (specifically transactionscope). I have run into a little bit of trouble with the sharing of a single connection to preform multiple commands.

     

    If these commands are all nonselects it is not an issue however if i do a select in the middle sometime i get the following error "There is already an open DataReader associated with this Command which must be closed first." Now as far as I know every one of the commands are being used within a using statement.

     

    So I am thinking that it is defiantly closing but .net seems to think otherwise. Hence I was wondering if anyone knows of a way of how i can track down which command it thinks is open. Note, as the error occurs in random positions it is not that easy to track down exactly where it is occurring. So i was wondering if anyone can help out.

     

    Thanks

    Anthony

    Wednesday, December 5, 2007 11:00 AM

Answers

All replies

  • TransactionScope let you pass in many transaction as one operation which in not valid in the database because ANSI SQL transaction is a unit of work, nesting is allowed but managed.  A DataReader uses one connection if you need two you need two connections.  There is more info about this error in the thread below.

     

    http://forums.microsoft.com/msdn/showpost.aspx?postid=123691&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

     

    Wednesday, December 5, 2007 6:02 PM
  • "This occurs when you have multiple DataReaders open concurrently on the same connection, ie you call SqlCommand.ExecuteReader but don't close the SqlDataReader returned by this method before calling it again (either on the same command or another command on the same connection)."

     

    Ok I see that if i wanted to have multiple active commands running at the exact same time (say two different threads using the same connection) that i would run into issues, but according to the above if I close the command it shouldn't matter how many commands I have. So again that would tend to lead towards the fact that I haven’t closed something but I am having trouble tracking that down.

    Thanks

    Anthony

    Wednesday, December 5, 2007 10:21 PM
  • Humm I think I found a DbDataReader that was not used in a using statement. Could this be the problem? But by default does putting it in a using statement close the connection because I cannot have it closing the connection?

    Thanks

    Anthony

    Wednesday, December 5, 2007 11:07 PM
  • In C# the second Using statement calls Dispose for you automatically on classes that implements the IDISPOSABLE interface so it is good practice to use that Using statement when you are using DbDataReader and Connection.  You should check your code and add the Using statements as needed.

     

    Wednesday, December 5, 2007 11:26 PM
  • Yep I know about using and what it does but I was more wondering is whether the fact that it or the close was not being call in the circumstance I described cause the issue I was talking about?

    Thanks

    Anthony

     

    Wednesday, December 5, 2007 11:32 PM
  • EDIT

     

    When the second using statement is not working your only option is the Dispose pattern because it calls Dispose Bool which removes both managed and unmanaged resources.  One more thing the second Using statement is not relevant to the Command Objects because they don't implement the IDISPOSABLE interface.

     

    http://blogs.msdn.com/andrewdownum/archive/2005/01/04/346448.aspx

    Wednesday, December 5, 2007 11:41 PM