Data Layer Abstract Factory

Question

I'm new on developing an Abstract Factory pattern, and would like to create an abstract factory in the data layer that will help me link this layer to any other databases for example sql and oracle. Can you help me on developing this task please. Note that the connection string of the database will be found in this layer not in the presentation..

Thanks

EDITED

public abstract class Database
{
    public string connectionString;

    #region Abstract Functions

    public abstract IDbConnection CreateConnection();
    public abstract IDbCommand CreateCommand();
    public abstract IDbConnection CreateOpenConnection();
    public abstract IDbCommand CreateCommand(string commandText, IDbConnection connection);
    public abstract IDbCommand CreateStoredProcCommand(string procName, IDbConnection connection);
    public abstract IDataParameter CreateParameter(string parameterName, object parameterValue);

    #endregion
}

---

public class SQLDatabase : Database
{
    public override IDbConnection CreateConnection()
    {
        return new SqlConnection(connectionString);
    }

    public override IDbCommand CreateCommand()
    {
        return new SqlCommand();
    }

    public override IDbConnection CreateOpenConnection()
    {
        SqlConnection connection = (SqlConnection)CreateConnection();
        connection.Open();

        return connection;
    }

    public override IDbCommand CreateCommand(string commandText, IDbConnection connection)
    {
        SqlCommand command = (SqlCommand)CreateCommand();

        command.CommandText = commandText;
        command.Connection = (SqlConnection)connection;
        command.CommandType = CommandType.Text;

        return command;
    }

    public override IDbCommand CreateStoredProcCommand(string procName, IDbConnection connection)
    {
        SqlCommand command = (SqlCommand)CreateCommand();

        command.CommandText = procName;
        command.Connection = (SqlConnection)connection;
        command.CommandType = CommandType.StoredProcedure;

        return command;
    }

    public override IDataParameter CreateParameter(string parameterName, object parameterValue)
    {
        return new SqlParameter(parameterName, parameterValue);
    }
}

Those are the two classes I created..

Answer 1

The functionality already exists.

Add a connection string to app/webb.config:

<connectionStrings>
    <add name="TheDatabase" providerName="System.Data.OleDb" connectionString="Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User Id=xxx;Password=yyy;Data Source=zzzz;Extended Properties="/>
  </connectionStrings>

Build the connection using a factory:

var connectionString = ConfigurationManager.ConnectionStrings["TheDatabase"];
var providerName = connectionString.ProviderName;
var factory = DbProviderFactories.GetFactory(providerName);

Get a connection:

var connection = factory.CreateConnection();

Get a command:

var command == connection.CreateCommand();

The only thing you need to do is to switch driver in the app/web.config. No other changes are required.

Update

public class Database
{
    public static IDbConnection CreateOpenConnection()
    {
        var connectionString = ConfigurationManager.ConnectionStrings["TheDatabase"];
        var providerName = connectionString.ProviderName;
        var factory = DbProviderFactories.GetFactory(providerName);
        var connection = factory.CreateConnection();
        connection.Open();
        return connection;
    }
}

class FlowerManager : DataWorker
{
    public static void GetFlowers()
    {
        using (IDbConnection connection = Database.CreateOpenConnection())
        {
            using (IDbCommand command = connection.CreateCommand("SELECT * FROM FLOWERS", connection))
            {
                using (IDataReader reader = command.ExecuteReader())
                {
                    // ...
                }
            }
        }
    }
}

Answer 2

Much of the required functionality can be obtained from

 System.Data.Common.DbProviderFactories

where you can get items of System.Data.Common.DbProviderFactory which are implemented by most dotnet-databaseproviders.

Update:

havig your own factory is fine. if you are lookig for examples of working database-factories see the sourcecode of

• queryexpress a working database-querying gui,
• queryexplus a spinof of queryexpress
• mygeneration a codegenerator which connects to many different databases
• NHibernate with a sophisticated driver modell for many databases