I am making a program in Visual Basic .NET 2008 which will connect to mysql database, retrieve records and probably insert/edit them if required. Now I am able to connect to the mysql db using mysql connector for .net. I do not want to keep the connection open all the time. I want that when data needs to be read or updated then only connection should be opened.
I thought of using a global variable (e.g. mysqlconnection) and opening a connection using that variable. But I read the following in mysql tutorial.
"The Connector/NET supports connection pooling. This is enabled by default, but can be turned off via connection string options.
Connection pooling works by keeping the native connection to the server live when the client disposes of a MySqlConnection.
Subsequently, if a new MySqlConnection object is opened, it will be created from the connection pool, rather than creating a
new native connection. This improves performance.
To work as designed, it is best to let the connection pooling system manage all connections. You should not create a globally accessible
instance of MySqlConnection and then manually open and close it. This interferes with the way the pooling works and
can lead to unpredictable results or even exceptions".
What should I do ? Should I create a new connection everytime to read data to & fro or using a variable which is global and use .open & .close to manually open/close it.
Will be grateful if anyone can help me out.