Oledb* Or SQL* classes

[Dinko Deranja]

I read that SQL* classes are optimized for working with SQL Server, and
OleDB classes are for all other Ole DB providers.

BUT, what if I now have an Access database and I wish to upgrade to SQL
Server, or downgrade from SQL Server to Access. Does this mean that I would
have to replace all OLEDB* with SQL* classes or vice versa? Isn't it better
to work with OleDB Classes and later only change the connection string in
case of upgrade?

 

[Cor Ligthert]

Hi Bill,

Are you in Europe, I saw your last message 5 hours ago.

Cor

 

[William Ryan eMVP]

Dinko:

SqlClient is optimized for SqlServer. To say that OleDb is optimized for
all other databases though is a bit of a stretch. I'd recommend using a
factory pattern to build your logic with. You can easily branch it to
another provider with minimal changes but it's important to remember that
there are some pretty notable differences in Database implementations. If
you upgrade from Access to SQL Server, you'd have a little rework, but what
if you went to Oracle? If you used OleDb originally, you'd still be in a
situation where you were using a non-native provider or you'd have the
rework to do to migrate to a new provider.

A factory will allow you to specify what's what and create objects
accordingly, but taking advantage of any provider specific db is
antagonsitic to any truly 'generic' approach. Access doesn't use stored
procs the same way sql server does and it has different datatypes.

Which leads to another point. Even if you did use OleDb, you'd still
probably have to port your code to SqlServer or Oracle if you changed b/c
ISNULL is totally different in Access than it is in Oracle or T-Sql. Dates
are handled differently. There are many other subtleties that could effect
the occassion as well.

Changing back ends is something that probably won't happen but once, twice
at the most in any case I've worked with...and twice was a LOT.
So a pretty simple factory pattern could handle this for you, and then
when/if you migrate to SqlServer, you could get it ported then start taking
advantage of the procs, UDF's etc and migrate at a comfortable pace.

HTH,

Bill

These might also help.. http://abstractadonet.sourceforge.net/
http://www.dnzone.com/showDetail.asp?TypeId=2&NewsId=276

 

[Mary Chipman]

There are other major differences between working with Sql Server data
and Access data besides the providers that you'll need to consider.
You will need to migrate Access SQL to T-SQL, re-write queries as
stored procecures, and so on. Although it seems easy on the surface,
upgrading is not a trivial task due to different data types, server
objects, etc. If the migration process is gradual and you're going to
end up with all-SQL Server at the end of the day, then you might want
to take a piecemeal approach to migration, but you'll want to end up
with SqlClient, which is going to give you better performance and more
functionality.

--Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[William Ryan eMVP]

No, I'm in the states in the east coast.

 

[Guest]

SqlClient library is optimized for SQL Server. You can use the OleDb package if you are looking at switching db later on or you can write your code using a factory pattern to provide this level of abstraction. Alternatively, you can use persistence framework that handles the database differences for you automatically

Tu-Thac
www.ongtech.co

----- Dinko Deranja wrote: ----

I read that SQL* classes are optimized for working with SQL Server, an
OleDB classes are for all other Ole DB providers

BUT, what if I now have an Access database and I wish to upgrade to SQ
Server, or downgrade from SQL Server to Access. Does this mean that I woul
have to replace all OLEDB* with SQL* classes or vice versa? Isn't it bette
to work with OleDB Classes and later only change the connection string i
case of upgrade

 

[Dinko Deranja]

Is there an example of factory pattern for this problem? I suppose that this
is a common problem that MS already solved in MSDN or somewhere within their
support?

SqlClient library is optimized for SQL Server. You can use the OleDb

package if you are looking at switching db later on or you can write your
code using a factory pattern to provide this level of abstraction.
Alternatively, you can use persistence framework that handles the database
differences for you automatically.

 

[Paul Hatcher]

Dinko

Have a look on GotDotNet at http://workspaces.gotdotnet.com/omniclient -
there's a project that I published that provides a wrapper ADO.NET provider
to encapsulate the various providers.

Regards

Paul