DB Independent application

  • Thread starter Thread starter inaqui.medina
  • Start date Start date
I

inaqui.medina

Hi,

I'm writting my application and I want it to be DBMS independent.

The problem is I don't want to use ODBC because if it is SQL Server
I know SQLClient is faster and the same with Oracle.

So my first idea was something like:

#if SQL_DB
#define CONN_TYPE SqlConnection
#else
#define CONN_TYPE OracleConnection
#endif
//... Same for all ADO objects

I soon found out that this is old C++ and not available in VS.Net.

Then I thought "Ok lets then do:

#if SQL_DB
public class DbConnection : SqlConnection
#else
public class DbConnection : OracleConnection
#endif
{}
//... Again same for all objects

Wrong again because classes like OracleConnection are sealed!!

So right now my only plausible idea is:

public class DbConnection
{
public DbConnection(string connectionString)
{
#if SQL_DB
impl = new SqlConnection(connectionString);
#elif ODBC_DB
impl = new OdbcConnection(connectionString);
#else
impl = new OracleConnection(connectionString);
#endif
}

public DbConnection()
{
#if SQL_DB
impl = new SqlConnection();
#elif ODBC_DB
impl = new OdbcConnection();
#else
impl = new OracleConnection();
#endif
}

public void Open()
{
impl.Open();
}

public void Close()
{
impl.Close();
}

//Implement all methods here and forward to impl

#if SQL_DB
SqlConnection impl;
#elif ODBC_DB
OdbcConnection impl;
#else
OracleConnection impl;
#endif
}

So my questions are:

Hasn't MS worried about this?
Isn't there a better solution?
If not, is this worth the trouble? (implementing all objects and all
methods again and just forward the calls)

Thanks a lot,

inaquimj
 
naquimj,

It has been my experience that DBMSs are just too different from each other
to make applications DBMS-independent.

I would love to be proved wrong on this issue.

Kerry Moorman
 
(e-mail address removed) wrote in @g43g2000cwa.googlegroups.com:
#if SQL_DB
public class DbConnection : SqlConnection
#else
public class DbConnection : OracleConnection
#endif
{}
//... Again same for all objects

Wrong again because classes like OracleConnection are sealed!!
Click to expand...

No - dont do this. Look at the interfaces. Use the interfaces instead. You can also look at the data
application block which can help you here.
 
I'm with Kerry. Trying to write a DBMS-independent backend is as easy as
trying to buy a single remote control that works on all of your audio
components, your garage door opener and your daughter as she plays soccer.

Each DBMS has its own features that makes it competitive and work in the
systems where it makes sense. While the DAB are interesting, and the new DB
components in ADO.NET 2.0 are innovative, they ignore the fact that stored
procedures are not supported in JET (not really), work very differently in
Oracle and SQL and DB2 and Informix. DataTypes that you might want to
leverage are available in one or two of the targets but not the rest.
Concurrency management is different, administration is different and I could
go on.

If I wanted to create a single front-end that worked on small to large to
gigantic systems, I would choose SQL Server. By coding to SQL Server you can
install SQL Express and go up from there. SQL CE is also a subset (but
again, very different) but is programmed in many of the same ways. No other
vendor has a single binary that is so widely scalable.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Kerry said:
naquimj,

It has been my experience that DBMSs are just too different from each other
to make applications DBMS-independent.

I would love to be proved wrong on this issue.
Click to expand...

Sorry, I don't agree, to a degree :). I did this in VB6 with MS and
Sybase. The only issues that I have encountered were the bugs in the
early versions of sybase oledb provider.

With .NET it seems that it's even simpler. You just use the IDb*
interfaces for holding your variables.
 
That's cheating. SQL Server and Sybase are roughly the same DBMS
engine--they share a common parent. If any cross-vendor solution would work,
this is one. However, that said, since the Sybase/Microsoft schism, the two
engines have implemented very different admin tools, data access features
and more. A common denominator application would have to ignore the new
features or include copious code to leverage the differences.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I regularly deploy systems that are DB independent and work on SQL Server, Oracle, Interbase, and
Firebird. If you follow the guidelines in my articles on Code Project its rather easy to achieve.
 
Back
Top