Manage data by using the DataAdapter and TableAdapter

DataAdapter

DbDataAdapter object is used to retrieve and update data between a data table and a data store.

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.

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.

For the Update method to work, all four commands must be assigned to the DbDataAdapter object.

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.

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");

DON’T EXECUTE TWICE! If you execute this code twice, an exception will be thrown indicating that you are trying to insert duplicate rows.

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

A TableAdapter component fills a dataset with data from the database, based on one or more queries or stored procedures that you specify.

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.

TableAdapters are generated by Visual Studio designers. If you are creating datasets programmatically, then use DataAdapter, which is a .NET class.

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.