C#, accessing different database types (Oracle, SQL Server, MySQL, ...) How do I use SqlConnection class?

In my C# application users can setup various datasources and connect to them to retrieve data. I have so far used OdbcConnection class but I am switching to SqlConnection class (and relevant family classes)
Now, do I need to load different libraries depending on whether it is a SQL Server or a Oracle DB before instantiating the SqlConnection object? Or is it transparent, .NET will use correct underlying libs based on the ADO connection string?

C# VS2010 Professional
jerraAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rajaamirapuConnect With a Mentor Commented:
You need use Abstract Factory pattern.
See this example
http://www.primaryobjects.com/CMS/Article81.aspx
HTH
0
 
hongjunCommented:
You need to use the different connection object.
0
 
Jini JoseSenior .Net DeveloperCommented:
are you using the helper class from microsoft data blocks ?

if so you can only use it for MS Sql.

you need to use different class for different databases.
0
 
dqmqCommented:
The connection string specifies a different data provider.  So, at a connection level, it's fairly transparent.  However, you still need whichever library is implicit for the dataprovider.

Beyond that, the dbms functionality available over each connection is different, so at the application level, transparency is not so good.   For example, SQL that works on one dbms may fail or return different results on another.  Generally, you mitigate those differences with a data access layer that has the same contract on all backend platforms.
0
 
jerraAuthor Commented:
>However, you still need whichever library is implicit for the dataprovider.
Does .NET take care of which library is used based on the connection string? I'll just create the SqlConnection object and go from there?

At this stage the differences in dbms' isn't a problem because I only need to use basic SELECTs and such. It is the setting up of the connection that I need understand. I have quite a load of refactoring to do and I don't want to cause myself even more work doing something stupid based on my poor knowledge on the subject.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.