OleDb vs. Sql Interoperability

[Guest]

I'm building a lot of ASPX pages and business objects that consume and
interact with database data. Some of this is done with data-binding, some is
done with programmatic calls to Stored Procedures, Views, etc.

I've created a DB class that I use to simplify and make consistent my
inteaction with the database, and for all of the aspects of connecting to the
database, building the call (query, Stored Procedure, Stored Procedure with
Parameters, etc.) that works just fine.

But here's my problem: Data that is returned comes back in objects like
XXDataReader (OleDbDataReader or SqlDataReader). If I use one of these in my
..aspx Pages, it means I've committed to using either OleDb for my database
access of SqlClient for my database access. What if I don't want to have to
decide? What if I want to set things up so that, if I want to switch from a
SqlServer to some other database backend, I can just change my DB class, but
not have to touch all of my pages and other objects?

What's the most elegant and prefered way to do this? I know that I *could*
just use OleDb for everything (including SqlServer), but that seems like such
a shame in that I understand that the SqlClient access for SqlServer is
supposed to be *way* more efficient.

Alex

 

[Cor Ligthert [MVP]]

Alex,

This should do what you need, although it is not yet complete in my opinion.

http://msdn2.microsoft.com/en-us/library/system.data.common.dbproviderfactory.aspx

I hope this helps,

Cor

 

[Guest]

Cor -

Thanks for this. I'll admit I don't really understand the Factory concept.
I'll look into it further. One question I *do* have though - it seems like I
could always return a DataTable object from all of the data objects and
functions I've created instead of returning OleDbDataReaders or
SqlDataReaders. This way, all of my binding, etc. could remain the same even
if I were to choose to switch between Sql and OleDb, right?

Is this a bad idea? It says in the documentation that a XXXDataReader object
is more effidient than a DataTable.

Thoughts?

Alex

 

[Kevin Yu [MSFT]]

Hi Alex,

I agree with Cor to use DbProviderFactory. If you wish your app to switch
between SQL Server and other database(such as Access), you can use this
class. Because you can always fill data into a DataSet or DataTable and
return it to the calling method.

XXXDataReader provides us with a fast, forward only access to the database.
It is sometimes faster than a DataTable. However, the DataTable lets you
insert, modify or delete data directly in memeory and update database when
all is done. So which one has better performance depends on the design of
your app.

For more information about the DataSet, please check the following links:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconupdatingdatabasewithdataadapterdataset.asp
http://msdn2.microsoft.com/en-us/library/zb0sdh0b.aspx

If anything is unclear, please feel free to let us know.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Cor Ligthert [MVP]]

Alex,

Maybe this sample can help you although it looks probably very difficult as
this is your first expirience with AdoNet.

Cor

 

[Cor Ligthert [MVP]]

Alex,

No this sample above does not help you

Maybe this one,

http://www.vb-tips.com/dbpages.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

By the way, I have nothing to add to the text from Kevin,

Cor

 

[David Browne]

I'm building a lot of ASPX pages and business objects that consume and
interact with database data. Some of this is done with data-binding, some
is
done with programmatic calls to Stored Procedures, Views, etc.

I've created a DB class that I use to simplify and make consistent my
inteaction with the database, and for all of the aspects of connecting to
the
database, building the call (query, Stored Procedure, Stored Procedure
with
Parameters, etc.) that works just fine.

But here's my problem: Data that is returned comes back in objects like
XXDataReader (OleDbDataReader or SqlDataReader). If I use one of these in
my
.aspx Pages,

First, you shouldn't be using DataReaders in your aspx pages. Use DataSets
instead. They are vastly more flexible, don't hold connections to the
database and perform just fine.

it means I've committed to using either OleDb for my database
access of SqlClient for my database access. What if I don't want to have
to
decide? What if I want to set things up so that, if I want to switch from
a
SqlServer to some other database backend, I can just change my DB class,
but
not have to touch all of my pages and other objects?

What's the most elegant and prefered way to do this? I know that I *could*
just use OleDb for everything (including SqlServer), but that seems like
such
a shame in that I understand that the SqlClient access for SqlServer is
supposed to be *way* more efficient.

Apart from just using DataSets and DataTables (which solves many problems
all at the same time), look at the type inheritence hierarchy for the data
access objects. For instance OleDbDataReader and SqlDataReader both
implement IDbDataReader; you can just code against the abstract types.

David