Detecting updates, and persisting to SQL Server

  • Thread starter Thread starter cpnet
  • Start date Start date
C

cpnet

I previously used DataSets in a project where there was not backing
database, and now I'm trying to use a DataSet in a WinForms project that
needs to edit data in a SQL Server database, but I'm having problems.

My first problem is how to detect if anything has changed in the DataSet
that may need to be sent to the SQL Server database. Currently when I
initialize my WinForm, I'm iterating through all DataTables in the DataSet
and adding a handler to the RowChanged event. This hander will check
myDataSet.HasChanges() and then enable/disable some save and cancel buttons.
This doesn't seem to pick up all changes at the moment, and I'm also
wondering if there's a more efficient way to handle this?

My second issue is understanding when to use DataSet.AcceptChanges() vs.
SqlDataAdapter.Update(myDataSet). From what I've read, if I want to persist
changes in my DataSet to my Database, then I should never be calling
DataSet.AcceptChanges(), but instead must call the Update() method for every
SqlDataAdapter that may have changes in the corresponding DataTable? If I
use AcceptChanges() then my changes won't be persisted to my database right?
Update() doesn't seem to be working for me right now. I don't get errors,
but I also don't get changes in my SQL Server database. The DataAdapter
wizard generated stored procs for all of my commands (and I have a distinct
DataAdapter for each table that I'm working with). The DataSet I'm using is
strongly typed with a DataTable corresponding to each DataAdapter. I'm
assuming that since I have a strongly typed dataset, I can use
myFirstDataAdapter.Update(myDataSet), as opposed to
myFirstDataAdapter.Update(myDataSet, "sometable")?

TIA,
cpnet
 
The SqlDataAdapter.Update() method is working ok (I was forgetting to call
Update for one of my DataAdapteres which is why stuff wasn't getting into
the DB).

However, DataSet.HasChanges() doesn't seem to be working. I'm thinking that
the DataSet.RowChanged event is maybe the wrong place to check
DataSet.HasChanges(), but where else should I be checking it?
 
We see problems with HasChanges when you have not done an EndEdit

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
This is really puzzling.

I've added a "RowState" (string) column to every DataTable in my DataSet.
I've also added a handler to the RowChanged event for every DataTable in my
DataSet, that sets the "RowState" value to the current value of
DataRow.RowState, any time the row changes. This column is showing the
DataRowState that I'd expect for all of my tables (except of course for
DataRowState.Deleted rows or DataRowState.Detached rows which won't show up
in a DataGrid).

I've also created a "checking" method that fires on a button click. It
iterates through every DataRow of every DataTable in my DataSet, and
displays the DataTable name, primary key id of the DataRow, and the
DataRow's DataRowState. Just after I have filled my DataSet, or called the
Update method for all DataAdapters that fill the DataSet, this shows a value
of DataRowState.Modified for every DataRow in every DataTable in my DataSet!
This is completely out of sync with what my "RowState" column is reporting.
If I subsequently add a DataRow, then both my "RowState" column and checking
method will report that that DataRow has a DataRowState of
DataRowState.Added (as it should). As soon as I call the Update() method of
the DataAdapter for the DataTable who's row I've changed, then my "checking"
method reports that the DataRow has become DataRowState.Modified, while my
"RowState" column says it's DataRowState.Unchanged?!?

Here's the code


//Check the row state of any row when it's changed. This seems to report
// the row state that I'd expect
bool settingRowState = false;

//This is assigned as an event handler for the RowChanged event
// of every DataTable
private void RowChanged( object sender, DataRowChangedEventArgs e)
{
if (!settingRowState)
{
settingRowState=true;
try
{
e.Row["RowState"] = e.Row.RowState.ToString();
}
finally
{
settingRowState=false;
}
}
}


//List the DataRowState of every DataRow of every DataTable
// in a ListView. This gives odd results
private void checkRowStates()
{
listView1.Items.Clear();
foreach( DataTable dt in myDataSet.Tables)
{
foreach(DataRow dr in dt.Rows)
{
listView1.Items.Add( dt.TableName + "; " +
dr[0].ToString() + "; " + dr.RowState.ToString());
}
}
}
 
Except I see the problem even after calling myDataSet.AcceptChanges(); which
is supposed to implicitly call EndEdit().
 
Hi cpnet,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you cannot check for changes in the
DataSet by using HasChanges property. If there is any misunderstanding,
please feel free to let me know.

I agree with Bill's advice that we have to call EndEdit explicitly to make
the changes submit to the DataSet, because sometimes in a data binding
environment, EndEdit will not be called automatically.

However, we should never check HasChanges property after calling
AccesptChagnes() method. DataSet checks for changes according to the
RowState property for each DataRow object. If you call AcceptChanges()
method, all the RowState will be set to Unchanged, and the HasChanges
property will always return false.

Here is an example for you.

private void dt_RowChanged(object sender, DataRowChangeEventArgs e)
{
e.Row.EndEdit();
}

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
My problem wass that .HasChanges() was returning true after calling
..AcceptChanges(). However, I found that I had 2 problems:

1) I was setting the value of a column in the RowChanged event of my
datatables to visually track RowState for debugging. This interfered with
what .HasChanges() sees. (Right fter setting the RowState to a "RowState"
column in my DataTable, in the handler for the DataTable's RowChanged event,
I checked HasChanges for the DataSet, which then always returned true,
despite a semaphore to prevent re-entry into my handler).

2) Once I stopped setting any column values in the RowChanged event
handler, checking .HasChanges() in the RowChanged event handler ALMOST
worked. The only time it failed was if I called myDataTable.LoadDataRow().
That seems to fire the RowChanged event before the RowState has been
updated, so .HasChanges() was reporting false, even though I had just added
a new row. (Manually calling .HasChanges() after calling .LoadDataRow()
returned true as I'd expect).

So, it seems that I shouldn't check .HasChanges() in a handler for the
RowChanged() event for all of the DataTables in my DataSet. When then
should I check DataSet.HasChanges(). The goal here is to automatically
enable/disable some cancel and save buttons based on whether or not there's
anything in the DataSet to save.
 
Hi cpnet,

The description for RowChanged event is "Occurs after a row in the table
has been edited successfully." Editing here means Changing old rows or
Adding new rows. It never said "after the row has been commited". So Yes,
doing something like ds.HasChanges inside this event will indicate that the
row is not changed because HasChanges looks for completed rows. If we are
inside that event the Action has not been completed yet. The row Addition
process will complete after exiting the event. So if you check the
HasChanges property after finishing adding the new row, then the HasChanges
will show the correct value. Another usage for this event is to do some
last minute data validation.

This behavior is actually by design. You can try to check more information
on this with the following links:

http://groups.google.com/groups?hl=zh-CN&lr=&threadm=V8LoDDuICHA.6392@cpms
ftngxa08&rnum=1&prev=/groups%3Fq%3Drowchanged%2Bhaschanges%2Bmsft%26hl%3Dzh-
CN%26lr%3D%26selm%3DV8LoDDuICHA.6392%2540cpmsftngxa08%26rnum%3D1

http://groups.google.com/groups?hl=zh-CN&lr=&threadm=9a04543.0210092359.1fa0
fb48%40posting.google.com&rnum=2&prev=/groups%3Fq%3Drowchanged%2Bhaschanges%
2Bmsft%26hl%3Dzh-CN%26lr%3D%26selm%3D9a04543.0210092359.1fa0fb48%2540posting
.google.com%26rnum%3D2

If you need to achieve the goal to automatically enable/disable some cancel
and save buttons, you can try to handle the Control.LostFocus event to
check the HasChanges() method. HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top