Common database code for Oracle, SQL Server, MS Access

I am writting an application in VB.Net, as part of programming I realize that depending on what database I connect to Access, SQL Server or Oracle, I have to use a different SQL Query Syntax... and include different type of database libraries in my code

In phase one we want to develop the app on MS Access and later on wants to migrate to Oracle or SQL Server.

>>What do I need to do to write the code in such a way that I don't have to rewrite any code when I migrate the app from MS Access to SQL Server or Oracle? Can I just use ODBC connection all across? Will ODBC work on ACCESS from VB.Net?

Thanks in advance
-Raj

anonymous
Thu Mar 04 02:46:10 CST 2004

Well...if you use the OLE DB provider you'll have to change only the connection string and , where necessary, the sql sintax of your query. if you'll use a specific providere (e.g. System.Data.SQL) you must create different object. In your situation you can use oledb an provide the connection string from an external source such the xml configuration fil

bye

Miha
Thu Mar 04 02:55:17 CST 2004

Hi Raj,

There are two ways:
Either use Odbc or OleDb providers (easier) or
create a factory pattern approach (faster).

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"Raj" <anonymous@discussions.microsoft.com> wrote in message
news:CCF96A69-8B55-4E0B-A4E9-3F2AE4B2E90B@microsoft.com...
> I am writting an application in VB.Net, as part of programming I realize
that depending on what database I connect to Access, SQL Server or Oracle, I
have to use a different SQL Query Syntax... and include different type of
database libraries in my code.
>
> In phase one we want to develop the app on MS Access and later on wants to
migrate to Oracle or SQL Server.
>
> >>What do I need to do to write the code in such a way that I don't have
to rewrite any code when I migrate the app from MS Access to SQL Server or
Oracle? Can I just use ODBC connection all across? Will ODBC work on ACCESS
from VB.Net?
>
> Thanks in advance,
> -Raj

Morten
Thu Mar 04 17:51:50 CST 2004

You should use a persistence framework to provide an additional layer of
abstraction. I've written one and released it as open source (LGPL,
which means you can use it in commercial apps too). It's called Gentle
and currently supports SQL Server, Access/Jet/Ole, MySQL, PostgreSQL,
SQLite. Firebird/Interbase support is in the works.

It generates all the sql for simple object IO and provides an SqlBuilder
for constructing more complicated queries. It does not support n:m
relations at the moment, so if you need complicated joins you have to
execute custom sql, but can still benefit from the other features (such
as automatic result parsing and object construction).

You can visit the workspace (and download the latest source) here:
http://workspaces.gotdotnet.com/gentle

I realise this all sounds a bit marketing-like, but I really believe in
the benefit of using such a framework. ObjectSpaces is similar (in
features if not in approach) but is unlikely to support very many
databases (at least for quite a while). There's also something called
Matchbox, which is similar to Gentle but less advanced. Also, there is
OJB.NET, which looks very nice but probably doesn't perform (it uses
ContextBound objects and ContextAttributes afaik). There's also a ton of
commercial apps available if you have the dough.

If you wish to go with Gentle, there's a quick-start tutorial here:
http://www.mertner.com/projects/gentle

Yours,
Morten


Raj wrote:
> I am writting an application in VB.Net, as part of programming I realize that depending on what database I connect to Access, SQL Server or Oracle, I have to use a different SQL Query Syntax... and include different type of database libraries in my code.
>
> In phase one we want to develop the app on MS Access and later on wants to migrate to Oracle or SQL Server.
>
>
>>>What do I need to do to write the code in such a way that I don't have to rewrite any code when I migrate the app from MS Access to SQL Server or Oracle? Can I just use ODBC connection all across? Will ODBC work on ACCESS from VB.Net?
>
>
> Thanks in advance,
> -Raj