connection pool failover support

Topics: Data Access Application Block
Sep 29, 2010 at 2:38 PM
Edited Sep 29, 2010 at 3:24 PM

Hello,

To date I've been using a DAL of my own design and am considering switching to DAAB.  There is a common scenerio that I've overcome with my DAL, and I'm wondering how it is or could be handled in DAAB.  Here's the scenerio (local smart client app):

Create your application as normal including a routine to return a dataset from a database.  Run your code and get the database, repeat...  normally there's nothing special here and should have no problems.  Now, run the application and return data from the database, then while the client maching is running unplug the network cable and plug it back in.  Then try to get data from the database.  You will likely return an error the first time, and if you try again, you'll probably be successfull.

I've seen this a lot on machines that run on a less than perfect network.  For example, a tablet pc in a WIFI network that's being carried around and may drop off the network momentarily.  It also happens in office situations with less than perfect wiring.  Anyway, the issue is the ADO.net connection pool.  Once a connection pool object is corrupt, the only way to detect the corruption is to try to use it and catch the error.

So in normal code situations like this...

    public class clsDal
    {
        public void DoCommand(string psConnectionString, string psSQL)
        {
            using (System.Data.SqlClient.SqlConnection lxCN = new System.Data.SqlClient.SqlConnection(psConnectionString))
            {
                using (System.Data.SqlClient.SqlCommand lxCD = new System.Data.SqlClient.SqlCommand(psSQL, lxCN))
                {
                    lxCD.Connection.Open();
                    lxCD.ExecuteNonQuery();
                    lxCD.Connection.Close();
                }
            }
        }
            
    }

I normally run code like this...

    public class clsDal
    {
        private int miRetryCount;

        public void DoCommand(string psConnectionString, string psSQL)
        {
            using (System.Data.SqlClient.SqlConnection lxCN = new System.Data.SqlClient.SqlConnection(psConnectionString))
            {
                using (System.Data.SqlClient.SqlCommand lxCD = new System.Data.SqlClient.SqlCommand(psSQL, lxCN))
                {
                    System.Data.SqlClient.SqlConnection lxPassConnection = lxCD.Connection;
                    if (EstablishConnection(ref lxPassConnection, 0))
                    {
                        lxCD.ExecuteNonQuery();
                        lxCD.Connection.Close();
                    }
                }
            }
        }

        public bool EstablishConnection(ref System.Data.SqlClient.SqlConnection pxCN, int piTryCount)
        {
            Boolean lbReturn = true;
            try
            {
                pxCN.Open();
            }
            catch
            {
                if (piTryCount < 4)
                {
                    lbReturn = EstablishConnection(ref pxCN, piTryCount + 1);
                }
                else
                {
                    pxCN.Close();
                    lbReturn = false;
                }
            }

            return lbReturn;

        }
            
    }

Has MS considered putting something like this into DAAB?  or where would I look to use something like this in my code?

Thanks,  Steve

Sep 30, 2010 at 3:38 AM

Hi Steve,

Connection pooling is supported in Entlib DAAB. By default it is enabled. I'm not really sure if I understand your scenario clearly though here's a related thread (http://entlib.codeplex.com/Thread/View.aspx?ThreadId=223999) regarding connection pooiling that I hope may give you more insights. Also, if you would want to check how connection pooling is implemented in DAAB the only thing I have in mind is by checking the source code itself. HTH

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

 

Sep 30, 2010 at 1:03 PM

I've looked at the other threads.  By everything I could find the current DAAB does not make any attempt to overcome a connection failure.  However, I couldn't find the concrete execution of a command object in the DAAB source code.  There were just too many virtual and abstract declaration to find the actual code.

As you know, the ADO.net best practice is to turn connection pooling on.  Then in your code you close open connections as soon a possible.  The operating system then keeps track of prior connections.  If you open a connection to the same connection string it will give you a prior connection instead of opening a new one, thus saving a lot of time creating connections.  The problem comes when one of those operating system held connections goes bad.  The only way to know if the pooled connection is bad is to try to use it.  If it throws an error, close the connection and re-open it.  That is why I use the recursive call to open the connection.  From what I can find, the DAAB will raise a connection failure error in this case, which is unneccessary if we could program it to just try again.

for what it's worth... I would rename the DAAB pooled connections to "DAAB persistent connections" to differentiate them from the ADO.net pooled connections.

Sep 30, 2010 at 2:09 PM

I did encounter a post requesting for this feature before but I don't see a corresponding workitem for it in the Issue Tracker.  You can log this then as feature request in the Issue Tracker.  More votes means greater possibility for it to get included in the future version since a lot of people are interested in it.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com