ADO.NET 2.0 vs ODBC

[Guest]

Hi,

In my company we are currently using ODBC and i would like them to switch
over to ADO.NET 2.0. Unfortunatly i can not find a document with any good
reasons. I know that datasets, datareaders, ... are more userfriendly but how
about performance is it proven to be resonably faster? Are there more reasons
then performance to make the switch?

 

[W.G. Ryan - MVP]

Pieter:

I'm guessing you mean ADO vs ADO.NET. Performance typically isn't better
per se in ADo.NET b/c you still have to fire the same queries so on the back
end, the same stuff is going on. Where you can realize a benefit is by not
having persistent connections open so you can scale things a lot better.
The 'main' benefit to using ADO.NET is the disconnected nature of it which
means that you can have a ton more users hitting the db provided they aren't
hitting it at the exact same time. You also have a lot more feature rich
set of APIs to work with IMHO and you can work with heterogeneous data
sources. You can have a dataset for isntance that's been populated from an
Oracle, Sql Server, DB2 and Excel data source respectively and work with
them like it all came from one place. Same goes for XML and Web Services.
Moreover you can use XPath for instance to traverse data structures that
aren't relational.

HTH,

Bill

 

[Paul Clement]

¤ Hi,
¤
¤ In my company we are currently using ODBC and i would like them to switch
¤ over to ADO.NET 2.0. Unfortunatly i can not find a document with any good
¤ reasons. I know that datasets, datareaders, ... are more userfriendly but how
¤ about performance is it proven to be resonably faster? Are there more reasons
¤ then performance to make the switch?

ODBC is simply an underlying technology that can be used under ADO.NET. Are you actually using the
ODBC API or just ODBC drivers?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Cowboy \(Gregory A. Beamer\)]

I can think of three high level reasons
Performance
Maintainability
Scalability

Security may also enter the picture. ODBC, in the MS world, adds an
additional layer (or two if you are using RDBMS's that have native
providers, like SQL Server).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***********************************************
Think Outside the Box!
***********************************************

 

[Miha Markic [MVP C#]]

Hi Gregory,

I can think of three high level reasons
Performance

Are you sure? Myth is that ODBC is faster (less overhead, less abstract).

 

[William \(Bill\) Vaughn]

Given the answers you've already been given consider:
Assuming you're accessing the ODBC API directly, writing to the ODBC API
from Visual Studio 2005 will be difficult--although possible. In my
Hitchhiker's Guides (4th-5th Editions) I discuss the complexities and
advantages of using an ODBC API approach and the benefits (and complexities)
of using a "data access layer" (DAL) like RDO or COM-based ADO. ADO.NET is
yet another beast. It permits closer access than any DAL (short of DBLib).
The .NET Framework has supported ODBC from (nearly) the beginning--it's now
exposed as System.Data.Odbc. The choice of a DAL really depends on what
you're doing (as I discuss in my more recent books on ADO.NET). Your first
choice of a DAL (when creating a .NET Framework application) should be a
"native" interface like SqlClient for SQL Server or OracleClient for Oracle.
If you're writing an application that switches from DBMS to DBMS you can use
the new "factory" classes in ADO.NET 2.0 (although I don't recommend this
approach).
As far as performance, no you'll (eventually) discover that any DAL will
be slower than a direct API approach. That's because there are more layers
involved. However, the productivity of the DALs is undisputed. This means
developers can create more applications more efficiently. It turns out that
it does not make much difference how fast you ask the question (setup a
query for execution) or capture the results if the question (query) is
inefficient so comparing the ODBC API with ADO (any flavor) is often a
meaningless exercise.

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.
__________________________________

 

[Guest]

Hi,

Currently we are using a custom written c library that is using the ODBC api
directly so I'm getting some worried that all the technologies that have more
layers will be slower.

I think that ADO.NET is really powerfull and has a great ease of use. The
way you can work disconnected is great, but I'm worried that as often the
decisive factor is the performance.

it is hard for me to accept that 10 years of evolution in data access
technology would not relect in performance gain!

Pieter

 

[Chuck Heatherly]

Given the answers you've already been given consider:
Assuming you're accessing the ODBC API directly, writing to the ODBC API
from Visual Studio 2005 will be difficult--although possible.

It's definitely possible. I am a software tester at my company, and I test ODBC
drivers and have done a lot of work with .NET code that calls the ODBC API
functions directly. You end up converting the #define's in sql.h and sqlext.h
to enumerations, and you pass ODBC handles and pointers to blocks of unmanaged
memory around as System.Runtime.InteropServices.HandleRef structures.

For example, the ODBC API definition for SQLAllocHandle is:

SQLRETURN SQL_API SQLAllocHandle(SQLSMALLINT HandleType,
SQLHANDLE InputHandle, SQLHANDLE *OutputHandle);

and I have this Platform/Invoke signature for it (C#):

[DllImport("odbc32", CharSet=CharSet.Unicode)]
public static extern ReturnCode SQLAllocHandle(HandleType hType, HandleRef
InputHandle, ref IntPtr OutputHandle);

(I omitted the ".dll" on the library name so this code will compile on Unix and
work with Mono.)

It's a LOT of work with Platform/Invoke and the methods in the
System.Runtime.InteropServices namespace. It works great for me because I need
low-level access to the API's and blocks of unmanaged memory, but if you were
considering it for speedier access to data, I don't know if the tradeoffs you'll
make for writing multi-row fetch/update routines and ODBC driver compatibility
checks (i.e., does the driver support fetching data this way? does it support
this statement attribute? etc) will be worth it.

Sometimes I sure wish I could skip all that effort and just open an
OdbcDataReader

Chuck