Filling a DataSet with an ADO Recordset or Record

  • Article

To provide access from ADO.NET to ADO Recordset and Record objects, the .NET Framework Data Provider for OLE DB overloads the Fill method of the OleDbDataAdapter to accept an ADO Recordset or Record object. Filling a DataSet with the contents of an ADO object is a one-way operation. That is, data can be imported from the ADO Recordset or Record into the DataSet, but any updates to the data must be handled explicitly. However, once you have populated a DataSet with data from an ADO object, you can resolve changes back to the data source using a DataAdapter, and you can also write data as XML.

The Fill operation appends rows to the existing DataTable objects in the DataSet if no primary key information is available. If primary key information is available for the DataTable, the Fill operation updates rows with matching primary keys. For more information about retrieving primary key information from a data source, see Adding Existing Constraints to a DataSet. Alternatively, primary key information can be set programmatically as described in Defining a Primary Key for a Table.

To consume a Component Object Model (COM) component that returns an ADO Recordset or Record object using .NET COM interop services and ADO.NET, you need to first import the type library information for the COM component and ADO using the Type Library Importer (Tlbimp.exe). For more information about accessing COM objects from the .NET Framework, see Exposing COM Components to the .NET Framework.

**Note   **Visual Studio.NET provides a Primary Interop Assembly (PIA) for ADO in the "Program Files\Microsoft.NET\Primary Interop Assemblies" directory in the file adodb.dll. If you have a copy of Visual Studio.NET installed, it is recommended that you use this file (adodb.dll) as opposed to importing your own using the Type Library Importer (tlbimp.exe). For more information on Primary Interop Assemblies, see Primary Interop Assemblies.

For example, an existing COM component with a ProgId of ADOComponent.DataClass is compiled into ADOComponent.dll. It has methods that return objects of type ADODB.Recordset. To consume this object from .NET, import both ADOComponent.dll, and msado15.dll, which contains the ADODB.Recordset and ADODB.Record objects. To import the COM type libraries to .NET, issue the following commands.

TlbImp "C:\Program Files\Common Files\System\Ado\msado15.dll" /out:ADODB.dll
TlbImp ADOComponent.dll /out:ADOCOM.dll

You can then pass the resulting .NET libraries, ADODB.dll and ADOCOM.dll, as library references when compiling a .NET program. The following code demonstrates compiling a Visual Basic .NET program using vbc.exe and supplying the imported COM libraries.

vbc MyVB.vb /r:system.dll /r:system.data.dll /r:system.xml.dll /r:ADODB.dll /r:ADOCOM.dll

The following code demonstrates compiling a C# program using csc.exe and supplying the imported COM libraries.

csc MyCS.cs /r:system.dll /r:system.data.dll /r:system.xml.dll /r:ADODB.dll /r:ADOCOM.dll

The following example shows the code you could write in .NET if the ADOComponent.DataClass object had a method called GetData that returned an ADODB.Recordset.

Dim adoComponent As ADOCOM.DataClass = New ADOCOM.DataClass
Dim adoRS As ADODB._Recordset = adoComponent.GetData()
[C#]
ADOCOM.DataClass adoComponent = new ADOCOM.DataClass();
ADODB._Recordset adoRS = adoComponent.GetData();

The following code example shows how the ADODB.Recordset object can be used to Fill a DataSet.

Dim myDA As OleDbDataAdapter = New OleDbDataAdapter
Dim myDS As DataSet = New DataSet

myDA.Fill(myDS, adoRS, "MyTable")
[C#]
OleDbDataAdapter myDA = new OleDbDataAdapter();
DataSet myDS = New DataSet();

myDA.Fill(myDS, adoRS, "MyTable");

**CAUTION   **When using ADO Recordset or Record objects in conjunction with .NET Framework applications, always call Close when you are finished. This ensures that the underlying connection to a data source is released in a timely manner, and also prevents possible access violations due to unmanaged ADO objects being reclaimed by garbage collection when existing references still exist.

Note that the OleDbDataAdapter.Fill overload that takes a DataSet and an ADO object implicitly calls Close on the ADO object when the Fill operation is complete. You need to explicitly close the ADO Recordset or Record object after calling the OleDbDataAdapter.Fill overload that takes a DataTable.

See Also

Accessing an ADO Recordset or Record from ADO.NET | Type Library Importer (Tlbimp.exe) | Exposing COM Components to the .NET Framework