Need some advice on ADO .NET

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

Guest

Just finishing up reading "Pragmatic ADO .NET" from the .NET Developer Series. Most of the examples deal with single tables. The ones that don't show a very simple relationship like Customer -> Orders.

In the examples, the author seems to advocate loading tables into DataTables using SELECT * queries, then defining the schemas and relationships. My DBA background is cringing at this approach because of the full table scans this would needlessly cause.

Moreover, I often have relationships that depend on several tables. I understand the value of knowing the schema at the ADO .NET level but it seems like it comes at a the cost of not allowing the database to do what it does best in retrieving only the rows it needs using indexes.

Does ADO .NET discourage the use of complex, multi-join queries with limiting WHERE conditions or am I grossly misreading things? If I am, can someone point me to a good resource that contains real-world examples and not just simple, single-table data manipulation?

Thanks in advance for any guidance or advice.

Billy
 
Billy,
I do not believe that ADO.NET discourages the use of database best
practices. ADO.NET is built to be generic so it can handle data from a
variety of sources (databases, XML) as well as provide for disconnected
environments (mobile users) and even allow programmers to define a schema
outside of any existing data source. Many of the features that you have
described are actually for working in disconnected environments or when you
need a smaller subset of the data you have already retrieved.

I like to tell people that just because you can do something doesn't mean
you have to. I could drive my car at 110mph in a 35 zone but that doesn't
mean I have to (though it could be fun...). The same rule holds true here.
Just because you can execute a query that requires a full table scan doesn't
mean you have to. Just because you can define use the DataView class or
Select method to filter the contents of a DataTable doesn't mean you have
to, especially when the DBMS could do it better. I'm sure that you get the
picture.

Most of what I work on is in a web based environment so I find myself using
a lot of DataReaders and DataTables but in almost every case, I'm retrieving
the data from a stored procedure or view but in either case, I'm allowing
the DBMS to perform the filtering. I strongly encourage following the best
practices that go along with querying databases and using the features that
you're questioning only as required by your environment.
 
Thank you for the response. A follow-up question if I may.

Suppose I were to use the more traditional approach of writing the queries and getting the result sets back in DataReaders or DataTables and then populating my controls. When it comes time to update, is it best to simply write my UPDATE statements accordingly or is there and ADO .NET approach I should investigate for this scenario.

My app is a stand-alone application, but future revs will most likely require a client-server, multi-user environment. That's why I want to be sure I get the best practices correct up front.

Thanks,

Billy
 
Yes Billy,
There are a lot of books out there that don't use best practices. My
books discuss the problems with these practices and discuss the
architectural issues you've raised.
As the other folks have said, ADO.NET (and ADO in general) is an "OSFA"
interface (one-size-fits-all). It's designed to permit (fairly) low-level
data access to a variety of backend data access servers and architectures.
It does not really dictate how your application is designed or approaches
data access problems. It simply provides the interfaces to the backend. Yes,
ADO.NET has quite a few classes to support "disconnected" architectures, but
you can also build connected applications as well. I, for one don't endorse
"universal" use of the DataReader as some do. I think it's expensive to code
and debug and if not handled correctly leads to other issues as well. For
those just starting out in .NET I agree that it's tough to know what's
"best". The answer is "it depends". As I said, my books talk to these issues
and are designed especially for those transitioning from ADO classic.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

billyb said:
Just finishing up reading "Pragmatic ADO .NET" from the .NET Developer
Click to expand...
Series. Most of the examples deal with single tables. The ones that don't
show a very simple relationship like Customer -> Orders.
In the examples, the author seems to advocate loading tables into
Click to expand...
DataTables using SELECT * queries, then defining the schemas and
relationships. My DBA background is cringing at this approach because of
the full table scans this would needlessly cause.
Moreover, I often have relationships that depend on several tables. I
Click to expand...
understand the value of knowing the schema at the ADO .NET level but it
seems like it comes at a the cost of not allowing the database to do what it
does best in retrieving only the rows it needs using indexes.
Does ADO .NET discourage the use of complex, multi-join queries with
Click to expand...
limiting WHERE conditions or am I grossly misreading things? If I am, can
someone point me to a good resource that contains real-world examples and
not just simple, single-table data manipulation?
 
Hi Bill,

After looking at the TOC of your book, I have two questions:

1) Is the ADO stuff (minus the Dot NET) relevant if I'm exclusively in .NET and am coming from a different background (PowerBuilder)? Just don't want to waste time reading it if it's not.

2) I want to be sure the the topics deal with "real world" examples and situations such as those I describe in my post. (It looks like it, but before I drop some more personal $ I want to be sure.)

Thanks for the helpful post.
Billy
 
Hi Bill,

After looking at the TOC of your book, I have two questions:

1) Is the ADO stuff (minus the Dot NET) relevant if I'm exclusively in .NET and am coming from a different background (PowerBuilder)? Just don't want to waste time reading it if it's not.

2) I want to be sure the the topics deal with "real world" examples and situations such as those I describe in my post. (It looks like it, but before I drop some more personal $ I want to be sure.)

Thanks for the helpful post.
Billy
 
Dave Fancher said:
I really need more information to answer this question specifically
because your DBMS and your table structure along with what data you're
updating will make a difference.
Click to expand...

I understand what you're saying, but I didn't want to get bogged down in a single example when I'm really looking for the right "approach" in general. Your post was actually very helpful. Thanks for taking the time to respond.

Billy
 
The VB version includes an update on ADOc technology with a bent toward
converting to ADO.NET. It does not address PowerBuilder issues (at all).
The topics are built around real-world problems and their solutions.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

billyb said:
Hi Bill,

After looking at the TOC of your book, I have two questions:

1) Is the ADO stuff (minus the Dot NET) relevant if I'm exclusively in
Click to expand...
..NET and am coming from a different background (PowerBuilder)? Just don't
want to waste time reading it if it's not.
2) I want to be sure the the topics deal with "real world" examples and
Click to expand...
situations such as those I describe in my post. (It looks like it, but
before I drop some more personal $ I want to be sure.)
 
For what it's worth (Bill's the author here!), I thoroughly agree with Bill
regarding his statement about not directly using DataReaders for everything.
There are two reasons I avoid using DataReaders for everything:
1.) Updates to the data become a chore
2.) Other commands cannot be executed on the connection until after the
reader is closed.

If you look at the stack trace for an error with the Fill method of a
DataAdapter you'll see that the method is actually using a DataReader to
populate the table. The nice thing here though is that the Fill method
closes the reader and places the data in a nice, usable package (a
DataTable).

On a side note, the post from Dave Fancher was from me, I just sent it from
work.
 
Back
Top