Frequent Opening and Closing of MySQL Database over Cloud

Question

I have a local server MS-SQL situated in one of our branch. I want to transfer new data from the local server to MySql table (over the cloud) every 1 minute.

I have coded a small C# application which opens both the server connections, search and insert the new rows into MySql Database and then close the connection.

Now my question is, as I am continously opening, updating and closing the Mysql connection every minute; will there be any issues? Is there any other alternate method through which I can establish a single connection to MySql database and then keep on inserting the new rows every minute. I appreciate your valuable support.

Below is the coding part I use to open, update and close the connection: Remember, the below method is executed every minute.

if (queryValues != "")
        {
            queryValues = queryValues.Remove(queryValues.Length - 1);
            query = query + queryValues + ")";
            MyCommand3 = new MySqlCommand(query, MyConn3);

            if (MyConn3.State == 0)
                MyConn3.Open();
            MyReader3 = null;
            MyReader3 = MyCommand3.ExecuteReader();     
            MyCommand3.Dispose();
            MyReader3.Dispose();
            MyConn3.Close();
        }

Answer 1

It is not a problem.

Connector/NET, the MySQL driver for C#, offers connection pooling by default. That is, it keeps the connection between your program and MySQL open, so you can re-use it with another .Open() operation.

It only closes the connections when they get to be three minutes old, so you should be fine with a once-per-minute operation. And, it manages stuff like lost connections pretty well, so you don't have to.

What's more, opening a connection isn't a high-overhead operation. Only if you do it multiple times a second does the overhead get out of hand.