3

I have a situation where I need to create an application which supports multiple databases. Multiple databases means the client can use any of the database like Oracle, SQL Server, MySQL, PostgreSQL at first.

I was trying to use ORM like NHibernate or MyBatis. But they have their limitation and need expertise to use.

So I decide to user the Data Providers provided by Microsoft like ADO.NET, OLEDB, ODP.NET etc.

Is there any way so that the my logic of database keep same for all the database? I have tried IDbConeection, IDbCommand etc but they have a problem in case of Oracle (Ref Cursor).

I there any way to achieve this? Some link or guide would be appreciated.

Edit:
There is problem with the DBTypes because they are enum define differently with different data providers.

user861594
  • 5,733
  • 3
  • 29
  • 45
शेखर
  • 17,412
  • 13
  • 61
  • 117
  • Have you considered using Entity Framework code first approach? – JCM Jun 06 '16 at 22:33
  • @JCM Entity has not good support for oracle versions. – शेखर Jun 07 '16 at 09:32
  • 1
    Your best bet is still using ORM like Entity Framework I think. Yes they have limitations, yes they need expertise (not too much though), but who said supporting multiple databases would be easy? In complicated cases you might use stored procedures or just conditionally execute code based on current provider (but only in complicated cases). – Evk Jun 08 '16 at 20:04
  • 1
    I would suggest you to continue using nHibernate because it works best with Oracle and it's the most recommended ORM for most of web applications. It also helps you avoid the headache of dealing with DB-specific queries. Once you are familiar with it, it will be your powerful tool to compose domain languages with Query Builder. You said it has limitation, can you let us know some of limitations affecting your consideration? – Robo Jun 09 '16 at 10:40
  • EF works fine in Oracle, with the managed drivers anyway... – solidau Jun 09 '16 at 22:41
  • If you just use common sense, classes and interfaces to isolate persistence from your business logic, you can do it with any database library you want. Stop calling your database directly from business logic classes and you'll be fine. Repository pattern is something you might want to use http://martinfowler.com/eaaCatalog/repository.html. – Alexey Zimarev Jun 10 '16 at 07:03
  • check this http://stackoverflow.com/questions/13094265/single-website-multiple-databases-database-switching – Abdul Jun 10 '16 at 10:32

4 Answers4

6

Well, real-life applications are complicated like that. Before you know it, you want to replace the UI with an App, expose your logic as a WCF service, change the e-mail service with another service provider, test pieces of your code while mocking the DAL and change the database with another one.

The usual way to deal with this is to pass all calls through an interface that separates the implementation from the caller. After that, you can implement the different DAL's.

Personally I usually go with this approach:

  • First create a single DLL that contains all interfaces. Basically the idea is to expose all calls that your UI, App or whatever needs through the interface. From now on, your UI doesn't talk to databases or e-mail providers anymore.
  • If you need to get access to the interface, you use a factory pattern. Never use 'new'; that will get you in trouble in the long run.
  • It's not trivial to create this, and needs proper crafting. Usually I begin with a bare minimum version, hack everything else in the UI as a first version, then move everything that touches a DB or a service into the right project while creating interfaces and finally re-engineer everything until I'm 100% satisfied.
  • Interfaces should be built to last. Sure, changes will happen over time, but you really want to minimize these. Think about what the future will hold, read up on what other people came up with and ensure your interfaces reflect that.

Basically you now have a working piece of software that works with a single database, mail provider, etc. So far so good.

Next, re-engineer the factory. Basically you want to use the configuration settings to pick the right provider (the right DLL that implements your interface) for your data. A simple switch can suffice in most cases.

At this point I usually make it a habit to make a ton of unit tests for the interfaces.

The last step is to create DLL's for the different database providers. One of these will be loaded at run-time in your application.

I prefer simple Linq to SQL (I also use the library from LinqConnect) because it's pretty fast. I simply start by copy-pasting the other database provider, and then re-engineer it until it works. Personally I don't believe in a magic 'support all sql databases' solution anymore: In my experience, some databases will handle certain queries a much, much faster than other databases - which means that you will probably end up with some custom code for each database anyways.

This is also the point where your unit tests are really going to pay off. Basically, you can just start with copy-paste and give it a test. If you're lucky, everything will run right away with decent performance... if not, you know where to start.

Build to last

Build things to last. Things will change:

  • Think about updates and test them. Prefer automatic tests.
  • You don't want to tinker with your Factory every day. Use Reflection, Expressions, Code generation or whatever your poison is to save yourself the trouble of changing code.
  • Spend time writing tests. Make sure you cover the bulk. I cannot stress this enough; under pressure people usually 'save' time by not writing tests. You'll notice that this time that you 'save' will double back on you as support when you've gone live. Every month.

What about Entity Framework

I've seen a lot of my customers get into trouble with performance because of this. In the many times that I've tested it, I had the same experience. I noticed customers hacking around EF for a lot of queries to get a bit of decent performance.

To be fair, I gave up a few years ago, and I know they have made considerable performance improvements. Still, I would test it (especially with complex queries) before considering it.

If I would use EF, I'd implement all EF stuff in a 'database common DLL', and then derive classes from that. As I said, not all databases are the same with queries - and you might want to implement some hacks that are necessary to get decent performance. Your tests will tell.

Bonuses

Other reasons for programming through interfaces has a lot of advantages in combination with proxy's. To name a few, you can easily create log sinks, caching, statistics, WCF, etc. by simply implementing the same interface. And if you end up hating your current OR mapper some day, you can just throw it away without touching a single line of your app.

atlaste
  • 30,418
  • 3
  • 57
  • 87
  • Do you still think the same? – शेखर Oct 05 '19 at 14:14
  • While EF certainly has become much better in terms of performance in the last 3 years, I just read this post again, and yea I believe this still holds. To make it practical, this isn't just theory: we currently have a code base of a little under 1 million lines of code. Without seperating code through interfaces and writing a lot of unit tests, that would have simply been impossible. – atlaste Oct 07 '19 at 16:53
0

I believe Microsoft's Data Access Components would be suitable to you. https://en.wikipedia.org/wiki/Microsoft_Data_Access_Components

Pethusamy Venkatachalam
  • 1
  • 2
  • 2
    It is not worked on since 11 years and does not support modern OS, so I would not recomend this. However, I do not know myself what would be apropriate in this situation. – Faling Dutchman Jun 03 '16 at 11:34
0

enter image description here

How about writing microservices and connect them by using a rest api? You (and maybe your team) could provide a core application which handles the logic and the ui. This is still based on your current technology. But instead of adding directly some kind of database connection, you could provide multiple types of microservices (based on asp.net or core) providing a rest api. You get your data from each database from such a microservice. So you would develop 1 micro service for e.g. MySQl and another one for MsSQL and when a new customer comes up with oracle you write a new small microservice which handles your expected API.

More info (based on .net core) is here: https://docs.asp.net/en/latest/tutorials/first-web-api.html

I think this is a teams discussion, which kind of technology you decide to use. But today I would recommend writing a micro service. It makes the attachment of a new app for a e.g. mobile device also much easier :)

Ani Menon
  • 27,209
  • 16
  • 105
  • 126
Bjego
  • 665
  • 5
  • 14
0

Yes its possible.

Right now am working with the same scenario where my all logic related data( typically you can call meta data) reside inside one DB and date resides in another DB.

What you need to do. you should have connection related parameter in two different file or you can call these file as prop files. now you need to have connection concrete class which take the parameter from these prop file. so where you need to create connection just supply the prop files and it will created the db connection as desired.

Vikas Kumar
  • 17
  • 9