Connection to SQLServer lost & open UnitOfWork

Hello.

I am using SQLServer2005. My application run in serveral computers accessing a central SQLServer database. Several computers connect to the database using a very slow VPN.

With LightSpeed the applicattion run faster than before using eager load and other optimizations, but sometimes there are problems.

Sometimes the conecction to the database is lost (a few seconds). When there is a open Unit Of Work sometimes the connection is never open again, so all commands fails. All the unit of work become unusable. Is there any way to reopen the lost conection to the database?

When I was using ADO.NET I open a connection to the database each time I need to send a query or command. When the query returns I close that connection. With the connection pooling this is very fast.

Thank you.

This is not possible using the built-in connection strategy.  The built-in connection strategy is to use the same connection for the duration of the UOW.  No recovery is attempted.  This is because UOWs should be short-lived so the chances of a connection failure during the lifetime of the UOW should be extremely small.

However, you can create your own connection strategy (e.g. RecoverableConnectionStrategy) to implement recovery behaviour.  See http://www.mindscape.co.nz/blog/index.php/2009/10/26/database-connection-strategies-in-lightspeed-3/ for info about this feature.

Hello Ivan.

I read the post carefully and I can use it but I need to know when the connection can be closed, as yo say in

I realise you might also want more notifications from the UnitOfWork so that you can make the strategy smarter about automatically closing connections instead of the app code having to do this manually.

The manually option is not practical.

But the question is: Its not easier for you to open a connection, perform the commands and later close the connection? I always do that in ADO.NET and it works without problems, why the connection must be open?

Thank you.

Regarding automatically closing connections and opening new ones:

We've considered this previously and we currently do not plan to do it because it would make the unit of work lifecycle significantly more complex, particularly around transactions.  Again, our recommended design guideline is to keep units of work short-lived where possible, so that connection management issues don't arise.

We'd be happy to look at adding notifications to help you automate open and closing in your own connection strategy, though.  Let us know what notifications you'd be interested in -- every time a command completes?  Every time a batch completes?

Thank you Ivan.

I dont know what is a batch in LS, but I suppose that there is several commands executed in sequence (for example to save changes). If this is true its better to receive a notification when a batch completes.

Hi Ivan,

I would like to be notified whenever LS did it's job done and know it didn't need a connection anymore. Of course your can't know when the the next DB request will be, that is something I have to manage. I guess that I will have a flag which I have to manage that signal the strategy to close or not close the connection after a notification.

So, I vote for an event at the strategy that gets called whenever LS wants to close the connection.

Kind regards,

Sörnt

The 14 April 2010 nightly build (available from about 1500 GMT) will add a new virtual method to ConnectionStrategy: OnDatabaseOperationComplete.  LightSpeed calls this method after each query or SaveChanges completes.  You can override this method to implement custom strategy behaviour, such as closing the connection.

This does not mean that LightSpeed "wants" to close the connection as Soernt puts it.  It means only that LightSpeed has finished a particular operation -- nothing more!  It is entirely possible that another operation is about to be kicked off.  For example, during a SaveChanges, LightSpeed may issue database queries as part of uniqueness validation, and you will receive notifications for these queries.  It doesn't even mean that it's safe to close the connection: for example, the connection may have a pending transaction that will be trashed if you close the connection now.  Therefore, a crude "close the connection whenever an operation completes" strategy may have significant performance issues and/or mess up transactions.

Once again, where possible we advise the use of short-lived units of work, which will automatically result in short-lived connections.  However, if that really isn't an option, we hope this will make low-level connection management a bit easier for you.

That sounds what I was looking for.

Thank you very much!

Kind regards,
Sörnt

Thank you Ivan.

I'll try it.

Hello Ivan

I implemented a closeable connection strategy using the new OnDatabaseOperationComplete() override and it seems to work OK. I'll try some time. Thank you.

Hello Ivan.

After some testing sometimes there are problems, LightSpeed tries to read data when the connection is not open. The stack trace is:

System.InvalidOperationException: Intento no válido de llamar a Read cuando el lector está cerrado. IN ENGLISH: Invalid call to Read when the reader is closed.
   en System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   en System.Data.SqlClient.SqlDataReader.Read()
   en Mindscape.LightSpeed.Entity.Get[TField](TField& field, String propertyName)
   en Mindscape.LightSpeed.Entity.Get[TField](TField& field)
   en Osted.Kernel.Database.HistFoto.get_DataImagen() en C:\LV\Osted\Kernel\Database\HistFoto.cs:línea 80
.....

Mi strategy class is (adapted from your blog):

    public class OstedConnectionStrategy : ConnectionStrategy
    {
        private IDbConnection connection;

        public OstedConnectionStrategy(LightSpeedContext context): base(context)
        {
        }

        protected override IDbConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    connection = Context.DataProviderObjectFactory.CreateConnection();
                    connection.ConnectionString = Context.ConnectionString;
                    connection.Open();
                }

                return connection;
            }
        }

        public bool IsConnectionOpen
        {
            get { return connection != null && connection.State != ConnectionState.Closed; }
        }

        public void CloseConnection()
        {
            if (connection != null)
            {
                connection.Dispose();
                connection = null;
           }
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                CloseConnection();
            }

            base.Dispose(disposing);
        }

        protected override void OnDatabaseOperationComplete(DatabaseOperation operation)
        {
            base.OnDatabaseOperationComplete(operation);
            CloseConnection();
        }
    }

This is a problem for lazy loaded fields -- the read from the data reader happens after the querying engine thinks the query is complete.  I'll look into a workaround (this will affect projections as well).  In the meantime, you should be able to work around this by forcing the field to eager-load in circumstances where you're going to need it.  You can do this using a named aggregate.

Hello Ivan,

I cant conver all to eager load, I will wait to a fix from you.

Why you didnt use the Connection property as allways you do? In this way the database is open when needed.

We do use the Connection property as we always do, and the database was open when we needed it.  The problem is that the connection is being closed again while we still need it, because we're notifying on command success rather than on data reader closure.

I've implemented a change which will be in the 23 April nightly build.  The DatabaseOperation object passed to ConnectionStrategy.OnDatabaseOperationCompleted will now include a HasOpenDataReaders property.  If this property is true then it is NOT safe to close the connection.  For lazy loads and LINQ projections, we will subsequently send a second notification of DatabaseOperationKind.DataReaderClose.  At this point it should be safe to close the connection.  Note that some operations that leave open data readers will NOT result in a second notification -- for example, if you call IUnitOfWork.Project, that leaves an open data reader, but we don't know when you close it, so you will need to write your own code to notify the connection strategy when you're finished with the data reader.

Hello Ivan,

I downloaded the nightly build and changed the connection strategy.

Now it seems to work well, I will test it.

Thank you.