Solved

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

Posted on 2010-08-21
5
939 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 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL to copy a database 37 66
Sql case statement to calculate totals 5 37
T-SQL Query - Group By Year 3 32
Related to SQL Query 5 21
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

726 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