Speed up Bulk Insert Dataset records!

28 Jun 2006 8:44 PM

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

28 Jun 2006 9:41 PM

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).

Show quote

"Jay Balapa" <jbal***@hotmail.com> wrote in message
news:eGZMJPvmGHA.1488@TK2MSFTNGP02.phx.gbl...
> 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
>
>
>