Speed up Bulk Insert Dataset records!

[Jay Balapa]

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

 

[Ilya Tumanov [MS]]

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/library/system.data.sqlserverce.sqlcecommand.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/microsoft.public.dotnet.framework.compactframework?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).