How to make data access layer for SQL Server and MySql

Question

I'm building a data access layer and need to be able switch between two providers in different environments.

How do I structure this? I'm using a repository pattern and have e.g. a CarRepository class and a Car class. The CarRepository class is responsible for saving, deleting and loading from the database.

I have a Database class, responsible for connecting to the database, and executing the query (sending a SqlCommand for SQL Server). The SQL syntax for the underlying databases is different and the parameter syntax is also different (SQL Server uses @ and MySql uses ?).

I would like an approach where I can make the least effort in making my application run on both platforms.

The obvious method is making a MySqlCarRepository and a SqlServerCarRepository, but that introduces a heavy amount of maintenance. Are there any good approaches to this scenario? Maybe keeping a static class with static strings containing the SQL statements for the different SQL flavours? (how about parameter syntax then?)

Any advice is welcome

(Please note that ORM (Nhibernate, Linq2Sql etc) is not an option)

Can you explain why ORM isn't an option?

Ash Burlaczenko
– Ash Burlaczenko

2012-05-02 08:26:03 +00:00
Commented May 2, 2012 at 8:26 — Ash Burlaczenko
– Ash Burlaczenko, Commented May 2, 2012 at 8:26

Eben Roux · Accepted Answer · 2012-05-02 08:47:08Z

2

The approach I follow is to first-of-all use the ADO Provider Factories to abstract the data access implementation. So I will use IDbConnection and so forth in the code.

Then I have an abstraction for a query. I can then use Query objects that contain the actual sql statements. These Query objects are created from RawQuery or various query builders (insert/update/delete/etc.) that have implementations for each provider type. The specific raw queries will need to be coded and obtained specific to the DB you need since there is no gettin passed that.

There is quite a bit of leg work involved in coding this 'plumbing' and I have not had a situation where I actually require different platforms so I have not bothered coding some small bits that I know need some ironing out but you are welcome to contact if you are interested in seeing some code.

answered May 2, 2012 at 8:47

Eben Roux

13.4k3 gold badges31 silver badges51 bronze badges

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

1 Comment

Brian Hvarregaard Over a year ago

I would like to see some code please, can you insert sample into answer or mail me at h4mm3rhead at gmail dot com. Cheers

Hakan Hastekin · Accepted Answer · 2012-05-02 08:38:20Z

0

Can you use any code generation tools?

I used to use Code Smith in another life and had templates that would generate POCO objects from DB tables, repository classes for each object and stored procedures. Worked alright after fine tuning the templates, and there were plenty examples on the net.

But this was way before I saw the light with NHibernate!

answered May 2, 2012 at 8:38

Hakan Hastekin

5533 silver badges7 bronze badges

Comments

Peadar Doyle · Accepted Answer · 2012-05-02 08:51:30Z

0

A pattern for accessing multiple database types is the DAO (Data Access Object) pattern. This could suit your particular need if you can't/don't use an ORM. The following article explains the pattern for Java but it is still very relevant for C#:

http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html

answered May 2, 2012 at 8:51

Peadar Doyle

1,1102 gold badges11 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to make data access layer for SQL Server and MySql

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest