filling a dataset with two tables using a single query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jiho Han (jiho.han_at_infinityinfo.com)
Date: 03/23/05 

Date: Wed, 23 Mar 2005 09:45:41 -0500

This something easily done in ADO but it's turning out to be unnecessarily
complicated in ADO.NET.

Consider a SQL statement like this:

SELECT A1.OPPORTUNITYID, A1.DESCRIPTION, A1.STATUS, A2.OPPORTUNITYID
EXTOPPID, A2.SOURCE
FROM OPPORTUNITY A1 INNER JOIN OPPEXT A2 ON (A1.OPPORTUNITYID =
A2.OPPORTUNITYID)
WHERE A1.DESCRIPTION = 'abc123'

In ADO, the following code will locate the record and if found, update both
tables with optimistic concurrency.

Set objConn = New ADODB.Connection
' assume connection opened
Set objRS = New ADODB.Recordset
With objRS
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Open "<above sql here>", objConn
    If Not .EOF Then
        .Fields("STATUS").Value = "Open"
        .Fields("SOURCE").Value = "Import"
        .Update
    End If
    .Close
End With

What's more, not seen in the code is that, the recordset knows how to
generate the insert/update sql statement dynamically -- parameterized and
prepared if necessary -- using only the fields that are modified.

Now in ADO.NET, there doesn't seem to be a simple way to do this. What I've
seen so far, requires two separate Adapter to be created, which will result
in two separate trips to the DB.
If I try to do it in one single trip, using the above query and
TableMappings, I don't know how I would do that other than:
1. Load the resultset as an untyped dataset or use a datareader.
2. Assign each column values to the typed dataset or an untyped dataset
accordingly.
3. Use DataAdapter on the assigned dataset to commit the changes.

The biggest hurdle is that the dynamic sql generation capability of classic
ADO is nowhere to be found. CommandBuilder doesn't handle optimistic
concurrency at all and the Wizard only creates the necessary sql statements
one time -- static.

Am I missing something here or does this seem like a step backward?
Thanks in advance.

Jiho

Relevant Pages

  • Re: ADO Behaving VERY badly...
    ... > VB6 and VBScript to retrieve ADO Recordsets for a number of individual SQL ... > two second pause, and a few more statements will execute, then another ... It's not dependent on the SQL statement being issued, ... This database is pretty tight, normalized, and indexed ...
    (microsoft.public.data.ado)
  • Re: ADO Timestamps
    ... not worry about date and time format conversion if you use ADO. ... if you need to execute SQL statement all you need to do is to use Command ... If you declare datetime parameters using proper datatype then it ...
    (microsoft.public.vb.database.ado)
  • Re: Updatable View with ADO and Trigger INSTEAD OF
    ... By default ADO/ADO.NET will generate INSERT SQL statement based on ... statements in ADO with the Command object to execute actual INSERT ... "Martino" wrote in message ... > Is there a vay to force ADO to generate insert for the view and not for ...
    (microsoft.public.data.ado)
  • Re: SQL string problem
    ... You must "escape" any single quotes when sending SQL statements directly to ... SQL Server via ADO. ... Just pass your SQL statement through this function when opening your ... > truncated and is not passed to SQL server 2000 in a complete form. ...
    (microsoft.public.excel.programming)