Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-02-12
21
Medium Priority
?
1,053 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

0
Comment
Question by:kkamm
  • 11
  • 9
21 Comments
 
LVL 27

Expert Comment

by:Dabas
ID: 23630437
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
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23631171
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.
0
 
LVL 1

Author Comment

by:kkamm
ID: 23654370
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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 27

Expert Comment

by:Dabas
ID: 23655515
Can you comment out lines 10 and 11 and post the results of your Debug.WriteLines statements?
0
 
LVL 1

Author Comment

by:kkamm
ID: 23661397
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

0
 
LVL 27

Expert Comment

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

Dabas
0
 
LVL 1

Author Comment

by:kkamm
ID: 23665051
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?

0
 
LVL 1

Author Comment

by:kkamm
ID: 23665316
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

0
 
LVL 1

Author Comment

by:kkamm
ID: 23672136
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.

0
 
LVL 27

Expert Comment

by:Dabas
ID: 23674036
Can you give more information about TestTableTableAdapter?
Also you are creating a new TestTableTableAdapter. How did you fill dtChanges?

Dabas
0
 
LVL 1

Author Comment

by:kkamm
ID: 23674375
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

0
 
LVL 27

Expert Comment

by:Dabas
ID: 23674729
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?
0
 
LVL 1

Author Comment

by:kkamm
ID: 23676156
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)

0
 
LVL 27

Expert Comment

by:Dabas
ID: 23676278
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
0
 
LVL 1

Author Comment

by:kkamm
ID: 23677485
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?

0
 
LVL 27

Accepted Solution

by:
Dabas earned 1500 total points
ID: 23677537
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
0
 
LVL 1

Author Comment

by:kkamm
ID: 23677668
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.


0
 
LVL 27

Expert Comment

by:Dabas
ID: 23685569
Looks like we are in agreement there.
Is there anything else you need my help with in this question?
0
 
LVL 1

Author Comment

by:kkamm
ID: 23700529
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?
0
 
LVL 27

Expert Comment

by:Dabas
ID: 23701559
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
0
 
LVL 1

Author Comment

by:kkamm
ID: 23710911
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question