how to create single Data Access Layer to access two different data source in asp.net

Question

I have two data sources, the first one uses OLEDB to connect and other uses SQL Data Provider.

I always create a DAL to encapsulate data access logic, but this time it seams I have to create two different DAL (OLDB and SQLProvider.

Will this is the right approach or I can communicate with single DAL. Please suggest what is the best approach followed while communicating different data source from ASp.NET application.

Thanks

Answer 1

You are right that you need to encapsulate the data access logic in a separate layer if you want to access two different data sources,

The key is to make sure that your other code, uses an interface, not an implementation, to access your DAL.

So for example:

    public interface IRepository
    {
        Person GetPersonById(Guid id);
    }

    public class OleRepository : IRepository
    {
        public Person GetPersonById(Guid id)
        {
            // Do some Ole specific stuff to return a person
        }
    }

    public class SqlRepository : IRepository
    {
        public Person GetPersonById(Guid id)
        {
            // Do some Sql Server specific stuff to return a person
        }
    }

In your code, you would program against the IRepository interface and you wouldn't depend on a specific implementation. At runtime, you would select the right Repository implementation (for example with Dependency Injection).