CODEWITHSUNDEEP
c#sql-serverado.net
idrissAbbou
idrissAbbou

Reputation: 105

Deleting a row from DataSet doesn't affect the database

I'm new at ADO.NET and I'm trying to understand the disconnected model, I wrote a code to do inset update and delete using SqlDataAdapter and SqlCommandBuilder and it worked with insert and update but not with delete, why is this?, and how to fix it?.

Here is my code inside the event handler, I have one table "client" that have 3 columns id, name, city, and my DataSet Called ds.

SqlDataAdapter ad = new SqlDataAdapter("select * from client", cnx);       
foreach (DataRow client in ds.Tables["client"].Rows)
{
    if (client.Field<int>(0) == 100)
    {
        ds.Tables["client"].Rows.Remove(client);
        break;
    }
}
new SqlCommandBuilder(ad);
ad.Update(ds, "client");

Upvotes: 1

Views: 933

Answers (1)

Steve
Steve

Reputation: 216313

If you remove the row, there is no way for the Update method to discover which row has been removed. You need to use the Delete method

foreach (DataRow client in ds.Tables["client"].Rows)
{
  if (client.Field<int>(0) == 100)
  {
      client.Delete();
      break;
  }
}

When you call the Delete method, the RowState property of the deleted row changes to DataRowState.Deleted but it is still present in the DataSet. So when you call the Update, the method now is able to search for all the rows that have a RowState different from DataRowState.Unchanged and executes the appropriate action against the database table

Upvotes: 4

Related Questions