DataAdapter.Update() should work assuming these rows are in correct state
(added/modified). If all rows are marked unchanged, Update() would do
nothing.
If you can't ensure correct state of rows, you can do it manually. You
should rewrite your code using prepared commands with parameters, that's
what DataAdapter would use and it should be significantly faster.
Here's sample (if you're on SQL CE 2.0 you should use unnamed parameters
marked as '?'):
http://msdn2.microsoft.com/en-us/lib...d.prepare.aspx
Also, it's a good idea to wrap it up in transaction.
--
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/...ramework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Jay Balapa" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello,
>
> I have an winform compact framework app which returns a dataset from
> webservice .
>
> I need to bulkinsert that database into my SQLCE database.
>
>
> Right now I iterate row by row and insert rows one at a time. To insert
> 1500 rows it takes me 10minutes.
>
> Which is unaceptable. Can you help me speed it up? I have tried using
> Dataset.update() and it does not work.
>
>
> Thanks.
>
>
> Here is the code snapshot-
> Public Shared Sub SetSyncDataset(ByVal myOldDataset As DataSet)
>
>
>
> For Each myTable As DataTable In myOldDataset.Tables
>
>
>
> Dim insertBuilder As StringBuilder = New StringBuilder()
>
> Dim deleteBuilder As StringBuilder = New StringBuilder()
>
> deleteBuilder.Append("DELETE FROM ")
>
> deleteBuilder.Append(myTable.TableName)
>
> 'MessageBox.Show(myTable.TableName)
>
>
>
> SQLDatabase.UpdateRecord(deleteBuilder.ToString())
>
> insertBuilder.Append("INSERT INTO ")
>
> insertBuilder.Append(myTable.TableName)
>
> Dim FirstColumn As Boolean = True
>
>
>
> For Each myRow As DataRow In myTable.Rows
>
> Dim valueBuilder As StringBuilder = New StringBuilder()
>
> Dim fieldBuilder As StringBuilder = New StringBuilder()
>
> fieldBuilder.Append("(")
>
>
>
> valueBuilder.Append("(")
>
> Dim FirstRowColumn As Boolean = True
>
> For Each myColumn As DataColumn In myTable.Columns
>
>
>
>
>
>
>
> If (myRow(myColumn).ToString().Length > 0) Then
>
> If (FirstRowColumn = True) Then
>
> FirstRowColumn = False
>
>
>
> Else
>
> valueBuilder.Append(",")
>
> fieldBuilder.Append(",")
>
> End If
>
>
>
> fieldBuilder.Append(myColumn.ColumnName)
>
> If (myColumn.ColumnName = "Changed") Then
>
> valueBuilder.Append(myRow(myColumn))
>
> ElseIf (myColumn.DataType Is GetType(String)) Then
>
>
>
> valueBuilder.Append("'")
>
> valueBuilder.Append(myRow(myColumn))
>
> valueBuilder.Append("'")
>
> ElseIf (myColumn.ColumnName = "Changed") Then
>
> valueBuilder.Append("0")
>
> ElseIf (myColumn.DataType Is GetType(DateAndTime))
> Then
>
> valueBuilder.Append("'")
>
> valueBuilder.Append(myRow(myColumn))
>
> valueBuilder.Append("'")
>
> ElseIf (myColumn.DataType Is GetType(DateTime))
> Then
>
> valueBuilder.Append("'")
>
> valueBuilder.Append(myRow(myColumn))
>
> valueBuilder.Append("'")
>
> ElseIf (myColumn.DataType Is GetType(Boolean)) Then
>
> 'valueBuilder.Append("'")
>
> If (myRow(myColumn) = True) Then
>
> valueBuilder.Append("1")
>
> Else
>
> valueBuilder.Append("0")
>
>
>
> End If
>
>
>
>
>
> 'valueBuilder.Append("'")
>
> Else
>
> valueBuilder.Append(myRow(myColumn))
>
>
>
> End If
>
> End If
>
>
>
>
>
>
>
>
>
> Next
>
> fieldBuilder.Append(") VALUES ")
>
> valueBuilder.Append(")")
>
> Dim insertRow As StringBuilder = New StringBuilder()
>
> insertRow.Append(insertBuilder.ToString())
>
> insertRow.Append(fieldBuilder.ToString())
>
> insertRow.Append(valueBuilder.ToString())
>
>
>
>
>
> 'MessageBox.Show(insertRow.ToString())
>
> SQLDatabase.UpdateRecord(insertRow.ToString())
>
>
>
>
>
> Next
>
> Next
>
>
>
> End Sub
>
>
>
>
>
> Public Shared Sub UpdateRecord(ByVal myString As String)
>
> Dim cn As System.Data.SqlServerCe.SqlCeConnection
>
> Dim cb As SqlCeCommand
>
> Try
>
>
>
>
>
> cn = New System.Data.SqlServerCe.SqlCeConnection(connStr)
>
> cn.Open()
>
>
>
> cb = New SqlCeCommand(myString)
>
> cb.Connection = cn
>
> cb.ExecuteNonQuery()
>
>
>
>
>
> Catch e As SqlCeException
>
> ' Use SqlCeException properties if you need specific
>
> ' application logic depending on the error condition
>
> '
>
>
>
>
>
> MessageBox.Show(e.Message)
>
> MessageBox.Show(e.Source)
>
> Finally
>
>
>
> ' Close the database.
>
> cb.Dispose()
>
>
>
> cn.Close()
>
>
>
>
>
> End Try
>
>
>
>
>
>
>
> End Sub
>
>
>
Similar Threads
