Using Adapter.Update for bulk update to SQLCE

Question

I am having an issue with the Adapter.Update command in that it appears to do nothing. What I am trying to do is update a database based on changes I have made in a dataset. I got this working by updating each change individually in a loop using dbcommand.executenonequery and an sql string but I found this too slow when the datatable exceeded more than 5000 records, as it is running 5000 update commands.

 

What I am trying to do is replace this with one Update as a batch. All this code does do at the moment is just update every record’s changeid field to 1. At least it should. Note this is heavily simplified code as this is a proof of concept at the moment and I am aware this can be done with just one update sql. It will be much more complex if I can get this bit working.

 

I have based the majority of this from one of the examples provided by microsoft. Note the dataset values are populated when I call update. It takes a second or two to run the update command but the changes are not reflected in my database. There is no error. One thing I noted is that all the fields came back readonly when I used the FillSchema command.

 

Public Sub buildComparisontReport

Connect(connectionstring)

dslearners = LoadAllDataFromLearner

For Each oldrow In dslearners.Tables(0).Rows

      UpdateLearnerChangedStatus(oldrow, changedindex)

changedindex = changedindex + 1

next

bulkupdate(dslearners)

end sub

  

Public Sub Connect(ByVal ConnectionString As String)

_dbConnect = New OleDbConnection

_dbConnect.ConnectionString = ConnectionString

_dbCommand = New OleDbCommand

_dbConnect.Open()

_dbCommand.Connection = _dbConnect

_ConnectionString = ConnectionString

End Sub

 

 

Public Function LoadAllDataFromLearner() As DataSet

Dim dsresult As DataSet

Dim colArr(1) As DataColumn

sqlstr = "SELECT * FROM Learner WHERE ProcessId = 0 "

gtheLogger.writeEntry(0, "ComparisontSQL.LoadAllDataFromLearner: SQL [" & sqlstr & "]")

dsresult = loadbulkdata(sqlstr)

'For some reason these columns come in as read only, so I override it to false.

dsresult.Tables("Learner").Columns("ChangeId").ReadOnly = False

dsresult.Tables("Learner").Columns("Changed").ReadOnly = False

Return dsresult

End Function

 

 

Public Function loadbulkdata(ByVal sqlstr As String) As DataSet

Dim dsresult As New DataSet

_dbCommand.CommandText = sqlstr

gdaAdapter = New OleDbDataAdapter

gdaAdapter.SelectCommand = _dbCommand

gdaAdapter.FillSchema(dsresult, SchemaType.Source, "Learner")

gdaAdapter.Fill(dsresult, "Learner")

gdaAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

'MissingSchemaAction property of your DataAdapter to AddWithKey,

Return dsresult

End Function

 

Public Sub UpdateLearnerChangedStatus(ByRef oldrow As DataRow, ByVal changeindex As Integer)

oldrow.BeginEdit()

oldrow.Item("Changeid") = changeindex

oldrow.Item("Changed") = 1

oldrow.EndEdit()

End

 

Public Sub bulkupdate(ByRef dsdata As DataSet)

'gdaAdapter.UpdateBatchSize = 0 Says its not supported.

Dim objCommandBuilder As New OleDbCommandBuilder(gdaAdapter)

' gdaAdapter.UpdateCommand = objCommandBuilder.GetUpdateCommand

gdaAdapter.Update(dsdata, "Learner")

End Sub

Is this yet another thing not possible in SQLCE, should have I used Access?

Answer 1

Hi Alex_Ritchie,

I will dig into this issue and gave an update as soon as possible. Thanks for your understanding.

---

Best Regards,
Stephanie Lv

Forum Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact [e-mail].

Answer 2

Thanks for your reply,  in the mean time I have gone back to my executenonequery method and attempted to make the code as efficent as possible, though it has only saved me about 10% on runtime.

Answer 3

Have you had a look at: http://sqlcebulkcopy.codeplex.com ?

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 4

Yes but this only inserts, I need something that bulk updates.

Answer 5

Somthing like this then: http://stackoverflow.com/questions/3161892/update-insert-to-a-table-using-sqlceresultset

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog