CODEWITHSUNDEEP
c#databasedatatabletableadapter
Julian
Julian

Reputation: 13

c# Delete row from DataTable and use TableAdapter.Update and .Fill

Why is the DataTable not empty even after it has been cleared?

In the beginning I cleared the the "PasswoerterDataTable". But afte updating and filling it back it is not empty anymore. Did I misunderstood the usage of the .fill and .update commands? How do I correct my mistake so that the "PasswoerterDataTable" is empty in the end?

PasswoerterDataTable.Rows.Clear();
// DataTable is now empty
PasswoerterTableAdapter.Update(PasswoerterDataTable);
PasswoerterTableAdapter.Fill(PasswoerterDataTable);
// DataTable is not empty anymore. Why?

I know that it does not make sense to use .update and .fill after each other but I am currently working on a PasswordManager and I want to delete Passwords. These deleted Passwords should not appaer when the program is closed and started again but I need to use .fill after Startup to get the Passwords which haven't been deleted. But after I used .fill the Passwords reappear.

Upvotes: 1

Views: 2777

Answers (1)

Steve
Steve

Reputation: 216313

The Fill method exists to read data from a database table and put it inside an in memory DataTable object. I think you are misunderstanding the work of the Clear method and the Update method.

The Clear method removes all rows from the in memory datatable but doesn't change anything on the database table. The Update method looks at the rows present in your PasswoerterDataTable and checks their RowState property. If it finds something to update then a command is executed to reflect the changes back to the database.
But you have cleared the rows from PasswoerterDataTable so Update has nothing to update and your database table is left unchanged. The following Fill reloads everything.

If you want Update to execute delete commands on the database you need to set the RowState of each row to Deleted. And you can do this with 

foreach(DataRow row in PasswoerterDataTable.Rows)
    row.Delete();  // This doesn't remove the row, but sets its RowState to Deleted

// Now the adapter knows what to do...
PasswoerterTableAdapter.Update();

Of course this is totally unnecessary and you can simply write a single Command to act directly on your database to TRUNCATE your table

SqlCommand cmd = new SqlCommand("TRUNCATE TABLE PasswoerterTable", connection);
cmd.ExecuteNonQuery();
PasswoerterDataTable.Rows.Clear(); 
// No need to call Fill again.

Upvotes: 1

Related Questions