There is already an open data reader....

 Hi ,

I'm getting a 'There is already an Open Data Reader associated with the command which must be closed first" error under the following circumstances.

1.Run a sotred procedure query (SQL2005 back end) using UnitOfWork.Calculate(query) inside a backgroundworker_DoWork call

2.Cancel the query by calling backgroundworker.CancelAsync

3. Run a linq query that uses the same UnitOfWork ( I have one static instance for the whole project)

I am presuming I am missing a simple call to close the data reader in the  CancelAsync call, but can't see how to do this on a unit of work.

Any pointers appreciated !

I would generally avoid using the same UOW on two threads because the UnitOfWork class is not thread-safe.  Could you not create a new UOW in the BackgroundWorker (and dispose it when complete)?  I am pretty sure that in your scenario the UOW that calls the sproc does not need to share entities with the UOW that handles entities in the UI thread.

At present there is no way to cancel a query in progress.

 >>I would generally avoid using the same UOW on two threads because the UnitOfWork class is not thread-safe. 

OK. In the current scenario, the user cannot use the UOW to do anything else whilst the background worker is running apart from cancel it, but if I mess up the code (or someone following me does) then it is possible, so yes, your suggestion of creating a new uow would be better, thanks.

>>At present there is no way to cancel a query in progress

OK, thanks.

.

I've only just had time to look at this and have another noob type question. I've moved the code into a class called ProcUnitOfWork to encapsulate everything which contains a couple of calls to stored prccs and a linq query to get some info and a property to get/set the command timeout. The first bit is as below :

public class ProcUnitOfWork : UnitOfWork
    {
        LightSpeedContext<ProcUnitOfWork> _context;
        ProcUnitOfWork _instance;
        public ProcUnitOfWork Instance
        {
            get
            {
                CreateInstance();
                return _instance;
            }
        }
        private void CreateInstance()
        {
            if (_instance == null)
            {
                _context = new LightSpeedContext<ProcUnitOfWork>("default");
                _instance = _context.CreateUnitOfWork();
            }
        }

 So, I want to add a Dispose method that I can call from outside the class that explicitly sets _instance to null, but I can't do that since Dispose is protected override in UnitOfWork. Is it acceptable practice to just have a public method like "DisposeMe" that then just calls Dispose in the ProcUnitOfWork class  like below which can then be called in the RunWorkerCompleted or Cancel events of the backgroundworker ? 

protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
            _instance = null;
            _context = null;
        }
        public void DisposeMe()
        {
            Dispose(true);
        }

After playing around with this a bit more I have found another problem.

I am calling Calculate(qry) on the UnitOfWork inside a backgroundworker DoWork event. However, if the CancelAsync() is called and DisposeMe is called, the sql command  just carries on running, so I think I need to do things differently.

Is there a way to expose the Cancel property of the underlying Command object , and if so, how do I then tell the UnitOfWork to run the stored proc  asynchronously ??

There are two overloads of Dispose in UnitOfWork: public Dispose() and protected virtual Dispose(bool).  The public Dispose() method already does exactly what your proposed DisposeMe() method does (and by the miracle of virtual functions will of course call your override of Dispose(bool)).  So you can just call that existing public Dispose() rather than creating your own method.

 Ah yes, haven't put my C# head back on properly this morning. Any thoughts on how to cancel the SQL stored proc that is being run in the background worker since even if the uow has been disposed and set to null along with the context, it continues to run.

Sorry to hassle you guys, but anyone got an idea of how I can fix the above problem i.e. cancel a stored proc running that was started using the UnitOfWork.Calculate method ?

I think what is needed is a way to start it asynchronously, then be able to call the Cancel of the underlying command, but I don't think these properties are exposed in Lightspeed. If not possible, then I can always just bypass Lightspeed and use ADO objects directly.

Cheers

Paul

Hi Paul,

Sorry we've not had chance to get around to this.  At present there is no way to get at the internal command that LightSpeed is using, and we do not currently provide a way to cancel it via the unit of work interface.  We'll take a look at this for the future, but we're not really in a position to get you a fix right now (we need to have a think about how we'd want to surface this kind of functionality, and thinking tends to take longer than just doing *grin*).  Therefore for now I would suggest dropping down to the ADO.NET level as you propose.  Sorry.

By the way, if you want to limit duplication, you can still get LightSpeed to create the connection for you (using its config settings) and then attach your handcrafted ADO.NET command to that connection.  See IUnitOfWork.PrepareCommand:

using (SqlCommand cmd = MakeEpicSprocCommand())
{
  uow.PrepareCommand(cmd);  // creates connection and attaches cmd to it
  cmd.BeginExecuteNonQuery();
}

(If the unit of work is in a transaction then this also hooks the command up to the transaction, though this probably isn't relevant in your case.)

Many thanks for the reply Ivan, I wasn't aware that I could do what you suggest above, so will go that way. It's only the one sproc that I need to be able to cancel in this way, so not a major. It takes a long time to run since it uses linked servers to access other databases around the country.