ANN: September 2, "ADO.NET with Visual Basic .NET"" chat

[Jason Cooke [MSFT]]

Every application uses data. Come and ask members of the Visual Studio and
SQL
teams about the best approaches for working with data in your applications.

Date:
September 2, 2003

Time:
1:00 - 2:00 P.M. Pacific time
4:00 - 5:00 P.M. Eastern time
20:00 - 21:00 GMT
21:00 - 22:00 BST
(For a list of local time zones relative to GMT, please see
http://msdn.microsoft.com/chats/timezones.asp.)

Location:
http://msdn.microsoft.com/chats (then click the name of the chat to enter
the chat room)

To see a list of upcoming chats or set a reminder for this chat, please see
http://msdn.microsoft.com/chats.
You can use the following link to add an event reminder to your calendar for
this chat.
http://msdn.microsoft.com/chats/outlook_reminders/VB_Sept2.vcs
To see an archive of previous chat transcripts, please see
http://msdn.microsoft.com/chats/recent.asp.

Thanks!
Jason Cooke
VB.NET Team
========
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
(c) 2003 Microsoft Corporation. All rights reserved.

 

[Bryan Martin]

Speaking of which......

Can someone explain to me the advantages of using ADO.net over ADO for
normal single query SQL or Access database operations? It seems to me that
ADO.net is extremely over used. From my stand point it requires much less
effort to create a ADO connection/recordset for most cases.

I think I read somewhere that ADO.net handles relationships, XML and other
features/extensions better but for me I am yet to see a instance where I
have not been able to quickly perform the task using ADO. Am I wrong in my
assumption that ADO.net is more of a fad that is way overly used for most?

Bryan

 

[William Ryan]

They aren't mutually exclusive, rather, ADO.NET is a more mature ADO. From
MS's perspective (and I can't speak for them) but I'm guessing that the
future isn't being built with Access/Desktop databases running single or <10
user applications.


You can use ADO.NET in 'connected' mode which takes advantage of DataReaders
and misc Command object methods. In general, if you aren't using Stored
procedures and passing in parameters via the command object, it takes only 5
lines of code, including declarations to retrieve a single value, change
Database structure (DDL) and insert data. To iterate through a datareader,
add 3 to a few more than 3 more lines of code..this is pretty much typical
of using the connected objects...


SqlConnection cn = new SqlConnection("connectionstring");
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM myTable", cn);
cn.Open();
int i = cmd.ExecuteScalar();
cn.Close();

Granted using a DataTable/DataSet/DataAdapter vs old school recordsets might
be a little more complex, but that difference is neglible if you are only
trying to implement ado 2.x functionaliy and not really take advantage of
these objects.

I am no ado rookie, but once I got comfortable with ADO.NET, I don't think
I've yearned for 2.x even one time. Just my two cents, but I think the more
you use it, the more you'll love it.

 

[Bryan Martin]

Hey, I agree .NET is the next big thing. However, you basically gave the
standard response that everyone gives ".NET is the future". This does not
however explain why I see so many people using ADO.NET when I cant under
stand the benefit. Mabe its just me, I dont know....

Take the below for example. Now if I have done the ADO.NET incorrectly then
please set me strait. This is the simplest way I know how. Now the results
on my machine is ADO.NET took 621ms and three objects and more code, while
ADO took two objects and 60ms to complete. Am I missing something by
thinking I would be wasting time, effort, overhead and memory should this
been a real query?


'Start timing
Dim sTime As Integer = System.Environment.TickCount

Dim obj_DotNetConn As New SqlConnection(str_ConnString)
obj_DotNetConn.Open()
Dim obj_DotNetDA As New SqlDataAdapter(New SqlCommand("SELECT ID
FROM tblSMTP", obj_DotNetConn))
Dim obj_DotNetDS As New DataSet()
obj_DotNetDA.Fill(obj_DotNetDS)

Dim obj_DotNetDR As DataRow
For Each obj_DotNetDR In obj_DotNetDS.Tables(0).Rows
Debug.WriteLine(obj_DotNetDR(0))
Next

obj_DotNetConn.Close()

obj_DotNetDS.Dispose()
obj_DotNetDA.Dispose()
obj_DotNetConn.Close()
obj_DotNetConn.Dispose()

'Stop timing
Debug.WriteLine("DOTNET: " & System.Environment.TickCount - sTime &
"ms")

'ADO
'Redo timer
sTime = System.Environment.TickCount

Dim obj_conn As New ADODB.Connection()
obj_conn.Open("Provider=sqloledb; " & str_ConnString)
Dim obj_RS As New ADODB.Recordset()
obj_RS.Open("SELECT ID FROM tblSMTP", obj_conn,
ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly)

Do Until obj_RS.EOF
Debug.WriteLine(obj_RS(0).Value)
obj_RS.MoveNext()
Loop

If obj_RS.State <> 0 Then obj_RS.Close()
If obj_conn.State <> 0 Then obj_conn.Close()
obj_RS = Nothing
obj_conn = Nothing

'Stop timing
Debug.WriteLine("ADO: " & System.Environment.TickCount - sTime &
"ms")


Again....

Bryan

 

[Neil]

You aren't doing equivalent operations in this test - the first test is
using disconnected data and the second a connected approach.

You should either compare DataSet vs ADO Disconnected Batch recordset,
or DataReader vs Forward-Only Read-Only Server-side recordset.


Further, you should factor in the JIT compilation of .Net Code. If you do
an operation once then the JIT may impact the elapsed time. If you repeat
the same operation it will be faster. Database Caching also will have an
impact on this - the first operation will be slower whichever approach you
take, as the data is read from disk.

 

[William Ryan]

As Neil mentions, this is apples and oranges. For one thing, the
DataAdapter will handle opening and closing the connection for you. This
would remove two lines of code. If you have a one table query and that's
all you need, you can simply fill a DataTable instead. That will get rid of
the dataset and in the comparison,

Since you are disposing of the DataSet and adapter in the proc, you didn't
need to use them. Look at me last post with the executescalar. All you'd
need to do isuse SqlDataReader dr = cmd.ExecuteReader();
Then
while dr.Read()
//your debug statement here.
end while

You'll notice a dramatic performance difference plus more concise code.

Cheers,

Bill

 

[William \(Bill\) Vaughn]

For years you guys (and gals) have been complaining about how MDAC
"upgrades" screw up your working applications. You complain about how COM
makes your (development) lives a living hell. You complain about how hard it
is to deploy and make certain that when you install a new application that
it does not break existing apps (and new apps don't break yours). THAT's
what ADO.NET and the .NET Framework is all about--it's about fixing those
problems and a lot more. For the first time since DB-Library you can write
code that talks (more or less) directly to the low-level protocol--whether
you're using SQL Server or Oracle or DB2. ADO.NET is fast (if you know how
to use it) and flexible. Today it's optimized for web applications, but it
can also be used for many client/server apps too. Tomorrow, more features
than you can imagine will appear that will far surpass ADO classic.
Sure, the biggest stumbling block is learning how to use it. That's my
job--to help teach you. Lucky for you (and unlucky for me) there are over 35
books on the shelves to help you learn ADO.NET so I don't make much on the
books. No, ADO classic is not dead. It's just as clumsy as ever and just as
prone to failure as ever. It's likely to be around for some time to
come--like Windows 95.

--
____________________________________
Bill Vaughn
MVP, hRD
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.
__________________________________