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?
 
CodeCruiserConnect With a Mentor Commented:
>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
 
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
 
whiwexAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.