Change of Database Connection impact on Code

Question

4.00/5 (1 vote)

See more:

I am using asp.net and currently working with SQL SERVER 2008.
In my code all the DML operation is happening with the help of
SqlConnection, SqlCommand, SqlDataAdapter.

But when If my database move to ORACLE or MySQL then I would need to write the code for
ORACLE -- OLEDBConnection, OLEDBCommand, OLEDBDataAdapter
MySQL -- Would need to include a MySQL dll.

Now due to this huge change I would need to write the entire code again.

Is there any way to overcome this problem that we write the code at once and that would not depend on the database provider?
Does the Entity Framework come in this picture anywhere?

Please guide me.

Thanks

Posted 29-Dec-12 2:24am

Manish Kumar Namdev

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2012-12-29T05:06:00

Actually you can use OleDb for most of the databases. You just have to make sure that you have proper OleDb provider downloaded. For example Oracle OleDb provider can be loaded from Oracle site, for MySql you can use OleDb for ODBC and MySql ODBC driver if no suitable OleDb provider is found for that (the only ones I know are commercial) and so forth.

However there are few things you must remember:
- when working with OleDb with multiple databases you cannot use database specific features for .Net objects (ones that exist for example for SqlCOnnection or OracleConnection)
- The SQL syntax is still different. For each database you may have to create alternative versions for all statements.

You could of course create small wrapper classes to hide the actual connection, command etc objects but still you have differences in SQL syntax between the databases.

EF can be used to make your code more compatible with all the databases. However there still may be situations where you have to write SQL and/or the behaviour is different between databases.