Comparison of dataAdapter .Fill and .Update

Question

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 --

Solution 1

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).

Solution 2

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

Comments

[Andrew Taswin]

if I use my code for deleting, it is not updated using dataAdapter.update() and I have to recall the select command to review the database. Is that a right way to do it?

[Andrew Taswin]

so basically I still have to use .fill() while deleting?

[spajce]

no. just use delete. my answer is just an example, obviously we should use the Fill before we can Update.

[Andrew Taswin]

Thanks for your explanation! So if you dont mind, I will reexplain my understanding. So if I send my delete query to the database, it will remodify the database directly so I dont have to update it, but if I change the datatable in the program, I have to update the database after changing its value, am I right?

[Parimal Raj]

it will modify the Database directly, but since u have already loaded rows in DataTable, items wont be deleted from DataTable but directly from Database, hence adapter as assigned update, delete & insert command via property. so whenever the update command is called all of them are called

[Parimal Raj]

from MSDN : Update(DataTable) -> Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataTable.

[Parimal Raj]

one bug fixed in my code i have changed a line under btnDelete_Click from table.Rows.Remove(row[i]); to row[i].Delete();

[Andrew Taswin]

adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); what is this 3 used for?

[Parimal Raj]

instead to creating command manually(which can be painful at a time), OleDbCommandBuilder is used : The OleDbDataAdapter does not automatically generate the SQL statements required to reconcile changes made to a DataSet/DataTable with the associated data source. However, you can create an OleDbCommandBuilder object to automatically generate SQL statements for single-table updates if you set the SelectCommand property of the OleDbDataAdapter. Then, any additional SQL statements that you do not set are generated by the OleDbCommandBuilder.

[Andrew Taswin]

adapter.DeleteCommand = builder.GetDeleteCommand(); i got an exception here - Dynamic SQL generation is not supported against multiple base tables.

[Andrew Taswin]

Thanks much i get it now! :D