[Release] Simple MySql Pool handler

[12tails]

Good morning (10:44 am here ;P)

I've build a simple mysql pool handler...

NewConnection method:

        public static MySqlConnection NewConnection()
        {
            try
            {
                return new MySqlConnection("Server=" + SERVER + ";Database='" + DATA_BASE + "';Username='" + USER_NAME + "';Password='" + PASSWORD + "'");
            }
            catch (Exception e) { Console.WriteLine(e.ToString()); return null; }
        }

MyConnection Class:

    public class MyConnection
    {
        public int m_Id;
        public MySqlConnection m_Conn;

        public MyConnection(int nId)
        {
            m_Id = nId;
            m_Conn = Information.NewConnection();
        }
    }

Main Handler:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using MySql.Data.MySqlClient;
using System.Data;

namespace MsgServer.Common.MYSQL
{
    public class MySqlPool
    {
        public MyConnection[] Connection_Pool = null;
        int m_Size = 50;//Default size
        int m_Counter = 0;

        /// <summary>
        /// Create a new mysqlpool instance
        /// </summary>
        public MySqlPool(int max_size)
        {
            if (max_size > 50)
                m_Size = max_size;

            Connection_Pool = new MyConnection[m_Size];
        }

        /// <summary>
        /// Store a connection to the mysql connection pool.
        /// </summary>
        public bool ToPool(MyConnection pConn)
        {
            if (Contains(pConn.m_Id))
                return true;

            if (m_Counter + 1 >= m_Size)
            {
                Console.WriteLine("[MySql] Connection pool handler has reached the max pool size.");
                return false;
            }

            Console.WriteLine("[MySql] Connection pool handler has stored a new mysql connection.");
            Connection_Pool[m_Counter] = pConn;
            m_Counter++;
            return true;
        }

        /// <summary>
        /// Return a stored connection from the connection pool.
        /// </summary>
        public MyConnection FromPool()
        {
            MyConnection pConn = null;
            Boolean Founded = false;
            for (int i = 0; i < m_Counter; i++)
            {
                if (Connection_Pool[i] == null)
                    continue;

                switch (Connection_Pool[i].m_Conn.State)
                {
                    case ConnectionState.Broken:
                        pConn = Connection_Pool[i];
                        pConn.m_Conn.Close();
                        Founded = true;
                        break;
                    case ConnectionState.Closed:
                        pConn = Connection_Pool[i];
                        Founded = true;
                        break;
                }
                if (Founded)
                    break;
            }

            if (pConn == null)
            {
                try { pConn = new MyConnection(m_Counter + 1); }
                catch (Exception Exception) { Console.WriteLine(Exception); }
            }

            return pConn;
        }

        /// <summary>
        /// Check if the connection already exist into the connection pool.
        /// </summary>
        public bool Contains(int m_Id)
        {
            Boolean Cont = false;
            for (int i = 0; i < m_Counter; i++)
            {
                if (Connection_Pool[i] == null)
                    continue;

                if (Connection_Pool[i].m_Id == m_Id)
                {
                    Cont = true;
                    break;
                }
            }
            return Cont;
        }
    }
}

I hope someone finds it usefull ;P

I'll improve when i get some free time...
P.S: New ideas are always welcome...

[zTek]

Thank you sir. Very useful. Most likely I will use this.

[Spirited]

Well... I wasn't going to say anything but I might as well now. It really doesn't take long to open the connection (way less than a millisecond)... so this code wouldn't really do anything but possibly slow it down by adding queues.

[BaussHacker]

I don't use MySQL anyways, but good job. I like to use flatfile or MSSQL.

[12tails]

Originally Posted by BaussHacker

I don't use MySQL anyways, but good job. I like to use flatfile or MSSQL.

I like flat file too... i'm currently testing various types of databases system.... right now i'm testing the flat file one... should be the one i'll use...

@Fang
Actually the problem is not open or close a connection... when u call the Close(); or Dispose(); the connection is not really closed... its put into the mysql pool in a sleep state... so if you don't have a way to get this conn again it'll be there for a long time until the win release it or when you close the program/server....

[Spirited]

Originally Posted by 12tails

I like flat file too... i'm currently testing various types of databases system.... right now i'm testing the flat file one... should be the one i'll use...

@Fang
Actually the problem is not open or close a connection... when u call the Close(); or Dispose(); the connection is not really closed... its put into the mysql pool in a sleep state... so if you don't have a way to get this conn again it'll be there for a long time until the win release it or when you close the program/server....

I found some people talking about Sleeping connections, but I dispose of mine and the connection is completely wiped out. I use a mysql pool for maximum connections- and I just tested it using "3" as the maximum. I don't get the error that the other people are specifying. Might it be possible that they're talking about an older version?

[-Shunsui-]

Originally Posted by 12tails

I like flat file too... i'm currently testing various types of databases system.... right now i'm testing the flat file one... should be the one i'll use...

@Fang
Actually the problem is not open or close a connection... when u call the Close(); or Dispose(); the connection is not really closed... its put into the mysql pool in a sleep state... so if you don't have a way to get this conn again it'll be there for a long time until the win release it or when you close the program/server....

correcto,

[12tails]

Originally Posted by Fаng

I found some people talking about Sleeping connections, but I dispose of mine and the connection is completely wiped out. I use a mysql pool for maximum connections- and I just tested it using "3" as the maximum. I don't get the error that the other people are specifying. Might it be possible that they're talking about an older version?

Hmmm... not rlly... i use the latest mysql version and the sleeping connections still exist.... there is a way to kill them... but the mysql seems to have some problems with that command... ;X

[Spirited]

Originally Posted by 12tails

Hmmm... not rlly... i use the latest mysql version and the sleeping connections still exist.... there is a way to kill them... but the mysql seems to have some problems with that command... ;X

Really? That's so weird...
I'm using this in my connection string: "Pooling=true; Max Pool Size = 30; Min Pool Size = 5;". Idk if that might fix it or not but I'm not getting that error. =|

[-impulse-]

Useless code.

With the code you provided you have two separate pools. One is thread-safe(the one that comes with the MySQL wrapper, and which stores the actual connections to the server, which are the so called 'drivers') and yours which is not thread-safe and which store just a wrapper to those drivers.

Your pool is for MySQL connections, connections which should be disposed after every use.
Note that a MySQLConnection that you use to execute a command IS NOT a direct connection the MySQL server. (You should download the source they provide for their wrapper.)


You can't have one hard used mysql connection because that's not how it works when you use the dll they(Oracle) give you.

The best thing you can do for a MySQL database is built a command wrapper that uses the most efficient way to create the queries, and if you think if you do something like "var cmd = new MySqlCommand("update table set var1=" + value1 +", var2=" + value2 +" where id=" + primaryKey + ";", conn);" is the best way to do it, I just gotta tell you there are better ways.
And, beside that, you should learn how to use connection properties properly, which to use, when to use. ( aka conn = new MySqlConnection(connection properties here); ).

Edit:
Sleeping connections. (connections here is as I was saying not MySqlConnection class. they are drivers (thats how they call the class)). If you don't want sleeping drivers(aka idle drivers), just use the Pooling=false property in which case you would be able to use the MySqlConnection class as a hard used connection directly to the sql server.

[Spirited]

Ah... I see the problem now. Thanks Impulse.

[12tails]

nice explanation impulse... tkx ;D

[marlyandedsel]

Originally Posted by -impulse-

Edit:
Sleeping connections. (connections here is as I was saying not MySqlConnection class. they are drivers (thats how they call the class)). If you don't want sleeping drivers(aka idle drivers), just use the Pooling=false property in which case you would be able to use the MySqlConnection class as a hard used connection directly to the sql server.

when doing that and not doing that, what is the advantage the "Pooling=flase" and the "Pooling=true" thing?

[Spirited]

Originally Posted by marlyandedsel

when doing that and not doing that, what is the advantage the "Pooling=flase" and the "Pooling=true" thing?

Basically, you're deciding whether or not the .dll file will control the pool. If you set it to true, MySql will control the pool and after a while, will slowly corrupt itself. If you have it set to false, it will let you make your own pool class for it. (Opening connections without pooling set to true take longer, so you have to make a new pool for it.)

You can also set it to true and kill connections that are in sleep every once in a while. I found a solution for that on Google.

[marlyandedsel]

my MysqlComman

using (var conn = DataHolder.MySqlConnection)
{
conn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(Command, conn);
cmd.ExecuteNonQuery();
}

Mysql REader

private void TryFill(MySqlCommand command)
{
MySql.Data.MySqlClient.MySqlConnection conn = DataHolder.MySqlConnection;
conn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(command.Comman d, conn);
Reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) ;

}

and part of the DataHolder

public static class DataHolder
{
private static string MySqlUsername, MySqlPassword, MySqlDatabase, MySqlHost;
private static string ConnectionString;
public static void CreateConnection(string user, string password, string database, string host)
{
MySqlUsername = user;
MySqlHost = host;
MySqlPassword = password;
MySqlDatabase = database;
ConnectionString = "Server=" + MySqlHost + ";Database='" + MySqlDatabase + "';Username='" + MySqlUsername + "';Password='" + MySqlPassword + "';Pooling=true; Max Pool Size = 1000; Min Pool Size = 5";

}

public static MySql.Data.MySqlClient.MySqlConnection MySqlConnection
{
get
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection();
conn.ConnectionString = ConnectionString;
return conn;
}
}

can you tell me if this is good or not?