Working sample
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
private OleDbConnection con =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\test.mdb\";");
private OleDbDataAdapter adapter;
DataTable table = new DataTable("person");
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
con.Open();
;
adapter = new OleDbDataAdapter("select ID, p_name, p_age from person", con);
adapter.Fill(table);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
dataGridView1.DataSource = table;
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
con.Close();
con.Dispose();
}
private void btnDelete_Click(object sender, EventArgs e)
{
DataRow[] row = table.Select("p_age = 10");
if (row.Length > 0)
{
for (int i = 0; i < row.Length; i++)
{
row[i].Delete();
}
}
adapter.Update(table);
}
}
}
In simple words.
DataAdapter.Fill() is used to load data from database
Example : Showing Data From database to gridview
using (DataTable table = new DataTable()) {
using (OleDbDataAdapter adapter = new OleDbDataAdapter("select name,age from person", conObject)) {
adapter.Fill(table);
BindingSource bs = new BindingSource { DataSource = table };
dgReader.DataSource = bs;
}
}
and once the edits are done, the DataAdapter.Update() commits all the changed data information to the database using the underlying connection.
DataAdapter.Fill()
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.
DataAdapter.Update()
The update is performed on a by-row basis. For every inserted,
modified, and deleted row, the Update method determines the type of
change that has been performed on it (Insert, Update or Delete).
Depending on the type of change, the Insert, Update, or Delete command
template executes to propagate the modified row to the data source.
When an application calls the Update method, the DataAdapter examines
the RowState property, and executes the required INSERT, UPDATE, or
DELETE statements iteratively for each row, based on the order of the
indexes configured in the DataSet. For example, Update might execute a
DELETE statement, followed by an INSERT statement, and then another
DELETE statement, due to the ordering of the rows in the DataTable.
It should be noted that these statements are not performed as a batch
process; each row is updated individually. An application can call the
GetChanges method in situations where you must control the sequence of
statement types (for example, INSERT before UPDATE). For more
information, see Updating Data Sources with DataAdapters (ADO.NET).
