We help IT Professionals succeed at work.

How do I manage connection to MySql db from VB.NET ?

1,624 Views
Last Modified: 2012-05-09
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
Comment
Watch Question

Staff Software Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
From the information what I get is to create mysql connection object locally and after work is done close it. Not to create object globally.
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.
Shahan AyyubStaff Software Engineer
CERTIFIED EXPERT

Commented:
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           MySqlConnection.Database property  is no           longer set to MyDb.          

           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.
Mohamed AbowardaSenior Software Engineer
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.
Shahan AyyubStaff Software Engineer
CERTIFIED EXPERT

Commented:
YES.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.