2

Is there a way to create one DAO class like EmployeeDAO and this class should be able to work with SQL Server, MySql and MongoDB. It should somehow change and work with MySqlConnection/SqlConnection and MySqlCommand/SqlCommand. Right now, I have to create three classses for each database. Are there any interfaces that I can use? I could not find any. Thank you.

public abstract class DaoFactory
{
  public static DaoFactory GetInstance(DaoConnection daoConnection)
    {
        DaoFactory instance = null;

        switch (daoConnection)
        {
                case DaoConnection.Default:
                case DaoConnection.MySQL:
            {
                instance = new MySqlConnectionFactory(MySqlServer, MySqlPort, MySqlUser, MySqlPassword);
                break;
            }
        }

        return instance;
    }

 protected string ConnectionString { get; set; }

 public abstract DbConnection GetConnection();

}

public class MySqlConnectionFactory : DaoFactory
{
    public MySqlConnectionFactory(string server, string port, string user, string password) : base(server, port, user, password)
    {
        this.ConnectionString = string.Format("Server={0}; Port={1} Uid={2}; Pwd={3}; pooling=true", server, port, user, password);
    }

    public override DbConnection GetConnection()
    {
        return new MySqlConnection(this.ConnectionString);
    }
}


public class EmployeeDao : IEmployeeDao
{
    private const string InsertEmployee = "INSERT INTO employees VALUES (@name,@age)";

    private DaoFactory daoFactory;

    EmployeeDao(DaoFactory daoFactory)
    {
        this.daoFactory = daoFactory;
    }

    public void Insert(Employee employee)
    {
        MySqlConnection connection =  (MySqlConnection) this.daoFactory.GetConnection();

        using (connection)
        {
            var commannd = new MySqlCommand(InsertEmployee, connection);
            commannd.ExecuteNonQuery();

        }
    }
2
  • You should probably just keep using 3 separate classes. This is better design and much easier to maintain. What is your reason for not wanting to continue using 3 classes? Commented Sep 1, 2014 at 5:55
  • Possible duplicate of Unit testing of DAO Commented Aug 19, 2016 at 17:14

1 Answer 1

1

Still a valid question in 2018. I believe the answer is Microsoft Orleans, which has incredibly serious design implications (see Actor Model), but abstracts the database entirely.
Recommend looking into this for a full boilerplate: https://github.com/Maarten88/rrod

From there you can use:

The design pattern is worthy of an entire topic on it's own: https://dotnet.github.io/orleans/Documentation/Introduction.html

It's a very hefty implementation, but I believe it's the correct approach to this problem. Additionally, using the RROD boilerplate as a reference should secure the application for Horizontal Scaling both at the App & DB levels (whether that be NoSql or ACID).

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.