CODEWITHSUNDEEP
c#dataset
gout
gout

Reputation: 812

Deleting values from dataset

I am trying to delete the data present in the dataset with following code:

stateflowDataSet dsobject = new stateflowDataSet();
stateflowDataSetTableAdapters.dbo_statetableTableAdapter statetableadapter = new         stateflowDataSetTableAdapters.dbo_statetableTableAdapter();
statetableadapter.Fill(dsobject.dbo_statetable);
dsobject.dbo_statetable.Clear();
statetableadapter.Update(dsobject);

But after this line when use statetableadapter.Fill(dsobject.dbo_statetable); the data is still retained.

Is the way in which I am clearing the data right?

Is there any other problem with the code?

Upvotes: 0

Views: 1723

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460098

Removing DataRows from a DataTable does not mean that you'll delete them from your DBMS. The opposite is true, actually you're preventing them from being deleted even if you would have called DataRow.Delete() before.

The reason is: only DataRows that belong to a DataTable can be deleted by 

DataAdapter.Update(table)

This will delete every row in the table with DataRowState=Deleted. Therefor you need to use the Delete method.

foreach(DataRow row in dsobject.dbo_statetable.Rows)
{
    row.Delete();
}
statetableadapter.Update(dsobject.dbo_statetable);

You could do it also in one batch which would be more efficient, therefor you need to set the DataAdapter's UpdateBatchSize to 0(unlimited).

Another way would to delete all rows is to use a simple SqlCommand with CommandText DELETE FROM Table:

using(var con = new SqlConnection(ConfigurationSettings.AppSettings["con"]))
using(var cmd = new SqlCommand())
{
    cmd.CommandText = "DELETE FROM Table";
    cmd.Connection = con;
    con.Open();
    int numberDeleted = cmd.ExecuteNonQuery();  // all rows deleted
}

Now you need to remove(not delete, what is the core of your question) the rows from the DataTable manually since you've also deleted them manually in the database:

dsobject.dbo_statetable.Clear();

Upvotes: 3

Daniel Mošmondor
Daniel Mošmondor

Reputation: 19956

Let me try re-wording Tims answer a little. I used DataSets alot when they were popular, and was confused with their 'magic'...

Dataset contains copy of the data from the database + your updates on the data since data is fetched from the database.

Update() method on the DataAdapter with your dataset isn't magic, it goes through your tables in the dataset, and in tables it scan rows. It will:

  • use DeleteCommand if row is marked for deletion
  • use UpdateCommand if row is marked for update

Former is the case when you delete a row with Delete() method, and later is the case when you update some cell in the row.

You will learn much about the process if you observe RowState property of each row in the dataset tables.

Upvotes: 2

Related Questions