Suggestions on multiple database support

  • Thread starter Thread starter Chuck
  • Start date Start date
C

Chuck

I am working on a project that will support several different database types
(Access, SQL Server, MySQL and Oracle) and was hoping that everyone could
help shed some light on the different options that are available for this.

What I am looking for are suggestions on a "generic" way to manipulate a
database in terms of creating the actual database and tables, INSERT'ing,
DELETE'ing and SELECT'ing rows. What it comes down to is what are the best
ways to support multiple database types?

I know that I could use hard coded SQL statements that are unique to each
database type but is there a better way?

Any thoughts or suggestions on this would be greatly appreciated.

Thanks,
Chuck
 
Chuck said:
I am working on a project that will support several different database types
(Access, SQL Server, MySQL and Oracle) and was hoping that everyone could
help shed some light on the different options that are available for this.

What I am looking for are suggestions on a "generic" way to manipulate a
database in terms of creating the actual database and tables, INSERT'ing,
DELETE'ing and SELECT'ing rows. What it comes down to is what are the best
ways to support multiple database types?

I know that I could use hard coded SQL statements that are unique to each
database type but is there a better way?

Any thoughts or suggestions on this would be greatly appreciated.
Click to expand...

Try to get as much abstraction as possible. Start with using types in
the database which resolve to .NET types and which are also available in
the other databases you'll support. So avoid 'unique_identifier' and
'bit' types in sqlserver, if you want to support oracle as well, as
oracle doesn't have equivalent types for example.

I'm likely be called 'biased' but I'd like to suggest to use a datatier
solution, like LLBLGen Pro, which supports Access, SqlServer, MySql and
Oracle. With such a solution you write software in a generic way and can
utilize all 4 databases with your code, on a per call basis (i.e. read
record from one db, save it in another one if you want to).

Frans

--
 
The other post addresses the issues of using interfaces so you are
generically accessing databases.

However, as far as writing the actual SQL statements, I don't know if there
is a simple answer here. If you have the most basic of queries, then you are
probably fine.

But as soon as you start needing functions that maybe are particular to a
database, or more complicated query constructs: it may turn out that you
have to do it differently for different databases. Or some may not support
what you want at all.

If you can put all your logic into stored procedures, then you can always
just call the stored procedure and have it do the work. Of course Access
doesn't support them, so right there you have a problem.

As far as I know, there is no real good solution here. This is why a lot of
products only support one database as the backend.
 
¤ If you can put all your logic into stored procedures, then you can always
¤ just call the stored procedure and have it do the work. Of course Access
¤ doesn't support them, so right there you have a problem.
¤

Actually this is probably the best solution you suggested. Access does support stored procedures
(known as QueryDefs) although they may not be as robust functionally as the server type database SPs
supported by SQL Server or Oracle.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top