TAdoDbxDataAdapter Class

Acts as a bridge between a DataSet and the underlying database.

Pascal

TAdoDbxDataAdapter = class(DbDataAdapter);

C++

class TAdoDbxDataAdapter : public DbDataAdapter;

File

Borland.Data.AdoDbxClientProvider

Description

The TAdoDbxDataAdapter object is a bridge between the DataSet and the underlying database. It represents a set of data commands and a database connection that are used to fill the DataSet and update the data source. The TAdoDbxDataAdapter object exposes methods to issue SQL commands against the underlying database, as well as others to manage the DataSet object.

The TAdoDbxDataAdapter class is an implementation of the ADO.NET DbDataAdapter class.

You must set the SelectCommand property with a valid SQL statement before you call the Fill method. This establishes the structure of the data to be returned from the database to the DataSet. If you choose to update your database with changes in the DataSet, you need to set the appropriate properties, either InsertCommand, UpdateCommand, or DeleteCommand, before you issue the update. For example, if you delete rows from the DataSet and you want this change to be reflected in the database, you need to first set the DeleteCommand property before starting your update of the database. Otherwise, if you do not set the command, there is no valid SQL command to issue against the database for the update and the update fails. In that case, the database throws an exception, which you can trap.

When the Active property on the TAdoDbxDataAdapter is set to True, the DataSet is automatically filled with data. You can see this behavior during design-time if you set the Active property to True, then connect a DataGrid component to the DataSet.

The TAdoDbxConnection associated with the SelectCommand is used to execute the command specified in the SelectCommand.CommandText. If the TAdoDbxConnection is already opened, it is used; otherwise, Fill takes care of opening the connection, executing the SQL statement, retrieving the result set, and closing the connection. The TAdoDbxDataAdapter provides InsertCommand, UpdateCommand and DeleteCommand properties for resolving DataSet changes back into the data source. You must specify a valid parameterized SQL statement for each of these commands before calling the Update method. Based on the DataSet changes, the Update method executes INSERT, UPDATE, and DELETE commands and persists all the changes to the data source.

For resolving data from a stored procedure or a complex SQL such as joins, the TAdoDbxDataAdapter class's DeleteCommand, UpdateCommand and InsertCommand should be explicitly specified and you should use the Update method. Like the GetSchemaTable method in TAdoDbxDataReader, TAdoDbxDataAdapter has a FillSchema method that creates a DataTable and configures the metadata to match the database. Consequently, to enforce integrity constraints in the DataSet, such as primary key and unique key, you must call FillSchema before calling Fill. The TableMappings property allows column names to be mapped from the data source to more meaningful names.

The following C# code shows how to provide and resolve data with a TAdoDbxDataAdapter.

Copy Code

static void FillDataAdapter ( TAdoDbxConnection Conn )
 {
      int Rows = 0;

TAdoDbxTransaction Trans = (TAdoDbxTransaction) Conn.BeginTransaction();
    
    TAdoDbxDataAdapter adapter = new TAdoDbxDataAdapter();
TAdoDbxCommand Comm = new TAdoDbxCommand("SELECT * FROM TESTTABLE", Conn);
    adapter.SelectCommand = Comm; 

    DataTableMapping dataTabMap = adapter.TableMappings.Add("Table1","TESTTABLE");
    DataSet ds = new DataSet(); 
    adapter.FillSchema(ds, SchemaType.Source, "TESTTABLE");
    Rows = adapter.Fill(ds, "TESTTABLE");

    InsertRecord(Conn, adapter, ds.Tables["TESTTABLE"]);
    adapter.Update(ds,"TESTTABLE");
    ds.AcceptChanges();

    Trans.Commit();
    
   }

static void InsertRecord(TAdoDbxConnection Conn, TAdoDbxDataAdapter adapter, DataTable dataTable )
  {
    TAdoDbxCommand CommIns = new TAdoDbxCommand("INSERT INTO TESTTABLE VALUES(?)", Conn);
    TAdoDbxParameter param1 = CommIns.Parameters.Add("FCHAR",DbType.StringFixedLength, 10);
    param1.SourceColumn = "FCHAR";

    adapter.InsertCommand = CommIns; 

    //Insert 10 records
    for ( int i=0; i < 10; i++)
    {
        DataRow newRow = dataTable.NewRow();
        newRow["FCHAR"] = "VINA" + i;
        dataTable.Rows.Add(newRow);
    }

  }
static void FillDataAdapter ( TAdoDbxConnection Conn )
 {
      int Rows = 0;

TAdoDbxTransaction Trans = (TAdoDbxTransaction) Conn.BeginTransaction();
    
    TAdoDbxDataAdapter adapter = new TAdoDbxDataAdapter();
TAdoDbxCommand Comm = new TAdoDbxCommand("SELECT * FROM TESTTABLE", Conn);
    adapter.SelectCommand = Comm; 

    DataTableMapping dataTabMap = adapter.TableMappings.Add("Table1","TESTTABLE");
    DataSet ds = new DataSet(); 
    adapter.FillSchema(ds, SchemaType.Source, "TESTTABLE");
    Rows = adapter.Fill(ds, "TESTTABLE");

    InsertRecord(Conn, adapter, ds.Tables["TESTTABLE"]);
    adapter.Update(ds,"TESTTABLE");
    ds.AcceptChanges();

    Trans.Commit();
    
   }

static void InsertRecord(TAdoDbxConnection Conn, TAdoDbxDataAdapter adapter, DataTable dataTable )
  {
TAdoDbxCommand CommIns = new TAdoDbxCommand("INSERT INTO TESTTABLE VALUES(?)", Conn);
TAdoDbxParameter param1 = CommIns.Parameters.Add("FCHAR",DbType.StringFixedLength, 10);
    param1.SourceColumn = "FCHAR";

    adapter.InsertCommand = CommIns; 

    //Insert 10 records
    for ( int i=0; i < 10; i++)
    {
        DataRow newRow = dataTable.NewRow();
        newRow["FCHAR"] = "VINA" + i;
        dataTable.Rows.Add(newRow);
    }

  }