Do you have to open a connection to MYSQL in vb 2008

I have always opened a connection to a MYSQL database as follows
Public daData1Adapter As MySqlDataAdapter
    Public cb1CommandBuilder As MySqlCommandBuilder
    Public conn As New MySql.Data.MySqlClient.MySqlConnection
    Public myConnectionString As String = "server=127.0.0.1;" _
                   & "uid=root;" _
                   & "pwd=somepassword" _
                   & "database=somedatabase pooling=false;" _
                   & "allow zero datetime=no;"

 conn.ConnectionString = myConnectionString
  conn.Open()
 daData1Adapter = New MySqlDataAdapter("Select * From  mydatabase  where field = " & fieldvalue & "  ", myConnectionString)
cb1CommandBuilder = New MySqlCommandBuilder(daData1Adapter)
daData1Adapter.Fill(dtTable)
conn.Dispose()
 conn.Close()

My questions is do I really need the
conn.ConnectionString = myConnectionString
 conn.Open()
conn.Dispose()
 conn.Close()

Because I can access the database and it doesn't look like any connections are open to MYSQL.



       
whiwexAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

blandyukCommented:
Because your referencing the connection string in myConnectionString and actually not use the object "conn", it should work without it. Test it and see but from what I can see, it'll work without it.
0
whiwexAuthor Commented:
It works but Im just wondering if there are connections being left open or memory being eaten up without the close connection.
0
blandyukCommented:
There are different ways of getting the data, your using a dataSet which can be done as above. When opening the connecting using MySQLConnection, you can use a dataReader, (which is faster):

Creating a DataReader Create and Open the Database Connection Create the DataReader From a Command Object Close the Reader and the Connection SqlCommand mySqlCommand = new SqlCommand( "select * from customers", mySqlConnection); SqlDataReader myReader = mySqlCommand.ExecuteReader(); SqlConnection mySqlConnection = new SqlConnection("server=(local)\\NetSDK; Trusted_Connection=yes;database=northwind"); mySqlConnection.Open(); If (myReader != null) myReader.Close(); if (mySqlConnection.State == ConnectionState.Open) mySqlConnection.Close();

Dataset:

Calling Stored Procedures Stored Procedures Provide Security for Database Set Up the DataAdapter Run the Stored Procedure and Store Returned Records SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = new SqlCommand(); mySqlDataAdapter.SelectCommand.Connection = mySqlConnection; mySqlDataAdapter.SelectCommand.CommandText = "GetProducts"; mySqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure; mySqlDataAdapter.Fill(myDataSet,"Products");

The dataAdapter manages the connection so you don't have to close it. The Adapter does all the work. I would however set the mySqlDataAdapter = Nothing just to be sure.

0
CodeCruiserCommented:
>My questions is do I really need the
>conn.ConnectionString = myConnectionString

No. You can change the following
Public conn As New MySql.Data.MySqlClient.MySqlConnection

to
Public conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
but place it after myconnectionstring



>My questions is do I really need the
 conn.Open()
conn.Dispose()
 conn.Close()


You need the Open() to be able to use the connection. Dispose() internally calls Close() so you can skip the Close() call.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
whiwexAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.