none
Using ADO datatable Copy() to restore original data RRS feed

  • Question

  • I have the datatable bound to a datagrid where the user can see the changes he/she's making. I make a copy of the table before changes start so that I can put the original data back if the user decides not to accept the changes.

    Making the copy uses very simple syntax (VB):

    Dim copyDataTable As DataTable
    copyDataTable = dataset.table.Copy()
    

    However, reversing the syntax to:

    dataset.table = copyDataTable
    

    Gives "dataset.table is read only."

    The only example I can find for using datatable Copy() is to create a new table, not copy back to an existing one.

    I suspect there is some simple syntax or method, equivalent to making the copy, but so far no luck in finding it.

    Anyone familiar with how to do it, or even a different approach to what I'm trying to do?

    Thanks, Chas

    Thursday, October 17, 2013 8:54 PM

Answers

  • Actually, if the user doesn't want to save the changes, all you need to do is dataset.RejectChanges(), which will put your DataSet back to it's original state (assuming you haven't issued an .AcceptChanges() at any point).

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by HorseCode Friday, October 18, 2013 5:23 PM
    Friday, October 18, 2013 5:13 AM

All replies

  • Hello,

    Thanks for posting your question to this forum.

    For the DataSet.Table, we can see that it actually is Read-Only, so we cannot do some changes on it.

    And for achieving what you want, we have to define two DataTable objects at least.

    I made sample and please have a look the codes below:

    Imports System.Data.SqlClient
    
    
    Public Class Form1
    
    
        Private originalTable As DataTable
    
        Private reuseTable As DataTable
    
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
    
            Dim ds As DataSet = Execute()
    
            Dim dt As DataTable = New DataTable()
    
            reuseTable = ds.Tables(0)
    
            originalTable = reuseTable.Copy()
    
            DataGridView1.DataSource = reuseTable
    
    
        End Sub
    
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
    
            If originalTable IsNot Nothing Then
    
                'reuseTable.Clear();
    
    
                reuseTable = originalTable.Copy()
    
    
                Me.DataGridView1.DataSource = reuseTable
    
            End If
    
    
        End Sub
    
    
        Friend Function Execute() As DataSet
    
            Dim connectionString As String = "server=(localdb)\V11.0;Integrated Security=SSPI;database=TestDataBase"
    
    
            Dim connection As New SqlConnection(connectionString)
    
    
            connection.Open()
    
    
            Dim sql As String = "select * from course"
    
    
            Dim cmd As New SqlCommand(sql, connection)
    
    
            Dim da As New SqlDataAdapter(cmd)
    
    
            Dim ds As New DataSet()
    
    
            da.Fill(ds, "Course")
    
    
            Dim dt As DataTable = ds.Tables(0)
    
    
            connection.Close()
    
    
            connection.Dispose()
    
    
            Return ds
    
        End Function
    
    
    
    End Class
    

    If this does not work for you, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 18, 2013 2:22 AM
    Moderator
  • Actually, if the user doesn't want to save the changes, all you need to do is dataset.RejectChanges(), which will put your DataSet back to it's original state (assuming you haven't issued an .AcceptChanges() at any point).

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by HorseCode Friday, October 18, 2013 5:23 PM
    Friday, October 18, 2013 5:13 AM
  • Thank you, Bonnie!  One of the pitfalls of revising old code is to avoid trying to replicate an old approach, which was what was going on here.   I knew there was probably a much cleaner approach that I was missing.  Thanks for the answer!
    Friday, October 18, 2013 5:23 PM
  • Thanks for taking the time to reply.  After posting my question I wrote some similar code to restore the copy--until I saw Bonnie's answer below, which uses a built-in ADO function that I just hadn't thought of.  Thanks again.
    Friday, October 18, 2013 5:31 PM
  • You're welcome! Glad I could help!   =0)

    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, October 18, 2013 6:03 PM