Accurately noting changed/deleted rows

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

 

[Miha Markic]

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

 

[Jon Skeet [C# MVP]]

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.

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.

 

[Miha Markic]

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

 

[Jon Skeet [C# MVP]]

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

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]

Hi Jon,

In addition to Miha,

Maybe you can look what dataset.getchanges can do for you

Cor

 

[Miha Markic]

Ah, I've got another idea.
What about Sql Server notification service?
http://tinyurl.com/2ljh9
It might be useful to you.

 

[Jon Skeet [C# MVP]]

Maybe you can look what dataset.getchanges can do for you

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.