> For the complete documentation index, see [llms.txt](https://dotnetweb30-ke.gitbook.io/ke/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dotnetweb30-ke.gitbook.io/ke/construction-db/data-access-layer/manage-data-by-using-the-dataadapter-and-tableadapter.md).

# Manage data by using the DataAdapter and TableAdapter

### DataAdapter

{% hint style="success" %}
**DbDataAdapter** object is used to retrieve and update data between a data table and a data store.
{% endhint %}

If the connection is open, the `DbDataAdapter` uses the open connection and leaves the connection open. If the connection is closed, `DbDataAdapter` opens the connection, uses it, and then closes it automatically.

`DbDataAdapter` has a `SelectCommand`, `InsertCommand`, `UpdateCommand`, and `DeleteCommand` properties.

#### `Fill` method

The `Fill` method moves data from the data store to the `DataTable` object you pass into this method. The `Fill` method has several overloads, some of which accept only a data set as a parameter. When a data set is passed to the `Fill` method, a new `DataTable` object is created in the data set if a source `DataTable` object is not specified.

```csharp
var cmd = (SqlCommand)connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT CustomerID, CompanyName FROM Customers";
var da = new SqlDataAdapter(cmd);
var nwSet = new DataSet("nw");
da.Fill(nwSet, "Customers");
MessageBox.Show("DataSet Filled");
```

#### Saving Changes

The `Update` method saves the data table modifications to the database by retrieving the changes from the data table and then using the respective `InsertCommand`, `UpdateCommand`, or `DeleteCommand` property to send the appropriate changes to the database on a row-by-row basis.

{% hint style="warning" %}
For the `Update` method to work, **all four commands must be assigned** to the `DbDataAdapter` object.
{% endhint %}

Normally, this means creating individual `DbCommand` objects for each command. Another way to populate the `DbDataAdapter` object’s commands is to use the **DbCommandBuilder** object.

```csharp
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM Customers";
var da = new SqlDataAdapter(cmd);
var nwSet = new DataSet("nw");
var bldr = new SqlCommandBuilder(da);
da.Fill(nwSet, "Customers");

//Add new row
customersTable.Rows.Add("AAAAA", "Five A Company");

//send changes to database
da.Update(nwSet, "Customers");
```

{% hint style="danger" %}
**DON’T EXECUTE TWICE!** If you execute this code twice, an exception will be thrown indicating that you are trying to insert duplicate rows.
{% endhint %}

One way to increase update performance is to send the changes to the database server in batches by assigning a value to the `DbDataAdapter` object’s **UpdateBatchSize** property. This property defaults to 1, which causes each change to be sent to the server on a row-by-row basis.

### TableAdapter

{% hint style="success" %}
A **TableAdapter** component fills a dataset with data from the database, based on one or more queries or stored procedures that you specify.
{% endhint %}

TableAdapters can also perform adds, updates, and deletes on the database to persist changes that you make to the dataset. You can also issue global commands that are unrelated to any specific table.

{% hint style="info" %}
TableAdapters are generated by Visual Studio designers. If you are creating datasets programmatically, then use DataAdapter, which is a .NET class.
{% endhint %}

```csharp
NorthwindDataSet northwindDataSet = new NorthwindDataSet();

NorthwindDataSetTableAdapters.CustomersTableAdapter customersTableAdapter = 
    new NorthwindDataSetTableAdapters.CustomersTableAdapter();

customersTableAdapter.Fill(northwindDataSet.Customers);
```

#### Methods

| Member                         | Description                                                                                                          |
| ------------------------------ | -------------------------------------------------------------------------------------------------------------------- |
| `TableAdapter.Fill`            | Populates the TableAdapter's associated data table with the results of the TableAdapter's `SELECT` command.          |
| `TableAdapter.Update`          | Sends changes back to the database and returns an integer that represents the number of rows affected by the update. |
| `TableAdapter.GetData`         | Returns a new DataTable that's filled with data.                                                                     |
| `TableAdapter.Insert`          | Creates a new row in the data table.                                                                                 |
| `TableAdapter.ClearBeforeFill` | Determines whether a data table is emptied before you call one of the `Fill` methods.                                |