Is there a way to check if a database is available? I would like to build in disaster recovery functions into my app so that if a network disconnection has occurred, it will perform certain tasks to return to original state. But I want to make sure database is available before initializing recovery.

Thanks.

Don't know a method to check connection that IDbConnection would provides. What about something like this:

try
{
     adapter.OpenConnection();
     thereIsAnError = false;
     adapter.CloseConnection();
}
catch (Exception ex)
{
      // something is wrong. manage the exception
      thereIsAnError = true;
}

I didn't mention in the original post but I am using self-servicing. Is there an equivalent method I can use there. I don't seem to be able to find it.

daelmo wrote:

Don't know a method to check connection that IDbConnection would provides. What about something like this:

try
{
     adapter.OpenConnection();
     thereIsAnError = false;
     adapter.CloseConnection();
}
catch (Exception ex)
{
      // something is wrong. manage the exception
      thereIsAnError = true;
}

Hi,

Maybe you can try this :

SqlConnection connection = DbUtils.CreateConnection();

{
     connection.Open();
     thereIsAnError = false;
     connection.Close();
}
catch (Exception ex)
{
     // something is wrong. manage the exception
     thereIsAnError = true;
}

This works. What I am noticing now is an interesting and related issue. When my network (sql database) connection is lost and then subsequently reconnected, any attemp to talk to the database results in an exception being thrown. Do I somehow have to reconnect my project with the database in code and how do I do this? I am using self-servicing.

Aurelien wrote:

Hi,

Maybe you can try this :

SqlConnection connection = DbUtils.CreateConnection();

{
     connection.Open();
     thereIsAnError = false;
     connection.Close();
}
catch (Exception ex)
{
     // something is wrong. manage the exception
     thereIsAnError = true;
}

Hi,

That's wierd... what is the exception thrown?

The exception says: System.Data.SqlClient.SqlException. It is thrown when I try to retrieve the entity "ContractSpecs". Below is the complete text.

ex {System.Data.SqlClient.SqlException} System.Exception [System.Data.SqlClient.SqlException] {System.Data.SqlClient.SqlException}System.Data.SqlClient.SqlException HelpLink Nothing String InnerException Nothing System.Exception Message "General network error. Check your network documentation." String Source ".Net SqlClient Data Provider" String StackTrace " at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IEntityFields fieldsToReturn) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAsDataTableAction(IEntityFields fieldsToReturn, DataTable tableToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Boolean allowDuplicates, IGroupByCollection groupByClause, ITransaction transactionToUse, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAsDataTableAction(Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Int32 pageNumber, Int32 pageSize) at HFDB.DaoClasses.ContractSpecsDAO.GetMultiAsDataTable(Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Int32 pageNumber, Int32 pageSize) at HFDB.CollectionClasses.ContractSpecsCollection.GetMultiAsDataTable(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, Int32 pageNumber, Int32 pageSize) at HFDB.CollectionClasses.ContractSpecsCollection.GetMultiAsDataTable(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses) at Northwater.DD.HFTrade.FormInstrument.FormInstrument_Load(Object sender, EventArgs e) in C:\DOTNET Models\Trade Models\High Frequency\HFTrade\UiClasses\FormInstrument.vb:line 80" String TargetSite {System.Reflection.RuntimeMethodInfo} System.Reflection.MethodBase

Aurelien wrote:

Hi,

That's wierd... what is the exception thrown?

After the connection is restored, do you call DbUtils.CreateConnection();?

No I don't. Am I supposed to? What do I do with the connection object returned by DbUtils.CreateConnection?

Walaa wrote:

After the connection is restored, do you call DbUtils.CreateConnection();?

No I don't. Am I supposed to? What do I do with the connection object returned by DbUtils.CreateConnection?

No, I actually don't recommend using it at all.

When the database is available again, does the code checking the availability of the database pass, or does it fail.

The issue is that I think the connection created by CreateConnection that you use should be disposed, instead of being re-used from the connection pool, at a later stage.

What I suggest instead of creating a connection to test the availability of the database, just select one row from any table that you are sure it has data.

Walaa wrote:

No I don't. Am I supposed to? What do I do with the connection object returned by DbUtils.CreateConnection?

No, I actually don't recommend using it at all.

When the database is available again, does the code checking the availability of the database pass, or does it fail.

The issue is that I think the connection created by CreateConnection that you use should be disposed, instead of being re-used from the connection pool, at a later stage.

What I suggest instead of creating a connection to test the availability of the database, just select one row from any table that you are sure it has data.

What you suggested works. Thanks.