How can we help you today?
Copy Data from one Database to Another Database
2 CommentsSorted by Oldest First
Rob Parker
said
12 months ago
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:
- Open the two connections, such as mainConn and backupConn
- Make sure the table exists in the destination database (with the desired schema) and perform any other desired preparation.
- 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.)
- Using a DataReader returned by selectCommand.ExecuteReader() set up a loop over the result rows, such as with While (reader.Read())
- 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.
- 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
Robert Dunham
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 UsingI would sure appreciate help on this.
Thanks,
Robert