Thursday, September 29, 2011 2:23 PM
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
dslearners = LoadAllDataFromLearner
For Each oldrow In dslearners.Tables(0).Rows
changedindex = changedindex + 1
Public Sub Connect(ByVal ConnectionString As String)
_dbConnect = New OleDbConnection
_dbConnect.ConnectionString = ConnectionString
_dbCommand = New OleDbCommand
_dbCommand.Connection = _dbConnect
_ConnectionString = ConnectionString
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
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.MissingSchemaAction = MissingSchemaAction.AddWithKey
'MissingSchemaAction property of your DataAdapter to AddWithKey,
Public Sub UpdateLearnerChangedStatus(ByRef oldrow As DataRow, ByVal changeindex As Integer)
oldrow.Item("Changeid") = changeindex
oldrow.Item("Changed") = 1
Public Sub bulkupdate(ByRef dsdata As DataSet)
'gdaAdapter.UpdateBatchSize = 0 Says its not supported.
Dim objCommandBuilder As New OleDbCommandBuilder(gdaAdapter)
' gdaAdapter.UpdateCommand = objCommandBuilder.GetUpdateCommand
Is this yet another thing not possible in SQLCE, should have I used Access?
- Edited by Alex_Ritchie Thursday, September 29, 2011 2:25 PM spacing
Tuesday, October 04, 2011 8:13 AMModeratorHi Alex_Ritchie,
I will dig into this issue and gave an update as soon as possible. Thanks for your understanding.
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 email@example.com.
Tuesday, October 04, 2011 8:35 AMThanks 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.
Tuesday, October 04, 2011 9:43 AMModerator
Wednesday, October 05, 2011 1:46 PMYes but this only inserts, I need something that bulk updates.
Wednesday, October 05, 2011 1:50 PMModeratorSomthing 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