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

It should be safe as long as you never change the connection string to connect to anything other than a SQL Server Database. If that's ever a possibility, then you should add a little more logic to make things safe:

Database database = DatabaseFactory.CreateDatabase("conn string");

using(DbConnection conn = database.CreateConnection())
{    
    if(conn is SqlConnection)
    {
        var sqlConn = conn as SqlConnection;
    }
}

Answer 2

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.

Answer 3

It depends on the databases you are using in your application. From the code you wrote it looks like that only SQL Server is used. If it is so, then you can cast DbConnection to SqlConnection safely. In fact DbConnection is a base class for any other database connection. In your case it is SqlConnection (which is used to work with SQL Server database), also there are different databases like Oracle, Mysql, etc and their providers usually have own classes for connections. So if your app uses another databases or may use in the future it is unsafe to have such cast.