How to do non dependence on database vendor?

[Brett]

If I want to easily swap the database I'm using, what is the best method for
developing that tier in my application?

I'll have basically a 4 tier app:
1. presentation
2. business logic
3. data layer containing standard SQL compliant queries
4. any database

I'm looking for the most efficient way to design tier 3. I can't use stored
procedures because those are specific two a particular database. Using
queries in an app layer is less efficient than SPs but there isn't much
choice at this point.

Any comments in general on this design are welcome.

Thanks,
Brett

 

[Alan Pretre]

If I want to easily swap the database I'm using, what is the best method
for developing that tier in my application?

Well, for example, you could use ODBC. I have a .NET app that uses ODBC to
access SQL Server and another vendor's DB. Debug builds adjust the ODBC
DataAdapters and connection string for SQL Server syntax, while release
builds of the _same_ app put in the syntax for the other vendor. I do it
this way because it integrates with databases that I don't have access to in
the dev environment. The app on top of the DB layer is none the wiser.

Maybe that approach is not what you are looking for.

Of course, you could make your DB tier a component that is specialized for a
specific DB vendor. You could define a common interface for communicating
with it/them that is not vendor specific.

-- Alan

 

[Brett]

Well, for example, you could use ODBC. I have a .NET app that uses ODBC
to access SQL Server and another vendor's DB. Debug builds adjust the
ODBC DataAdapters and connection string for SQL Server syntax, while
release builds of the _same_ app put in the syntax for the other vendor.
I do it this way because it integrates with databases that I don't have
access to in the dev environment. The app on top of the DB layer is none
the wiser.

Maybe that approach is not what you are looking for.

Of course, you could make your DB tier a component that is specialized for
a specific DB vendor. You could define a common interface for
communicating with it/them that is not vendor specific.

The problem is that if you need to make any changes to the DB tier, adding
new SPs for example, you'll have to make changes to all the vendor specific
components.

Brett

 

[Alan Pretre]

The problem is that if you need to make any changes to the DB tier, adding
new SPs for example, you'll have to make changes to all the vendor
specific components.

Yes. Is that avoidable in any case?

What is your specific situation? Are you needing to talk to multiple types
at the same time? Are you wanting to install a single flavor at install
time? Just concerned about changing requirements down the line?

-- Alan

 

[Bjorn Abelli]

If I want to easily swap the database I'm using, what is
the best method for developing that tier in my application?

I'll have basically a 4 tier app:
1. presentation
2. business logic
3. data layer containing standard SQL compliant queries
4. any database

I'm looking for the most efficient way to design tier 3. I can't use
stored procedures because those are specific two a particular database.
Using queries in an app layer is less efficient than SPs but there isn't
much choice at this point.

Any comments in general on this design are welcome.

"Any comments"... ;-)

Alright then...

From your specifications I would split the "data layer" really into two.

3a. data layer containing standard SQL compliant queries
3b. A "pluggable" DB-proxy

This would mean that the proxy would be specific for a database, but would
be easy enough to implement for other DBs as you'd need them, so you could
exchange it, even "on the fly".

Using reflection you could even make some of the implementations enough
flexible to be able to be used in e "generic" way, i.e. for more than one
database...

The key to this solution is to *not* expose any DB-specific classes from the
proxy, only the necessary interfaces (IDbConnection, IDbCommand, etc).

But as I said, this was based "from your specifications", and you said you
accepted "any comments"... ;-)

My first thought was actually to include yet another layer between the
business logic and the data layer, on a higher abstraction level than to use
SQL queries, as not all databases even support standard SQL...

// Bjorn A

 

[Brett]

...

"Any comments"... ;-)

Alright then...

From your specifications I would split the "data layer" really into two.

3a. data layer containing standard SQL compliant queries
3b. A "pluggable" DB-proxy

This would mean that the proxy would be specific for a database, but would
be easy enough to implement for other DBs as you'd need them, so you could
exchange it, even "on the fly".

This is good. The database will probably stay with Oracle, SQL Server, IBM,
MS Access, and MySQL. What exactly does the DB proxy layer do?

Using reflection you could even make some of the implementations enough
flexible to be able to be used in e "generic" way, i.e. for more than one
database...

What exactly do you mean here? Can you give an example?

The key to this solution is to *not* expose any DB-specific classes from
the proxy, only the necessary interfaces (IDbConnection, IDbCommand, etc).

But as I said, this was based "from your specifications", and you said you
accepted "any comments"... ;-)

My first thought was actually to include yet another layer between the
business logic and the data layer, on a higher abstraction level than to
use SQL queries, as not all databases even support standard SQL...

Say in the future, some one wants to use my app but they have a database
which doesn't support standard SQL. I could keep trying to get as generic
as possible but I loose much power. I would simply define the domain of
database this app will interact with and stay there. This means standard
SQL will always be available. Does this change your thoughts on using a
higher abstraction layer?

Thanks,
Brett

 

[Brett]

Yes. Is that avoidable in any case?

What is your specific situation? Are you needing to talk to multiple
types at the same time? Are you wanting to install a single flavor at
install time? Just concerned about changing requirements down the line?

Yes - changes down the line are a concern. All database involved must be
able to use standard SQL. However, how far off base is making use of XML
files as the database?

Brett

 

[Bjorn Abelli]

...

This is good. The database will probably stay with Oracle, SQL Server,
IBM, MS Access, and MySQL. What exactly does the DB proxy layer do?

It only provides a "glue layer" between your "SQL-layer" and the
ADO.NET-driver.

Small "pseudo-example":

interface IDbProxy
{
IDbConnection GetConnection( string cns );
IDbCommand CreateCommand( );

...
}

class OracleProxy : IDbProxy
{
IDbConnection GetConnection( string cns )
{
return new OracleConnection( cns );
}

IDbCommand CreateCommand()
{
return new OracleCommand();
}

...

}

In your SQL-layer only the IDbProxy-interface is exposed, through some
plugin-mechanism (for the latter there are a lot of different techniques,
that I'm sure you can find in a lot of different places).

What exactly do you mean here? Can you give an example?

You could actually get away with only a single proxy if you use
reflection... ;-)

Through reflection you can dynamically load the ADO.NET-driver, using some
kind of "ini-file" to let the proxy know where to look for it, which
classnames are used for the driver, connection-strings, etc.

The key is still to *not* expose any DB-specific classes from the proxy,
only the necessary interfaces (IDbConnection, IDbCommand, etc).

I made exactly this in Java a couple of years ago, to be able to switch
freely between different DBs and make adhoc queries, and I will soon be
working on a similar one for C#/.NET...

Unfortunately I haven't started on it, so I don't have any code to show
yet... ;-)

Say in the future, some one wants to use my app but they have a database
which doesn't support standard SQL. I could keep trying to get as generic
as possible but I loose much power. I would simply define the domain of
database this app will interact with and stay there. This means standard
SQL will always be available. Does this change your thoughts on using a
higher abstraction layer?

You never know what the future holds...

I would still look at the "data layer" from both perspectives, i.e. from the
"Business Perspective" and from the "DB-perspective".

What do I need to store persistent?

That would make up a layer between the business logic and the data layer,
focusing on storing and retrieval of the explicit data, but free from other
considerations in the business logic, and free to make use of different
DB-approaches. Though it would be dependant on the rest of the business
logic, it frees the layer from the details of SQL, if you in the future want
to make use of another DB than those supporting SQL.

// Bjorn A

 

[Brett]

...

It only provides a "glue layer" between your "SQL-layer" and the
ADO.NET-driver.

Small "pseudo-example":

interface IDbProxy
{
IDbConnection GetConnection( string cns );
IDbCommand CreateCommand( );

...
}

class OracleProxy : IDbProxy
{
IDbConnection GetConnection( string cns )
{
return new OracleConnection( cns );
}

IDbCommand CreateCommand()
{
return new OracleCommand();
}

...

}

In your SQL-layer only the IDbProxy-interface is exposed, through some
plugin-mechanism (for the latter there are a lot of different techniques,
that I'm sure you can find in a lot of different places).


You could actually get away with only a single proxy if you use
reflection... ;-)

Through reflection you can dynamically load the ADO.NET-driver, using some
kind of "ini-file" to let the proxy know where to look for it, which
classnames are used for the driver, connection-strings, etc.

However, there would be a performance hit to using Reflection. Also, I like
the interface approach. Everything in classes that you can instantiate.
Would that better than an INI file and Reflection...both of which are going
to be slower?

The interface approach seems more scalable and contained.

The key is still to *not* expose any DB-specific classes from the proxy,
only the necessary interfaces (IDbConnection, IDbCommand, etc).

I made exactly this in Java a couple of years ago, to be able to switch
freely between different DBs and make adhoc queries, and I will soon be
working on a similar one for C#/.NET...

Unfortunately I haven't started on it, so I don't have any code to show
yet... ;-)


You never know what the future holds...

I would still look at the "data layer" from both perspectives, i.e. from
the "Business Perspective" and from the "DB-perspective".

What do I need to store persistent?

That would make up a layer between the business logic and the data layer,
focusing on storing and retrieval of the explicit data, but free from
other considerations in the business logic, and free to make use of
different DB-approaches. Though it would be dependant on the rest of the
business logic, it frees the layer from the details of SQL, if you in the
future want to make use of another DB than those supporting SQL.

I'm still not seeing what exactly this other abstraction layer is. I
understand the use of a proxy (interface) and how that allows me to support
multiple databases using standard SQL. Say now I have another database that
uses non standard SQL. I add another proxy class for it. Now what exactly
needs to be done on this other higher level abstraction layer to support the
non standard SQL? Perhaps example with psuedo code?

Thanks,
Brett

 

[Bjorn Abelli]

"Brett" <wrote...

...

However, there would be a performance hit to using Reflection. Also, I
like the interface approach. Everything in classes that you can
instantiate. Would that better than an INI file and Reflection...both of
which are going to be slower?

The interface approach seems more scalable and contained.

I would rather say that both approaches really are heavily based on
interfaces, though with reflection, your implementation would be more
flexible, as you wouldn't even need to implement a new concrete proxy for
each new DB.

For the performance hit, I would say that it possibly could be neglectable,
as it could be "reflected" at only the startup of the application, but that
also depends on the design of the rest of it...

I'm still not seeing what exactly this other abstraction layer is. I
understand the use of a proxy (interface) and how that allows me to
support multiple databases using standard SQL. Say now I have another
database that uses non standard SQL. I add another proxy class for it.
Now what exactly needs to be done on this other higher level abstraction
layer to support the non standard SQL? Perhaps example with psuedo code?

Well, you have possibly already thought of it, but included it into your
business logic. This kind of layer is often considered a part of that layer
as it's so dependent on the classes in the business logic.

What I mean is simply an interface to the storage/retrieval of the data.
This can be done in several ways. One way is similar to the proxy variant
above, but foremost the use of a distinct interface for this, makes it
easier to change it afterwards anyway.

In short...

interface MyBusinessStorageManager
{
ArrayList GetCustomers();
ArrayList GetProducts();
ArrayList GetOrders();
boolean SaveOrder(Order o);
...
}

....which in turn is implemented by a class that *calls* the classes with the
SQL or others...

// Bjorn A

 

[Brett]

"Brett" <wrote...

I would rather say that both approaches really are heavily based on
interfaces, though with reflection, your implementation would be more
flexible, as you wouldn't even need to implement a new concrete proxy for
each new DB.

For the performance hit, I would say that it possibly could be
neglectable, as it could be "reflected" at only the startup of the
application, but that also depends on the design of the rest of it...


Well, you have possibly already thought of it, but included it into your
business logic. This kind of layer is often considered a part of that
layer as it's so dependent on the classes in the business logic.

What I mean is simply an interface to the storage/retrieval of the data.
This can be done in several ways. One way is similar to the proxy variant
above, but foremost the use of a distinct interface for this, makes it
easier to change it afterwards anyway.

In short...

interface MyBusinessStorageManager
{
ArrayList GetCustomers();
ArrayList GetProducts();
ArrayList GetOrders();
boolean SaveOrder(Order o);
...
}

...which in turn is implemented by a class that *calls* the classes with
the SQL or others...

Right. But how does Reflection come into play here? Say for the non SQL
compliant DBs and for the purpose of not having to create a class that
inherits from the interface for each DB.

Thanks,
Brett

 

[Bjorn Abelli]

"Brett" <wrote...

[snip]

Well, you have possibly already thought of it, but included it into your
business logic. This kind of layer is often considered a part of that
layer as it's so dependent on the classes in the business logic.

What I mean is simply an interface to the storage/retrieval of the data.
This can be done in several ways. One way is similar to the proxy variant
above, but foremost the use of a distinct interface for this, makes it
easier to change it afterwards anyway.

[snip]

...which in turn is implemented by a class that *calls* the classes with
the SQL or others...

Right. But how does Reflection come into play here? Say for the non SQL
compliant DBs and for the purpose of not having to create a class that
inherits from the interface for each DB.

Aha, I think I've been somewhat muddled and unclear here...

It's two different questions, or rather answers, that are independent of
each other.

I would have the Business Storage layer *above* the data layer, where I
would have plugins for different types of storage, where the SQL-variant
would be implemented with reflection, in order to shift easily between
different SQL compliant databases...

In short, the abstraction part would be a layer above the SQL-part, and
reflection is one way to implement a DB-proxy for SQL-compliant DBs, instead
of using plugins.

1. presentation
2. business logic

2b. Abstraction of storage/retrieval of business data

3.1. data layer containing 3.2. data layer for other
standard SQL compliant DB-technologies
queries

3.1b DB-proxy 3.2b. DB-proxy for those...

4.1. SQL-compliant databases 4.2. Other DBs

I don't know if I made it any clearer what I meant this time, but at least I
tried... ;-)

// Bjorn A

 

[Alan Pretre]

Yes - changes down the line are a concern. All database involved must be
able to use standard SQL.

Standard SQL? I'm not sure there is such a thing across many vendors. I
think the best you can hope for is "mostly the same".

However, how far off base is making use of XML files as the database?

I don't think you would be happy with XML. You see that example alot with
ADO.NET DataSets because they can save and load themselves to/from XML with
serialization. But DataSets as provided by MS don't have any general
querying capability, just limited filtering (though see
http://queryadataset.com/).

Anyway, I do like ODBC because the major vendors supply drivers and the ODBC
SQL syntax is fairly standardized.

-- Alan

 

[Brett]

"Brett" <wrote...
...

You could actually get away with only a single proxy if you use
reflection... ;-)

Through reflection you can dynamically load the ADO.NET-driver, using
some kind of "ini-file" to let the proxy know where to look for it,
which classnames are used for the driver, connection-strings, etc.

[snip]

Well, you have possibly already thought of it, but included it into your
business logic. This kind of layer is often considered a part of that
layer as it's so dependent on the classes in the business logic.

What I mean is simply an interface to the storage/retrieval of the data.
This can be done in several ways. One way is similar to the proxy
variant above, but foremost the use of a distinct interface for this,
makes it easier to change it afterwards anyway.

[snip]

...which in turn is implemented by a class that *calls* the classes with
the SQL or others...

Right. But how does Reflection come into play here? Say for the non SQL
compliant DBs and for the purpose of not having to create a class that
inherits from the interface for each DB.

Aha, I think I've been somewhat muddled and unclear here...

It's two different questions, or rather answers, that are independent of
each other.

I would have the Business Storage layer *above* the data layer, where I
would have plugins for different types of storage, where the SQL-variant
would be implemented with reflection, in order to shift easily between
different SQL compliant databases...

In short, the abstraction part would be a layer above the SQL-part, and
reflection is one way to implement a DB-proxy for SQL-compliant DBs,
instead of using plugins.

1. presentation
2. business logic

2b. Abstraction of storage/retrieval of business data

3.1. data layer containing 3.2. data layer for other
standard SQL compliant DB-technologies
queries

3.1b DB-proxy 3.2b. DB-proxy for those...

4.1. SQL-compliant databases 4.2. Other DBs

I don't know if I made it any clearer what I meant this time, but at least
I tried... ;-)

The above sounds good conceptually and can be done with interfaces. That
still gets into greating multiple classes for each specific implementation.
What I'm saying is I still don't understand how you plan to use Reflection.
You've not demonstrated how this is done.

Thanks,
Brett

 

[Bjorn Abelli]

The above sounds good conceptually and can be done with interfaces. That
still gets into greating multiple classes for each specific
implementation. What I'm saying is I still don't understand how you plan
to use Reflection. You've not demonstrated how this is done.

As I said, I hadn't started yet on my
adhoc-query-machine-for-many-databases, but as I will do it soon I'll need
it anyway, so I scrambled together a small example:

-----------------------
using System;
using System.Data;
using System.Reflection;

namespace DbPlus
{
public class ReflectionProxy : IDbProxy
{
IDbConnection cn;

public ReflectionProxy(string assemblyName, string connectionClass,
string connectionString)
{
Assembly a = Assembly.Load( assemblyName );
Type t = a.GetType(connectionClass);
cn = (IDbConnection) Activator.CreateInstance(t, true);
cn.ConnectionString = connectionString;
cn.Open();
}

public bool IsOpen
{
get { return (cn.State == ConnectionState.Open); }
}

public IDbCommand CreateCommand()
{
return cn.CreateCommand();
}
}
}
-----------------------

When I used it in my testprogram I have hardcoded the values, but these can
easily be put in some ini-file or something...

In this case I simply connect to an Access DB, just to show how it can work.

-----------------------
IDbProxy proxy = new ReflectionProxy(
"System.Data, Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089",
"System.Data.OleDb.OleDbConnection",
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mytest.mdb");
-----------------------

I hope this example show what I mean when I say that you only need one proxy
for the standard SQL compliant DBs.


// Bjorn A

 

[Brett]

As I said, I hadn't started yet on my
adhoc-query-machine-for-many-databases, but as I will do it soon I'll need
it anyway, so I scrambled together a small example:

-----------------------
using System;
using System.Data;
using System.Reflection;

namespace DbPlus
{
public class ReflectionProxy : IDbProxy
{
IDbConnection cn;

public ReflectionProxy(string assemblyName, string connectionClass,
string connectionString)
{
Assembly a = Assembly.Load( assemblyName );
Type t = a.GetType(connectionClass);
cn = (IDbConnection) Activator.CreateInstance(t, true);
cn.ConnectionString = connectionString;
cn.Open();
}

public bool IsOpen
{
get { return (cn.State == ConnectionState.Open); }
}

public IDbCommand CreateCommand()
{
return cn.CreateCommand();
}
}
}
-----------------------

When I used it in my testprogram I have hardcoded the values, but these
can easily be put in some ini-file or something...

In this case I simply connect to an Access DB, just to show how it can
work.

-----------------------
IDbProxy proxy = new ReflectionProxy(
"System.Data, Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089",
"System.Data.OleDb.OleDbConnection",
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mytest.mdb");

I see. So you just pass in the connection parameters at runtime and get a
connection? That's all it does?

Then you use the other data layer to hold you DB specific query calls if you
in fact have any. Then another layer that uses standard SQL all DBs can
utilize? It's a dumb layer and doesn't know which DB it is interacting with
because it doesn't need too.

If there are two DB layers below the business logic (one DB specific and
another standard SQL both of which are only for querying and those types of
functions), you could build another DB layer that encapsulates non common DB
queries but are the same conceptually. Based on what you used in
reflection, overloading would call the correct query version (for
corresponding DB). Would that work? I guess you would need multiple
classes for this part but maintenance has signaficantly been reduced at this
point.

Thanks,
Brett

 

[Bjorn Abelli]

...

I see. So you just pass in the connection parameters
at runtime and get a connection? That's all it does?

Yes, that's about it. And that's all that is needed for the next layer,
where the SQL statements resides.

In this way you can use plain strings to tell what assembly to use, what the
class name for the Connection is, etc.

And it will work for most ADO.NET-drivers around...

Based on what you used in reflection, overloading would
call the correct query version (for corresponding DB).
Would that work?

In my example I used an OleDbConnection, but it works just as well with all
ADO.NET-drivers I know of, as all you really need for standard SQL compliant
DBs, is to expose the interface IDbConnection.

I guess you would need multiple classes for this part
but maintenance has signaficantly been reduced at this point.

Actually, you do *not* need multiple classes for this part, if you with
"this part" means the SQL-driven layer.

If you with "this part" also mean the non-SQL-DBs-layer, yes, then you would
also need classes on the higher abstraction layer, and the classes to
connect to those non-SQL-DBs

But as I believe I said already in my first post, that abstraction layer
might not even be interesting for you, if you think you can suffice with
only SQL-DBs. It was only a first thought on my behalf...

// Bjorn A

 

[Brett]

...


Yes, that's about it. And that's all that is needed for the next layer,
where the SQL statements resides.

In this way you can use plain strings to tell what assembly to use, what
the class name for the Connection is, etc.

And it will work for most ADO.NET-drivers around...


In my example I used an OleDbConnection, but it works just as well with
all ADO.NET-drivers I know of, as all you really need for standard SQL
compliant DBs, is to expose the interface IDbConnection.


Actually, you do *not* need multiple classes for this part, if you with
"this part" means the SQL-driven layer.

If you with "this part" also mean the non-SQL-DBs-layer, yes, then you
would also need classes on the higher abstraction layer, and the classes
to connect to those non-SQL-DBs

But as I believe I said already in my first post, that abstraction layer
might not even be interesting for you, if you think you can suffice with
only SQL-DBs. It was only a first thought on my behalf...

// Bjorn A

What is this part of the reflection loading exactly?
Assembly a = Assembly.Load( assemblyName );

You mentioned INI files but I assume the above is a DLL or EXE, which all
assemblies are. Sorry to keep probing but it is interesting and I don't
fully grasp it all.

Thanks,
Brett

 

[Bjorn Abelli]

"Bjorn Abelli" wrote...

What is this part of the reflection loading exactly?
Assembly a = Assembly.Load( assemblyName );

It simply loads the assembly with the provider... ;-)

When it's loaded into memory, you can access it's classes, without the need
to include it beforehand, when compiling your application.

Depending on which ADO.NET-provider you want to load, this way you can load
it dynamically on the fly, e.g. "MySql.Data.dll" for MySQL, etc.

You mentioned INI files but I assume the above is a DLL
or EXE, which all assemblies are.

With ini-file, I just mean some kind of configuration file, containing plain
text strings with information about assembly names, names for the connection
classes, connection strings etc, in short the strings needed for the
reflection, to be able to load the right assemblies, instantiate the
classes, etc. Personally, I'm going for some XML-formatted configuration
file in my adhoc-query-machine... ;-)

// Bjorn A

 

[Brett]

...


It simply loads the assembly with the provider... ;-)

When it's loaded into memory, you can access it's classes, without the
need to include it beforehand, when compiling your application.

Depending on which ADO.NET-provider you want to load, this way you can
load it dynamically on the fly, e.g. "MySql.Data.dll" for MySQL, etc.


With ini-file, I just mean some kind of configuration file, containing
plain text strings with information about assembly names, names for the
connection classes, connection strings etc, in short the strings needed
for the reflection, to be able to load the right assemblies, instantiate
the classes, etc. Personally, I'm going for some XML-formatted
configuration file in my adhoc-query-machine... ;-)

I see. Why not just put the connection info for each database into a DLL
and load it dynamically? You are already have to specify the specific
assembly in the Assembly.Load() part. So the INI doesn't seem to help here.

Or are you saying the INI file will also have information regarding any DB
specific layers as well? That could also go in the DLL.

Is it possible I could see a simple working copy once you have it available?

Thanks,
Brett