Solved

Develop .Net Database-Independant Application

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

820 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