Accurately noting changed/deleted rows

  • Thread starter Thread starter Jon Skeet [C# MVP]
  • Start date Start date
J

Jon Skeet [C# MVP]

I've got a pretty simple situation: a very simple table with an ID
(which has been marked as the primary key) in SQL Server.

I then have a SqlDataConnection to the appropriate database, and a
simple select statement: select * from Articles
(I know selecting * is generally bad practice. This is just a test
database where I often muck around with the columns. If using select *
is relevant to why I'm not seeing the behaviour I'd expected, please
let me know!)

I create a new DataSet, and a DataTable within it called Articles. I
pre-create this so that I can add handlers for row
changing/changed/deleted/deleting and column changing/changed. I set
the missing schema action for the adapter to be AddWithKey. I then call
DataAdapter.Fill, passing the DataTable as the parameter.

Lo and behold, I get the appropriate data in the table, with a series
of Add and then Commit operations.

Now, in the background (after the Fill has completed) I remove one row
from the database, and change another one (not the key column). I then
call Fill again.

I had expected to see one row being deleted and the other being
changed, amd that's all - but instead, I see *all* the existing (in the
database) rows being changed (whether or not the data has changed) and
no deletions - the now obsolete row still remains in my data table.

I see this behaviour whether or not I call DataTable.AcceptChanges.

Am I doing something fundamentally wrong? The bulk of the code is:

SqlConnection conn = new SqlConnection
("Persist Security Info=False;"+
"Integrated Security=SSPI;"+
"database=News;server=treebeard");

SqlDataAdapter sda = new SqlDataAdapter
("select * from Articles", conn);
sda.MissingSchemaAction = MissingSchemaAction.AddWithKey;

DataSet ds = new DataSet();

DataTable articles = new DataTable("Articles");
ds.Tables.Add(articles);
articles.ColumnChanged+=new DataColumnChangeEventHandler
(ColumnChanged);
articles.ColumnChanging+=new DataColumnChangeEventHandler
(ColumnChanging);
articles.RowChanged+=new DataRowChangeEventHandler(RowChanged);
articles.RowChanging+=new DataRowChangeEventHandler(RowChanging);
articles.RowDeleted+=new DataRowChangeEventHandler(RowDeleted);
articles.RowDeleting+=new DataRowChangeEventHandler(RowDeleting);

while (true)
{
sda.Fill(articles);
Console.WriteLine ("Accepting changes");
ds.AcceptChanges();

Console.WriteLine ("Number of entries: {0}", articles.Rows.Count);
Console.WriteLine ("Change some data");
Console.ReadLine();
}

The handlers just print out some diagnostics.
 
Hi Jon,

This is by design.
You have to know that Fill just does that - Fill.
It is not that inteligent - it is inteligent enough not to add duplicates
but that's it.
If you want to get rid of deleted rows, you should do:
Do a Fill to another table (temporary).
Remove all rows in original table that aren't listed in temporary.
Do a DataSet.Merge to merge the new data.

If you have more questions...

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com
 
This is by design.
You have to know that Fill just does that - Fill.
It is not that inteligent - it is inteligent enough not to add duplicates
but that's it.
If you want to get rid of deleted rows, you should do:
Do a Fill to another table (temporary).
Remove all rows in original table that aren't listed in temporary.
Do a DataSet.Merge to merge the new data.
Click to expand...

Urgh. Fair enough. At least knowing that it's meant to work that way
will stop me from wasting any more time fiddling with it to try to get
it to do "the right thing". Cheers.
 
Of course, the simplier method would be to Clear the table before doing
Fill... if you are not interested in persisting changes of old data... :)
 
Of course, the simplier method would be to Clear the table before doing
Fill... if you are not interested in persisting changes of old data... :)
Click to expand...

No, I need to know which rows have been deleted as I'm then deleting
them from another database. In other words, I'm doing a sort of limited
form of replication.
 
Cor said:
Maybe you can look what dataset.getchanges can do for you
Click to expand...

I don't see how that would help - if the changes were reflected in the
DataSet in the first place, I wouldn't see the behaviour I'm seeing.
DataSet.Fill isn't tracking changes, it's just doing a fill with a few
smarts to make sure that rows with an identical keep are updated
instead of duplicated.
 
Back
Top