Solved

Develop .Net Database-Independant Application

Posted on 2008-10-29
4
399 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

747 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

12 Experts available now in Live!

Get 1:1 Help Now