[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2010-08-21
5
Medium Priority
?
972 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:jerra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 33492160
You need to use the different connection object.
0
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33492172
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
 
LVL 5

Accepted Solution

by:
rajaamirapu earned 2000 total points
ID: 33492210
You need use Abstract Factory pattern.
See this example
http://www.primaryobjects.com/CMS/Article81.aspx
HTH
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33492267
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
 

Author Comment

by:jerra
ID: 33492518
>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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question