C# mysqlconnector - how to check for loss of con?

[Red Squirrel]

What is a proper way to check at query time if the connection has been lost? This is the code where I'd like to have this, I just can't figure out how to do it. I do get a query error but that's too general, I can't assume that a query error is loss of connectivity. (in my case it most likely will be though). I *could* do a string search but that seems dirty as the error messages may vary.


public bool Query(string query)
{
/* if(m_con.State != m_con.ConnectionState.Open)
{
ThrowError("query...","Seem to have lost connection, will try to reconnect...");

try
{
m_con.Open();
}
catch
{
ThrowError(query,"FATAL:Unable to connect to DB");
return false;
}
}*/

try
{
if(m_Reader!=null) m_Reader.Close();

MySqlCommand command = m_con.CreateCommand();
command.CommandTimeout = 0;
command.CommandText = query;

m_Reader = command.ExecuteReader();
}
catch (Exception ex)
{
string exstr=ex.ToString()

/*
if( Connection is lost )
{
//handle properly
//set flag to mark this connection as lost so it can try to reconnect later
return false;
}*/


ThrowError(query,exstr);
return false;
}

Debugger.Write(String.Format("mysql{0}",m_conid),"Query:{0}",query);

return true;
}

 

[nickbits]

You shouldn't need to hold onto the connection. .NET maintains a pool of connections itself. But aside from that just catch the exception and check the SqlException.Number property.

Disclaimer: I have only used MS SQL server but I am assuming the MySQL classes work in the same manner.

 

[imported_Dhaval00]

It shouldn't matter, but what is the type of "m_con" object?

The DbConnection class (base class for all SQL connections) exposes a StateChange event which is raised every time the state of the underlying connection changes. Here is the link: http://msdn.microsoft.com/en-u...atechange(VS.100).aspx. You can subscribe to the event and check the EventArgs to inform your logic.

 

[Red Squirrel]

m_con is of type MysqlConnection.

I think I figured it out, this seems to work so far:

public bool Query(string query)
{
//check if last query had a connection error
if(m_lostcon && !Reconnect())
{
Debugger.Write("mysqlcon","{0}: Query failed, no connection",m_conid);
return false;
}

//do query:
try
{
if(m_Reader!=null) m_Reader.Close();

MySqlCommand command = m_con.CreateCommand();
command.CommandTimeout = 0;
command.CommandText = query;

m_Reader = command.ExecuteReader();
}
catch (Exception ex)
{
string exstr=ex.ToString();

ThrowError(query,exstr);

//check if we lost con to server
if(m_con.State.ToString()!="Open")
{
Debugger.Write("mysqlcon","{0}: Lost connectivity to server.",m_conid);
m_lostcon=true;

//try to reconnect at least once:
if(Reconnect())
{
Debugger.Write("mysqlcon","{0}: Reconnected, retrying query...",m_conid);
return Query(query);
}

return false;
}
//----

return false;
}

Debugger.Write("mysql","Query:{0}",query);

return true;
}

There's also a state variable but not sure how consistant it is set so decided to check at the query instead to be extra safe. In my program I need to handle the query error then write the data in a cache until server is back up so it's important to catch it at the query.

 

[MrChad]

Holding onto a connection object is generally a bad idea. When you're ready to interact with the database, establish the connection (ADO.NET will issue one from its pool), execute the query(ies) and close the connection (returning it to the pool). You'll know if you can't establish a connection right away.

 

[Red Squirrel]

Originally posted by: MrChad
Holding onto a connection object is generally a bad idea. When you're ready to interact with the database, establish the connection (ADO.NET will issue one from its pool), execute the query(ies) and close the connection (returning it to the pool). You'll know if you can't establish a connection right away.

Well it depends. This app is constantly querying so it would make no sense to keep opening/closing the connection. Or would it not be an issue? I'm talking like, multiple queries per second.

 

[imported_Dhaval00]

Even if you're issuing multiple commands, you should [in most logical implementations] close the connection. You have to remember that when you call Open() and Close(), you're not physically opening and closing a connection - you're simply accessing a connection from the pooler.

In short, let the pooler do its job and always open and close your connections. I have yet to see an ADO.NET application (ignoring the buggy ones) that persisted an open connection because of "performance issues."

 

[Red Squirrel]

Originally posted by: Dhaval00
Even if you're issuing multiple commands, you should [in most logical implementations] close the connection. You have to remember that when you call Open() and Close(), you're not physically opening and closing a connection - you're simply accessing a connection from the pooler.

In short, let the pooler do its job and always open and close your connections. I have yet to see an ADO.NET application (ignoring the buggy ones) that persisted an open connection because of "performance issues."

Oh I see so the TCP connection still stays up? That may not be as bad then, I may go ahead and open/close. Though since I got it working without needing that, is there still an advantage to change it? I might just go ahead and do it anyway but I just want to get an idea of why it's better.

Also what does ADO.net refer to, is that something just for SQL or will it work the same way in MySQL?

 

[JasonCoder]

If you're issuing a ton of queries in a logical unit of work than I would go ahead and keep the connection open. Probably not terrible to open/close after each operation but if you're doing, say, 100 queries in a loop, I'd keep the connection around.

ADO.Net is the collective name of data access libraries in .Net. Your connection and command objects you are using are ADO.Net objects.