Batch Data Transfer

[95se5m]

I have a PPC app that I am transferring about 300 records from an SQL connection to my SQLCE database. Currently I am filling a datareader, doing the following.

 

Do while sqlDR.read

'Write record to sqlCE database.

loop

 

This is painfully slow but it works, I do not want to do anything fancy like syncing to sql server through IIS. Is there a way to fill a datareader, then pass said datareader object over to the sqlCE database. I am sure it will still take a moment or two, but I am also sure it will be more efficent than a record by record loop.

 

Any thoughts or examples?

Thank you.

[Nerseus]

If you only have 300 records, I'd use a DataSet and DataAdapter. Read in 300 records into your DataSet and use the DataAdapter to insert the data into the other DB.

 

If you can, I'd use the DTS (data transformation services) provided by SQL Server to do this transfer. You can setup a package to do the transfer and kick that off programmatically, if needed.

 

-ner

[95se5m]

If you only have 300 records, I'd use a DataSet and DataAdapter. Read in 300 records into your DataSet and use the DataAdapter to insert the data into the other DB.

 

If you can, I'd use the DTS (data transformation services) provided by SQL Server to do this transfer. You can setup a package to do the transfer and kick that off programmatically, if needed.

 

-ner

I like the dataset/dataadapter idea I will give that a try, my biggest problem with DTS is that I am trying to go from MSSQL full to sqlCE, not as clean as one might expect, anyway I will give the adapter/set a try.

 

Now, I have worked very little with these items, if I build the dataset, then link the adapter to an identical table in another database will it update?

[95se5m]

Here's a quick update. This is the code I am using, but it does not work, it doesn't fire off any errors to the contrary however. Any assistance would be great.

 

Private Sub TransferSQLtoCE

dbc.DBSource.Open()

Dim ssceconn As New SqlCeConnection(_DataSource)

 

Dim command As String = "SELECT company, phone, address, city, state, zipcode FROM pt6cs_user where user_type='EndUser'"

Dim DS As New DataSet("CustomerData")

Dim adaptorSQL As New System.Data.SqlClient.SqlDataAdapter(command, dbc.DBSource)

adaptorSQL.Fill(DS, "customers")

adaptorSQL.Dispose()

 

da.InsertCustomer(True)

 

command = "SELECT company, phone, address, city, state, zipcode FROM Customers"

ssceconn.Open()

Dim adaptorCE As New System.Data.SqlServerCe.SqlCeDataAdapter(command, ssceconn)

ssceconn.Close()

 

adaptorCE.Update(DS, "customers")

adaptorCE.Dispose()

 

MessageBox.Show("Completed Loading Customers into the SQL Database", "Customer Refresh")

dbc.DBSource.Close()

End Sub