I miss ADODB... so bad... :'( RRS feed

  • Question


         Hi Everybody,


         Don't get me wrong, I know that ADO.NET is the way to go, and all new development must be done with this platform. Still, it is giving me a big headache, specifically, System.Data.ODBC:


    1. Strings.

       a) Using ADO.NET, SQL 2005, VB2005 and an SQL statement that does not return null values, the following statement works:

             strMyString = "Here is an integer value from the database: " & dtrQuery.GetString(lngColumnIndex) & ". Isn't it nice?"

       b) However, using ADO.NET, SQL 2005, VB2005 and EXACLTY THE SAME SQL statement, the following statement does not work:

            strMyString = CStr(dtrQuery.GetString(lngColumnIndex))             '*** <- it fails saying that it doesn't know how to convert to a string.

       c) As a work around:

            Select Case LCase(dtrQuery.GetDataTypeName(lngColumnIndex))
                Case "varchar", "nvarchar"
                    strMyString = dtrQuery.GetString(lngColumnIndex)
                Case "int", "int identity"
                    strMyString = CStr(dtrQuery.GetInt32(lngColumnIndex))
                Case "float", "decimal"
                    strMyString = CStr(dtrQuery.GetDouble(lngColumnIndex))

            End Select

       I don't even want to think about what I am going to do so my application works equally with Oracle and Access, just like it used to do when it was in VB6 and ADODB.


    2. Dates

       a) Using ADO.NET, SQL 2005 and VB2005, the following statement returned the time part on the very first record, but not in the subsequent ones:

            datMyDate = dtrQuery.GetDate(dtrQuery.GetOrdinal("datColumnWithDateAndTime"))

       b) As a work-around, always use

            datMyDate = dtrQuery.GetDateTime(dtrQuery.GetOrdinal("datColumnWithDateAndTime"))


    3. Connection

        a) Just today, I got a brand new error when trying to use the same connection object to perform two SQL statements: "Connection is busy with results for another command". This never happened to me using ADODB.


         Oh, well. I must embrace the new ways and proceed to learn new tricks... however painful it may be...


         Best Regards,


         Tarh Ik

    PS: This posting has been posted "AS IS"


    Wednesday, September 26, 2007 2:53 AM

All replies

  • Do you have any specific reason to use System.Data.ODBC working with SQL Server from .NET? I believe it is least efficient way to work with SQL Server. System.Data.ODBC basically exists for backward compatibility when you do not have .NET Managed provider or OLEDB provider for your database. With SQL Server this is not the case and you should use .NET managed provider for SQL Server


    Wednesday, September 26, 2007 11:01 AM
  • I hear you.  Good old ADODB is pretty nice.  3 lines of code and you have a updatable scrollable dynamic cursor recordset.  How many lines of code do I have to write with ADO.NET to create a scrollable dynamic cursor recordset?  We call this progress? 


    How many lines of code to grab a record, modify it and save it back to the database in ADO.NET?  Lots.  In ADODB it's about 5 lines of code.


    I suspect the customer is using System.Data.ODBC because he likes the abstraction that ODBC provides to his application.  This was one of the core design prinicples of ODBC, you write your app once and then you can easily swap out different back ends (Oracle, Sybase, DB2, SQL Server) and the code pretty much works.


    ADO.NET is a different story on this front, different design principles.  It's much more efficient and scalable than ADODB but has less of the luxurious ease of use features that ADODB provided. 


    ADO.NET is like a pile of parts that you have to somehow figure out how to bolt together to produce something.  Some basic stuff is easy to do (sort of) like running TSQL.  Ok, takes about 5-6 lines of code to execute a TSQL statement.  Boy this should be one line (SqlConnection.ExecuteSQL).  Now say I want to get a resultset.  Ok, SqlDataReader is a bit clunky but works.  Where is the cursor support?  DataSet?  A bit of a cop out if you ask me. (G)


    But to be honest I like ADO.NET better in the end.  Once you start learning how to put the parts together you can build much more efficient code.  And I like the abstraction that DataSet provides from various quirks in the drivers cursor implementation.  It just feels like someone needs to put together some of the parts (aka SqlCommandBuilder is one example) to help less experienced database programmers to use it.  Perhaps DLinq will help here, not sure.

    Wednesday, September 26, 2007 4:32 PM
  • Try using dtrQuery[lngColumnIndex].ToString() instead of dtrQuery.GetString(lngColumnIndex).  I think that the idea is to eliminate ambiguity.  The first asks for a string conversion while the second asks for a string specifically. Also, your 'workaround' might be made a little easier if you made use of IConvertible.


    Thursday, September 27, 2007 6:13 PM