0

I am trying to implement the use of two database in my application, sqlite and mysql.

Currently i added references to both database connection library, and duplicate all first checking which database option the end user selected, and calling the appropriate function.

Though i only implemented a test, i thought this would be tasking as the application does alot of database calls

is there any general data connection class or library in the .net framework so that instead of instantiating MysqlConnection and SqliteConnection differently, i just instantiate DatabaseConnection?

Ts there any better way of doing this?

  • 2
    I recommed the repository pattern to hide the underling connection and sql – Ewan Aug 28 '16 at 12:48
3

This is a situation where things have gone wrong for a lot of developers over the time.

In a perfect world it would just be to inject the chosen connection everywhere a connection is needed.

But SQL isn't just SQL. There are so many minor differences between to different database providers.

This means that you most often will need different SQL statements for the different database providers.

That will work against injecting the connection.

I would wrap a statement strategy (or a similar construction) around the connection so that the statement strategy can be injected, removing the knowledge of which connection is actually used is removed from the data objects.

3

Use an ORM or Database Abstraction Layer

There are a lot of existing tools and frameworks that will do the heavy lifting for you and allow you to support multiple databases while writing database-agnostic code. Linq to SQL, NHibernate, Entity Framework... all allow you to deal with the database in an abstract way and take that work away from you. These tools are very mature and have been battle-hardened, providing you with a solution that is better than a lot of things you would otherwise be writing yourself.

0

In theory both implement a class such as DbDataReader

If you are going to get that across MysqlConnection and SqliteConnection I am not sure

DbConnection Class

DbCommand Class

So you would have a method to return a DbCommand and the rest of the app is not even aware of which underlying provider is used.

What you lose is extras that may have been added by the specific provider.

You may get into a case of select statements are not compatible even if they both implement DbCommand Class.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.