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,644 questions
Accepted answer
  1. Karen Payne MVP 35,366 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
Most helpful Newest Oldest
  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
  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.