Data Layer Abstract Factory

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();
        connec开发者_如何转开发tion.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..

---

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())
                {
                    // ...
                }
            }
        }
    }
}

---

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

---

I would not have "createcommand" or "createconnection" methods.

A much better approach to take is to let each of the access methods (like "GetAccounts") handle their own connection / command instantiation.

Connection and Command objects implement IDisposable. As such it is better to have using statements in which those are created and disposed of as necessary. Teh way you have it now could lead to massive memory issues.

Further the CreateParameter method appears to not provide any real benefit over just calling "new SqlParameter" in the code that needs to create those parameters.

I would do the following:

public interface IDbAccess {
  String ConnectionString;

  Collection<Account> GetAccountsById(Int32 id);
  Boolean StoreAccount(Account acct);
}

public class SqlDatabase : IDbAccess {
  public String ConnectionString {get; set;}

  public SqlDatabase(String connection) {
    ConnectionString = connection;
  }

  public Collection<Account> GetAccountsById(Int32 id) {
    using (SqlConnection connect = new SqlConnection(ConnectionString)) { 
       using (SqlCommand cmd = new SqlCommand(connect)) {
          /// etc.
        }
    }
  }
}

This way your datalayer is specific to the functionality you are providing. There are already great wrappers for db access like Enterprise Library. The approach you are taking adds nothing and introduces errors.

Further this approach means you can implement non-database providers like XML, web services, etc with zero code changes.

---

Hi People i know this is old post, but i would like share something with you.

The Enterprise Library and OleDb has some problem, when you want to insert image bigger than 32k it will throw Exception, so de solve this i have done:

Create a project Which you can call CustomProvider

Create a Classe Which you will call Database

 public abstract class Database
 {
    public string ConnectionString { get; set; } // Preciso uma variavel para guardar o ConnectionString
    public IDbConnection Connection { get; set; }


    //public abstract string ProviderName { get; } // Preciso uma variavel para guardar o ConnectionString


    //public abstract IDbConnection CreateConnection(string ConnectionString);

    public abstract IDbConnection CreateConnection(); // Preciso um Metodo Abstract para CreateConnection Para Tratar da Connection
    public abstract IDbCommand CreateCommand();

    }
}

2. Create Seccond Class OracleDatabase.cs

3. Create Third class SQLDatabase.cs

    public class OracleDatabase : Database
    {
   
     public override IDbConnection CreateConnection()
     {
         return new OracleConnection(ConnectionString);
     }
   
     public override IDbCommand CreateCommand()
     {
         return new OracleCommand();
     }
   
     public override IDbConnection CreateOpenConnection()
     {
         OracleConnection connection = (OracleConnection)CreateConnection();
         connection.Open();
   
         return connection;
     }
   
     public override IDbCommand CreateCommand(string commandText, IDbConnection connection)
     {
         OracleCommand command = (OracleCommand)CreateCommand();
   
         command.CommandText = commandText;
         command.Connection = (OracleConnection)connection;
         command.CommandType = CommandType.Text;
   
         return command;
     }
   }
   

   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;
       }
    }
   

4. and then on the program

       Database db = Factory.CreateDatabase("ConnectionString");
   
       try
       {
           using (IDbConnection w_connection = db.Connection)
           {
               w_connection.Open();
   
               IDbTransaction transation = w_connection.BeginTransaction();
   
               IDbCommand dbcomand = db.CreateStoredProcCommand("INSERTTEST");
   
               db.AddInParameter(dbcomand, "@ATTCH", DbType.Binary, bytes);
               db.ExecuteNonQuery(dbcomand, transation);
   
               transation.Commit();
           }
       }
       catch (Exception)
       {
   
       }
     }
   

You must Override all defined method in the Master Class

5. Create a Factory.cs

   public static Database CreateDatabase(string ConnectionString)
   {
       //var Conn = ConfigurationManager.ConnectionStrings[ConnectionString].ToString();
   
   
       if (string.IsNullOrEmpty(ConnectionString))
           throw new Exception("Connectionstring Not Found" + ConnectionString);
   
       Database db = null;
   
       if (ConfigurationManager.ConnectionStrings[ConnectionString].ProviderName.Contains("Oracle"))
       {
           db = new OracleDatabase();
           db.ConnectionString = GetConnectionString(ConnectionString);
           db.Connection = db.CreateConnection();
       }
       else
       {
           db = new SQLDatabase();
           db.ConnectionString = GetConnectionString(ConnectionString);
           db.Connection = db.CreateConnection();
       }
   
   
       return db;
   }