Implementing ODBC in ASP .NET

<b>The Problem</b><br><br>Anybody who has ever connected a web page to a simple database is probably familiar with Microsoft’s Open Database Connectivity (ODBC) interface. This is the interface that enables a developer to assign a Data Source Name (DSN) to the database, and then interact with it in a consistent fashion regardless of the specific flavor of database behind the DSN.<br><br>In combination with Microsoft’s Active Server Pages (ASP), ODBC became a very popular technology, because it’s very simple to use and it works. In fact, if you are developing in a shared hosting environment and are not paying a premium for access to a SQL or Oracle server, then ODBC may well be your only reasonable option.<br><br>Enter ASP .NET. When Microsoft folded ASP .NET into its high-end development environment, Visual Studio .NET, they did not include native support for ODBC. Why not? Because Microsoft has a new technology, called OLE DB, that is supposed to replace ODBC. As a result, the data server controls that ship with Visual Studio .NET support OLE DB and database server environments such as SQL Server, but not ODBC.<br><br><b>The Solution (Sort Of)</b><br><br>Microsoft’s omission caused an enormous hue and cry from stalwart ASP developers who wanted to move to the more powerful .NET application framework while retaining access to their beloved ODBC drivers. It also caused a great deal of confusion among newcomers to Visual Studio .NET who tried—and failed—to make the new .NET OLE DB server controls connect to their ODBC DSN’s. Microsoft responded with a new set of data server controls targeted specifically at ODBC connections, which are available for download on Microsoft's website.<br><br>Unfortunately, there’s a catch. The normal procedure for establishing an OLE DB or Server data connection within Visual Studio .NET goes something like this:<br><ul><br><li>Drag the desired table or data view onto the web form. Visual Studio creates the appropriate Data Connection and Data Adapter objects. <br><br><li>Select Generate Dataset from the Data Adapter’s pop-up menu. The resulting wizard guides the user through a Dataset design process, ultimately producing an approriately formatted XML schema for the dataset. <br><br><li>Select Configure Adapter from the Data Adapter’s pop-up menu. The resulting wizard guides the developer through generating the appropriate Data Command objects and hooks these to the dataset, generating all the required code behind the scenes. <br>On attempting the same procedure with the new ODBC server controls, the user quickly finds that the ODBC Data Adapter server control’s pop-up menu does not include the Generate Dataset and Configure Adapter options. In short, Microsoft has provided the requisite data objects, but has left out the logic required to wire these objects into .NET’s new XML-based Dataset standard.<br></ul><br>ASP die-hards will wonder why all the fuss… after all, the old ADO recordset object is still available, and it works as well now as it did before. The developers of the new ODBC server controls must have had the same thought. The answer, however, is that in bypassing the new .NET Dataset, a developer cuts himself off from one of the more flexible and innovative articles in the .NET toolbox. Besides, nobody negotiates the very steep .NET learning curve in order to keep doing the same old thing.<br><br><b>Making It Work</b><br><br>Ultimately, the solution to this problem is for some enterprising developer to pick up the reins and finish what Microsoft started by writing a set of fully functional ODBC server controls. In the meantime, however, it turned out to be relatively straightforward to examine the code generated by the OLE DB server controls and adapt it to the ODBC object set.<br><br>Like to read more? I've posted the entire text of this article, with fully documented source code, to our site at <a href="http://www.hotquant.com/proj/hqweb/odbc.aspx" target="_blank">www.hotquant.com/proj/hqweb/odbc.aspx</a>. Hope it proves helpful.<br><br>Jason W.