Database1 to DataTable to Database2

[TexMax007]

Good Afternoon All,

I'm fairly new at VB.Net and Visual Studio so please bear with me if what I'm asking seems ridiculous.

I would like to query a MS SQL Server database (not on the local machine) and store the results in a so-called "in-memory" DataTable.

I've done this... not too difficult.

However, I would then like to put that DataTable and its contens into a different database (I created a local database in the application using I believe SQL Compact Edition).

The reason for doing this is that my initial query that I ran against the MS SQL Server is providing me with intermediate results. I would then like to combine several of these intermediate results and query and join them (using SQL statements) on the local machine, rather than having to do this on our main server.

Basically, my main hiccup is copying a DataTable (with data in it) into a database it didn't come from.

I've searched high and low for ways of doing this but with no luck.

I would greatly appreciate any assistance regarding this.

As a note: I've looked at LINQ, but that seems rather complicated.

Thanks in advance!

Max

 

[servantofone]

What about:

Dim dtOriginalTable as DataTable
Dim dtDuplicateTable as DataTable

dtDuplicateTable = dtOriginalTable.Copy()

 

[jmcilhinney]

A DataTable is a DataTable. Where the data came from is irrelevant. Basically, your problem is that calling Fill on a DataAdapter implicitly calls AcceptChanges on the DataTable, which means the RowState of each DataRow is Unchanged. In order to be inserted when you call Update on a DataAdapter, the RowState of a DataRow must be Added.

All you need to do is set the AcceptChangesDuringFill property of your first DataAdapter to False. That will leave the RowState of every DataRow as Added. You can then use a second DataAdapter to save the entire DataTable to a second database.

The schemas of the two databases don't even have to match exactly. As long as you create the InsertCommand of the second DataAdapter with the appropriate SQL code and parameters, you can massage the data a little during the transfer.

 

[TexMax007]

Still stuck...

Thanks for the replies, but I still need a bit more help.

I still can't figure out how to use the second dataAdapter to move the DatTable into my local database (database 2).

Below you'll find my code, along with my comments about what I'm trying to do:

 Public Sub MoveTable(ByVal serverIP As String, ByVal databaseName As String, ByVal userName As String, ByVal password As String)

        Try

            Dim remoteConnectionString As String
            Dim remoteConnection As SqlConnection
            Dim remoteCommand As SqlCommand
            Dim remoteAdapter As SqlDataAdapter

            Dim tempDataTable As DataTable

            Dim localConnectionString As String
            Dim localConnection As SqlCeConnection
            Dim localCommand As SqlCeCommand
            Dim localAdapter As SqlCeDataAdapter

            'Create the connection string
            remoteConnectionString = "Data Source = " + serverIP + "; Initial Catalog = " + databaseName + "; User Id = " + userName + "; Password = " + password + ";"
            'Create the connection object with the connection string that was just created
            remoteConnection = New SqlConnection(remoteConnectionString)
            'Open the connection
            remoteConnection.Open()
            'Create a command object associated with the remote connection
            remoteCommand = remoteConnection.CreateCommand
            'Specify the query to send to the remote server 
            remoteCommand.CommandText = "SELECT * FROM TestTable"
            'Create the the remote dataAdapter object
            remoteAdapter = New SqlDataAdapter
            'Set the AcceptChangesDuringFill property to false
            remoteAdapter.AcceptChangesDuringFill = False
            'Assocaite the remote command with the remote adapter
            remoteAdapter.SelectCommand = remoteCommand
            'Create a new instance of the tempDataTable
            tempDataTable = New DataTable
            'Fill the tempDataTable using the remote dataAdapter
            remoteAdapter.Fill(tempDataTable)
            Form_Login.DataGridView.DataSource = tempDataTable

            '----Now we need to move the dataTable contents into the local database----'

            'Create the local connection string
            localConnectionString = "Data Source=|DataDirectory|\LocalTestDatabase.sdf"
            'Create the local connection object with the connection string that was just created
            localConnection = New SqlCeConnection(localConnectionString)
            'Open the connection
            localConnection.Open()
            MsgBox("Local Connection Opened", vbCritical, "Local Connection Opened")
            'Create a command object associated with the local connection
            localCommand = localConnection.CreateCommand

            '.... this is where I don't know what to do
            'I want to put the DataTable into my local database
            localAdapter = New SqlCeDataAdapter
            'How do I use a SQL statement to grab my data from my DataTable called tempDataTable?
            'How do I associate my localAdapter with my localConnection?
            localAdapter.InsertCommand = localCommand       'I don't think this is right
            'I don't have anything for the CommandText property of my localCommand

            localAdapter.Update(tempDataTable)

        Catch ex As Exception

        End Try


    End Sub

When I run this, my DataGridView gets populated with the remote data just like I asked.... the connection to the local database is successfully established, but then it says that the CommandText property hasn't been initialized (which I knew would happen, just don't know what to put there.)

Continued help with this would be awesome!

Thanks again!

Max

 

[jmcilhinney]

What is the second DataAdapter supposed to do? It's supposed to insert data, right? So, it needs an INSERT statement to do it. You need to create Command with the appropriate SQL code and assign it to the InsertCommand property.

 

[TexMax007]

?????????

I understand that the second adapter is going to do my insert, what I don't understand is two things:

1) How do I write SQL code to get the data from my DataTable object called "tempDataTable". (I want to insert into a local database table called "localNewTable")

This table DOES NOT EXIST in a SQL Database, it's an in-memory DataTable object. I tried writing normal SQL code to grab it, and it says the table doesn't exist, which is correct, it doesn't exist in either database; it's a DataTable.

2) How do I make a Create Table statement to create a table into my local database using VB? Also, what about a Drop Table statement?

 

[jmcilhinney]

1. You're not retrieving data from the DataTable. You're inserting the data into the database with DataTable as the source. You need to use a DataAdapter but you don't need a SELECT statement because you're not selecting any data. You need an INSERT statement because you're inserting data. You create a Command with your INSERT statement as its CommandText and assign it to the DataAdapter's InsertCommand property. The data gets saved EXACTLY as it would if it had been retrieved from the same database in the first place. If you want an example of creating an INSERT statement, try here.

2. Pretty much all SQL code gets executed using the appropriate Command object, either via a DataAdapter or else directly, by calling its ExecuteScalar, ExecuteReader or ExecuteNonQuery method. In your case, it's not a query that you're executing so it's ExecuteNonQuery that you call.