Factory Pattern Database connection

Question

I am trying to implement factory pattern on database connection using MySQL, SQL Server facing weird error

Object reference not set to an instance of an object

on SQL command object

internal class SqlServerDB : IDatabase
{
    private SqlConnection _Connection = null;
    private SqlCommand _Command = null;

    public IDbCommand Command
    {
            get
            {
                if (_Command == null)
                {
                    _Command.Connection = (SqlConnection)Connection;

                    //_Command = new SqlCommand();
                }
                return _Command;
            }
    }

    public IDbConnection Connection
    {
            get
            {
                if (_Connection == null)
                {
                    string connectionString = ConfigurationManager.ConnectionStrings["testSQL"].ConnectionString;
                    _Connection = new SqlConnection(connectionString);
                }
                return _Connection;
            }
    }
}

Database factory section :

public static class DatabaseFactory
{
            public static IDatabase CreateDatabase(DBType type)
            {
                switch (type)
                {
                    case DBType.SqlServer:
                        return new SqlServerDB();

                    case DBType.MySql:
                        return new MySQLDB();
                }

                return null;
            }
}

Main method

static void Main(string[] args)
{
    IDatabase database;
    DBType databaseType = DBType.SqlServer;

    database = DatabaseFactory.CreateDatabase(databaseType);
    IDbConnection connection = database.Connection;

    IDbCommand command = database.Command;
    command.CommandType = CommandType.Text;
    command.CommandText = "select * from User";

    connection.Open();
}

and the selection of database by Enum.

Solution 1

there is an error on the first if,

if (_Command == null)
{
    _Command.Connection = (SqlConnection)Connection;
    //_Command = new SqlCommand();
}

Could be more like:

if (_Command == null)
{
    _Command = new SqlCommand();
    _Command.Connection = (SqlConnection)Connection;        
}

Solution 2

You do not have to use your own switch statement.

First of all, make sure that your connectionString contains a providerName like this:

< ?xml version='1.0' encoding='utf-8'?>
<configuration>
    <connectionstrings>
        <add name="Name"  providerName="System.Data.ProviderName" connectionString="Valid Connection String;"></add>
    </connectionstrings>
</configuration>

then you can create your connection by using:

var connectionString = ConfigurationManager.ConnectionStrings["MyConName"];
var providerName = connectionString.ProviderName;
var factory = DbProviderFactories.GetFactory(providerName);
var connection = factory.CreateConnection();
connection.ConnectionString = connectionString.ConnectionString;
connection.Open();

(Add null checks so that you can tell your user/developer that the configuration is wrong)

that way you can support most SQL databases as long as you stick to common sql queries.

Want to know more about proper ADO.NET handling? http://blog.gauffin.org/2013/01/ado-net-the-right-way/