DbDataAdapter.Fill Method

Definition

Fills a DataSet or a DataTable.

Overloads

Fill(DataSet, Int32, Int32, String, IDbCommand, CommandBehavior)

Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet and source table names, command string, and command behavior.

Fill(DataTable[], Int32, Int32, IDbCommand, CommandBehavior)

Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet and DataTable names.

Fill(DataSet, String, IDataReader, Int32, Int32)

Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet, DataTable, and IDataReader names.

Fill(DataSet, Int32, Int32, String)

Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet and DataTable names.

Fill(Int32, Int32, DataTable[])

Adds or refreshes rows in one or more DataTable objects to match those in the data source starting at the specified record and retrieving up to the specified maximum number of records.

Fill(DataTable)

Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataTable name.

Fill(DataTable, IDataReader)

Adds or refreshes rows in a DataTable to match those in the data source using the specified DataTable and IDataReader names.

Fill(DataSet, String)

Adds or refreshes rows in the DataSet to match those in the data source using the DataSet and DataTable names.

Fill(DataSet)

Adds or refreshes rows in the DataSet.

Fill(DataTable, IDbCommand, CommandBehavior)

Adds or refreshes rows in a DataTable to match those in the data source using the specified DataTable, IDbCommand and CommandBehavior.

Fill(DataSet, Int32, Int32, String, IDbCommand, CommandBehavior)

Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet and source table names, command string, and command behavior.

protected:
 virtual int Fill(System::Data::DataSet ^ dataSet, int startRecord, int maxRecords, System::String ^ srcTable, System::Data::IDbCommand ^ command, System::Data::CommandBehavior behavior);
protected virtual int Fill (System.Data.DataSet dataSet, int startRecord, int maxRecords, string srcTable, System.Data.IDbCommand command, System.Data.CommandBehavior behavior);
override this.Fill : System.Data.DataSet * int * int * string * System.Data.IDbCommand * System.Data.CommandBehavior -> int
Protected Overridable Function Fill (dataSet As DataSet, startRecord As Integer, maxRecords As Integer, srcTable As String, command As IDbCommand, behavior As CommandBehavior) As Integer

Parameters

dataSet
DataSet

A DataSet to fill with records and, if necessary, schema.

startRecord
Int32

The zero-based record number to start with.

maxRecords
Int32

The maximum number of records to retrieve. Specify 0 to retrieve all records after the start record.

srcTable
String

The name of the source table to use for table mapping.

command
IDbCommand

The SQL SELECT statement used to retrieve rows from the data source.

behavior
CommandBehavior

One of the CommandBehavior values.

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.

The startRecord parameter is less than 0.

-or-

The maxRecords parameter is less than 0.

Remarks

A maxRecords value of 0 gets all records found after the start record. If maxRecords is greater than the number of remaining rows, only the remaining rows are returned and no error is issued.

The Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property. The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

The Fill operation then adds the rows to destination DataTable objects in the DataSet, creating the DataTable objects if they do not already exist. When creating DataTable objects, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created.

If the DbDataAdapter 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). If your app uses column and table names, ensure there are no conflicts with these naming patterns.

The Fill method supports scenarios where the DataSet contains multiple DataTable objects whose names differ only by case. In such situations, Fill performs a case-sensitive comparison to find the corresponding table, and creates a new table if no exact match exists. The following C# code illustrates this behavior.

DataSet dataset = new DataSet();  
dataset.Tables.Add("aaa");  
dataset.Tables.Add("AAA");  
adapter.Fill(dataset, "aaa"); // Fills "aaa", which already exists in the DataSet.  
adapter.Fill(dataset, "Aaa"); // Adds a new table called "Aaa".  

If Fill is called and the DataSet contains only one DataTable whose name differs only by case, that DataTable is updated. In this scenario, the comparison is case insensitive. The following C# code illustrates this behavior.

DataSet dataset = new DataSet();  
dataset.Tables.Add("aaa");  
adapter.Fill(dataset, "AAA"); // Fills table "aaa" because only one similarly named table is in the DataSet.  

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 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 ensure that duplicate rows are resolved correctly. For more information, see Defining Primary Keys.

Note

When handling batch SQL statements that return multiple results, the implementation of Fill and FillSchema for a .NET Framework data provider retrieves schema information for only the first result.

Notes to Inheritors

This overload of the Fill(DataSet) method is protected and is designed for use by a .NET Framework data provider.

See also

Applies to

Fill(DataTable[], Int32, Int32, IDbCommand, CommandBehavior)

Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet and DataTable names.

protected:
 virtual int Fill(cli::array <System::Data::DataTable ^> ^ dataTables, int startRecord, int maxRecords, System::Data::IDbCommand ^ command, System::Data::CommandBehavior behavior);
protected virtual int Fill (System.Data.DataTable[] dataTables, int startRecord, int maxRecords, System.Data.IDbCommand command, System.Data.CommandBehavior behavior);
override this.Fill : System.Data.DataTable[] * int * int * System.Data.IDbCommand * System.Data.CommandBehavior -> int
Protected Overridable Function Fill (dataTables As DataTable(), startRecord As Integer, maxRecords As Integer, command As IDbCommand, behavior As CommandBehavior) As Integer

Parameters

dataTables
DataTable[]

The DataTable objects to fill from the data source.

startRecord
Int32

The zero-based record number to start with.

maxRecords
Int32

The maximum number of records to retrieve. Specify 0 to retrieve all records after the start record.

command
IDbCommand

The IDbCommand executed to fill the DataTable objects.

behavior
CommandBehavior

One of the CommandBehavior values.

Returns

The number of rows added to or refreshed in the data tables.

Exceptions

The DataSet is invalid.

The source table is invalid.

-or-

The connection is invalid.

The connection could not be found.

The startRecord parameter is less than 0.

-or-

The maxRecords parameter is less than 0.

Remarks

A maxRecords value of 0 gets all records found after the start record. If maxRecords is greater than the number of remaining rows, only the remaining rows are returned and no error is issued.

The Fill method retrieves the data from the data source using a SELECT statement. The IDbConnection object associated with the SELECT statement must be valid, but it does not need to be open. If the IDbConnection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

If a command does not return any rows, no tables are added to the DataSet, but no exception is raised.

If the DbDataAdapter object encounters duplicate columns while populating a DataTable, it will generate 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 the query specified returns multiple results, 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). Since no table is created for a query that does not return rows, if you were to process an insert query followed by a select query, the table created for the select query would be named "Table", because it is the first table created. If your app uses column and table names, ensure there are no conflicts with these naming patterns.

The Fill method supports scenarios where the DataSet contains multiple DataTable objects whose names differ only by case. In such situations, Fill performs a case-sensitive comparison to find the corresponding table, and creates a new table if no exact match exists. The following C# code illustrates this behavior.

DataSet dataset = new DataSet();  
dataset.Tables.Add("aaa");  
dataset.Tables.Add("AAA");  
adapter.Fill(dataset, "aaa"); // Fills "aaa", which already exists in the DataSet.  
adapter.Fill(dataset, "Aaa"); // Adds a new table called "Aaa".  

If Fill is called and the DataSet contains only one DataTable whose name differs only by case, that DataTable is updated. In this scenario, the comparison is case insensitive. The following C# code illustrates this behavior.

DataSet dataset = new DataSet();  
dataset.Tables.Add("aaa");  
adapter.Fill(dataset, "AAA"); // Fills table "aaa" because only one similarly named table is in the DataSet.  

If an error or an exception is encountered while populating the data tables, rows added prior to the occurrence of the error remain in the data tables. The remainder of the operation is aborted.

When the SELECT statement used to populate the DataTable objects returns multiple results, such as a batch SQL statement, be aware of the following:

  • When processing multiple results from a batch SQL statement, maxRecords only applies to the first result. The same is true for rows containing chaptered results (.NET Framework Data Provider for OLE DB only). The top-level result is limited by maxRecords, but all child rows are added.

  • If one of the results contains an error, all subsequent results are skipped.

Note

The DataSet will not contain more than the number of records indicated by maxRecords. However, the entire resultset generated by the query is still returned from the server.

Notes to Inheritors

When overriding Fill(DataSet) in a derived class, be sure to call the base class's Fill(DataSet) method.

See also

Applies to

Fill(DataSet, String, IDataReader, Int32, Int32)

Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet, DataTable, and IDataReader names.

protected:
 virtual int Fill(System::Data::DataSet ^ dataSet, System::String ^ srcTable, System::Data::IDataReader ^ dataReader, int startRecord, int maxRecords);
protected virtual int Fill (System.Data.DataSet dataSet, string srcTable, System.Data.IDataReader dataReader, int startRecord, int maxRecords);
override this.Fill : System.Data.DataSet * string * System.Data.IDataReader * int * int -> int
Protected Overridable Function Fill (dataSet As DataSet, srcTable As String, dataReader As IDataReader, startRecord As Integer, maxRecords As Integer) As Integer

Parameters

dataSet
DataSet

A DataSet to fill with records and, if necessary, schema.

srcTable
String

The name of the DataTable to use for table mapping.

dataReader
IDataReader

The name of the IDataReader.

startRecord
Int32

The zero-based record number to start with.

maxRecords
Int32

The maximum number of records to retrieve. Specify 0 to retrieve all records after the start record.

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

startRecord is less than 0.

-or-

maxRecords is less than 0.

Remarks

A maxRecords value of 0 gets all records found after the start record. If maxRecords is greater than the number of remaining rows, only the remaining rows are returned and no error is issued.

Applies to

Fill(DataSet, Int32, Int32, String)

Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet and DataTable names.

public:
 int Fill(System::Data::DataSet ^ dataSet, int startRecord, int maxRecords, System::String ^ srcTable);
public int Fill (System.Data.DataSet dataSet, int startRecord, int maxRecords, string srcTable);
override this.Fill : System.Data.DataSet * int * int * string -> int
Public Function Fill (dataSet As DataSet, startRecord As Integer, maxRecords As Integer, srcTable As String) As Integer

Parameters

dataSet
DataSet

A DataSet to fill with records and, if necessary, schema.

startRecord
Int32

The zero-based record number to start with.

maxRecords
Int32

The maximum number of records to retrieve. Specify 0 to retrieve all records after the start record.

srcTable
String

The name of the source table to use for table mapping.

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 DataSet is invalid.

The source table is invalid.

-or-

The connection is invalid.

The connection could not be found.

The startRecord parameter is less than 0.

-or-

The maxRecords parameter is less than 0.

Examples

The following example uses the derived class, OleDbDataAdapter, to fill a DataSet with 15 rows, beginning at row 10, from the Categories table. This example assumes that you have created an OleDbDataAdapter and a DataSet.

public void GetRecords()
{
    // ...
    // create dataSet and adapter
    // ...
    adapter.Fill(dataSet,9,15,"Categories");
}
Public Sub GetRecords()
    ' ...
    ' create dataSet and adapter
    ' ...
    adapter.Fill(dataSet, 9, 15, "Categories")
End Sub

Remarks

A maxRecords value of 0 gets all records found after the start record. If maxRecords is greater than the number of remaining rows, only the remaining rows are returned, and no error is issued.

If the corresponding select command is a statement returning multiple results, Fill only applies maxRecords to the first result.

The Fill method retrieves the data from the data source using a SELECT statement. The IDbConnection object associated with the SELECT statement must be valid, but it does not need to be open. If the IDbConnection is closed before Fill is called, it is opened to retrieve data and then closed. If the connection is open before Fill is called, it remains open.

If a command does not return any rows, no tables are added to the DataSet, but no exception is raised.

If the DbDataAdapter object encounters duplicate columns while populating a DataTable, it will generate 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 the query specified returns multiple results, 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). Because no table is created for a query that does not return rows, if you process an insert query followed by a select query, the table created for the select query is named "Table", because it is the first table created. If your app uses column and table names, ensure there are no conflicts with these naming patterns.

The Fill method supports scenarios where the DataSet contains multiple DataTable objects whose names differ only by case. In such situations, Fill performs a case-sensitive comparison to find the corresponding table, and creates a new table if no exact match exists. The following C# code illustrates this behavior.

DataSet dataset = new DataSet();  
dataset.Tables.Add("aaa");  
dataset.Tables.Add("AAA");  
adapter.Fill(dataset, "aaa"); // Fills "aaa", which already exists in the DataSet.  
adapter.Fill(dataset, "Aaa"); // Adds a new table called "Aaa".  

If Fill is called and the DataSet contains only one DataTable whose name differs only by case, that DataTable is updated. In this scenario, the comparison is case insensitive. The following C# code illustrates this behavior.

DataSet dataset = new DataSet();  
dataset.Tables.Add("aaa");  
adapter.Fill(dataset, "AAA"); // Fills table "aaa" because only one similarly named table is in the DataSet.  

If an error or an exception is encountered while populating the data tables, rows added prior to the occurrence of the error remain in the data tables. The remainder of the operation is aborted.

When the SELECT statement used to populate the DataSet returns multiple results, such as batch SQL statements, be aware of the following:

  • When processing multiple results from a batch SQL statement, maxRecords only applies to the first result. The same is true for rows containing chaptered results (.NET Framework Data Provider for OLE DB only). The top level result is limited by maxRecords, but all child rows are added.

  • If one of the results contains an error, all subsequent results are skipped and not added to the DataSet.

When using subsequent Fill calls to refresh the contents of the DataSet, two conditions must be met:

  1. The SQL statement should match the one initially used to populate the DataSet.

  2. The Key column information must be present.

If primary key information is present, any duplicate rows will be reconciled and only appear once 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 ensure that duplicate rows are resolved correctly. For more information, see Defining Primary Keys.

Note

When handling batch SQL statements that return multiple results, the implementation of FillSchema for the .NET Framework Data Provider for OLE DB retrieves schema information for only the first result. To retrieve schema information for multiple results, use Fill with the MissingSchemaAction set to AddWithKey.

Note

The DataSet will not contain more than the number of records indicated by maxRecords. However, the entire result set generated by the query is still returned from the server.

Notes to Inheritors

When overriding Fill(DataSet) in a derived class, be sure to call the base class's Fill(DataSet) method.

See also

Applies to

Fill(Int32, Int32, DataTable[])

Adds or refreshes rows in one or more DataTable objects to match those in the data source starting at the specified record and retrieving up to the specified maximum number of records.

public:
 int Fill(int startRecord, int maxRecords, ... cli::array <System::Data::DataTable ^> ^ dataTables);
public int Fill (int startRecord, int maxRecords, params System.Data.DataTable[] dataTables);
override this.Fill : int * int * System.Data.DataTable[] -> int
Public Function Fill (startRecord As Integer, maxRecords As Integer, ParamArray dataTables As DataTable()) As Integer

Parameters

startRecord
Int32

The zero-based record number to start with.

maxRecords
Int32

The maximum number of records to retrieve. Specify 0 to retrieve all records after the start record.

dataTables
DataTable[]

The DataTable objects to fill from the data source.

Returns

The number of rows successfully added to or refreshed in the DataTable objects. This value does not include rows affected by statements that do not return rows.

Exceptions

dataTables is null or an empty array.

startRecord is less than 0.

-or-

maxRecords is less than 0.

Remarks

A maxRecords value of 0 gets all records found after the start record. If maxRecords is greater than the number of remaining rows, only the remaining rows are returned, and no error is issued.

The Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property. The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, and then it is closed. If the connection is open before Fill is called, it remains open.

The Fill operation then adds the rows to destination DataTable objects in the DataSet, creating the DataTable objects if they do not already exist. When creating DataTable objects, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created.

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 ensure that duplicate rows are resolved correctly. For more information, see Defining Primary Keys.

If the data adapter 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). If your app uses column and table names, ensure there are no conflicts with these naming patterns.

When the SELECT statement used to populate the DataSet returns multiple results, such as a batch SQL statements, if one of the results contains an error, all subsequent results are skipped and not added to the DataSet.

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.

Note

When handling batch SQL statements that return multiple results, the implementation of FillSchema for the .NET Framework Data Provider for OLE DB retrieves schema information for only the first result. To retrieve schema information for multiple results, use Fill with the MissingSchemaAction set to AddWithKey.

See also

Applies to

Fill(DataTable)

Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataTable name.

public:
 int Fill(System::Data::DataTable ^ dataTable);
public int Fill (System.Data.DataTable dataTable);
override this.Fill : System.Data.DataTable -> int
Public Function Fill (dataTable As DataTable) As Integer

Parameters

dataTable
DataTable

The name of the DataTable to use for table mapping.

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 Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property. The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

The Fill operation then adds the rows to destination DataTable objects in the DataSet, creating the DataTable objects if they do not already exist. When creating DataTable objects, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created.

If the DbDataAdapter 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.

The overload of Fill that takes DataTable as a parameter only obtains the first result. Use an overload of Fill that takes DataSet as a parameter to obtain multiple results.

The Fill method supports scenarios where the DataSet contains multiple DataTable objects whose names differ only by case. In such situations, Fill performs a case-sensitive comparison to find the corresponding table, and creates a new table if no exact match exists. The following C# code illustrates this behavior.

DataSet dataset = new DataSet();  
 dataset.Tables.Add("aaa");  
 dataset.Tables.Add("AAA");  
 adapter.Fill(dataset, "aaa"); // Fills "aaa", which already exists in the DataSet.  
    adapter.Fill(dataset, "Aaa"); // Adds a new table called "Aaa".  

If Fill is called and the DataSet contains only one DataTable whose name differs only by case, that DataTable is updated. In this scenario, the comparison is case insensitive. The following C# code illustrates this behavior.

DataSet dataset = new DataSet();  
 dataset.Tables.Add("aaa");  
    adapter.Fill(dataset, "AAA"); // Fills table "aaa" because only one similarly named table is in the DataSet.  

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 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 ensure that duplicate rows are resolved correctly. For more information, see Defining Primary Keys.

Note

When handling batch SQL statements that return multiple results, the implementation of Fill and FillSchema for a .NET Framework data provider retrieves schema information for only the first result.

Notes to Inheritors

This overload of the Fill(DataSet) method is protected and is designed for use by a .NET Framework data provider.

See also

Applies to

Fill(DataTable, IDataReader)

Adds or refreshes rows in a DataTable to match those in the data source using the specified DataTable and IDataReader names.

protected:
 virtual int Fill(System::Data::DataTable ^ dataTable, System::Data::IDataReader ^ dataReader);
protected virtual int Fill (System.Data.DataTable dataTable, System.Data.IDataReader dataReader);
override this.Fill : System.Data.DataTable * System.Data.IDataReader -> int
Protected Overridable Function Fill (dataTable As DataTable, dataReader As IDataReader) As Integer

Parameters

dataTable
DataTable

A DataTable to fill with records and, if necessary, schema.

dataReader
IDataReader

The name of the IDataReader.

Returns

The number of rows successfully added to or refreshed in the DataTable. This does not include rows affected by statements that do not return rows.

Applies to

Fill(DataSet, String)

Adds or refreshes rows in the DataSet to match those in the data source using the DataSet and DataTable names.

public:
 int Fill(System::Data::DataSet ^ dataSet, System::String ^ srcTable);
public int Fill (System.Data.DataSet dataSet, string srcTable);
override this.Fill : System.Data.DataSet * string -> int
Public Function Fill (dataSet As DataSet, srcTable As String) As Integer

Parameters

dataSet
DataSet

A DataSet to fill with records and, if necessary, schema.

srcTable
String

The name of the source table to use for table mapping.

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.

Examples

The following example uses the derived class, OleDbDataAdapter, to fill a DataSet with rows from the categories table. This example assumes that you have created an OleDbDataAdapter and a DataSet.

public void GetRecords()
{
    // ...
    // create dataSet and adapter
    // ...
    adapter.Fill(dataSet, "Categories");
}
Public Sub GetRecords()
    ' ...
    ' create dataSet and adapter
    ' ...
    adapter.Fill(dataSet, "Categories")
End Sub

Remarks

The Fill method retrieves the data from the data source using a SELECT statement. The IDbConnection object associated with the select command must be valid, but it does not need to be open. If the IDbConnection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

If a command does not return any rows, no tables are added to the DataSet, and no exception is raised.

If the DbDataAdapter object encounters duplicate columns while populating a DataTable, it will generate 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 the query specified returns multiple results, 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). Since no table is created for a query that does not return rows, if you were to process an insert query followed by a select query, the table created for the select query would be named "Table", because it is the first table created. If your app uses column and table names, ensure there are no conflicts with these naming patterns.

The Fill method supports scenarios where the DataSet contains multiple DataTable objects whose names differ only by case. In such situations, Fill performs a case-sensitive comparison to find the corresponding table, and creates a new table if no exact match exists. The following C# code illustrates this behavior.

DataSet dataset = new DataSet();  
dataset.Tables.Add("aaa");  
dataset.Tables.Add("AAA");  
adapter.Fill(dataset, "aaa"); // Fills "aaa", which already exists in the DataSet.  
adapter.Fill(dataset, "Aaa"); // Adds a new table called "Aaa".  

If Fill is called and the DataSet contains only one DataTable whose name differs only by case, that DataTable is updated. In this scenario, the comparison is case insensitive. The following C# code illustrates this behavior.

DataSet dataset = new DataSet();  
dataset.Tables.Add("aaa");  
adapter.Fill(dataset, "AAA"); // Fills table "aaa" because only one similarly named table is in the DataSet.  

If an error or an exception is encountered while populating the data tables, rows added prior to the occurrence of the error remain in the data tables. The remainder of the operation is aborted.

When the SELECT statement used to populate the DataSet returns multiple results, such as a batch SQL statement, be aware of the following:

  • If one of the results contains an error, all subsequent results are skipped and not added to the DataSet.

When using subsequent Fill calls to refresh the contents of the DataSet, two conditions must be met:

  1. The SQL statement should match the one initially used to populate the DataSet.

  2. The Key column information must be present. If primary key information is present, any duplicate rows are reconciled and only appear once 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 ensure that duplicate rows are resolved correctly. For more information, see Defining Primary Keys.

Note

When handling batch SQL statements that return multiple results, the implementation of FillSchema for the .NET Framework Data Provider for OLE DB retrieves schema information for only the first result. To retrieve schema information for multiple results, use Fill with the MissingSchemaAction set to AddWithKey.

Notes to Inheritors

When overriding Fill(DataSet) in a derived class, be sure to call the base class's Fill(DataSet) method.

See also

Applies to

Fill(DataSet)

Adds or refreshes rows in the DataSet.

public:
 override int Fill(System::Data::DataSet ^ dataSet);
public override int Fill (System.Data.DataSet dataSet);
override this.Fill : System.Data.DataSet -> int
Public Overrides Function Fill (dataSet As DataSet) As Integer

Parameters

dataSet
DataSet

A DataSet to fill with records and, if necessary, schema.

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.

Implements

Remarks

The Fill method retrieves the data from the data source using a SELECT statement. The IDbConnection object associated with the select command must be valid, but it does not need to be open. If the IDbConnection is closed before Fill is called, it is opened to retrieve data and then closed. If the connection is open before Fill is called, it remains open.

If an error or an exception is encountered while populating the data tables, rows added prior to the occurrence of the error remain in the data tables. The remainder of the operation is aborted.

If a command does not return any rows, no tables are added to the DataSet, and no exception is raised.

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 the query specified returns multiple results, the result set for each row returning query 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). Because no table is created for a query that does not return rows, if you process an insert query followed by a select query, the table created for the select query is named "Table" because it is the first table created. If your app uses column and table names, ensure there are no conflicts with these naming patterns.

When the SELECT statement used to populate the DataSet returns multiple results, such as batch SQL statements, if one of the results contains an error, all subsequent results are skipped and are not added to the DataSet.

When using subsequent Fill calls to refresh the contents of the DataSet, two conditions must be met:

  1. The SQL statement should match the one initially used to populate the DataSet.

  2. The Key column information must be present.

If primary key information is present, any duplicate rows are reconciled and only appear once 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 ensure that duplicate rows are resolved correctly. For more information, see Defining Primary Keys.

Note

When handling batch SQL statements that return multiple results, the implementation of FillSchema for the .NET Framework Data Provider for OLE DB retrieves schema information for only the first result. To retrieve schema information for multiple results, use Fill with the MissingSchemaAction set to AddWithKey.

See also

Applies to

Fill(DataTable, IDbCommand, CommandBehavior)

Adds or refreshes rows in a DataTable to match those in the data source using the specified DataTable, IDbCommand and CommandBehavior.

protected:
 virtual int Fill(System::Data::DataTable ^ dataTable, System::Data::IDbCommand ^ command, System::Data::CommandBehavior behavior);
protected virtual int Fill (System.Data.DataTable dataTable, System.Data.IDbCommand command, System.Data.CommandBehavior behavior);
override this.Fill : System.Data.DataTable * System.Data.IDbCommand * System.Data.CommandBehavior -> int
Protected Overridable Function Fill (dataTable As DataTable, command As IDbCommand, behavior As CommandBehavior) As Integer

Parameters

dataTable
DataTable

A DataTable to fill with records and, if necessary, schema.

command
IDbCommand

The SQL SELECT statement used to retrieve rows from the data source.

behavior
CommandBehavior

One of the CommandBehavior values.

Returns

The number of rows successfully added to or refreshed in the DataTable. This does not include rows affected by statements that do not return rows.

Remarks

The Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property. The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data and then closed. If the connection is open before Fill is called, it remains open.

The Fill operation then adds the rows to the specified destination DataTable object in the DataSet, creating the DataTable object if it does not already exist. When creating a DataTable object, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created.

If the DbDataAdapter object encounters duplicate columns while populating a DataTable, it will generate 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.

The overload of Fill that takes DataTable as a parameter only obtains the first result. Use an overload of Fill that takes DataSet as a parameter to obtain multiple results.

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 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 ensure that duplicate rows are resolved correctly. For more information, see Defining Primary Keys.

Note

When handling batch SQL statements that return multiple results, the implementation of FillSchema for the .NET Framework Data Provider for OLE DB retrieves schema information for only the first result. To retrieve schema information for multiple results, use Fill with the MissingSchemaAction set to AddWithKey.

Notes to Inheritors

This overload of the Fill(DataSet) method is protected and is designed for use by a .NET Framework data provider.

See also

Applies to