Re: running multiple queries with one connection
Posted by: Chris Bassett
Date: September 19, 2014 01:11PM

Here is a snippet of the code I'm using, and where the problem is occuring:

try {
m_strQuery = _query;
m_sqlCommand.Connection = m_sqlConnection;
m_sqlCommand.CommandText = m_strQuery;
m_sqlDataReader = m_sqlCommand.ExecuteReader();
} catch (Exception ex) {
MessageBox.Show(ex.ToString());
}

Notes:
* m_sqlCommand is an instance of the MySqlCommand class
* m_sqlDataReader is an instance of the MySqlDataReader class
* m_sqlQuery is a private member variable that is assigned the query string

This appears as part of a class module, and the reason I put this into a class is so that I can have error checking done automatically, so these functions are in a class I created called MySqlInterface (which basically allows for the general functions with MySQL--reading/writing to the database, and also helps implement the Singleton pattern to help control database connection to one instance at a time).

The thing is that the above code could be called two or three times in a row (With different queries, of course), but after the first one runs, that's where I get the errror about a connection alread being open for the reader.

I had added (but removed from the listing above) the two lines (That go right after the TRY opening brace):

m_sqlConnection.Close(); // Close connection
m_sqlConnection.Open(); // Reopen connection

(m_sqlConnection is an instance of the MySqlConnection class).

I did this, and it seems to work, but want to see if this is the best option as I feel it could cause problems if done reapeatedly over the course of a day where the app may run a few dozen queries.

Options: ReplyQuote


Subject
Written By
Posted
Re: running multiple queries with one connection
September 19, 2014 01:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.