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..

Curettage

Please provide us with whatever design effort you've made so far. We are not going to simply give you the UML solution. – Chiliarch

Please find the edited posts with the classes I created – Curettage

Thanks but what about your UML? Did you actually flesh out the design and how the abstract factory pattern would be used in your solution BEFORE you started writing code? – Chiliarch

I have never used abstract factory and I don't know how to design this... Right know I am using only those classes I have posted. I'm getting the connection string from the .settings file and use it in the SQLDatabase. So no, I don't have a UML of the abstract factory – Curettage

I think you may have missed my point, which is that you should focus on designing your solution (e.g.: create a high-level UML class diagram, and/or lower-level activity or sequence diagrams) BEFORE you actually write code. In general, when trying to implement any design pattern writing code should be the final (and hypothetically the easiest) step. – Chiliarch

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

Medea

I want to create my own factory – Curettage

Why build something that already exists and are quite easy to use? – Medea

cause that is my task... my task is to create an abstract factory not use what is ready.. – Curettage

It was my assumption since you refuse/can't use something that already exists in the framework. – Medea

Ohhh ic..... what I really want is to do something like this: primaryobjects.com/CMS/Article81.aspx – Curettage

The code found in that link does not use an already made factory – Curettage

I've updated the answer to reproduce the last example in the article that you linked to. – Medea

Yes ok... but the code you have posted is using "DbProviderFactories.GetFactory(providerName);" but the link is not using that... – Curettage

No, the code in the article does not use the built in factory. It just shows the authors lack of knowledge about the .Net framework. – Medea

I give up since you obviously refuse to see the point in using something that already exists. – Medea

cause the task I was given is to create an abstract factory not use an already made abstract class. You are not understandin my task.... – Curettage

If I told you do build a car, would you do that even if someone offered you one for free? – Medea

@jgauffin: I think a better analogy is: If I told you to build a car, would you analyze blue prints from someone who wasn't an engineer or from an established car company. – Cryptograph

ok. let's go further. What's wrong with your current solution? – Medea

That I don't have an abstract factory and if I use what is posted on that link it gives me an error... the section I have put in the app.config won't work – Curettage

@Mark: This question is tagged as asp.net. Asp.net does not use app.config files; it uses web.config files. Same structure, different name. – Cryptograph

not syntax no... the get section is still remaining empty... note that I think that the getsection is looking in the web.config found in the presentation layer not in the app.config found in the data layer – Curettage

@Chris Lively The database cannot be found in the presentation layer but it is found in the data layer which is a class library.. – Curettage

@Mark: The web.config controls it at the application level. Class libraries used by a web app use the web.config to get their configuration. Class libraries used by a win forms app use app.config. You need to have your configuration in the web.config for the class library – Cryptograph

@Mark, @Chris lively is correct. There can be only one. – Medea

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

Houston

I would like to create my own abstract factory – Curettage

Why build something that already exists and are quite easy to use? – Medea

cause that is my task... my task is to create an abstract factory not use what is ready – Curettage

right, these are open source propragrams that implemented there own database abstraction layer. you can watch the source code how they implemented databstraction. – Houston

Answer 3

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.

Cryptograph

I don't think that the example you gave is an abstract factory method and I don't want to add Enterprise Library... I would like to create my own factory.. – Curettage

@mark: Actually it is. A Factory's purpose is to create an object. The above does just this in a way that can cross any type of data store. The abstract part is the interface which defines what the factories are doing. – Cryptograph

What i want is to create something like this primaryobjects.com/CMS/Article81.aspx – Curettage

@Mark: The design is horrible because it calls for creating methods to return connection and command objects. It's not thread safe, and it certainly will lead to bad coding practices and memory loss issues. The right design was put in my answer: focus on the actual functionality needed by the database layer which is to return, store and operate on usable objects (flowers, accounts etc). (Which is the definition of a factory..) Even the article mentioned uses ADO.Net, which you can certainly do. – Cryptograph

Answer 4

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

Immingle