OleDbDataAdapter.Fill Method
Definition
Important
Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.
Adds or refreshes rows in the DataSet to match those in an ADO Recordset or Record object.
Overloads
| Fill(DataTable, Object) |
Adds or refreshes rows in a DataTable to match those in an ADO |
| Fill(DataSet, Object, String) |
Adds or refreshes rows in the DataSet to match those in an ADO |
Fill(DataTable, Object)
- Source:
- OleDbDataAdapter.cs
- Source:
- OleDbDataAdapter.cs
public:
int Fill(System::Data::DataTable ^ dataTable, System::Object ^ ADODBRecordSet);public int Fill (System.Data.DataTable dataTable, object ADODBRecordSet);override this.Fill : System.Data.DataTable * obj -> intPublic Function Fill (dataTable As DataTable, ADODBRecordSet As Object) As IntegerParameters
- ADODBRecordSet
- Object
An ADO Recordset or Record object.
Returns
The number of rows successfully refreshed to the DataTable. This does not include rows affected by statements that do not return rows.
Remarks
The link between ActiveX Data Objects (ADO) and ADO.NET is a one-way operation in that you can copy data from ADO to the DataSet, but any updates to the data must be handled by ADO.NET.
This overload of the Fill method does not close the input Recordset on completion of the Fill operation.
When handling batch SQL statements that return multiple results, this implementation of Fill and FillSchema for the OLE DB.NET Framework Data Provider retrieves schema information for only the first result.
The Fill operation adds the rows to the specified destination DataTable object in the DataSet, creating the DataTable object if it does not already exist. When you create a DataTable object, the Fill operation ordinarily creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created.
You can use the Fill method multiple times on the same DataTable. If a primary key exists, incoming rows are merged with matching rows that already exist. If no primary key exists, incoming rows are appended to the DataTable. If primary key information is present, any duplicate rows are reconciled and only appear one time in the DataTable that corresponds to the DataSet. Primary key information may be set either through FillSchema, by specifying the PrimaryKey property of the DataTable, or by setting the MissingSchemaAction property to AddWithKey.
If the SelectCommand returns the results of an OUTER JOIN, the DataAdapter does not set a PrimaryKey value for the resulting DataTable. You must explicitly define the primary key to make sure that duplicate rows are resolved correctly. For more information, see Defining Primary Keys.
To function correctly with the .NET Framework Data Provider for OLE DB, AddWithKey requires that the native OLE DB provider obtains required primary key information by setting the DBPROP_UNIQUEROWS property, and then determines which columns are primary key columns by examining DBCOLUMN_KEYCOLUMN in the IColumnsRowset. Alternatively the user may explicitly set the primary key constraints on each DataTable. This makes sure that incoming records that match existing records are updated instead of appended.
If the OleDbDataAdapter encounters duplicate columns while populating a DataTable, it generates names for the subsequent columns, using the pattern "columnname1", "columnname2", "columnname3", and so on. Empty column names are added to the DataTable, using an empty string for the first column, followed by "1", "2", "3", and so on for the subsequent empty columns.
Values in ADO Recordset or Record objects are converted to common language runtime types for storage in the DataSet.
Caution
This overload of the Fill method does not implicitly call Close on the ADO object when the fill operation is complete. Therefore, always call Close when you are finished using ADO Recordset or Record objects. This makes sure that the underlying connection to a data source is released in a timely manner, and also prevents possible access violations because of unmanaged ADO objects being reclaimed by garbage collection when existing references still exist.
When you call the TableMappings.Add method on a DataAdapter and you explicitly map the source table parameter to an empty string, the dataset is successfully filled using the source table, but the dataset will be populated with nothing. For example, in the following example, rDataSet will be populated with nothing.
rAdapter.TableMappings.Add("source table", "");
rAdapter.Fill(rDataSet, "source table");
This example shows how you can skip a result when dealing with multiple results.
The following example uses an OleDbDataAdapter to fill a DataTable using an ADO Recordset. This example assumes that you have created an ADO Recordset.
Dim custDA As OleDbDataAdapter = New OleDbDataAdapter()
Dim custDS As DataSet = New DataSet
Dim custTable As DataTable = New DataTable("Customers")
custTable.Columns.Add("CustomerID", Type.GetType("System.String"))
custTable.Columns.Add("CompanyName", Type.GetType("System.String"))
custDS.Tables.Add(custTable)
'Use ADO objects from ADO library (msado15.dll) imported
' as.NET library ADODB.dll using TlbImp.exe
Dim adoConn As ADODB.Connection = New ADODB.Connection()
Dim adoRS As ADODB.Recordset = New ADODB.Recordset()
adoConn.Open("Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;", "", "", -1)
adoRS.Open("SELECT CustomerID, CompanyName FROM Customers", adoConn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1)
custDA.Fill(custTable, adoRS)
adoRS.Close()
adoConn.Close()
OleDbDataAdapter custDA = new OleDbDataAdapter();
DataSet custDS = new DataSet();
DataTable custTable = new DataTable("Customers");
custTable.Columns.Add("CustomerID", typeof(String));
custTable.Columns.Add("CompanyName", typeof(String));
custDS.Tables.Add(custTable);
//Use ADO objects from ADO library (msado15.dll) imported
// as.NET library ADODB.dll using TlbImp.exe
ADODB.Connection adoConn = new ADODB.Connection();
ADODB.Recordset adoRS = new ADODB.Recordset();
adoConn.Open("Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;", "", "", -1);
adoRS.Open("SELECT CustomerID, CompanyName FROM Customers", adoConn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1);
custDA.Fill(custTable, adoRS);
adoRS.Close();
adoConn.Close();
See also
Applies to
Fill(DataSet, Object, String)
- Source:
- OleDbDataAdapter.cs
- Source:
- OleDbDataAdapter.cs
public:
int Fill(System::Data::DataSet ^ dataSet, System::Object ^ ADODBRecordSet, System::String ^ srcTable);public int Fill (System.Data.DataSet dataSet, object ADODBRecordSet, string srcTable);override this.Fill : System.Data.DataSet * obj * string -> intPublic Function Fill (dataSet As DataSet, ADODBRecordSet As Object, srcTable As String) As IntegerParameters
- ADODBRecordSet
- Object
An ADO Recordset or Record object.
- srcTable
- String
The source table used for the table mappings.
Returns
The number of rows successfully added to or refreshed in the DataSet. This does not include rows affected by statements that do not return rows.
Exceptions
The source table is invalid.
Remarks
The link between ActiveX Data Objects (ADO) and ADO.NET is a one-way operation in that you can copy data from ADO to the DataSet, but any updates to the data must be handled by ADO.NET.
The Fill method iterates through multiple results by calling the NextRecordset method on the Recordset, closing the input Recordset on completion of the Fill operation.
The Fill operation adds the rows to the specified destination DataTable object in the DataSet, creating the DataTable object if it does not already exist. When you create a DataTable object, the Fill operation ordinarily creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created.
If primary key information is present, any duplicate rows are reconciled and only appear one time in the DataTable that corresponds to the DataSet. Primary key information may be set either through FillSchema, by specifying the PrimaryKey property of the DataTable, or by setting the MissingSchemaAction property to AddWithKey.
To function correctly with the .NET Framework Data Provider for OLE DB, AddWithKey requires the native OLE DB provider to obtain required primary key information by setting the DBPROP_UNIQUEROWS property, and then determine which columns are primary key columns by examining DBCOLUMN_KEYCOLUMN in the IColumnsRowset. Alternatively the user may explicitly set the primary key constraints on each DataTable. This ensures that incoming records that match existing records are updated instead of appended.
If the SelectCommand returns the results of an OUTER JOIN, the DataAdapter does not set a PrimaryKey value for the resulting DataTable. You must explicitly define the primary key to make sure that duplicate rows are resolved correctly. For more information, see Defining Primary Keys.
If the Recordset is closed before the starting of the Fill operation, no error results. This is required for handling of multiple results, because queries that do not return rows are indicated by a closed Recordset. The OleDbDataAdapter just calls NextRecordset on the closed Recordset and continues processing.
If an error is encountered while populating the data set, rows added before the occurrence of the error remain in the DataSet. The rest of the operation is aborted.
If the DbDataAdapter object encounters duplicate columns while populating a DataTable, it generates names for the subsequent columns, using the pattern "columnname1", "columnname2", "columnname3", and so on. If the incoming data contains unnamed columns, they are placed in the DataSet according to the pattern "Column1", "Column2", and so on. When multiple result sets are added to the DataSet each result set is placed in a separate table. Additional result sets are named by appending integral values to the specified table name (for example, "Table", "Table1", "Table2", and so on.). Applications that use column and table names should make sure that conflicts with these naming patterns does not occur.
Values in ADO Recordset or Record objects are converted to common language runtime types for storage in the DataSet.
Note
This overload of the Fill method implicitly calls Close on the ADO object when the fill operation is complete.
The following example uses an OleDbDataAdapter to fill a DataSet using an ADO Recordset that is an ADO Record object. This example assumes that you have created an ADO RecordSet and Record object.
Dim custDA As OleDbDataAdapter = New OleDbDataAdapter()
Dim custDS As DataSet = New DataSet
'Use ADO objects from ADO library (msado15.dll) imported
' as.NET library ADODB.dll using TlbImp.exe
Dim adoConn As ADODB.Connection = New ADODB.Connection()
Dim adoRS As ADODB.Recordset = New ADODB.Recordset()
adoConn.Open("Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;", "", "", -1)
adoRS.Open("SELECT * FROM Customers", adoConn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1)
custDA.Fill(custDS, adoRS, "Customers")
adoConn.Close()
OleDbDataAdapter custDA = new OleDbDataAdapter();
DataSet custDS = new DataSet();
//Use ADO objects from ADO library (msado15.dll) imported
// as.NET library ADODB.dll using TlbImp.exe
ADODB.Connection adoConn = new ADODB.Connection();
ADODB.Recordset adoRS = new ADODB.Recordset();
adoConn.Open("Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;", "", "", -1);
adoRS.Open("SELECT * FROM Customers", adoConn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1);
custDA.Fill(custDS, adoRS, "Customers");
adoConn.Close();
