Abstracting away DB Server type in C#/ADO.NET

Question

C# / ADO.NET has:

SqlConnection / NpgsqlConnection / ...
SqlCommand / NpgsqlCommand / ...
SqlDataReader / NpgsqlDataReader / ...
...

This means that all of your DB code ends up being DB vendor specific. If you change DB vendors, you have to change all of your DB calls. And, you can't make your code DB vendor agnostic.

I don't mind creating DB vendor specific connections, but from that point forward, I want all of my DB calls to be generic, e.g. Command and DataReader.

I know I can do this by creating the Command and DataReader, etc. classes that can contain switch statements that call the appropriate vendor specific calls, but I'd prefer to do something a bit more elegant. I sense that one of the standard design patterns may help, but I've yet to find an elegant solution. Any help would sure be appreciated.

Not really. ADO.NET uses abstract factory classes. You only get vendor-specific classes if you *ask* for them, instead of using DbProviderFactory etc — Panagiotis Kanavos, Mar 29 '16 at 16:34
In fact there is a [duplicate question from 2010](http://stackoverflow.com/questions/3080601/keeping-an-application-database-agnostic-ado-net-vs-encapsulating-db-logic) — Panagiotis Kanavos, Mar 29 '16 at 16:35

score 0 · Answer 1 · answered Mar 29 '16 at 16:16

This means that all of your DB code ends up being DB vendor specific. If you change DB vendors, you have to change all of your DB calls. And, you can't make your code DB vendor agnostic.

Yes. UNLESS you read the documentation and realize that they all implement common interfaces. All it takes is a small easy to replace factory method.

And THEN the problems start. SQL Dialects are very different, the way parameters are attached is (not the C# part - but how they have to be named and referenced in SQL code.

The easy to standardize programmatic interfaces are totally trivial to make.

There is no way to generalize the SQL level except going full driver model, i.e. have an abstraction (like LINQ) and then take it from there with specific generators.

Even better, ADO.NET implements both common interfaces *and* abstract provider classes. In order to implement a new provider, one only needs to *inherit* from the abstract classes and only replace certain parts. — Panagiotis Kanavos, Mar 29 '16 at 16:32

score -1 · Answer 2 · answered Mar 29 '16 at 16:26

-1

    abstract class AbstractFactory  {
        public abstract System.Data.Common.DbConnection CreateConnection(string cs);
        public abstract System.Data.Common.DbCommand  CreateCommand(string stmt, System.Data.Common.DbConnection conn);
    }

    class ConcreteFactorySql : AbstractFactory {
        public override System.Data.Common.DbConnection CreateConnection(string cs) {
            return new SqlConnection(cs);
        }
        public override System.Data.Common.DbCommand CreateCommand(string stmt, System.Data.Common.DbConnection conn) {
            return new SqlCommand(stmt, (SqlConnection) conn);
        }
    }

    class ConcreteFactoryPg : AbstractFactory {
        public override System.Data.Common.DbConnection CreateConnection(string cs) {
            return new NpgsqlConnection(cs);
        }
        public override System.Data.Common.DbCommand CreateCommand(string stmt, System.Data.Common.DbConnection conn) {
            return new NpgsqlCommand(stmt, (NpgsqlConnection) conn);
        }
    }

answered Mar 29 '16 at 16:26

Blake McBride

394
3
16

ANd *that* is what ADO.NET *already* does with the DbFactory etc methods – Panagiotis Kanavos Mar 29 '16 at 16:31
And that is the value of stackoverflow. Thanks! – Blake McBride Mar 29 '16 at 16:42
I think it may make more sense to use my classes rather than DbFactory for the following reason. If trying to use a provider not supplied by MS (like PostgreSQL), you have to do all sorts of system config stuff. With my code, it's not necessary. – Blake McBride Mar 29 '16 at 16:50
No it doesn't. These are simply abstract classes and interfaces that do exactly what you posted. Nothing forces you to use only the providers used by Microsoft - there *is* a Postgres provider already and it [does implement](https://github.com/npgsql/npgsql/blob/develop/src/Npgsql/NpgsqlConnection.cs#L58) the abstract factories. Using your own classes would only introduce bugs and prevent you from using the provider in Entity Framework or any database-agnostic code – Panagiotis Kanavos Mar 29 '16 at 16:56
Everything I've read says you have to register a foreign (not MS) provider. I tried DbFactory and I got the error: Unable to find the requested .Net Framework Data Provider. It may not be installed. --- When I use my class it works. – Blake McBride Mar 29 '16 at 17:25

Abstracting away DB Server type in C#/ADO.NET

2 Answers2