Solved

Develop .Net Database-Independant Application

Posted on 2008-10-29
4
400 Views
Last Modified: 2012-08-13
Hello Experts,

I need to change the Data-Layer of a VB.Net Windows Application that uses SQL Server.
Now the requirement is that it can connect to SqlServer, Postgres, or Oracle.
What will be the best way to handle this change?
I am using VB.Net 2.0, so I can't use LinQ. Besides, I use a lot DataSets with TableAdapters, which have the .Net Data Provider objects (SqlClient) hard-coded.

Any ideas on how is the best way to accomplish an application that can connect to multiple database engines?
0
Comment
Question by:jorgesv13
  • 2
4 Comments
 
LVL 12

Expert Comment

by:techExtreme
ID: 22838246
Hello,
if you want to connect to multiple database engines, you cannot use sqlclient because that is microsoft specific. You must use oledb stuff.
0
 
LVL 8

Expert Comment

by:rpkhare
ID: 22838489
Take a look at this:
http://www.codeproject.com/KB/database/Multi-Tier.aspx

This will solve your problem.
0
 
LVL 8

Accepted Solution

by:
rpkhare earned 300 total points
ID: 22838498
0
 
LVL 1

Assisted Solution

by:Abh4IT
Abh4IT earned 200 total points
ID: 22838769
Hi,

As such the idea can be only be developing a DBLayer
So you will have some common DBLayer which provides certain interfaces. Say like
DataAccesFactory, DataAccess. DataAccess Factory provides the implementation for the specific dataaccess based on which DB to use.

Interface IDataAccess methods can be like
OpenConnection(String Connstr)
CloseConnection()
DataSet ExecuteQuery(String sqlQuery) //for select
int ExecuteNonQuery(String sqlEuery) //for insert/update or delete

Now implement this in a class like for example SqlDataAccess, similar <myDB>DataAccess ...Implement these as required by the specific database. Now use DataAccessFactory and load the required DBSupport from configuration or pass an enum. like DataAccessFactory.GetDataAccess(SQLDB) on which you get the IDataAccess object implementing the interface above for that DB.

Also you can explore usage of porvider pattern using
http://www.codeproject.com/KB/database/MpmCodeproject.aspx
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how the fundamental information of how to create a table.

929 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

14 Experts available now in Live!

Get 1:1 Help Now