C# DbConnection cast to SqlConnection

Question

I found this piece of code in one application

Database database = DatabaseFactory.CreateDatabase("connection string");
DbConnection connection = database.CreateConnection();
connection.Open();
SqlConnection sqlConnection = (SqlConnection)connection;

Is it safe, SqlConnection derieve from DbConnection. Database comes from Microsoft.Practices.EnterpriseLibrary.Data. According to documentation CreteDatabase returns DbConnection.

Answer 1

No it is not safe, casting is never safe and it may blow anytime while your application is running. While SqlConnection derives indeed from DbConnection you are not guaranteed that database.CreateConnection() will return a SqlConnection as this could be parametrized in the configuration file. Also why do you need to cast to SqlConnection? It is always better to work with classes that are higher in the hierarchy to avoid coupling your code with a specific implementation which will make your code impossible to test in isolation.

While the EnterpriseLibrary does a decently good job in keeping things abstract you are killing everything with this cast. Also you should make sure that disposable resources are always disposed properly. How about this instead:

Database database = DatabaseFactory.CreateDatabase("connection string");
using (var conn = database.CreateConnection())
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT id FROM foo";
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // TODO: work with the results here
        }
    }
}

This way your code is less fragile to database changes in the config file. Well of course you still have this SQL hardcoded and there are ORMs that will take care of this situation. They will also allow you to focus on the real domain of your application instead of wasting time in writing SQL queries and casting from one database provider to another. But for a simple application this is OK.