[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 919
  • Last Modified:

DB Connection pool

Hi,

I created a db pool class to use in various applications. Can people tell me ways of getting the best performance/use from it?


public class MySqlConnectionPool
    {

        private string dbUrl;
        private string database;
        private string username;
        private string password;
        private int initPoolSize;

        private string connectionString;

        private ConcurrentBag<MySqlConnection> availableConnections = new ConcurrentBag<MySqlConnection>();

        /// <summary>
        /// Constructor for a mysql connection pool
        /// </summary>
        /// <param name="dbUrl">Url of the db that we wish to connect to</param>
        /// <param name="username">Username that we will use to connect</param>
        /// <param name="password">Password that we will use to connect</param>
        /// <param name="initPoolSize">Size of the pool initially</param>
        public MySqlConnectionPool(string dbUrl,string database, string username, string password,
            int initPoolSize)
        {
            this.dbUrl = dbUrl;
            this.database = database;
            this.username = username;
            this.password = password;
            this.initPoolSize = initPoolSize;

            connectionString = "SERVER = "+this.dbUrl+";DATABASE="+this.database+
                ";UID="+this.username+";PASSWORD="+this.password+";Connection Timeout = 5;";

            for (int i = 0; i < initPoolSize; i++)
            {
                MySqlConnection con = new MySqlConnection(this.connectionString);
                availableConnections.Add(con);
            }
        }

        [MethodImpl(MethodImplOptions.Synchronized)]
        public MySqlConnection checkOut()
        {       
            MySqlConnection con = null;

            while (con == null)
            {
                bool checkedOut = this.availableConnections.TryTake(out con);
            }
            return con;
        }

        public void checkIn(MySqlConnection con)
        {
            if (con.State != ConnectionState.Closed)
                con.Close();

            this.availableConnections.Add(con);
        }


    }

Open in new window

0
basil365
Asked:
basil365
1 Solution
 
AJRDevCommented:
I assume that you are using the MySQL Connector/Net class library to connect to MySQL Server. This implements the required ADO.NET interfaces, and as such provides connection pooling by default, so the type of code above is not really neccessary. Every new connection you create, with exactly the same connection string, will be part of a pool implemented internally by the MySQL ADO.NET provider.

The connection string can include Pooling=true, MinimumPoolSize=, MaximumPoolSize=, and various other values to control pooling.
http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlconnection.html#connector-net-examples-mysqlconnection-connectionstring


The only time you should need to write your own pooling is if you are coding connections at the socket level, when you would want to maintain a collection of sockets and their states (as you would if you were writing your own provider).
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now