Hierarchial recordsets ADO vs ADO.NET

[Utpal]

Hi everybody,

I currently use VB6 for database programming. I am in the process of switching over to VB.NET. I am substituting VB6 code with VB.NET code for some trial programs.
In VB6, I have made most of hierarchial recordsets using the SHAPE syntax of the SHAPE provider. E.g.

With rReport
        .CursorLocation = adUseClient
        .Open "SHAPE { SELECT stage FROM QEAF_Stage }" & _
            " APPEND ( { SELECT * FROM QEAFs_Stages_Chances" & _
            " WHERE [month] = #04/01/2004# }" & _
            " RELATE stage TO stage ) AS EAFs_Stages_Chances", _
            ConnShape, Options:=adCmdText
     End With

I have used the above code in a report. It requires only one round trip to the server to extract the data for the report.

To achieve the same thing in ADO.NET, a DataSet has to be created with two Data Tables in it and a Data Relation has to be created to relate the two tables. E.g.

Dim Conn As SqlConnection = New SqlConnection("Data Source=localhost;" & _
                                            "Integrated Security=SSPI;Initial Catalog=EAF;")
Dim StageDA As SqlDataAdapter = New SqlDataAdapter("SELECT stage FROM QEAF_Stage", Conn)
Dim ChancesDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM QEAFs_Stages_Chances WHERE [month] = #04/01/2004#", Conn)
 
Conn.Open()
 
Dim StageDS As DataSet = New DataSet("Stage")
StageDA.Fill(StageDS, "QEAF_Stage")
ChancesDA.Fill(StageDS, "QEAFs_Stages_Chances")
 
Conn.Close()
 
Dim Rel As DataRelation = StageDS.Relations.Add("StageChances",
                     StageDS.Tables("QEAF_Stage").Columns("stage"),
                     StageDS.Tables("QEAFs_Stages_Chances").Columns("stage"))

Now my question is, using this code, how many round trips to the server would be made to extract the data? Would all the data be extracted from the server to the Data Adapters in a single round trip on executing the command Conn.Open() ?

[alextyx]

Dear Utpal, I can't give you an answer, because I don't know how to use ADO.NET. I only want to say that you are not forced to use ADO.NET in VB.NET. I'm using VB.NET with ADO, because I think ADO.NET was designed for geographic net, not LAN, practically, it is a good choice for web. I have some friends that made the same choice: to use VB.NET, but not ADO.NET. In general this way is preferred when you need a lock of record. ADO.NET supports only optimistic locking! I'will have to face ADO.NET, soon or later, because in some kind of application it offers some advantage, but not every application will have a gain by using it! Choose the way you think is better and good job!

[Utpal]

Thanks alextyx ! I know that I could use ADO as well with VB.NET. That's the reason I am comparing them to choose one of them. It seems from what you said, that ADO is better for entry modules, because it supports all types of locking. But, from the point of view of reports, maybe ADO.NET is better, because they say it is faster. That's why I want to know more about it.

[salvelinus]

ADO.Net and datasets allow you to minimize network traffic. A dataset is like a subset database on your local computer. You connect to the main db to get your data, disconnect, work on the data, then reconnect to update the main db.
This is good for a lot of apps, although it does have the problems with conflicting updates you mention. .Net isn't suited for real time apps (the usual example is airline flight reservation bookings, which could be updated several times a minute). Supposedly, a future release of .Net will allow connected datasets. At least that's what I've read.
If you need to lock the record while the user has it, .Net may not be the way to go.