I've been reading through the MSDN resources and several forums and still don't understand what's the difference between those two dataAdapter.Fill() and dataAdapter.Update(), I tried to use both of them to update the database from my program and it works, but when I try to remove the update() function, it is still working perfectly, therefore I think of it as useless.
Can anyone please clarify this?
Edit:
this is my code to delete:
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Public\\Documents\\inventorySystem\\branches\\Database\\inventorySystemDatabase.accdb";
string query = "DELETE FROM Product WHERE product_id=" + productDataGridView[1, e.RowIndex].Value.ToString();
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
OleDbCommandBuilder deleteBuilder = new OleDbCommandBuilder(dAdapter);
DataTable deleteTable = new DataTable();
dAdapter.Update(deleteTable);
-- I have to make an extra select command to update the datagridview --
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).
For short the definition.
DataAdapter.Fill() stands for SELECT query statement to database from the Server.
// 1
// Open connection
using (SqlConnection c = new SqlConnection(
Properties.Settings.Default.DataConnectionString))
{
c.Open();
// 2
// Create new DataAdapter
using (SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM EmployeeIDs", c))
{
// 3
// Use DataAdapter to fill DataTable
DataTable t = new DataTable();
a.Fill(t);
// 4
// Render data onto the screen
// dataGridView1.DataSource = t; // <-- From your designer
}
}
DataAdapter.Update() stands for Update, Insert and Delete query statement to database from the Server.
public DataSet CreateCmdsAndUpdate(DataSet myDataSet,string myConnection,string mySelectQuery,string myTableName)
{
OleDbConnection myConn = new OleDbConnection(myConnection);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
myDataAdapter.SelectCommand = new OleDbCommand(mySelectQuery, myConn);
OleDbCommandBuilder custCB = new OleDbCommandBuilder(myDataAdapter);
myConn.Open();
DataSet custDS = new DataSet();
myDataAdapter.Fill(custDS);
//code to modify data in dataset here
//Without the OleDbCommandBuilder this line would fail
myDataAdapter.Update(custDS);
myConn.Close();
return custDS;
}
Reference:
C# SqlDataAdapter
DataAdapter.Update Method
