How can I test the database connection in LightSpeed?

We occasionally get asked if it’s possible to test the database connection in LightSpeed, for example to check a database name entered by the user. Although this functionality isn’t build in, it’s very easy to add for yourself. Here’s an extension method for the LightSpeedContext class that does the job:

public static bool TestConnection(this LightSpeedContext context)
{
  try
  {
    using (IDbConnection connection = context.DataProviderObjectFactory.CreateConnection())
    {
      connection.ConnectionString = context.ConnectionString;
      connection.Open();
      return true;
    }
  }
  catch (Exception)
  {
    return false;
  }
}
 
// Usage: bool connectable = context.TestConnection()

A couple of things to watch out for:

1. I’m catching Exception rather than DbException to detect a connection failure. This is because the Oracle ODP driver and SQL Server Compact Edition can throw exceptions that don’t derive from DbException (thanks chaps). If you’re not using either of these drivers, you can catch DbException instead and make FxCop a happy FxCop.

2. The TestConnection method doesn’t try to enforce any particular timeout. The default connection timeout is often quite long, which can make for a bad user experience if they enter an invalid server name, so for databases that live on remote servers you’ll probably want to add a timeout setting to the connection string. For most databases, this is done using Connection Timeout=…, but some databases use a different keyword (e.g. PostgreSQL) or don’t support timeouts at all (e.g. in-process databases like SQLite).

Finally, don’t forget that just because the connection succeeded when you tested it, doesn’t mean the connection will succeed when the user comes to use it! Networks go down, DBAs schedule maintenance, etc. etc. So you still need to provide exception handling around your main data access code — this function is only advisory!

Tagged as LightSpeed

3 Responses to “How can I test the database connection in LightSpeed?”

  • Awesomeness, thank you very much :)

  • I’m not very familiar with Lightspeed (or ADO.NET), but using this code (and assuming it succeeds to connect). Won’t the connection stay open unnecessary?

  • Hi Frank, thanks for your comment. We don’t explicitly close the connection because the connection is in a using statement which means it will be disposed of automatically when the closing brace of the using statement is hit. By all means you can call close explicitly if you want but it shouldn’t make any difference.

  • Leave a Reply

Archives

Join our mailer

You should join our newsletter! Sent monthly:

Back to Top