what is the best way to create an vendor independent dal in c# without using ORM?

Question

Currently I have a project that involves two database providers:

1. SqlClient
2. OleDb

I need to create a DAL that handles these providers. I was thinking to create a interface like this:

public interface IDataAccessLayer
{
    public string GetSomeData();
}

And implement it separately (although it's simple this is the wrong way)

public class SqlClientDal : IDataAccessLayer
{
    public string GetSomeData() { }
}

public class OleDbDal : IDataAccessLayer
{
    public string GetSomeData() { }
}

I have read some post like this:

.Net: how to create vendor independent Dataset, Tableadapters, bindings (DB decided at runtime) (it's good, but it doesn't provide any example)

Obtaining a DbProviderFactory

Creating a Data Access Layer (C#) (really good, but table adapters are vendor dependent)

I would like to create a DAL with this features:

1. Use of typed data tables (using visual studio designer)
2. Use of vendor independent data adapters (manually created)
3. Not ORM

How can I create a simple DAL with these features?

Answer 1

Look into deriving your classes from the following classes:

System.Data.Common.DbConnection BaseConnection;
System.Data.Common.DbDataAdapter BaseAdapter;

namespace DAL
{
    public class DataAccess : DbConnection
    {
        protected override DbTransaction BeginDbTransaction(System.Data.IsolationLevel isolationLevel)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public override void ChangeDatabase(string databaseName)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public override void Close()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public override string ConnectionString
        {
            get
            {
                throw new Exception("The method or operation is not implemented.");
            }
            set
            {
                throw new Exception("The method or operation is not implemented.");
            }
        }

        protected override DbCommand CreateDbCommand()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public override string DataSource
        {
            get { throw new Exception("The method or operation is not implemented."); }
        }

        public override string Database
        {
            get { throw new Exception("The method or operation is not implemented."); }
        }

        public override void Open()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public override string ServerVersion
        {
            get { throw new Exception("The method or operation is not implemented."); }
        }

        public override System.Data.ConnectionState State
        {
            get { throw new Exception("The method or operation is not implemented."); }
        }
    }
}

mess around with each one of those functions and classes. It's very flexible.

Answer 2

See my answer Writing driver class generic for any database support. Unless you have very specific need in writing your own DbAL, I recommend using Micro ORMs like Dapper or FluentData or SqlFu. The ease of use (according to me) is what's compelling with these structures.