ADO to ADO.NET 2.0

[steve]

We're beginning a major rewrite of a VB6 application that currently uses ADO
and an Access database. In this application, we routinely query multiple
table through JOINs and place the result in a single connected record set
that is often bound to a grid for display and editing by the user. Changes
to the database are implemented by the record set's Update command.

We're looking to rewrite using VB.NET in VS2005 and either SQL Express or
Access (haven't decided yet). We can seem to get a handle on how to acheive
the same sort of data handling in ADO.NET 2.0 as we used in ADO, that is,
query into multiple table combined into a single entity that can be bound to
a grid for display and editing by the user, and with a simple Update command
updating the database tables.

There are plenty of examples of this using single tables, but how do we go
about combining data from multiple db tables via query JOINs, and then
binding the result to a single grid or view for display and editing, and
then updating the tables?

Can someone point us to samples or examples illustrating the best way to do
this? TIA!

 

[David Browne]

We're beginning a major rewrite of a VB6 application that currently uses
ADO
and an Access database. In this application, we routinely query multiple
table through JOINs and place the result in a single connected record set
that is often bound to a grid for display and editing by the user.
Changes
to the database are implemented by the record set's Update command.

We're looking to rewrite using VB.NET in VS2005 and either SQL Express or
Access (haven't decided yet). We can seem to get a handle on how to
acheive
the same sort of data handling in ADO.NET 2.0 as we used in ADO, that is,
query into multiple table combined into a single entity that can be bound
to
a grid for display and editing by the user, and with a simple Update
command
updating the database tables.

There are plenty of examples of this using single tables, but how do we go
about combining data from multiple db tables via query JOINs, and then
binding the result to a single grid or view for display and editing, and
then updating the tables?

Can someone point us to samples or examples illustrating the best way to
do
this? TIA!

The good news is that in ADO.NET you don't have to use a JOIN to fetch, bind
and update data from multiple tables. The ADO.NET DataSet lets you use
multiple related tables. In general this much better than binding to join
tables.

However, you can use a JOIN in ADO.NET. The specifics depend on the
back-end. In SQL Server (Express) you would create a VIEW in the database
and use it just like a table. So long as your updates affect only one base
table, it all just works. If your updates target multiple tables you can
define INSTEAD OF triggers on the view to route the changes to the
appropriate base tables.


David

 

[steve]

David,

Thanks for the reply. We're too early in the process to know if we're going
to use SQL Express or Access or to know if and what relationships will be
defined in the db.

However, it's not clear to me why using multiple related tables in ADO.NET
datasets is better than binding to joined tables for updates. Could you
elaborate or steer me to a reference?

Steve

 

[William \(Bill\) Vaughn]

Steve, I'm giving a day-long workshop on ADO.NET 2.0 architectures and best
practices at the VSLive (Vegas) conference. I think this could go a long way
to answering your questions. In addition, my APress book ("ADO and ADO.NET
Examples and Best Practices") was designed specifically for you. It walks
through the issues faced by developers with the same challenges--migrating
from VB6 to VB.NET. While it does not discuss the 2.0 version of ADO.NET
(that book is in edit), the core discussion will help a lot and it's written
with VB developers in mind. I would also be happy to spend a few minutes on
the phone to help get you started out on the right foot. IMHO, it would be a
mistake to stick with JET/Access at this point in the technology. While JET
can seem like an easy-to-use engine, MS is doing whatever it can to back
away from this dated engine. SQL Express is far more mature (it's based on
the SQL Server binaries that have been around for a long time). It also
might be overkill so I'm encouraging developers to consider SQL Everywhere
as a lightweight alternative. I discuss all of these issues in my workshop.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________

 

[steve]

Bill,

Thanks for info. Unfortunately, I won't be attending VSLive. Any chance of
workshop appearing on-line?
Any idea when the 2.0 version of the book will be published? It looks like
it could be very helpful. And thanks for offering your help. When we know
more about what we don't know, I might take you up on your offer. Contact
you first via email?

Thanks again.

Steve

 

[William \(Bill\) Vaughn]

Nope, it's too long to do online and the profit motive is just not there. I
can come to your site to give a custom talk that includes the 2.0 changes as
well as bring you and your team up to speed on the conversion issues. The
2.0 book won't be out until AW finishes editing it.
See www.hitchhikerguides.net for current info.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________

 

[steve]

I've been following some of the discussions regarding Access and SQL/x and
SQL/e and such. Many of our customers have small networks (< 10
workstations), with one workstation used as a file server. No domain
server, etc. Our users share our application's Acces database through a
shared network folder. They don't want and can't afford SQL Server.

We like Access because of the ease of installation, small footprint (even
including MDAC and Jet). We don't like Access because of it seems prone to
corruption, kind of slow, and it's not quite as easy to deal with from .NET
apps as it is from VB6, and most importantly, we can't get a good handle on
how much longer it's going to be around.

SQL/e sound perfect for us - except I've seen that it won't support sharing
the database file. SQL/x would work for us, I think, but presents
installation and administration issues for our customers. Migration to SQL
Server is not in our future, so this is not a factor.

Any advice?

 

[William \(Bill\) Vaughn]

See my blog http://betav.com/blog/billva/2006/05/jet_are_developers_still_consi.html.I just posted a response there.

By the time you see this I should have my JET category in place that gathers other similar commentaries on JET.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________

 

[Cor Ligthert [MVP]]

Bill,

Nice text I will use it in future. Can you add in it, the impossibility to hide Jet by using roles. I think that this in Vista will be even more important.

Cor
"William (Bill) Vaughn" <[email protected]> schreef in bericht
See my blog http://betav.com/blog/billva/2006/05/jet_are_developers_still_consi.html.I just posted a response there.

By the time you see this I should have my JET category in place that gathers other similar commentaries on JET.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________