How to support SQLite and PostgreSQL at the same time?

[Karlo Lozovina]

Hi everyone!

One quick question, with hope that the answers will be the same :). What
to use if I want to ship two versions of my application, one using SQLite,
small embedded database for single-computer instalations, and one using
PostgreSQL for multi-computer instalations.

Of course, my goal is to make as little as possible changes in the code to
support both DB engines, especially as the application grows. So if you
could point out few hints to Google about, I would be most gratefull.

Thanks in advance...

--
_______ Karlo Lozovina - Mosor
| | |.-----.-----. web: http://www.mosor.net || ICQ#: 10667163
| || _ | _ | Parce mihi domine quia Dalmata sum.
|__|_|__||_____|_____|

[Nicholas Paldino [.NET/C# MVP]]

Karlo,

Typically, this would require you to use the base class/interfaces that
are common to the data providers. This means using IDbConnection,
IDbCommand, etc, etc, (or, in .NET 2.0, all data classes should inherit from
the new base classes, DbConnection, DbCommand, etc, etc) instead of the
database-specific classes.

This also means that you will have to account for different flavors of
SQL (or rather, different query languages altogether). If you are not doing
anything horribly complex with SQL that requires platform specific commands,
then you can get away with using SQL embedded/generated in your code (if
that is your preference). However, the better way to do this is to abstract
out the database functionality into stored procedures, which require a name,
and a parameter list. You just have to make sure the name and parameter
list are the same between the two database implementations. Then, calling
your stored proc using the same code should be a breeze.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Karlo Lozovina" <_karlo_@_mosor.net_> wrote in message
news:Xn*****************************@161.53.2.66.. .

Hi everyone!

One quick question, with hope that the answers will be the same :). What
to use if I want to ship two versions of my application, one using SQLite,
small embedded database for single-computer instalations, and one using
PostgreSQL for multi-computer instalations.

Of course, my goal is to make as little as possible changes in the code to
support both DB engines, especially as the application grows. So if you
could point out few hints to Google about, I would be most gratefull.

Thanks in advance...

--
_______ Karlo Lozovina - Mosor
| | |.-----.-----. web: http://www.mosor.net || ICQ#: 10667163
| || _ | _ | Parce mihi domine quia Dalmata sum.
|__|_|__||_____|_____|

[Peter Bromberg [C# MVP]]

Karlo,
As a follow - up to Nicholas' post, if you use the provider model with
interfaces as he described, the only other caution is that you will need to
consider textual SQL statements rather than stored procedures, as SQLite does
not support stored procs.

If you can use .NET 2.0, Robert Simpson has done a remarkable job with his
SQLite ADO.NET provider for .NET 2.0 here:

http://sqlite.phxsoftware.com/

Unfortunately, I am not aware if there is yet a PostGres provider for the
2.0 framework.
--Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Karlo Lozovina" wrote:

Hi everyone!

One quick question, with hope that the answers will be the same :). What
to use if I want to ship two versions of my application, one using SQLite,
small embedded database for single-computer instalations, and one using
PostgreSQL for multi-computer instalations.

Of course, my goal is to make as little as possible changes in the code to
support both DB engines, especially as the application grows. So if you
could point out few hints to Google about, I would be most gratefull.

Thanks in advance...

--
_______ Karlo Lozovina - Mosor
| | |.-----.-----. web: http://www.mosor.net || ICQ#: 10667163
| || _ | _ | Parce mihi domine quia Dalmata sum.
|__|_|__||_____|_____|