Appendix A. ADOLibrary／付録A ADOLibrary

  • A.1. Features and Limitations／機能と制限

  • A.2. Installing and Using ADOLibrary／ADOLibraryのインストールと使用方法

  • A.3. ADOLibrary Reference／ADOLibraryリファレンス

One of the most complex problems in migrating a VB6/COM application to .NET is the conversion of legacy database access techniques to ADO.NET.

ADO.NET differs from all the database access libraries that were popular in the VB6 days, including ADODB, DAO, and RDO. Most notably, ADO.NET promotes the so-called “disconnected” approach to database processing: data is brought from the server to the client, where it can be processed locally, and then all (and only) the changes are uploaded back to the database. Another important difference is that ADO.NET doesn’t offer any kind of support for server-side cursors, such as keysets and dynamic cursors.

Of all the three database object models available to VB6 developers, only ADODB supports a programming model that is “enough” similar to ADO.NET’s disconnected approach. ADODB allows you to open a client-side recordset and use batch updates commands with optimistic lock, client-side disconnected recordsets are akin to ADO.NET’s DataTables, and server-side forwardonly-readonly (FO-RO) recordsets are similar to ADO.NET DataReader objects.

Thanks to this similarity, converting a VB6/ADODB application to VB.NET/ADO.NET is relatively simple, at least according to many articles about VB6 migration. The truth is different, however, for at least two reasons. First, few commercial VB6 applications use disconnected recordsets and FO-RO recordsets exclusively, and many of them relies on server-side cursors, such as keysets. Second, the ADODB and ADO.NET object models differ in so many details – parameterized commands, store procedure invocation, field attributes, just to name a few – that the number of necessary manual adjustments is very high and defeats the convenience of automatic migration software.

We at CodeArchitects decided to solve the problem of ADODB-to-ADO.NET conversion in a new, revolutionary way.

Rather than using our code converter to generate code that compensates for the many differences between the two object model – an approach that other vendors have adopted and that has proven to be largely insufficient for any real-world application – CodeArchitects has authored a .NET library named ADOLibrary, that is basically a hierarchy of ADO.NET objects that has exactly the same object model as and is functionally equivalent to ADODB.

For example, the ADOLibrary contains a class named ADOConnection, which exposes the same properties, methods, and events as the ADODB.Connection object. Likewise, the ADORecordset object has the same programming interface – and, more important, the same behavior – as the ADODB.Recordset object, and so forth.

Microsoft ADODB library and CodeArchitects’ ADOLibrary are very similar, except that

    1. The name of each class is different (the ADODB.classname type corresponds to the CodeArchitects.ADOLibrary.ADOclassname type)

    2. ADOLibrary is a “pure” .NET library and has no dependency on COM.

Thanks to feature a), once you have migrated a VB6 project to VB.NET, you can use a global Find & Replace command inside Visual Studio to replace all references to ADODB into references to ADOLibrary. Unless your project uses some ADODB features that ADOLibrary doesn’t support (see next sections), in the end you obtain a VB.NET project that behaves exactly like the original VB6 program except it uses ADO.NET objects exclusively.

Notice that ADOLibrary doesn’t depend on VB Migration Partner and can be successfully used also if you have converted from VB6 manually, or by using Microsoft Upgrade Wizard, or by using a conversion tool from another vendor.

Even if these two software components are independent from each other, ADOLibrary integrates perfectly with CodeArchitects’ VB Migration Partner. If convert from VB6 using our software, you don’t even need to perform any Find & Replace command, because all ADODB types are converted to types in ADOLibrary automatically, if you wish so.


A.1. Features and Limitations／機能と制限

ADOLibrary version 1.0 replicates and supports many of the most important ADODB features, including:

    • Forwardonly-readonly (FO-RO) recordsets


    • Client-side recordsets with optimistic batch updates


    • Server-side cursors, including keysets, static and dynamic cursors (Microsoft SQL Server only)


    • Standalone, disconnected recordsets (e.g. Dim rs As New Recordset) with custom Fields collection


    • Parameterized commands


    • Stored procedure calls, with input and output parameters


    • Most dynamic properties, e.g. UpdateCriteria, UniqueTable and ResyncCommand

ADOLibrary preserves one of the best ADODB features, namely the ability to interact with different databases by simply changing the ConnectionString property of the Connection object. Internally, ADOLibrary can use the most appropriate ADO.NET data provider. In version 1.0 the following providers are supported

  • System.Data.OleDb
  • System.Data.SqlClient

Support for Oracle and ODBC providers will be added in future releases. (Current version can access Oracle databases by using the OleDb provider, though.)

Interestingly, many objects in the ADOLibrary expose additional properties and methods that aren’t found in ADODB and that allow you to leverage the best features of ADO.NET. For example, the ADORecordset class – in addition to all the members “inherited” from the ADODB.Recordset class – exposes the DataTable property, which returns the System.Data.DataTable object containing all the rows read from the database and transferred to the client-side cursor. Thanks to this property you can then read and write such rows either by using the MoveNext method (the ADODB way) or by directly accessing the DataTable object, as in following example:

        Sub ReadRows(ByVal cn As ADOConnection, ByVal sql As String)
            Dim rs As New ADORecordset
            rs.CursorLocation = ADOCursorLocationEnum.adUseClient
            rs.Open(sql, cn, ADOCursorTypeEnum.adOpenStatic, _
                  ADOLockTypeEnum.adLockBatchOptimistic)
  
            ' access individual rows using ADODB-like approach
            Do Until rs.EOF
                Debug.WriteLine(rs(0).Value)
                '…
                rs.MoveNext()
            Loop
  
            ' access all rows together via the DataTable property
            For Each dr As DataRow in rs.DataTable.Rows
                Debug.WriteLine(dr(0))
                '…
            Next
  
            ' you can also use the DataTable property to bind the recordset to a
            ' standard DataGridView control, or any other bindable .NET control
            DataGridView1.DataSource = rs.DataTable
            rs.BindingManager = Me.BindingManager
        End Sub

Limitations／機能制限

ADOLibrary doesn’t support all ADODB features. Here is the list of the main features that aren’t supported or are supported only partially:

    • Hierarchical recordsets aren’t supported.


    • Server-side cursors (other than FO-RO cursors) are supported only for Microsoft SQL Server databases.


    • Multiple, semicolon-delimited SQL statements are supported in Recordset.Open methods, but not in the Execute method of the Connection and Command objects.


    • Asynchronous execution is allowed for the Open method of Connection and Recordset objects, but not for the Execute method of the Connection and Command objects.


    • The UpdateBatch method of the Recorset object requires that the SQL source statement includes the key fields of all involved tables and doesn’t work with old-syntax JOIN statements, with nested SELECT statements, and with derivate tables.


    • The SELECT statement used to open server-side keyset and dynamic cursors must include at least one non-nullable key column.


    • The Move method doesn’t work and raises an exception with serverside dynamic cursors.


    • The Index, Seek, MarshalOption, StayInSync members of the Recordset class aren’t implemented and are marked as obsolete.


    • The PageSize, Clone, and Find members of the Recordset class aren’t implemented for keysets and other server-side cursors.


    • The CommandStream, Dialect, NamedParameters, and Prepared members of the Command class aren’t implemented and are marked as obsolete.


    • The ReadText, WriteText, and SkipLines methods of the Stream class aren’t implemented and are marked as obsolete.


    • A few dynamic properties of the Connection or Recordset objects aren’t supported.


    • The ADODB.Record class isn’t supported.

More details about these limitations are provided later in this document.


A.2. Installing and Using ADOLibrary／ADOLibraryのインストールと使用方法

You can download ADOLibrary from VB Migration Partner’s Download page, which you can reach from the Help menu. (If you purchased ADOLibrary separatedly, you will receive the URL to this page via email.)

If you are converting a VB6 application using VB Migration Partner, you can enable ADODB-to-ADO.NET conversion using the ADOLibrary in either of the following ways:

    1. You copy the CodeArchitects.ADOLibrary.dll file into VB Migration Partner’s setup folder.


    1. You use an AddLibraryPath pragma to point to the folder where you have stored the CodeArchitects.ADOLibrary.dll file, as in:

    1.     '## AddLibraryPath "c:\adolib"
      Notice that the AddLibraryPath pragma can be only inserted in a *.pragmas file.


    1. You use an ImportTypeLib pragma to alias the ADODB type library with the CodeArchitects.ADOLibrary.dll assembly:

    1.     '## ImportTypeLib msado27.tlb "@c:\adolib\CodeArchitects.AdoLibrary.dll"
      Notice that the ImportTypeLib pragma can be only inserted in a *.pragmas file.

If you apply strategy A), then any VB6 project that references the ADODB type library will be converted to a VB.NET project that references the ADOLibrary instead. Strategies B) and C) allows you to be more granular, therefore they are the approaches we recommend if you want to preserve ADODB in some of your converted projects.

If you are not using VB Migration Partner – for example, you have migrated from VB6 either manually, or using Microsoft Upgrade Wizard (included in Visual Studio 2005 or 2008), or using a converted from another vendor – you can still adopt ADOLibrary. In this case, the manual procedure is as follows:

    1. Load the converted VB.NET project inside Visual Studio


    1. Open the References tab of the My Project designer


    1. Drop the reference to the ADODB.dll file (this will cause many compilation errors to appear)


    1. Add a reference to the CodeArchitects.ADOLibrary.dll file


    1. Add “CodeArchitects.ADOLibrary” as a project-level imported namespace


    1. Using Visual Studio’s Find and Replace command, replace all occurrences of “ADODB.” (dot included) into “ADO” (this step should solve all compilation errors caused at point 3)

The effect of the last action is to replace class names such as “ADODB.Recorset” into “ADORecordset”, so that all statements that were using ADODB objects will now use the corresponding object in ADOLibrary.


A.3. ADOLibrary Reference／ADOLibraryリファレンス

This section describes each class in the library, with details on all the properties, methods, and events that might work differently from “classic” ADODB or that were added to the library to compensate for the differences between the two environments.

The ADOConfig class／ADOConfigクラス

ADOLibrary exposes a special ADOConfig class, which allows you to change the behavior of objects in the library and take full advantage of ADO.NET versatility, with just minor edits in source code. ADOConfig exposes static (Shared in VB.NET) properties exclusively.

BatchUpdatesSetAllValues property／BatchUpdatesSetAllValuesプロパティ
If this property is set to False (the default value) then UPDATE statements generated by UpdateBatch methods assign only the columns that have been modified. For example, if you have read Name, Company, and City fields from a given table and you later modify only the Name and Company fields, the UPDATE statement generated for that row when you issue the UpdateBatch method will assign only these two fields and won’t modify the value of the City field currently stored in the database.

Even if this behavior perfectly mimics what ADODB does, many developers consider it a design flaw of ADODB and would prefer the UpdateBatch method to assign all the fields in the row, regardless of whether they were modified by the client application. You can achieve this safer behavior by simply assigning True to the BatchUpdatesSetAllValues property:

        ADOConfig.BatchUpdatesSetAllValues = True

Notice that the ADORecordset class also exposes the BatchUpdatesSetAllValues property, therefore you can change the behavior for each individual Recordset.

EnforceConstraintsOnLoad property／EnforceConstraintsOnLoadプロパティ
When used to create a client-side static cursor, the Open method of the ADORecordset reads rows from the database and loads them into a private, temporary DataSet object whose EnforceConstraints property is set to False, and only later data is moved into a DataTable. This step is necessary to perfectly replicate the ADODB behavior, which never raises an error if an incoming row doesn’t match all required constraints. (For example, ADODB doesn’t raise an error if the incoming row contains a NULL value for a non-nullable column.)

On the other hand, loading data into a temporary DataSet slightly degrades performances. If you are sure that incoming data doesn’t violate any database constraint you can skip this intermediate step by setting the EnforceConstraintsOnLoad property to True:

        ADOConfig.EnforceConstraintsOnLoad = True

Notice that the ADORecordset class also exposes the EnforceConstraintsOnLoad property, therefore you can change the behavior for each individual recordset.

IgnoreDataSchema property／IgnoreDataSchemaプロパティ
If this property is set to False (the default value) and you open a Recordset in batch optimistic mode, ADOLibrary queries the data source and retrieves schema information about individual fields. This information is used to correctly implement a few properties of the Field object, namely DefinedSize, NumericScale, Precision and the Properties collection. If you are sure that your code doesn’t rely on this metadata information, you can speed up Recordset.Open methods by setting this property to True.

        ADOConfig.IgnoreDataSchema  = True

Notice that this assignment affects all instances of the ADORecordset class. You can be more granular by leaving this global property set to False and assign True to the IgnoreDataSchema property of individual ADOConnection, ADORecordset, and ADOCommand objects.

LibraryKind property／LibraryKindプロパティ
By default, ADOLibrary internally uses the OleDb ADO.NET data provider. If you know that the VB.NET application will only access a specific database – for example, Microsoft SQL Server – you can improve performance by assigning a new value to this property:

        ADOConfig.LibraryKind = ADOLibraryKind.SqlClient

Valid values for the LibraryKind property are: OleDb, SqlClient, Odbc, OracleClient. However, version 1.0 of ADOLibrary only supports the OleDb and SqlClient values. Any other value throws an exception (if ThrowOnUnsupportedMembers is True) or are ignored.

SynchronizingObject property／SynchronizingObjectプロパティ
The ADOConnection and ADORecordset objects can raise asynchronous events. By default, these events run in thread other than the main thread of the VB.NET application. This detail can cause problems and unexpected crashes if the code in the event handler accesses one or more user interface elements, such as a form or a control. You can avoid this problem by assigning a form or a control reference to the ADOConfig.SynchronizingObject property. Any form or control will do:

        ' if inside a form class, "Me" is a reference to the current form
        ADOConfig.SynchronizingObject = Me

Assigning the ADOConfig.SynchronizingObject property affects all the asynchronous events of all the objects instantiated from the ADOLibrary. If only a few event handlers access UI elements you’re better off assigning the SynchronizingObject property of individual ADOConnection and ADORecordset instances.

ThrowOnUnsupportedMembers property／ThrowOnUnsupportedMembersプロパティ
The default behavior of the ADOLibrary is to ignore assignments to properties that aren’t supported – for example the Index property of the ADORecordset class – as well as calls to unsupported methods. This behavior is OK during the early stages of the migration, but can be problematic when running more rigorous tests. By setting the ThrowOnUnsupportedMembers property to True you cause any invocation of unsupported members to cause a runtime exception:

        ADOConfig.ThrowOnUnsupportedMembers = True

Note for VB Migration Partner users: this property is similar to and has the same effect of the VBConfig.ThrowOnUnsupportedMembers property.


The ADOConnection class／ADOConnectionクラス

The ADOConnection class is similar and behaves like the ADODB.Connection class, except for the following details.

Close method／Closeメソッド
Under ADODB, a Connection object is automatically closed when the last reference to it is set to Nothing or just go out of scope. Because of how .NET manages memory, ADOLibrary objects aren’t automatically closed when they go out of scope. Instead, it is essential that you explicitly close an ADOConnection object just before setting it to Nothing or letting it go out of scope.

Failing to do so may cause problems in the migrated .NET application, because the connection is closed only sometime later. For example, if the connection was opened in exclusive mode, no other application will be able to re-open it, until the .NET Framework starts a garbage collection some seconds or even minutes later.

Also, according to .NET programming guidelines, you should never invoke the Close method of an ADOConnection object from inside the Finalize method of a .NET class, or (equivalently) from the Class_Terminate event of the VB6 application being migrated. This practice is to be avoided because the inner ADO.NET object might have been already finalized or disposed of when the .NET Finalize method is invoked.

Connection property (.NET only)／Connectionプロパティ（.NETのみ）
This readonly property returns the DbConnection object that is used for the current ADOConnection instance. This property allows you to mix ADODB-like statements and ADO.NET statements.

        Sub ExecuteUnderTransaction(ByVal cn As ADOConnection)
            Dim trans As DbTransaction = cn.Connection.BeginTransaction()
            ' ... more statements here
            
            
            trans.Commit()
        End Sub

The ADOConnection class also exposes other readonly properties named OleDbConnection, SqlConnection, and OdbcConnection, which return a more specific connection object, or Nothing if the ADO.NET data provider used internally doesn’t match the property type.

DefaultDatabase property／DefaultDatabaseプロパティ
Unlike the ADODB property, this property doesn’t reflect changes in the default database that were enforced by means of a direct USE dbname SQL statement.

Execute method／Executeメソッド
This method works as in ADODB, except it throws an exception if multiple, semicolon-delimited SQL statements are passed as an argument.

IgnoreDataSchema property (.NET only)／IgnoreDataSchemaプロパティ（.NETのみ）
If this property is set to False (the default value) and you open a Recordset in batch optimistic mode, ADOLibrary queries the data source and retrieves schema information about individual fields. This information is used to correctly implement a few members of the Field object, namely DefinedSize, NumericScale, Precision, and Properties. If you are sure that your code doesn’t rely on this metadata information, you can speed up Recordset.Open methods by setting this property to True.

        Sub ReadWithNoSchema(ByVal cn As ADOConnection, ByVal rs As ADORecordset)
            cn.IgnoreDataSchema = True
            rs.Open "Select * From Orders", cn
            ' …
        End Sub

The default value of this property when the ADOConnection object is instantiated is equal to ADOConfig.IgnoreDataSchema.

LibraryKind property (.NET only)／LibraryKindプロパティ（.NETのみ）
By default, ADOLibrary internally uses the OleDb ADO.NET data provider. If you know that a given ADOConnection object will only access a specific database – for example, Microsoft SQL Server – you can improve performance by assigning a new value to this property:

        Dim cn As New ADOConnection
        cn.LibraryKind = ADOLibraryKind.SqlClient

Valid values for the LibraryKind property are: OleDb, SqlClient, Odbc, OracleClient. However, version 1.0 of ADOLibrary only supports the OleDb and SqlClient values. Any other value throws an exception (if ThrowOnUnsupportedMembers is True) or are ignored. If this property is left unassigned, it is set equal to the value of ADOConfig.LibraryKind property.

Open method／Openメソッド
This method works exactly as in ADODB, except that it issues the following SQL command immediately after opening a SQL Server connection:

        SET LANGUAGE 'English'

This step is necessary because ADOLibrary must interpret error messages coming from the database, in order to raise the corresponding ADODB error message.

OpenSchema method／OpenSchemaメソッド
This method accepts only a subset of the enumerated values you can pass to the corresponding ADODB method, namely: adSchemaCatalogs, adSchemaTables, adSchemaColumns, adSchemaProcedures, adSchemaProcedureParameters, adSchemaIndexes. However, an overload that takes the ADO.NET collection name is also provided:

        Sub Test(ByVal cn As ADOConnection)
            ' the following two statements are equivalent
            Dim rs As ADORecordset
            rs = cn.OpenSchema(ADOSchemaEnum.adSchemaCatalogs)
            ' the following statement uses the ADO.NET syntax
            rs = cn.OpenSchema("Catalogs")
        End Sub

SynchronizingObject property (.NET)／SynchronizingObjectプロパティ（.NET）
The ADOConnection object can raise asynchronous events. By default, these events run in thread other than the main thread of the VB.NET application. This detail can cause problems and unexpected crashes if the code in the event handler accesses one or more user interface elements, such as a form or a control. You can avoid this problem by assigning a form or a control reference to the SynchronizingObject property. Any form or control will do:

        ' if inside a form class, "Me" is a reference to the current form
        Dim cn As New ADOConnection
        cn.SynchronizingObject = Me

Version property／Versionプロパティ
This property always returns the value “2.8”.


The ADORecordset class／ADORecordsetクラス

The ADORecordset class is similar and behaves like the ADODB.Recordset class, except for the following details.

BatchUpdatesSetAllValues property (.NET only)／BatchUpdatesSetAllValuesプロパティ（.NETのみ）
If this property is set to False (the default value) then UPDATE statements generated by UpdateBatch methods assign only the columns that have been modified. For example, if you have read Name, Company, and City fields from a given table and you later modify only the Name and Company fields, the UPDATE statement generated for that row when you issue the UpdateBatch method will assign only these two fields and won’t modify the value of the City field currently stored in the database.

Even if the abovementioned behavior perfectly mimics what ADODB does, many developers consider it a design flaw of ADODB and would prefer the UpdateBatch method to assign all the fields in the row, regardless of whether they were modified by the client application. You can achieve this safer behavior by simply assigning True to the BatchUpdatesSetAllValues property:

        Dim rs As New ADORecordset
        rs.BatchUpdatesSetAllValues = True

When an ADORecordset is created, this property is set equal to ADOConfig.BatchUpdatesSetAllValues property.

BindingManager property (.NET)／BindingManagerプロパティ（.NET）
When manually binding an ADORecordset object to a .NET control – such as a DataGridView control – you should also assign this property, as shown in following example:

        DataGridView1.DataSource = rs.DataTable
        rs.BindingManager = Me.BindingManager

 

Bookmark property／Bookmarkプロパティ
This property works as in ADODB, except that you cannot assign it to move to a record that has been deleted. Under ADODB this operation is legal, whereas it throws an exception with the ADOLibrary.

CacheSize property／CacheSizeプロパティ
This property preserves any value you assign to it, but is otherwise ignored because ADOLibrary doesn’t maintain a row cache.

Close method／Closeメソッド
Under ADODB, a Recordset object is automatically closed when the last reference to it is set to Nothing or just go out of scope. Because of how .NET manages memory, ADOLibrary objects aren’t automatically closed when they go out of scope. Instead, it is essential that you explicitly close an ADORecordset object just before setting it to Nothing or letting it go out of scope.

Failing to do so may cause problems in the migrated .NET application, because the connection is closed only sometime later. For example, if the recordset had opened a SQL Server server-side cursor, the current application cannot open another server-side cursor until the .NET Framwork starts a garbage collection (unless you are using the MARS feature that comes with Microsoft SQL Server 2005 and later versions).

Also, according to .NET programming guidelines, you should never invoke the Close method of an ADORecordset object from inside the Finalize method of a .NET class, or (equivalently) from the Class_Terminate event of the VB6 application being migrated. This practice is to be avoided because the inner ADO.NET object might have been already finalized or disposed of when the .NET Finalize method is invoked.

CommandBehavior property (.NET only) ／CommandBehaviorプロパティ（.NETのみ）
The value of this property is used internally and is assigned to the CommandBehavior property of the inner ADO.NET Command object used to open the ADORecordset. For example, if you know that the rowset being read contains only a single row, you can slightly optimize execution as follows

        Sub TestSingleRow(ByVal rs As ADORecordset, ByVal cn As ADOConnection)
            rs.CommandBehavior = CommandBehavior.SingleRow
            ' notice that following statement can return max row at the maximum
            
            
            rs.Open("SELECT * FROM Orders WHERE OrderID=1", cn)
            ' …
        End Sub

This property is ignored if assigned to an opened ADORecordset.

Copy method (.NET)／Copyメソッド（.NET）
This method returns a distinct copy of the current ADORecordset and is equivalent to saving and reloading an ADORecordset object to/from a file. The Copy method takes two parameters, a MarshalOptionEnum value and an optional filter string:

        Sub TestCopy(ByVal rs As ADORecordset)
            Dim copyRs As ADORecordset
            Dim filter As String = "City = 'Boston'" 
            copyRs = rs.Copy(ADOMarshalOptionEnum.adMarshalModifiedOnly, filter)
            '…
        End Sub

If the second parameter is omitted, then the current value of the Filter property is used. The Copy method is only valid for client-side ADORecordset objects.

CursorHandle property (.NET only)／CursorHandleプロパティ（.NETのみ）
This readonly property returns the handle of the server-side cursor used internally when the ADORecordset is used to open a server-side SQL Server cursor. In advanced scenarios you can use this cursor handle to send direct commands to SQL Server.

CursorType property／CursorTypeプロパティ
This property works as in ADODB, except that only the adOpenStatic (when CursorLocation = adUseClient) and adForwardOnly values are guaranteed to work in all cases. All other values refer to server-side cursors and are valid only when accessing a SQL Server database. If a CursorType value isn’t supported, the Open method throws an exception.

Please notice that under ADODB, it is possible to specify CursorType=Keyset (or Dynamic) and LockType=BatchOptmisitic. In this case, all updates are cached until a UpdateBatch command is issued. Conversely, the ADOLibrary updates each individual record immediately. To alert the user that the behavior is different, an exception is intentionally thrown when the UpdateBatch command is issued.

Current version of ADOLibrary doesn’t fully support server-side dynamic cursors.

CurrentCursorRow property (.NET only)／CurrentCursorRowプロパティ（.NETのみ）
This readonly property returns the DataRow object that contains the current row. It is used only when the ADORecordset object is used to access a SQL Server server-side cursor.

DataAdapter property (.NET only)／DataAdapterプロパティ（.NETのみ）
This readonly property returns the DbDataAdapter object that is used internally when the ADORecordset object is used to open a client-side cursor. It can be used to access ADO.NET specific members that have no corresponding member under ADODB.

The ADORecordset class exposes additional readonly properties, named OleDbDataAdapter, SqlDataAdapter, and OdbcDataAdapter – which return a strongly-typed DataAdapter object, or Nothing if the ADO.NET data provider used internal doesn’t match the property type.

DataReader property (.NET only)／DataReaderプロパティ（.NETのみ）
This readonly property returns the DbDataReader object that is used internally when the ADORecordset object is used to open a forwardonly-readonly (FO-RO) cursor. It can be used to access ADO.NET specific members that have no corresponding member under ADODB, for example using the GetBoolean, GetInteger, etc. methods to read column values in a more efficient way.

The ADORecordset class exposes additional readonly properties, named OleDbDataReader, SqlDataReader, and OdbcDataReader – that return a strongly-typed DataReader object, or Nothing if the ADO.NET data provider used internal doesn’t match the property type.

DataSource property／DataSourceプロパティ
This property is currently not implemented.

DataTable property (.NET only)／DataTableプロパティ（.NETのみ）
This readonly property returns the DataTable that contains the rows that have been read from the database when the ADORecordset is used to open a client-side cursor. You can use this property to bind data to a .NET control, such as a DataGridView control.

The inner DataTable also used when working with SQL Server server-side cursors, in which case the DataTable contains only the current database row.

DataView property (.NET only)／DataViewプロパティ（.NETのみ）
This readonly property returns the DataView that is associated to the inner DataTable and that honors the current Filter and Sort settings. This property is non-Nothing only when the ADORecordset is used to open a client-side cursor. You can use this property to bind data to a .NET control, such as a DataGridView control.

EnforceConstraintsOnLoad property (.NET only)／EnforceConstraintsOnLoadプロパティ（.NETのみ）
When used to create a client-side static cursor, the Open method of the ADORecordset reads rows from the database and loads them into an private, temporary DataSet object whose EnforceConstraints property is set to False, and only later data is moved into a DataTable. This step is necessary to perfectly replicate the ADODB behavior, which never raises an error if an incoming row doesn’t match all required constraints. (For example, ADODB doesn’t raise an error if the incoming row contains a NULL value for a non-nullable column.)

On the other hand, loading data into a temporary DataSet slightly degrades performances. If you are sure that incoming data doesn’t violate any database constraint you can skip this intermediate step by setting the EnforceConstraintsOnLoad property to True:

        Dim rs As New ADORecordset
        rs.EnforceConstraintsOnLoad = True

When an ADORecordset is created, this property is set equal to ADOConfig.EnforceConstraintsOnLoad property.

FetchProgress event／FetchProgressイベント
This event is never raised and is marked as obsolete, because ADOLibrary doesn’t support asynchronous fetching. (It does support asynchronous connection and execution, though.)

Filter property／Filterプロパティ
Under ADODB this property can be assigned a string containing a WHERE clause, a FilterGroupEnum enumerated value, or an array of bookmarks. The ADOLibrary accepts the first two types, but supports neither bookmark arrays nor the (undocumented) value 4-adFilterPredicate. Moreover, the value 3-adFilterFetchedRecords is ignored, because ADOLibrary doesn’t maintain a row cache. Finally, when setting the value 2-adFilterAffectedRecord after invoking the UpdateBatch method, bear in mind that deleted records that were successfully committed to the database won’t be included in the filtered Recordset.

Find method／Findメソッド
This method isn’t currently implemented for server-side cursors.

IgnoreDataSchema property (.NET only)／IgnoreDataSchemaプロパティ（.NETのみ）
If this property is set to False (the default value) and you open a Recordset in batch optimistic mode, ADOLibrary queries the data source and retrieves schema information about individual fields. This information is used to correctly implement a few members of the Field object, namely DefinedSize, NumericScale, Precision, and Properties. If you are sure that your code doesn’t rely on this metadata information, you can speed up Recordset.Open methods by setting this property to True.

        Sub ReadWithNoSchema(ByVal cn As ADOConnection, ByVal rs As ADORecordset)
            rs.IgnoreDataSchema = True
            rs.Open "Select * From Orders", cn
            ' …
        End Sub

When an ADORecordset is created, this property is set equal to ADOConfig.IgnoreDataSchema property.

Index property／Indexプロパティ
The Index property is unsupported. It always returns an empty string; assigning a different value throws an exception (if ADOConfig.ThrowOnUnsupportedMembers is True).

LibraryKind property (.NET only)／LibraryKindプロパティ(.NETのみ)
By default, ADOLibrary internally uses the OleDb ADO.NET data provider. If you know that the ADORecordset object will only access a specific database – for example, Microsoft SQL Server – you can improve performance by assigning a new value to this property:

        Dim rs As New ADORecordset
        rs.LibraryKind = ADOLibraryKind.SqlClient

Valid values for the LibraryKind property are: OleDb, SqlClient, Odbc, OracleClient. However, version 1.0 of ADOLibrary only supports the OleDb and SqlClient values. Any other value throws an exception (if ThrowOnUnsupportedMembers is True) or are ignored. If this property is left unassigned, it is set equal to the value of ADOConfig.LibraryKind property or equal to the LibraryKind property of the ADOConnection object used to open the recordset.

LockType property／LockTypeプロパティ
This property works as in ADODB, except that only the adLockReadOnly and adLockBatchOptimistic values are guaranteed to work in all cases. All other values refer to server-side cursors and are valid only when accessing a SQL Server database. If a LockType value isn’t supported, the Open method throws an exception.

MarshalOptions property／MarshalOptionsプロパティ
The MarshalOptions property is unsupported. It always returns 0-adMarshalAll; assigning a different value throws an exception (if ADOConfig.ThrowOnUnsupportedMembers is True).

MaxRecords property／MaxRecordsプロパティ
This property works as in ADODB, except it is implemented internally by adding a TOP clause to the SELECT statement sent to the database. If you assign a nonzero value to MaxRecords and the SQL dialect doesn’t support the TOP clause, the Open method throws an exception.

Move method／Moveプロパティ
This method doesn’t work and raises an exception when used with server-side dynamic cursors.

Open method／Openメソッド
This method fails with server-side keyset and dynamic cursors if the SELECT statement doesn’t include at least one non-nullable key column.

RecordCount property／RecordCountプロパティ
In current ADOLibrary version, this property returns an inconsistent value when used with server-side cursors and the Filter property is being used to filter rows.

Resync method／Resyncメソッド
This method works as in ADODB and correctly honors the Resync Command dynamic property. However, if a custom resync command is specified in the Resync Command property, then the command must include a single “?” (question mark) placeholder and when the SELECT statement contains a single key column.

RowIndex property (.NET only)／RowIndexプロパティ（.NETのみ）
This property sets or returns the zero-based row index of the current row into the inner DataTable, and is significant only when the ADORecordset is used to open a client-side cursor. Assigning this property is roughly equivalent to a Move(n) method.

Save method／Saveメソッド
This method works as in ADODB, except for two details. First, it only works with client-side recordsets (CursorLocatoin=adUseClient). Second, the storage format – both in binary and XML mode – is different from ADODB and therefore you can’t use this method to persist recordsets and share them between VB6 and .NET applications. Moreover, only the binary format (adPersistADTG) is supported when saving to a Stream object.

Seek method／Seekメソッド
The Seek method is unsupported. Invoking it throws an exception (if ADOConfig.ThrowOnUnsupportedMembers is True).

State property／Stateプロパティ
This property works as in ADODB, except it can never return the value 8-adStateFetching, because ADOLibrary doesn’t support asynchronous fetching. (It can return the values 2-adStateConnecting and 4-adStateExecuting because ADOLibrary supports asynchronous connections and asynchronous commands.)

Status property／Statusプロパティ
The implementation of this property is incomplete in current version of ADOLibrary, in that it can return only one of the following enumerated values: adRecOK, adRecUnmodified, adRecDBDeleted, adRecNew, adRecDeleted, adRecModified, adRecConcurrencyViolaton.

StayInSync property／StayInSyncプロパティ
The StayInSync property is unsupported. In always returns True; assigning a different value throws an exception (if ADOConfig.ThrowOnUnsupportedMembers is True).

Supports method／Supportsメソッド
For highest compatibility, this method returns the same value that would be returned under ADODB. However, because some advanced features aren’t currently supported by ADOLibrary, a feature that is returned as “supported” and yet the VB.NET code can later throw an exception when the corresponding property or method is actually invoked.

SynchronizingObject property (.NET only)／SynchronizingObjectプロパティ（.NETのみ）
The ADORecordset object can raise asynchronous events. By default, these events run in thread other than the main thread of the VB.NET application. This detail can cause problems and unexpected crashes if the code in the event handler accesses one or more user interface elements, such as a form or a control. You can avoid this problem by assigning a form or a control reference to the SynchronizingObject property. Any form or control will do:

        ' if inside a form class, "Me" is a reference to the current form
        Dim rs As New ADORecordset
        rs.SynchronizingObject = Me

When an ADORecordset is created, this property is set equal to ADOConfig.Synchronizing property.

UpdateBatch method／UpdateBatchメソッド
This method works as in ADODB, except for the following details:

    • the SELECT statement must include the key/identity field of the table (or tables, if it’s a JOIN statement).


    • JOIN SQL statements are supported, provided that

      • the key/identity field of each table is included in the field list


      • if * is used to indicate “all fields”, then the joined tables must not have fields with same name


      • old join syntax (e.g. SELECT * FROM Table1,Table2) isn’t supported


    • all valid SQL syntax for table names are supported (e.g. database.dbo.table), except

      • field names and table names cannot include dots (even if names are included between square brackets or double quotes)


      • if a table name in the FROM clause is embedded in square brackets (or double quotes), then the same enclosing delimiters must be used if the table name is used a prefix for the field name. For example:

    •     SELECT [Order Details].OrderID FROM [Order Details]      is supportedサポートされます
          SELECT [Order Details].OrderID FROM “Order Details”      isn’t supportedサポートされません

    • derivate tables aren’t supported (e.g. SELECT * FROM (SELECT ….)  ).


    • the UNION clause isn’t supported.


    • if two UpdateBatch commands are issued on the same recordset and you need to modify one or more fields of a row that has been already batch-updated, it is necessary that you perform the following command after the first UpdateBatch and before modifying the fields:

        myRecordset.DataTable.AcceptTable()

Notice that an ADODB UpdateBatch command works even when CursorType=Keyset/Dynamic and LockType=BatchOptimistic. In this case, ADODB postpones all changes in the DB until the UpdateBatch command is executed. In the same circumstances, the ADOLibrary updates each record immediately.

If the table being updated has an auto-increment field or uses a key that is generated by the database server, current version of ADOLibrary can correctly retrieve the auto-generated key only when working with SQL Server databases.


The ADOCommand class／ADOCommandクラス

The ADOCommand class is similar and behaves like the ADODB.Command class, except for the following details.

Command property (.NET only)／Commandプロパティ（.NETのみ）
This readonly property returns the DbCommand object that is used internally by the ADOCommand object. It can be used to access ADO.NET specific members that have no corresponding member under ADODB, for example using the ExecuteScalar method to retrieve single values in a more efficient way.

        Sub GetSingleValue(ByVal cmd As ADOCommand)
            Dim value As Object
            value = cmd.Command.ExecuteScalar()
            ' …
        End Sub

The ADOCommand class exposes additional readonly properties, named OleDbCommand, SqlCommand, and OdbcCommand – which return a strongly-typed Command object, or Nothing if the ADO.NET data provider used internal doesn’t match the property type.

CommandBehavior property (.NET only)／CommandBehaviorプロパティ（.NETのみ）
The value of this property is used internally and is assigned to the CommandBehavior property of the inner Command object. For example, if you know that the rowset being read contains only a single row, you can slightly optimize execution as follows

        Sub TestSingleRow(ByVal cmd As ADOCommand)
            cmd.CommandBehavior = CommandBehavior.SingleRow
            Dim rs As ADORecordset = cmd.Execute()
            ' …
        End Sub

IgnoreDataSchema property (.NET only)／IgnoreDataSchemaプロパティ（.NETのみ）
If this property is set to False (the default value) and you open a Recordset in batch optimistic mode, ADOLibrary queries the data source and retrieves schema information about individual fields. This information is used to correctly implement a few members of the Field object, namely DefinedSize, NumericScale, Precision, and Properties. If you are sure that your code doesn’t rely on this metadata information, you can speed up Recordset.Open methods by setting this property to True.

        Sub ReadWithNoSchema(ByVal cmd As ADOCommand)
            cmd.IgnoreDataSchema = True
            Dim rs As ADORecordset = cmd.Execute()
            ' …
        End Sub

The default value of this property when the ADOCommand object is instantiated is equal to ADOConfig.IgnoreDataSchema.

LibraryKind property (.NET only)／LibraryKindプロパティ（.NETのみ）
By default, ADOLibrary internally uses the OleDb ADO.NET data provider. If you know that a given ADOCommand object will only access a specific database – for example, Microsoft SQL Server – you can improve performance by assigning a new value to this property:

        Dim cmd As New ADOCommand
        cmd.LibraryKind = ADOLibraryKind.SqlClient

Valid values for the LibraryKind property are: OleDb, SqlClient, Odbc, OracleClient. However, version 1.0 of ADOLibrary only supports the OleDb and SqlClient values. Any other value throws an exception (if ThrowOnUnsupportedMembers is True) or are ignored. If this property is left unassigned, it is set equal to the value of ADOConfig.LibraryKind property.

Name property／Nameプロパティ
This property retains the value assigned to it, but is otherwise ignored by ADOLibrary.

CommandStream property／CommandStreamプロパティ
This property is marked as obsolete always returns Nothing. Assigning a different value to it throws an exception (if ADOConfig.ThrowOnUnsupportedMembers property is True).

Dialect property／Dialectプロパティ
This property is marked as obsolete and always returns the following GUID:

        {C8B521FB-5CF3-11CE-ADE5-00AA0044773D}

which corresponds to the SQL language dialect. Assigning a different value to it throws an exception (if ADOConfig.ThrowOnUnsupportedMembers property is True).

NamedParameters property／NamedParametersプロパティ
This property is marked as obsolete always returns False. Assigning a different value to it throws an exception (if ADOConfig.ThrowOnUnsupportedMembers property is True).

Parameters collection／Parametersコレクション
As it happens with ADODB, when an ADOCommand object contains a query with parameters, ADOLibrary has to parse the SQL statement to isolate each and every parameter, corresponding to “?” placeholders. In addition to having an open connection, the following restrictions apply to the SQL SELECT statement assigned to the CommandText property:

    • aliased columns are supported (e.g. “LastName AS LN”), but aliased expressions aren’t (e.g. “SUBSTRING(title,1,10) AS Title”).


    • aliased tables aren’t supported.


    • derived tables aren’t supported (e.g. “SELECT a.au_lname AS Name, d1.title_id FROM authors a, (SELECT title_id, au_id FROM titleauthor) AS d1”).


    • parameters can’t precede the BETWEEN keyword: for example “…WHERE fieldname BETWEEN ? AND ?” is supported but “… ? BETWEEN 10 AND 20” is not.


    • if the statement contains a nested SELECT, parameters can’t appear both in the main WHERE clause and in the nested SELECT statements. (Parameters can correspond to fields belonging to different tables only if the tables appear in a JOIN.)

The following restrictions apply to INSERT SQL statements:

    • the list of columns must be present (* isn’t allowed) and the VALUES keyword must be present.


    • parameters used in the VALUES can’t appear in expressions (i.e. must be the only value assigned to a field).

The following restrictions apply to EXEC SQL statements and stored procedure invocations:

    • parameters can appear in the list of stored procedure arguments, but can’t be part of an expression. For example, “EXEC spname ?, ‘1abc’, ?” is legal, but “EXE spname ?+12” is not.

Prepared property／Preparedプロパティ
This property is marked as obsolete always returns False. Assigning a different value to it throws an exception (if ADOConfig.ThrowOnUnsupportedMembers property is True).

Execute method／Executeメソッド
This method works as in ADODB, except it throws an exception if multiple, semicolon-delimited SQL statements are passed as an argument. Moreover, if the Execute method invokes a stored procedure that returns no records and you are interested in the value returned by the stored procedure, then it is mandatory to specify adExecuteNoRecords in the options argument (in ADODB passing this value is optional):

        ' cmd points to a stored proc that returns no records
        ' and has an output parameter
        
        cmd.Execute( , , ADOExecuteOptionEnum.adExecuteNoRecords)
        ' you can retrieve the stored procedure’s return value now
        
        Dim retValue As Object = cmd.Parameters(0).Value

The ADOField class／ADOFieldクラス

The ADOField class is similar to and behaves like the ADODB.Field class, except for the following details.

Attributes property／Attributesプロパティ
The Attributes property works as in ADODB, except it never returns the following bits: adFldMayDefer, adFldUnknownUpdatable, adFldFixed, adFldMayBeNull, adFldRowID, adFldRowVersion, adFldCacheDeferred

Status property／Statusプロパティ
The Status property is marked as obsolete and always returns zero, because it is only useful with Fields belonging to a Record object (which the ADOLibrary doesn’t support).

HasDefaultValue (.NET)／HasDefaultValueプロパティ（.NETのみ）
This property (which is missing in ADODB) should be set to True for non-nullable fields for which a default value is defined inside SQL Server. Without this information, ADOLibrary is unable to build the correct SQL string when a new record is added to a server-side keyset and dynamic cursors.

You typically need to set this property only once, immediately after opening a Recordset with CursorType=adOpenKeyset or CursorType=adOpenDynamic, only for those fields that have a default value defined in SQL Server, as in this example:

        rs.Open("SELECT * FROM Customers", myConnection, adOpenKeyset)
        ' we know that the Country field has a default value set equal to "US"
        
        rs.Fields("Country").HasDefaultValue = True
        ...

If you open a Recordset other than keyset or dynamic, or if you don’t plan to add records to this Recordset, then you can ignore the HasDefaultValue property.


The ADOParameter class／ADOParameterクラス

The ADOParameter class is similar to and behaves like the ADODB.Parameter class, except for the following details.

Attributes, NumericScale, Precision, Type, Size properties／Attributesプロパティ、NumericScaleプロパティ、Precisionプロパティ、Typeプロパティ、Sizeプロパティ
For improved performance, these properties are read “on demand”, when any of them is accessed for the first time. For this reason, the first time you access any of these properties (for any parameter of a given ADOCommand object) it is mandatory that the connection is still open, else an exception is thrown.


The ADOStream class／ADOStreamクラス

The ADOStream class is similar to and behaves like the ADODB.Stream class, except for the following details.

ReadText, WriteText, SkipLine methods／ReadTextメソッド、WriteTextメソッド、SkipLineメソッド
These methods aren’t currently implemented


The ADORecord class／ADORecordクラス

None of the members in this class is currently implemented.