OleDb vs. Sql Interoperability

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 -

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
 
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.)
 
Alex,

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

Cor
 
Alex Maghen said:
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,
Click to expand...

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.
Click to expand...

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
 
Back
Top