OleDb vs. Sql Interoperability

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
Sun Aug 20 11:13:22 CDT 2006

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

"Alex Maghen" <AlexMaghen@newsgroup.nospam> schreef in bericht
news:EE0F0CD2-F333-450B-BC13-AD6D22FF11B0@microsoft.com...
> 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
>

AlexMaghen
Sun Aug 20 16:16:02 CDT 2006

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


"Cor Ligthert [MVP]" wrote:

> 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
>
> "Alex Maghen" <AlexMaghen@newsgroup.nospam> schreef in bericht
> news:EE0F0CD2-F333-450B-BC13-AD6D22FF11B0@microsoft.com...
> > 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
> >
>
>
>

v-kevy
Sun Aug 20 21:20:15 CDT 2006

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
Mon Aug 21 00:23:46 CDT 2006

Alex,

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

Cor

"Alex Maghen" <AlexMaghen@newsgroup.nospam> schreef in bericht
news:E7F84D60-43F3-4685-8253-462F1B0296F3@microsoft.com...
> 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
>
>
> "Cor Ligthert [MVP]" wrote:
>
>> 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
>>
>> "Alex Maghen" <AlexMaghen@newsgroup.nospam> schreef in bericht
>> news:EE0F0CD2-F333-450B-BC13-AD6D22FF11B0@microsoft.com...
>> > 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
Mon Aug 21 00:38:10 CDT 2006

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

"Cor Ligthert [MVP]" <notmyfirstname@planet.nl> schreef in bericht
news:ObUhNHOxGHA.4192@TK2MSFTNGP06.phx.gbl...
> Alex,
>
> Maybe this sample can help you although it looks probably very difficult
> as this is your first expirience with AdoNet.
>
> Cor
>
> "Alex Maghen" <AlexMaghen@newsgroup.nospam> schreef in bericht
> news:E7F84D60-43F3-4685-8253-462F1B0296F3@microsoft.com...
>> 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
>>
>>
>> "Cor Ligthert [MVP]" wrote:
>>
>>> 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
>>>
>>> "Alex Maghen" <AlexMaghen@newsgroup.nospam> schreef in bericht
>>> news:EE0F0CD2-F333-450B-BC13-AD6D22FF11B0@microsoft.com...
>>> > 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
>>> >
>>>
>>>
>>>
>
>

David
Mon Aug 21 08:50:29 CDT 2006


"Alex Maghen" <AlexMaghen@newsgroup.nospam> wrote in message
news:EE0F0CD2-F333-450B-BC13-AD6D22FF11B0@microsoft.com...
> 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