Undo/Redo database

DRGAGI 146 Reputation points
2020-12-05T17:43:10.82+00:00

Hello everyone, i have a DataGridView with a database. I need help about adding option Undo and Redo. I use DataSet, DataBindingSource and TableAdapter. Any suggestions?

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,566 questions
{count} votes

Accepted answer
  1. Karen Payne MVP 35,031 Reputation points
    2020-12-09T20:52:50.397+00:00

    @DRGAGI

    The following only does the last record marked as deleted (not actually deleted) and if more are needed will require more logic. In the table, there is a primary key id, first and last names as strings then IsDeleted as a Boolean.

    Note that there may be some minor alterations needed as I had limited time to write this up but you should get the idea. Note also, working with a TableAdapter in this case requires much more code than using non TableAdapter methods to access data and that there are frameworks out there for undo and redo but not for TableAdapter or DataSet or DataTables.

    In the .xsd

    Read non deleted data

    SELECT        Id, FirstName, LastName, IsDeleted  
    FROM            Person2  
    WHERE        (IsDeleted = 0)  
    

    Get deleted records

    SELECT        Id, FirstName, LastName, IsDeleted  
    FROM            Person2  
    WHERE        (IsDeleted = 1)  
    

    Get single person record

    SELECT        Id, FirstName, LastName, IsDeleted  
    FROM            Person2  
    WHERE        (Id = @Id)  
    

    The above is for one TableAdapter, the following to get the last record deleted with no custom query.

    Form code (easy enough to figure out

    Public Class Form1  
        Private Sub Person2BindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) _  
            Handles Person2BindingNavigatorSaveItem.Click  
      
            Validate()  
            Person2BindingSource.EndEdit()  
            TableAdapterManager.UpdateAll(ForumExampleDataSet)  
      
            '  
            ' Reload from database  
            '  
            Person2TableAdapter.FillByNotDeletedRecords(ForumExampleDataSet.Person2)  
      
        End Sub  
      
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load  
            Person2TableAdapter.FillByNotDeletedRecords(ForumExampleDataSet.Person2)  
        End Sub  
      
        Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) _  
            Handles BindingNavigatorDeleteItem.Click  
      
            '  
            ' Check for stored key from person2 table  
            '  
            Person2TrackerTableAdapter.Fill(ForumExampleDataSet1.Person2Tracker)  
      
            '  
            ' Get current row primary key  
            '  
            Dim row = CType(Person2BindingSource.Current, DataRowView).Row  
            Dim currentId = row.Field(Of Integer)("Id")  
      
            '  
            ' Do we need a row or modify the single row  
            '  
            If ForumExampleDataSet1.Person2Tracker.Rows.Count = 0 Then  
                ForumExampleDataSet1.Person2Tracker.Rows.Add(New Object() {Nothing, currentId})  
            Else  
                ForumExampleDataSet1.Person2Tracker.Rows(0).SetField("LastPersonIdentifier", currentId)  
            End If  
      
      
            '  
            ' Save changes to tracker table  
            '  
            Person2TrackerTableAdapter.Update(ForumExampleDataSet1.Person2Tracker)  
      
            '  
            ' Set current row as deleted and set in save button code  
            '  
            row.SetField("IsDeleted", True)  
      
        End Sub  
        ''' <summary>  
        ''' Undue last set to deleted row  
        ''' </summary>  
        ''' <param name="sender"></param>  
        ''' <param name="e"></param>  
        Private Sub UndueButton_Click(sender As Object, e As EventArgs) Handles UndueButton.Click  
            '  
            ' Get tracking record  
            '  
            Person2TrackerTableAdapter.Fill(ForumExampleDataSet1.Person2Tracker)  
      
            If ForumExampleDataSet1.Person2Tracker.Rows.Count = 1 Then  
      
                Dim dt As New ForumExampleDataSet.Person2DataTable  
                Person2TableAdapter.FillByGetDeletedRecords(dt)  
      
                Dim savedId = ForumExampleDataSet1.Person2Tracker.  
                        Rows(0).  
                        Field(Of Integer)("LastPersonIdentifier")  
      
                Dim row = dt.AsEnumerable().FirstOrDefault(Function(pRow) pRow.Id = savedId)  
      
                If row IsNot Nothing Then  
                    '  
                    ' Set not deleted, save, reload  
                    '  
                    row.SetField("IsDeleted", False)  
                    Person2TableAdapter.Update(dt)  
                    Person2TableAdapter.FillByNotDeletedRecords(ForumExampleDataSet.Person2)  
                    Person2BindingSource.ResetBindings(True)  
                End If  
      
            End If  
        End Sub  
    End Class  
    

    In the screenshot I told the delete button to do nothing from the property window, added a undue button (last button)

    46695-a2.png

    46712-a1.png


6 additional answers

Sort by: Most helpful
  1. Xingyu Zhao-MSFT 5,356 Reputation points
    2020-12-08T02:43:21.897+00:00

    Hi @DRGAGI ,
    In order to undo changes in the past, you need to back up you database and then restore the backup.
    You can refer to the following example's code.

    • Back up database:
             Dim connString As String = "your Database1 conection string"  
           Dim sqlConn As New SqlConnection(connString)  
           sqlConn.Open()  
      
           Dim sCommand = "BACKUP DATABASE [yourDatabase1] TO DISK = N'D:\Test\Backup.bak' WITH COPY_ONLY"  
      
           Using cmd As New SqlCommand(sCommand, sqlConn)  
               cmd.ExecuteNonQuery()  
           End Using  
      
    • Restore the backup:
             Dim connString As String = "other database connection string" ' Also check: https://stackoverflow.com/a/10982185/12666543  
           Dim sqlConn As New SqlConnection(connString)  
           sqlConn.Open()  
      
           Dim sCommand = "RESTORE DATABASE [yourDatabase1] FROM DISK = N'D:\Test\Backup.bak' WITH REPLACE"  
      
           Using sqlCmd As New SqlCommand(sCommand, sqlConn)  
               sqlCmd.ExecuteNonQuery()  
           End Using  
      

    Hope it could be helpful.

    Best Regards,
    Xingyu Zhao
    *
    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. DRGAGI 146 Reputation points
    2020-12-08T16:44:45.593+00:00

    This is my form, i will post my code also46257-untitled-1.jpg


  3. DRGAGI 146 Reputation points
    2020-12-08T16:45:05.197+00:00
    Public Class Form1
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'TestDataDataSet.TestTable' table. You can move, or remove it, as needed.
            Me.TestTableTableAdapter.Fill(Me.TestDataDataSet.TestTable)
    
        End Sub
    
        Private Sub saveDatabase_Click(sender As Object, e As EventArgs) Handles saveDatabase.Click
    
            Me.Validate()
            Me.TestTableBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TestDataDataSet)
    
        End Sub
    
        Private Sub deleteRow_Click(sender As Object, e As EventArgs) Handles deleteRow.Click
    
            TestTableBindingSource.RemoveCurrent()
    
        End Sub
    
        Private Sub addNew_Click(sender As Object, e As EventArgs) Handles addNew.Click
    
            TestTableBindingSource.AddNew()
    
        End Sub
    
        Private Sub nextRow_Click(sender As Object, e As EventArgs) Handles nextRow.Click
    
            TestTableBindingSource.MoveNext()
    
        End Sub
    
        Private Sub previous_Click(sender As Object, e As EventArgs) Handles previous.Click
    
            TestTableBindingSource.MoveNext()
    
        End Sub
    End Class
    

  4. DRGAGI 146 Reputation points
    2020-12-09T16:51:57.47+00:00

    There must be a way to undo deleted record for datagridview rows, something like when you undo TextBox.Text. I can undo TextBox.Text who uses database, but when i save the database then i am not able to do the same.