How do I manage connection to MySql db from VB.NET ?
Hello Guys,
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.
Thanks,
Cheers,
Saurabh
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.
Thanks,
Cheers,
Saurabh
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi! Sukhani
Sorry for the late response.
See this I hope so it will answer your question:
http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlconnection.html
A part From link:
Resetting the ConnectionString on a closed connection resets all connection string values (and related properties) including the password. For example, if you set a connection string that includes "Database= MyDb", and then reset the connection string to "Data Source=myserver;User Id=myUser;Password=myPass"
The connection string is parsed immediately after being set. If errors in syntax are found when parsing, a runtime exception, such as ArgumentException, is generated. Other errors can be found only when an attempt is made to open the connection.
So, If you will create a connection globally and it has something wrong you will be informed at .Open() method. On the other hand if you will pass it as a parameter ArgumentException will be throw.
So, this can be the reason for that. Otherwise I don't think so that declaration globally is WRONG.
Sorry for the late response.
See this I hope so it will answer your question:
http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlconnection.html
A part From link:
Resetting the ConnectionString on a closed connection resets all connection string values (and related properties) including the password. For example, if you set a connection string that includes "Database= MyDb", and then reset the connection string to "Data Source=myserver;User Id=myUser;Password=myPass"
The connection string is parsed immediately after being set. If errors in syntax are found when parsing, a runtime exception, such as ArgumentException, is generated. Other errors can be found only when an attempt is made to open the connection.
So, If you will create a connection globally and it has something wrong you will be informed at .Open() method. On the other hand if you will pass it as a parameter ArgumentException will be throw.
So, this can be the reason for that. Otherwise I don't think so that declaration globally is WRONG.
The way to use database without keeping the conntection is storing all database tables in Dataset, see the following:
http://vb.net-informations.com/dataset/dataset-oledb.htm
http://vb.net-informations.com/dataset/dataset-oledb.htm
ASKER
I would like to sum up. If I declare a new mysql connection object everytime a connection is established it is okay. It will be a part of the connection pool. I need not declare it globally and then open/close it when I want. Right. I declare it locally, open connection , get data close it. And this repeats as and when required.
YES.
ASKER
But what does this mean -
"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.
One approach that simplifies things is to avoid manually creating a MySqlConnection object. Instead use the overloaded methods that take a connection string as an argument. Using this approach, Connector/NET will automatically create, open, close and destroy connections, using the connection pooling system for best performance. "
It says avoid manually creating a mysqlconnection object. What does this mean ? And what about use overload methods. In methods also we will create a connection & close it.