Solved

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now