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
27  1936 
"Brett" <no@spam.net> wrote in message
news:O0**************@TK2MSFTNGP12.phx.gbl... 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
"Alan Pretre" <al********@newsgroup.nospam> wrote in message
news:u6*************@TK2MSFTNGP15.phx.gbl... "Brett" <no@spam.net> wrote in message
news:O0**************@TK2MSFTNGP12.phx.gbl... 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.
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
"Brett" <no@spam.net> wrote in message
news:ee**************@TK2MSFTNGP10.phx.gbl... 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
"Brett" wrote... 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
"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...
"Brett" wrote... 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".
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
"Alan Pretre" <al********@newsgroup.nospam> wrote in message
news:O7**************@TK2MSFTNGP15.phx.gbl... "Brett" <no@spam.net> wrote in message
news:ee**************@TK2MSFTNGP10.phx.gbl... 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?
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
"Brett" wrote... "Bjorn Abelli" wrote... "Brett" wrote...
If I want to easily swap the database I'm using, what is
the best method for developing that tier in my application?
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?
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). 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?
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... ;-) 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?
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
"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:O1*************@TK2MSFTNGP14.phx.gbl...
"Brett" wrote... "Bjorn Abelli" wrote... "Brett" wrote... If I want to easily swap the database I'm using, what is
the best method for developing that tier in my application? 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?
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).
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?
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... ;-)
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?
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
"Brett" <wrote... "Bjorn Abelli" 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.
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 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?
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
"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:uj**************@TK2MSFTNGP09.phx.gbl...
"Brett" <wrote... "Bjorn Abelli" 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.
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 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?
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
"Brett" wrote... "Bjorn Abelli" wrote... "Brett" <wrote... "Bjorn Abelli" 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... ;-)
// Bjorn A
"Brett" <no@spam.net> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... 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
"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:es**************@TK2MSFTNGP15.phx.gbl...
"Brett" wrote... "Bjorn Abelli" wrote...
"Brett" <wrote...
"Bjorn Abelli" 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
"Brett" <no@spam.net> skrev i meddelandet
news:eG**************@TK2MSFTNGP10.phx.gbl... 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
"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:uJ**************@TK2MSFTNGP15.phx.gbl...
"Brett" <no@spam.net> skrev i meddelandet
news:eG**************@TK2MSFTNGP10.phx.gbl...
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.
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
"Brett" wrote... "Bjorn Abelli" wrote...
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();
}
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?
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
"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:ed**************@TK2MSFTNGP12.phx.gbl...
"Brett" wrote... "Bjorn Abelli" wrote...
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();
}
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?
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
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
"Brett" wrote... "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
"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:eu**************@TK2MSFTNGP12.phx.gbl...
"Brett" wrote... "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... ;-)
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
"Brett" wrote... "Bjorn Abelli" wrote...
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.
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.
I think you confuse the two concepts with each other...
My application will read from the configuration file (plain text, e.g. an
ini-file), to get the information needed to do the reflection (loading the
right assembly).
That way, I will not need to create a new dll each time I want to configure
for a new DB-connection. I just add some lines in the ini-file, e.g.:
----------------
[Test]
provider = "System.Data, Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089"
cclass = "System.Data.OleDb.OleDbConnection"
aclass = "System.Data.OleDb.OleDbDataAdapter"
cstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb"
[MySQL]
provider = "MySql.Data"
cclass = "MySql.Data.MySqlClient.MySqlConnection"
aclass = "MySql.Data.MySqlClient.MySqlDataAdapter"
cstring = "Database=Test;Data Source=localhost;User
Id=username;Password=password"
----------------
(The lines above will probably wrap in the post, but nevermind...)
So why would I make myself the trouble of compiling a dll, when all I need
is to add a few plain text lines?
Is it possible I could see a simple working copy once
you have it available?
Sure, when it's available... ;-)
// Bjorn A
"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
"Brett" wrote... "Bjorn Abelli" wrote...
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.
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.
I think you confuse the two concepts with each other...
My application will read from the configuration file (plain text, e.g. an
ini-file), to get the information needed to do the reflection (loading the
right assembly).
That way, I will not need to create a new dll each time I want to
configure for a new DB-connection. I just add some lines in the ini-file,
e.g.:
I see. What are the advantages/disadvantages of using the DLL and INI file
approaches?
I do see the INI file will be much quicker to preare and modify. Also, do
you need to drag this INI file around with your EXE or can you put it into a
module assembly file with other metadata? What about compiling it as a
resource with your EXE?
Thanks,
Brett
"Brett" wrote... "Bjorn Abelli" wrote... My application will read from the configuration file (plain
text, e.g. an ini-file), to get the information needed to do
the reflection (loading the right assembly).
That way, I will not need to create a new dll each time I
want to configure for a new DB-connection. I just add some
lines in the ini-file, e.g.:
I see. What are the advantages/disadvantages of using the
DLL and INI file approaches?
I do see the INI file will be much quicker to preare and modify.
Also, do you need to drag this INI file around with your EXE or
can you put it into a module assembly file with other metadata?
What about compiling it as a resource with your EXE?
Sure you can, but then you would lose the "easy to prepare and
modify"-approach, as you still would need to re-compile for each new
DB-configuration.
The thing with this approach is that you *don't* need to modify the exe- or
dll-files, when you add new ADO.NET-providers to your configuration.
There are of course also other ways to achieve a similar result, such as
making use of the Windows-registry, instead of a separate configuration
file.
// Bjorn A
"Bjorn Abelli" <bj**********@DoNotSpam.hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...
"Brett" wrote... "Bjorn Abelli" wrote... My application will read from the configuration file (plain
text, e.g. an ini-file), to get the information needed to do
the reflection (loading the right assembly).
That way, I will not need to create a new dll each time I
want to configure for a new DB-connection. I just add some
lines in the ini-file, e.g.:
I see. What are the advantages/disadvantages of using the
DLL and INI file approaches?
I do see the INI file will be much quicker to preare and modify.
Also, do you need to drag this INI file around with your EXE or
can you put it into a module assembly file with other metadata?
What about compiling it as a resource with your EXE?
Sure you can, but then you would lose the "easy to prepare and
modify"-approach, as you still would need to re-compile for each new
DB-configuration.
The thing with this approach is that you *don't* need to modify the exe-
or dll-files, when you add new ADO.NET-providers to your configuration.
Ok. This would still require an application restart correct? The point is
that we are easily changing out a database. I don't imagine the application
to remain running and the user only pushes the "Release SQL Server" button
then the "Load Oracle" button. Or did you see it that way?
Of course we also need to have code that creates the proper DB tables and
schema right? The queries are hard coded in the lower DB layer. Where
would you put the initialization part for tables and schema, assuming they
do not exists? The tables and schema scripts would need to be generic
enough to work across multiple databases. Otherwise, you have a maintenance
issue. I'm not sure if something like ERwin will do this. However, you
would be married to such a tool everytime you require an update. Unless of
course you wanted to edit those scripts directly. I suppose those scripts
could also be placed into an INI or TXT file as the connection info is.
If tables and schema exits than the above part would be skipped, decreasing
load time.
There are of course also other ways to achieve a similar result, such as
making use of the Windows-registry, instead of a separate configuration
file.
// Bjorn A
"Brett" wrote... "Bjorn Abelli" wrote...
Sure you can, but then you would lose the "easy to prepare and
modify"-approach, as you still would need to re-compile for each new
DB-configuration.
The thing with this approach is that you *don't* need to modify the exe-
or dll-files, when you add new ADO.NET-providers to your configuration.
Ok. This would still require an application restart correct?
Nope! :-)
The point is that we are easily changing out a database. I don't
imagine the application to remain running and the user only pushes
the "Release SQL Server" button then the "Load Oracle" button.
Or did you see it that way?
That's exactly how I see it.
Or rather to change connection via a ComboBox or other type of
dropdown-list...
Of course we also need to have code that creates the
proper DB tables and schema right?
Well...
That's a completely different question.
The queries are hard coded in the lower DB layer. Where would you put the
initialization part for tables
and schema, assuming they do not exists?
Assuming it's a one time activity, I'd put it into a separate application...
The tables and schema scripts would need to be generic enough to work
across multiple databases.
There you could have some problems...
As someone said in this thread before, one issue is that not very many DBs
really comply to standard SQL, which can be overcome to some degree in a
high level API, such as ADO.NET, when it comes to queries, updates, etc.
But when it comes to actually creating the tables, you'll probably find some
issues that will require special handling for some cases. One that I've
wrestled with in the past is the handling of Dates, DateTimes, etc...
If you manage to create a script that will work against all possible DBs,
you're very lucky... ;-)
Otherwise, you have a maintenance issue. I'm not sure
if something like ERwin will do this.
Yes, ERwin would work against most DBs.
However, you would be married to such a tool everytime
you require an update. Unless of course you wanted to
edit those scripts directly. I suppose those scripts could also be placed
into an INI or TXT file as the
connection info is.
That would also open up for the possibility to have different scripts
depending on DBs, if necessary.
// Bjorn A
"Brett" <no@spam.net> a écrit dans le message de news: O0**************@TK2MSFTNGP12.phx.gbl... 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.
I have designed several OPFs (Object Persistence Frameworks) and these have
proven to be highly effective in separating business logic from data
storage. I can swap databases at runtime, import from one database and
export to another and even use ini-files and XML as temporary storage.
A simplistic interface to the OPF would look something like this :
class PersistenceStore
{
bool Store(object obj);
bool Delete(object obj);
IList RetrieveCollectionForType(Type t);
IList RetrieveCollectionForCriteria(Criteria crit);
void RetrieveObject(ref object obj);
}
Behind the scenes, there are many different classes that: manage
Connections, one for each database/store; translate objects and types into
SQL statements and execute them.
Each Connection has a set of abstract methods that know nothing about SQL or
any other specific storage type, and it is only in database specific
implementations of these abstract methods that you actually get to write SQL
statements built from SQL syntax specific to that flavour of database.
If you need more info, I have some articles on my site www.carterconsulting.org.uk.
Joanna
--
Joanna Carter
Consultant Software Engineer This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Anders |
last post by:
We need to create a lookup structure that can contain a flexible amount of
attributes.
We have discussed different design solutions for lookup-functionality, and
come up with this suggestion:
...
|
by: James |
last post by:
Hello,
The PRINT command works fine on Query Analyzer.
However, when I used it with other Database Client,
eg: Aqua Data Studio, nothing got printed out.
Is there a way to make it work?
...
|
by: php newbie |
last post by:
We are negotiating with a vendor and we have a few questions:
1) From a licensing point of view, what is a database? Can we install
multiple copies of SQLServer on one box? In that case, would...
|
by: Dan Williams |
last post by:
I am using Microsoft SQL Server 2000 and have a question about our database
design.
Here is a sublist of tables and columns we currently have:-
Employee
----------
Ee_Code PRIMARY KEY...
|
by: rkusenet |
last post by:
http://biz.yahoo.com/rc/040526/tech_database_marketshare_1.html
Interesting to see that database sales for windows is more than
Unix.
|
by: ImraneA |
last post by:
Hi there
Many thanks to those people who contributed to this group, helped me
greatly.
Enclose, my code, hope it helps others :-
Public Function Export_Excel_9(tbx1 As Variant, tbx2 As...
|
by: Marc DVer |
last post by:
I am at kind of a loss on how to design a certain database project I am
working on. Basically, we have a proprietary program with a standard
backend (though we do not have direct write access to...
|
by: jmDesktop |
last post by:
Hi, probably a simple question that I have for what I think is a simple
database. I have a Vendors table and an Items table. There is only
one items, but several Vendors may have that same item. ...
|
by: qazplm114477 |
last post by:
Hi, i have a problem with a small program i wrote which adds vendors to the database. i created a windows form and made it into a dialog that adds vendors to my database. When I open the dialog to...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
