When new users approach NetCOBOL for .NET and are using embedded SQL in their COBOL code, one question we often get is whether to use ODBC or ADO.NET as the database access mechanism. NetCOBOL for .NET supports both and the good news is that choosing between the two can be as simple as changing configuration settings in your application’s configuration file (usually with the help of the NetCOBOL for .NET provided Runtime Environment Setup Utility). This is because the NetCOBOL compiler and runtime are responsible for handling all the work of translating your embedded SQL queries into calls that are made to the database that you specify.

So why have two different access mechanisms? The answer is that the two access mechanisms do behave differently. The simplest way I can think to distill the difference is to say that ODBC provides a feature set that supports common COBOL programming behaviors better, but ADO.NET can in some cases yield better performance. For new users, I would recommend that you start by using ODBC and then when you are looking to tune the performance of your application, you try testing with ADO.NET to see if a) you aren’t affected by the feature limitations of using ADO.NET and b) you actually get a performance benefit from using it.

The rest of this blog post will try to give you some more specifics about how the two access mechanisms differ. The fundamental reason for most of the feature limitations in ADO.NET stems from the fact that ADO.NET does not support server side cursors. COBOL applications tend to use lots of cursors so what’s a COBOL runtime to do? The answer is that the NetCOBOL runtime emulates needed cursor behavior using client side cursors. There simply isn’t a way, however, to get the same database consistency mechanisms with client side cursors that you can get with server side cursors, so only optimistic concurrency is supported with ADO.NET.

If you are going to try to use ADO.NET, you will definitely want to read through the “Notes on Using ADO.NET” topic found in the NetCOBOL for .NET User’s Guide. It provides you with a long list of feature limitations that exist when using ADO.NET as the access mechanism, many of which are the upshot of not having server side cursor support.

So, now to the good parts of using ADO.NET. The most important point is really that Microsoft has invested a lot in making the ADO.NET data access drivers perform very well and this can result in better performance for COBOL applications as well. Note, however, that this is not universally the case. If you have an application that uses updatable cursors that select large numbers of rows, but only read a few, ADO.NET will perform poorly and consume lots of memory. This is again because a lot more data is going to be loaded on the client side rather than just using a server side cursor.

Another reason why ADO.NET may be a better choice is due to transaction integration. With ADO.NET, the data access driver uses what is sometimes referred to as a lightweight transaction manager that can manage multiple coordinated transactions within an application without being promoted to a heavyweight transaction that has to be managed by Microsoft’s Distributed Transaction Coordinator (DTC). If you aren’t coordinating transactions between your COBOL code and other parts of your application, then this won’t affect you, but heavyweight distributed transactions perform orders of magnitude slower than lightweight transactions, which can be a significant consideration if you are using them.