Keep valid DB connections alive without native pooling

Question

Is there any way to keep DB connections open and ready to use in runtime? Our WCF service should keep connections that way. Now we have code that looks similar to this piece of code:

// This is the class of objects that has connections within.
[DataContract]
public class SomeObj
{
    [DataMember]
    public string Name { get; set; }

    public OracleConnection Conn { get; set; }
}

// Dictionary of SomeObjs
private static Dictionary myObjs = new Dictionary();

When service is starting it also connects all databases from own configuration files with connections strings inside. But as you know connections may become corrupted and unusable, or even be inaccessible at the end. So for eliminating this issue we have special timer that checks all connections in config:

    // Setup timer method. Timer will fire every minute.
    static private void CreateTimer()
    {
        Timer Timer1 = new System.Timers.Timer();
        Timer1.Enabled = true;
        Timer1.Interval = 60000;
        Timer1.Elapsed += new System.Timers.ElapsedEventHandler(Timer1_Elapsed);
    }

    static private void Timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
    {
            Parallel.ForEach(Program.config.Root.Elements("databases"), el =>
            {
                try
                {
                    Program.OpenConnection(el.Attribute("name").Value);
                }
                catch (Exception exc)
                {
                    Console.WriteLine(DateTime.Now.ToString() + " " + exc);                        
                }
            });

            // This is old way we checks connections consistently
            //foreach (XElement element in Program.config.Root.Elements("databases"))
            //{
            //    try
            //    {                
            //        AServ.OpenConnection(el.Attribute("name").Value);                
            //    }
            //    catch (Exception exc)
            //    {
            //        Console.WriteLine(DateTime.Now.ToString() + " " + exc);                
            //    }
            //}                
    }


    public static bool OpenConnection(string name)
    {
        // if connections exactly doesn't exists
        if (!myObjs.ContainsKey(name))
            {
                XElement el = (from t in config.Root.Elements("databases")
                               where t.Attribute("name").Value == name
                               select t).FirstOrDefault();
                if (el == null)
                {
                    lock (myObjs)
                    {
                        myObjs.Remove(name);                            
                    }
                    return false;
                }
                lock (myObjs)
                {
                    myObjs.Add(name, new SomeObj { Conn = new OracleConnection { ConnectionString = el.Attribute("connectionString").Value } }, Name = el.Attribute("name").Value);
                }
            }
            // if connection broken
            if ((myObjs[name].Conn.State != ConnectionState.Open || !myObjs[name].Conn.Ping()) && myObjs.ContainsKey(name))
            {
                try
                {
                    // Trying to get it alive 
                    lock (myObjs)
                    {
                        myObjs[name].Conn.Close();
                        myObjs[name].Conn.Open();
                    }
                }
                catch (Exception e)
                {
                    if (!myObjs.ContainsKey(name))
                        return false;

                    lock (myObjs)
                    {
                        myObjs.Remove(name);
                    }

                    return false;                        
                }
                Console.WriteLine(DateTime.Now.ToString() + " " + myObjs[name].Conn.ConnectionString);                    
            }

        return true;
    }

And one more thing you should know, guys, is how we use keeping connections in method itself:

    private static int GetParameterValue(string name, int id)
    {
        // if we don't get parameter value at the moment because broken connection
        if (!OpenConnection(name))
            return -1;

        // assuming connection is OK and getting the parameter value
        string parameterQuery = @"select GetInfo(:id) from dual";
        OracleCommand parameterCommand = new OracleCommand(parameterQuery, myObjs[name].Conn);
        parameterCommand.Parameters.Add("id", id);            
        return Convert.ToInt32(parameterCommand.ExecuteScalar());
    }

The issue is that sometimes first timer checking can't get done in time and second timer checking started while the first is running. It gives some strange errors with heap memory leaks (I think so). Another issue is that even we develop singleton design pattern to have only one checking in time, checking may get hanging and another checkings never get fired (because opening connection takes a long overpriced time for some databases. I don't know why it happens, but we tried to add Connection timeout=30;, but honestly I can't remember gave it right result or not. Certainly not.).

Then this service running in time it takes as many memory as it running. I have tried many manipulations with this code, even faced one time with dead locks somewhere in code (almost 100% CPU load).

Yes, I know about native ADO .NET pooling but we can't use it because we have huge legacy code base that works in way illustrated above and should somehow improve existing check logic.

So tell me your thoughts how can we get connections alive in time without pooling? I need some way that can use as little amount of memory as possible (existing code takes as more memory as it running), and work fast with small code changes.

Any help is very appreciated! Really hope for your help stackoverflow community!

I think about using special thread that will check connections in cycle with Thread.Sleep(60000) at the end. But I'm not sure about it.

ADO .NET provider is Devart dotConnect for Oracle. Database is Oracle 9-11 (depends on server), C# .NET Framework 4.

Keep valid DB connections alive without native pooling

Answers (1)

Related Questions