Start a new topic

Copy Data from one Database to Another Database

Hi,

I have two Vistadb Databases (I'm using Version 6.3.2). One is Database1 and the other is Backup1. Both databases have the same tables and fields. I want to copy records from Backup1 (Clients Table) to Database1 (Clients Table) I'm not sure if I need two different connections for this. Here is what I have so far.  

            'Add the Contents to the Database
Dim ClientId as Integer  = TxtFileId.text
            Using conn As New VistaDBConnection(VdbConn)
                conn.Open()
                StrSql = "INSERT INTO Into Clients(ClientType, Name, Address, City, State, Zip, Phone, CellPhone, Fax, Email, Web, Since, ClientImage) 
                          SELECT ClientType, Name, Address, City, State, Zip, Phone, CellPhone, Fax, Email, Web, Since, ClientImage
'From Backup 1 Database to Database1
                          FROM Clients 
                          WHERE (ClientId = @ClientId)"

                Using cmd As New VistaDBCommand(StrSql, conn)
                    With cmd.Parameters
                        .AddWithValue("@ClientId", ClientId)
                    End With
                    'Execute the Statement
                    cmd.ExecuteNonQuery()
                    'Close the Connection
                    conn.Close()
                End Using
            End Using

  I would sure appreciate help on this.

Thanks,

Robert


VistaDB does not support operations spanning more than one database and there is no supported syntax for specifying it.


However, it's not too hard to do this sort of simple bridging from .NET code using two separate connections.  You can find sample source code for a general ADO.NET table (or database) copy program on the Internet on sites like Code Plex, Code Project, and so on.  (Usually in C#, but you can also find a code translator to convert it into Visual Basic.)


But the basic outline for copying a single table between databases would be:


  1. Open the two connections, such as mainConn and backupConn
  2. Make sure the table exists in the destination database (with the desired schema) and perform any other desired preparation.
  3. Create a selectCommand instance and an insertCommand instance on the respective databases.  The insert command text (for inserting a single row at a time) should use named parameters in the value list so they can be easily passed in for each row.  When adding the parameters to the insertCommand.Parameters make sure to retain a variable for each one so the values can be easily assigned without recreating Parameters for each row.  insertCommand.Paramters.Add(...) should return the added parameter to help with this.  (You know the specific schema so you can code a more direct approach than the generic copying examples which have to read the schema and handle it more dynamically.)
  4. Using a DataReader returned by selectCommand.ExecuteReader() set up a loop over the result rows, such as with While (reader.Read())
  5. Within the loop, copy each result column of the DataReader to the appropriate insert parameter's Value property.  Once they are all set, insertCommand.ExecuteNonReader() to insert the row into the destination table.
  6. After the loop, end the Using on the DataReader.  Also manage the Dispose() of selectCommand, insertCommand, and the database connections depending on whether they are being kept for reuse.  Make sure each reader, command, and connection instance gets disposed before it goes out of scope (or before exiting the app for those kept for the duration).


Thanks for the reply. I was able to get this to work using the following code in case anyone else would like to perform this operation in vb.net First I loaded a backup vistadb.vdb6 database file name containing the data I wanted to get records from in a treelist control. When you select the Vistadb.vdb6 backup (There could be more than 1) The program displays a list of tables to get records from (Other code logic). Selecting the table in a listbox control, displays the records in that table in another treelist control. Choosing a record in the treelist control loads the record number in another listbox control which are the records that are copied to the master database. vdb6. Below is an example getting data from the client's table. I hope this helps someone else trying to achieve the same thing.

  

    Private Sub TransferClient()

        Dim MyFile As String = TreeListTransfer.FocusedNode(0).ToString
        Dim VdbConn1 As String = "Data Source=" & Application.StartupPath & "\Backup\" & MyFile & ".vdb6"

        Try
            'Count the files in the Listbox
            For i As Integer = 0 To LstFiles.Items.Count - 1
                LstFiles.SelectedIndex = i
                FileId = CInt(LstFiles.Text)

                'Add the Contents to the Database
                Using conn As New VistaDBConnection(VdbConn)
                    conn.Open()
                    StrSql = "INSERT INTO Clients(ClientType, Name, Address, City, State, Zip, Phone, CellPhone, Fax, Email, Web, Since, ClientImage) 
                          SELECT ClientType, Name, Address, City, State, Zip, Phone, CellPhone, Fax, Email, Web, Since, ClientImage
                          FROM dbo.Clients
                          WHERE (ClientId = @ClientId)"

                    Using cmd As New VistaDBCommand(StrSql, conn)
                        With cmd.Parameters
                            .AddWithValue("@ClientId", FileId)
                        End With
                        'Execute the Statement
                        cmd.ExecuteNonQuery()
                        'Close the Connection
                        conn.Close()

                    End Using
                End Using
            Next
            MessageBox.Show("Transfer successfully completed on the 'Clients' table", "Transfer Client Data", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Transfer Client Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        Finally

        End Try

    End Sub

  

Login to post a comment