Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Develop .Net Database-Independant Application

Posted on 2008-10-29
4
Medium Priority
?
410 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 1200 total points
ID: 22838498
0
 
LVL 1

Assisted Solution

by:Abh4IT
Abh4IT earned 800 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

885 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