Testing Connection problems - How

Topics: Data Access Application Block
Jan 12, 2007 at 12:41 PM

Hey,

How can I test if there is an connection-exception when using database application block from the enterprise service?

I stopped the sqlserver.
I do now the following :

Database db = null;
IDataReader reader = null;
try
{
db = DatabaseFactory.CreateDatabase("Logging");
}
catch (Exception ex)
{
string e = ex.Message;
}

string sqlQuery = "SELECT * FROM log";
try
{
reader = db.ExecuteReader(CommandType.Text, sqlQuery);
}
catch (Exception ex)
{
string e1 = ex.Message;
}

It is only in the second catch that I receive an error.
But just in case of connection problems I want to try it (the executereader) again after a few seconds.

I receive an SQLException : but how can I see that it is an connection problem?
And how can I test this when I change my connection to an oracle database without changing my code in the catch block.

I just need to know there is an connectionproblem independant of which database (oracle or SQL) I used so I can take actions.

Thanks in advance,
jac
Jan 12, 2007 at 5:14 PM
The reason this line is not causing an exception

db = DatabaseFactory.CreateDatabase("Logging");

is because it does not open a connection to the database. You are just creating an instance of a Database Class. A connection is not opened until you actually do something with the class requiring a connection to be opened.

You could try to do something like this:

DbConnection connection = db.CreateConnection();
connection.Open();

and see if that causes an error, but my guess is that you may run into connection pooling issues if connections have been successfully used in the past and pooled for later use.

I believe the best way is to run a query or call a stored procedure that you know will work as long as the database connection is available. The below is database independent:

try {
db = DatabaseFactory.CreateDatabase("Logging");
db.ExecuteNonQuery("SomeStoredProcedureThatDoesNothingAndShouldWork");
}
catch (SqlException ex)
{
// Perhaps Look at Error Codes...
}

SQL Server returns error codes, so you could look more into them and see if you can determine if it is definitely a connection problem based on the error code.

Regards,

Dave

__________________

David Hayden
Microsoft MVP C#
http://www.davidhayden.com/