Testing an Entity Framework database connection

I have an app that connects to a MYSQL database through the entity framework. It works 100% perfectly, but I would like to add a small piece of code that will test the connection to the database upon app startup.

I had the idea of simply running a tiny command to the database and catching any exceptions, however if there is a problem (eg App.Config missing or Database server down) the app takes a huge amount of time to run this code and then throw the exception (~1 min). I imagine this is due to connection timeouts etc but I have fiddled with such properties to no avail.

Would anyone be able to assist with any ideas as to where to go?

Are you just wanting to see if the DB connection is valid? If so take a look at the

using (DatabaseContext dbContext = new DatabaseContext())
{
     dbContext.Database.Exists();
}

http://msdn.microsoft.com/en-us/library/gg696617(v=vs.103).aspx

and for checking if a server machine is up, DB server or web services server , try this:

public PingReply Send( string hostNameOrAddress )

http://msdn.microsoft.com/en-us/library/7hzczzed.aspx

The solution as @Danilo Breda pointed out is to call the DbContext.Database.Connection.Open()

It is tested with EF6.

My implementaion:

    public static bool CheckConnection()
    {
        try
        {
            MyContext.Database.Connection.Open();
            MyContext.Database.Connection.Close();
        }
        catch(SqlException)
        {
            return false;
        }
        return true;
    }

In EntityFramework Core you can simply call: Database.CanConnect();.

(using EF Core 2.2.1)

Summary: Determines whether or not the database is available and can be connected to.

Note that being able to connect to the database does not mean that it is up-to-date with regard to schema creation, etc.

I use this code for my project:

private bool TestConnectionEF()
        {
            using (var db = new SistemaContext())
            {
                try
                {
                    db.Database.Connection.Open();
                    if (db.Database.Connection.State == ConnectionState.Open)
                    {
                        Console.WriteLine(@"INFO: ConnectionString: " + db.Database.Connection.ConnectionString 
                            + "\n DataBase: " + db.Database.Connection.Database 
                            + "\n DataSource: " + db.Database.Connection.DataSource 
                            + "\n ServerVersion: " + db.Database.Connection.ServerVersion 
                            + "\n TimeOut: " + db.Database.Connection.ConnectionTimeout);
                        db.Database.Connection.Close();
                        return true;
                    }
                    return false;
                }
                catch(Exception ex)
                {
                    throw ex;
                }
            }
        }

I know this is an old question, but here is my answer for anyone looking for a newer implementation.

I was able to use CanConnect to check the status of the database:

_database.Database.CanConnect();

# Async too
await _database.Database.CanConnectAsync(_cancellationTokenSource.Token);

I hope this helps others as well. Cheers!

Testing an Entity Framework database connection

Related

Recent Posts