We help IT Professionals succeed at work.

Which data table event is most appropriate to perform table adapter update operations?

kkamm
kkamm asked
on
Medium Priority
1,187 Views
Last Modified: 2012-05-06
I would like to be able to run an update operation for a table when a row is changed to force new changes to the backend database immediately.

I started with the RowChange event, but it seems to continue to fire after each Update, resulting in an endless loop. I attached a code block that does this.

Where should a tableadapter update command go? Which methods should be used to return edited rows to their unchanged state?



Public Sub RowChangeHandler(ByVal sender As Object, ByVal e As TestTableRowChangeEvent)
 
        Using dtChanges As TestTableDataTable = CType(_TestTable.GetChanges, TestTableDataTable)
 
            If Not dtChanges Is Nothing Then
                Debug.WriteLine("rows changed:" & dtChanges.Rows.Count)
 
                Using ta As New TestTableTableAdapter
                    ta.Connection.ConnectionString = Me.ConnectionString
                    If ta.Update(dtChanges) > 0 Then
                        Debug.WriteLine("row updated")
                        _TestTable.Merge(dtChanges)
 
                    Else
                        Debug.WriteLine("row NOT updated")
                    End If
                End Using
            Else
                Debug.WriteLine("No changes")
            End If
 
        End Using

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
You are not using the Event (ByVal e As TestTableRowChangeEvent) anywhere, and it can be quite useful!!

Try to check if e.Action = DataRowAction.ChangeOriginal

Your event is triggering after the update, because in the background the datatable row is marking the row as being unchanged.

Dabas
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
My experience with the GetChanges is that it would return the whole table back even if few rows are changed or even when no change has been made.

Author

Commented:
Dabas,

I attached some code I used to show me the row states of rows I change in my table. Initially, I used the RowEditEnding of the datagrid to process the update. The edited row did not register a 'commit' state until the NEXT row edit. I then put the update method in the data table rowchange event handler. This resulted in a stack overflow error because the event kept refiring. No 'commit' state was ever reached for the edited row.

My question here is how do I actually finalize/commit the edit and where do I do that? I thought the update method took care of finalizing a row change edit. Surely there must be a 'best practices' methodology that registers the row change, forwards the updated row to the server, and changes the row state of the edited row back to unchanged.

By 'best practices' I mean something that works in a repeatable and predictable fashion that I can count on without dealing with special cases. I am going to use this as a generic data access foundation for a larger app and I would prefer to build on bedrock rather than sand. :)

I appreciate any assistance I can get on this.

  Public Sub RowChangeHandler(ByVal sender As Object, ByVal e As TestTableRowChangeEvent)
 
        Select Case e.Action
 
            Case DataRowAction.Add
                Debug.WriteLine(e.Row.UniqueID & " ADDED")
            Case DataRowAction.Change
                Debug.WriteLine(e.Row.UniqueID & " CHANGED")
                Using ta As New TestTableTableAdapter
                    ta.Connection.ConnectionString = Me.ConnectionString
                    ta.Update(_TestTable)
                End Using
            Case DataRowAction.ChangeCurrentAndOriginal
                Debug.WriteLine(e.Row.UniqueID & " ChangeCurrentAndOriginal")
            Case DataRowAction.ChangeOriginal
                Debug.WriteLine(e.Row.UniqueID & "ChangeOriginal")
            Case DataRowAction.Commit
                Debug.WriteLine(e.Row.UniqueID & "Commit")
            Case DataRowAction.Delete
                Debug.WriteLine(e.Row.UniqueID & "Delete")
            Case DataRowAction.Nothing
                Debug.WriteLine(e.Row.UniqueID & "Nothing")
            Case DataRowAction.Rollback
                Debug.WriteLine(e.Row.UniqueID & "Rollback")
 
        End Select
 
    End Sub

Open in new window

CERTIFIED EXPERT

Commented:
Can you comment out lines 10 and 11 and post the results of your Debug.WriteLines statements?

Author

Commented:
Here is the output: I am handling the datagrid RowEditEnding event to apply the Update() method. (Snippet 1)
Data Grid row changed:0
-169 CHANGED
-169 CHANGED
-169 CHANGED
-169Commit
-171 CHANGED
Data Grid row changed:1
-171 CHANGED
-171 CHANGED
-171 CHANGED
-171Commit
-170 CHANGED

I updated 2 rows (PK -169 and -171). The negative numbers are the primary key values for each  row. It seems as though the 'COMMIT' state was reached only after the Update() method was run after a subsequent edit. I want the row updated as soon as the user is finished editing. By update I mean the changes are written to the database.

I tried using the table AcceptChanges method to see if that would force a write to the database but it did not have any effect.

This seems like it should be a repeatable and consistent process, as I am sure the majority of developers encounter this at one time or another, but I cannot seem to get a consistent methodology that I can depend on to write changes to a database on demand.

Is there another method I should be using to force a row update to be written to the server?





 ''''''''''''''''''''''''''Snippet 1''''''''''''''''''''''''''
Private Sub WPFDataGridCRUDTestTable_RowEditEnding(ByVal sender As Object, ByVal e As Microsoft.Windows.Controls.DataGridRowEditEndingEventArgs) Handles Me.RowEditEnding
        Debug.WriteLine("Data Grid row changed:" & e.Row.GetIndex)
 
        Using ta As New TestTableTableAdapter
            ta.Connection.ConnectionString = Me.ConnectionString
            ta.Update(_TestTable)
        End Using
 
    End Sub
 
''''''''''''''''''''''''Snippet 2'''''''''''''''''''''''''''''
 Public Sub RowChangeHandler(ByVal sender As Object, ByVal e As TestTableRowChangeEvent)
 
        Select Case e.Action
 
            Case DataRowAction.Add
                Debug.WriteLine(e.Row.UniqueID & " ADDED")
            Case DataRowAction.Change
                Debug.WriteLine(e.Row.UniqueID & " CHANGED")
            Case DataRowAction.ChangeCurrentAndOriginal
                Debug.WriteLine(e.Row.UniqueID & " ChangeCurrentAndOriginal")
            Case DataRowAction.ChangeOriginal
                Debug.WriteLine(e.Row.UniqueID & "ChangeOriginal")
            Case DataRowAction.Commit
                Debug.WriteLine(e.Row.UniqueID & "Commit")
            Case DataRowAction.Delete
                Debug.WriteLine(e.Row.UniqueID & "Delete")
            Case DataRowAction.Nothing
                Debug.WriteLine(e.Row.UniqueID & "Nothing")
            Case DataRowAction.Rollback
                Debug.WriteLine(e.Row.UniqueID & "Rollback")
 
        End Select
 
    End Sub
 
 
              

Open in new window

CERTIFIED EXPERT

Commented:
What happens if you move lines 10 and 11 from your original code to the "Commit" Case?

Dabas

Author

Commented:
If I Update() in the 'Commit' block nothing happens. The database on the server remains unchanged.

If I Update() on the datagrid 'RowEditEnding' event the database is updated, but only AFTER I edit another row.

 Does Update() only set the rowstate of the changed rows to something that can be referenced the next time Update() is called, and use that state to actually WRITE the changes?

Author

Commented:
Here is yet another try with a slightly different approach. Same result. Update needs to be called again to see the changes on the server.

-173 was modified.
-173 was modified.
-173 was modified.
-173 was modified.
-173Commit
-173 is unchanged.

This really does not seem like the way this was intended to work. To me, 'Update' means UPDATE at the point the method is called and with no further qualifications  or calls.

 Private Sub WPFDataGridCRUDTestTable_RowEditEnding(ByVal sender As Object, ByVal e As Microsoft.Windows.Controls.DataGridRowEditEndingEventArgs) Handles Me.RowEditEnding
        Using ta As New TestTableTableAdapter
            ta.Connection.ConnectionString = Me.ConnectionString
            Using dtChanges As TestTableDataTable = CType(_TestTable.GetChanges, TestTableDataTable)
                ta.Update(_TestTable)
                If Not dtChanges Is Nothing Then
                    dtChanges.AcceptChanges()
                    _TestTable.Merge(dtChanges)
                End If
 
            End Using
        End Using
    End Sub

Open in new window

Author

Commented:
I set up a SQL profile trace and confirmed that updates are not being written until the NEXT time Update() is called.

Sequence:
1) Change ROW1 in datagrid
2) Datagrid RowEditEnding event fired, which contains Update()
3) Update() called. Changes to ROW1 not written.
4) Change ROW2 in datagrid
5) Datagrid RowEditEnding event fired, which contains Update()
6) Update() called
7) Changes made to ROW1 written. Changes to ROW2 not written

Is there another way to flush all pending client transactions on demand?   I can't use this because I have users who enter detailed text that takes a fair amount of time and if they finish an entry but do no further editing and the power goes out they will have lost work and time.

CERTIFIED EXPERT

Commented:
Can you give more information about TestTableTableAdapter?
Also you are creating a new TestTableTableAdapter. How did you fill dtChanges?

Dabas

Author

Commented:
The code is attached.

Yes, I created a new instance of  TestTableTableAdapter  and dtChanges is filled by the
_TestTable.GetChanges method.

I also waited on a possible flush interval to see if changes were written after a certain amount of time. (10 seconds is apparently the default) No luck. No changes are written until I call Update() again.
Using ta As New TestTableTableAdapter
            ta.Connection.ConnectionString = Me.ConnectionString
            Using dtChanges As TestTableDataTable = CType(_TestTable.GetChanges, TestTableDataTable)
                ta.Update(_TestTable)
                If Not dtChanges Is Nothing Then
                    dtChanges.AcceptChanges()
                    _TestTable.Merge(dtChanges)
                End If
 
            End Using
        End Using

Open in new window

CERTIFIED EXPERT

Commented:
Concerning line 6, I just want to clarify what the AcceptChanges() method really does, as its name confuses many users

What it really means is "The server has accepted these changes, mark all records as unchanged"

Is this what you want?

Author

Commented:
As I understand it ,AcceptChanges() iterates through all rows in the a table and changes their rowstates to 'Unchanged' so that new row changes can be indicated.

What I would like is for the Update() method, or any combination of Update(), AcceptChanges(), EndEdit,etc, to  actually write changes to the server WHEN I call them and not the NEXT time I call them.

Hypothetically, if I have one server-side database table with one row, and I have a client-side typed dataset data table that is filled with that one row of data, and I change one field in that row and press enter-what command(s) do I need to run to get the server-side row to be changed right then and there?  It's not Update() because it does not change the row on the server right then and there. (At least not in my app)

CERTIFIED EXPERT

Commented:
What about changing your approach completely?
Since all you want to do is update ONE row at a time, why do you not just create a command object with the correct UPDATE or INSERT query and execute it?

Dabas

Author

Commented:
Yeah, to be honest, that was the direction I was heading in anyway, but I was hopeful that the typed dataset  designer stuff would provide a more maintainable code base.

It's not really that I want ONE row updated - I want my changes written when I make them. If I programmatically changed 10000 rows on a table, called Update(), and then found I had to edit one more row to get the 10000 rows  written to the server, I would consider that less-than-adequate functionality.

Maybe I should have a dummy row that holds no real data, but I could change its rowstate to 'modified' and then update it every time I wanted to do a 'real' update.

This issue has caused me to drift away from the original question. I am finding that processing any updates in the RowChange event handler causes a recursive calling loop. Are there any other table-based events that may be more suitable to capturing a data change?

CERTIFIED EXPERT
Commented:
I distrust designer stuff, and prefer to write my own code.
In most cases I have been faced with similar situations as yourself now, where an advanced feature is not working and it is hard to understand why.
The Adapters are good for updating LOTS of rows actually, and I seldom have problems with the .Update command. But then I usually use the same DataAdpater that I used to fill the table in the first place too.

In this particular case you are interested to update only one row (otherwise you would not be using the Row Handler). The DataAdapter is an overkill as  you are causing a background process to check the whole dataset for changes.

Not to avoid the complexity of your question, but maybe you can simplify things considerably by providing the user with a Save button, and make it his/her responsibility to save the data appropriately. It will mean that all data that is to be changed gets sent in the one time, reducing traffic, etc.

The way you are doing it, might be good for data integrity, etc, but think about the headache if the user has a slow connection and your constant saves take their time....

Dabas

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Another approach I was thinking of using is a timing loop and a backgroundworker thread to process accumulated SQL command objects. I have used this before in an application that had asynchronous data flowing into a serial port and I did not want to continuously flood the server with individual updates everytime I got a burst of data.

I want to keep the users out of the loop of requiring them to save data manually as they have been using a system that does not require it, and this would change their workflow.

I have a saying-The long way is the short way. With regards to the wizards(shortway), I spend more time compensating for lack of access and documentation than I save using them. I think Microsoft could do better in this area as it is probably one of the most common development tasks.


CERTIFIED EXPERT

Commented:
Looks like we are in agreement there.
Is there anything else you need my help with in this question?

Author

Commented:
Just to confirm- the data table RowChange event is the only table level event that fires on a rowstate change in the table. Is this the case, or are there other events that are fired for specific rowstate changes?
CERTIFIED EXPERT

Commented:
What about RowChanging?

Otherwise, you can try events at the DataAdapter level.
Or you can try events at DataBinding level

But these do not seem to be relevant to your actual project

Dabas

Author

Commented:
I am going to try the XCeed WPF datagrid and use a business object collection to hold my data. This seems to be the prevailing best practice. Binding to a typed dataset seems more trouble than it is worth, although I may still use it to encapsulate my database queries.

Thanks for your help, Dabas.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.