How to do non dependence on database vendor?

[Bjorn Abelli]

...

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

 

[Brett]

...

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

 

[Bjorn Abelli]

...

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

 

[Brett]

...

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.

 

[Bjorn Abelli]

...

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

 

[Joanna Carter \(TeamB\)]

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