ADO vs. ADO.NET

[EY]

I've migrated a VB6 program to .NET. The VB program uses ADO
(Provider=OraOLEDB.Oracle) and the .NET program uses ADO.NET (same
provider). I know that ADO is different from ADO.NET, but I would like
to make the .NET program works the same as before (especially
performance).

In the VB program, the cursor location and type of the recordset are
not defined. So, I think it's server-side cursor and forward-only
(cache size and max row are not defined as well). What it does is to
execute a SELECT statement with a Command object and assign the result
to a global variable (recordset). Then, it executes a function with an
input parameter, nOutputRow, to output [nOutputRow] number of rows
from the recordset, i.e. it's doing paging whenever this function is
called. Therefore, read-only and forward-only is good enough. When
running a query that returns 50,000 records, it's fast because I think
it's concurrent, i.e. it can process data before all records are
returned.

In .NET, I tried to use data adapter but it's not concurrent. And if I
use data reader, it's concurrent but it doesn't allow me to perform
other database operations to the connection unless the data reader is
closed. In my application, I need to allow other operations before the
data reader is closed and I want to make sure I don't open too many
database connections.

Q1) How does ADO and ADO.NET do caching with the settings that I've
mentioned above? Are all the returned records cached to memory on the
database server/client?
Q2) What is the best way to make the .NET program work the same as
before (especially performance)?
Q3) If data reader is the best option, how can I manage the
connections?

Thanks in advance!

EY

 

[Sylvain Lafontaine]

ADO has the same inability as ADO.NET to do parallel work on the same open
connection. What it does is to open a second, hidden connection when you
are executing another operation on an already busy connection. This process
can lead to subtil and nearly untraceable bugs in situations like editing
two open recordsets from inside a transaction. In ADO.NET, this hidden work
under the hood have been removed: if you need two open connections, then you
have to open these explicitly.

Don't try to hard to find the best performance, otherwise you will often
find yourself with something else. The real work for a SQL-Server is first
to find your data and, after that, to serialize and send them over the
connection to your client application. From the point of view of your
client application, the difference of work required beetween reading
directly the data from the connection (the datareader solution) or storing
them in a dataset before using them is irrelevant in much of the cases.
Only in an ASP.NET application (or where the number of lines is really,
really big but then you have probably a design error) will you find some
avantage of using a datareader because the web server must be seen as a
server, having the duty of answering many clients at the same time.

As to your problem, you will probably find the best performance by writing a
stored procedure directly on the server.

S. L.

I've migrated a VB6 program to .NET. The VB program uses ADO
(Provider=OraOLEDB.Oracle) and the .NET program uses ADO.NET (same
provider). I know that ADO is different from ADO.NET, but I would like
to make the .NET program works the same as before (especially
performance).

In the VB program, the cursor location and type of the recordset are
not defined. So, I think it's server-side cursor and forward-only
(cache size and max row are not defined as well). What it does is to
execute a SELECT statement with a Command object and assign the result
to a global variable (recordset). Then, it executes a function with an
input parameter, nOutputRow, to output [nOutputRow] number of rows
from the recordset, i.e. it's doing paging whenever this function is
called. Therefore, read-only and forward-only is good enough. When
running a query that returns 50,000 records, it's fast because I think
it's concurrent, i.e. it can process data before all records are
returned.

In .NET, I tried to use data adapter but it's not concurrent. And if I
use data reader, it's concurrent but it doesn't allow me to perform
other database operations to the connection unless the data reader is
closed. In my application, I need to allow other operations before the
data reader is closed and I want to make sure I don't open too many
database connections.

Q1) How does ADO and ADO.NET do caching with the settings that I've
mentioned above? Are all the returned records cached to memory on the
database server/client?
Q2) What is the best way to make the .NET program work the same as
before (especially performance)?
Q3) If data reader is the best option, how can I manage the
connections?

Thanks in advance!

EY