Problem insering data with sqlcebulkcopy

Question

I use the code below to insert data from datatable into Sqlce compact database without problem:

Dim bulkInsert As SqlCeBulkCopy = New SqlCeBulkCopy(connString)
bulkInsert.DestinationTableName = "MyTableFromSqlCeCompact"
bulkInsert.WriteToServer(MyDatatable),

but when I add line with ColumnMappings like below:

Dim bulkInsert As SqlCeBulkCopy = New SqlCeBulkCopy(connString)
bulkInsert.DestinationTableName = "MyTableFromSqlCeCompact"
Dim mapHp As New SqlCeBulkCopyColumnMapping(0, 0)
bulkInsert.ColumnMappings.Add(mapHp)
bulkInsert.WriteToServer(MyDatatable),

there is no exception alert, but data is not inserted to the SqlCe Compact database.

Kindly advise what can go wrong here.

Thank you.

---

TS Lim

Answer 1

Hi TS,

As far as I know, we need to include column name if we want to use SqlCeBulkCopyColumnMapping, for example (please note: ProductID, Name, ProductNumber in SqlCommand):

Using sourceConnection As SqlConnection = _
           New SqlConnection(connectionString)
            sourceConnection.Open()

Dim commandSourceData As SqlCommand = New SqlCommand( _
               "SELECT ProductID, Name, ProductNumber " & _
               "FROM Production.Product;", sourceConnection)
            Dim reader As SqlDataReader = commandSourceData.ExecuteReader

Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
                bulkCopy.DestinationTableName = _
                "dbo.BulkCopyDemoDifferentColumns" 

                ' Set up the column mappings by ordinal. 
                Dim columnMapID As New _
                  SqlBulkCopyColumnMapping(0, 0)
                bulkCopy.ColumnMappings.Add(columnMapID)

                Dim columnMapName As New _
                 SqlBulkCopyColumnMapping(1, 2)
                bulkCopy.ColumnMappings.Add(columnMapName)

                Dim columnMapNumber As New _
                 SqlBulkCopyColumnMapping(2, 1)
                bulkCopy.ColumnMappings.Add(columnMapNumber)

                ' Write from the source to the destination. 
                Try
                    bulkCopy.WriteToServer(reader)

                Catch ex As Exception
                    Console.WriteLine(ex.Message)

                Finally 
                    ' Close the SqlDataReader. The SqlBulkCopy 
                    ' object is automatically closed at the end 
                    ' of the Using block.
                    reader.Close()
                End Try 
            End Using

For more detail information, please refer to the following link:

SqlBulkCopyColumnMapping Constructor (Int32, Int32):
http://msdn.microsoft.com/en-us/library/6styzxks.aspx

---

Allen Li

TechNet Community Support

Answer 2

Hi Allen,

I changed my code following your advise but result still the same. Below are the two procedure tested. Not sure what can go wrong with code in procedure 2.

Procedure 1: able to bulk copy from datatable to table in sqlce database.
Private Sub btnBulkCopy_Click(sender As System.Object, e As System.EventArgs) Handles btnBulkCopy.Click
        Dim dt As New DataTable
        dt.Columns.Add("Name", GetType(String))
        dt.Columns.Add("Cat", GetType(String))
        dt.Columns.Add("Type", GetType(String))

        dt.Rows.Add("MyName1", "Category1", "Type1")
        dt.Rows.Add("MyName2", "Category2", "Type2")
        
        Dim bulkCopy As SqlCeBulkCopy = New SqlCeBulkCopy(connString)

        bulkInsert.DestinationTableName = "myDatabaseTable"
         Try
             bulkCopy.WriteToServer(dt)
          Catch ex As Exception
              MessageBox.Show(ex.Message)           
         End Try
End Sub

Procedure 2: Not exception alert, data from datatable not copied to table in sqlce database
Private Sub btnBulkCopy_Click(sender As System.Object, e As System.EventArgs) Handles btnBulkCopy.Click
        dt.Columns.Add("Name", GetType(String))
        dt.Columns.Add("Cat", GetType(String))
        dt.Columns.Add("Type", GetType(String))

        dt.Rows.Add("MyName1", "Category1", "Type1")
        dt.Rows.Add("MyName2", "Category2", "Type2")

        Using conn As New SqlCeConnection(connString)
            conn.Open()
            Dim commandSourceData As SqlCeCommand = New SqlCeCommand("SELECT Name, Cat, Type FROM myDatabaseTable;", conn)
            Dim reader As SqlCeDataReader = commandSourceData.ExecuteReader

            Dim bulkCopy As SqlCeBulkCopy = New SqlCeBulkCopy(connString)
            Dim mapName As New SqlCeBulkCopyColumnMapping(1, 1)
            Dim mapCat As New SqlCeBulkCopyColumnMapping(2, 2)
            Dim mapType As New SqlCeBulkCopyColumnMapping(3, 3)
            bulkCopy.ColumnMappings.Add(mapName)
            bulkCopy.ColumnMappings.Add(mapCat)
            bulkCopy.ColumnMappings.Add(mapType)
          
            bulkInsert.DestinationTableName = "myDatabaseTable"
            Try
                bulkCopy.WriteToServer(dt)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
              End Try
        End Using
End Sub

---

TS Lim

Answer 3

Are you lokking at the correcet database file? Check your bin/debug folder...

---

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

Answer 4

Hi Erik,

Yes, I am looking at the database at bin/debug folder.  I can see the data copied to the file when I remove the ColumnMapping statements, but data was not copied if they are not removed.

Procedure 2: Not exception alert, data from datatable not copied to table in sqlce database
Private Sub btnBulkCopy_Click(sender As System.Object, e As System.EventArgs) Handles btnBulkCopy.Click
        dt.Columns.Add("Name", GetType(String))
        dt.Columns.Add("Cat", GetType(String))
        dt.Columns.Add("Type", GetType(String))

        dt.Rows.Add("MyName1", "Category1", "Type1")
        dt.Rows.Add("MyName2", "Category2", "Type2")

        Using conn As New SqlCeConnection(connString)
            conn.Open()
            Dim commandSourceData As SqlCeCommand = New SqlCeCommand("SELECT Name, Cat, Type FROM myDatabaseTable;", conn)
            Dim reader As SqlCeDataReader = commandSourceData.ExecuteReader
            Dim bulkCopy As SqlCeBulkCopy = New SqlCeBulkCopy(connString)

            '6 statements below are removed to testing

            'Dim mapName As New SqlCeBulkCopyColumnMapping(0, 0)
            'Dim mapCat As New SqlCeBulkCopyColumnMapping(1, 1)
            'Dim mapType As New SqlCeBulkCopyColumnMapping(2, 2)
            'bulkCopy.ColumnMappings.Add(mapName)
            'bulkCopy.ColumnMappings.Add(mapCat)
            'bulkCopy.ColumnMappings.Add(mapType)
          
            bulkInsert.DestinationTableName = "myDatabaseTable"
            Try
                bulkCopy.WriteToServer(dt)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
              End Try
        End Using
End Sub

---

TS Lim

Answer 5

But why do you need the column mappings, the do not change anything from the deafult behavior?

---

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

Answer 6

I did want to change from the default behavior. The above is to test if the code works before I change to other column mappings.

---

TS Lim

Answer 7

OK, I could have a look to see what is wrong, if you send me a repro project. (Contact me via my blog)

---

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

Answer 8

            Dim bulkCopy As SqlCeBulkCopy = New SqlCeBulkCopy(connString)
            Dim mapName As New SqlCeBulkCopyColumnMapping(1, 1)
            Dim mapCat As New SqlCeBulkCopyColumnMapping(2, 2)
            Dim mapType As New SqlCeBulkCopyColumnMapping(3, 3)
            bulkCopy.ColumnMappings.Add(mapName)
            bulkCopy.ColumnMappings.Add(mapCat)
            bulkCopy.ColumnMappings.Add(mapType)
          

Hi, please modify the codes as following and test again:

Dim bulkCopy As SqlCeBulkCopy = New SqlCeBulkCopy(connString)
             Dim mapName As New SqlCeBulkCopyColumnMapping(0, 0)
             Dim mapCat As New SqlCeBulkCopyColumnMapping(1, 1)
             Dim mapType As New SqlCeBulkCopyColumnMapping(2, 2)
             bulkCopy.ColumnMappings.Add(mapName)
             bulkCopy.ColumnMappings.Add(mapCat)
             bulkCopy.ColumnMappings.Add(mapType)

---

Allen Li

TechNet Community Support

Answer 9

I have tried as suggested, but still can't copy the  data to table in SqlCe database.

---

TS Lim

Answer 10

Hi Allen,

I changed my code following your advise but result still the same. Below are the two procedure tested. Not sure what can go wrong with code in procedure 2.

Procedure 1: able to bulk copy from datatable to table in sqlce database.
Private Sub btnBulkCopy_Click(sender As System.Object, e As System.EventArgs) Handles btnBulkCopy.Click
        Dim dt As New DataTable
        dt.Columns.Add("Name", GetType(String))
        dt.Columns.Add("Cat", GetType(String))
        dt.Columns.Add("Type", GetType(String))

        dt.Rows.Add("MyName1", "Category1", "Type1")
        dt.Rows.Add("MyName2", "Category2", "Type2")
        
        Dim bulkCopy As SqlCeBulkCopy = New SqlCeBulkCopy(connString)

        bulkInsert.DestinationTableName = "myDatabaseTable"
         Try
             bulkCopy.WriteToServer(dt)
          Catch ex As Exception
              MessageBox.Show(ex.Message)           
         End Try
End Sub

Procedure 2: Not exception alert, data from datatable not copied to table in sqlce database
Private Sub btnBulkCopy_Click(sender As System.Object, e As System.EventArgs) Handles btnBulkCopy.Click
        dt.Columns.Add("Name", GetType(String))
        dt.Columns.Add("Cat", GetType(String))
        dt.Columns.Add("Type", GetType(String))

        dt.Rows.Add("MyName1", "Category1", "Type1")
        dt.Rows.Add("MyName2", "Category2", "Type2")

        Using conn As New SqlCeConnection(connString)
            conn.Open()
            Dim commandSourceData As SqlCeCommand = New SqlCeCommand("SELECT Name, Cat, Type FROM myDatabaseTable;", conn)
            Dim reader As SqlCeDataReader = commandSourceData.ExecuteReader

            Dim bulkCopy As SqlCeBulkCopy = New SqlCeBulkCopy(connString)
            Dim mapName As New SqlCeBulkCopyColumnMapping(1, 1)
            Dim mapCat As New SqlCeBulkCopyColumnMapping(2, 2)
            Dim mapType As New SqlCeBulkCopyColumnMapping(3, 3)
            bulkCopy.ColumnMappings.Add(mapName)
            bulkCopy.ColumnMappings.Add(mapCat)
            bulkCopy.ColumnMappings.Add(mapType)
          
            bulkInsert.DestinationTableName = "myDatabaseTable"
            Try
                bulkCopy.WriteToServer(dt)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
              End Try
        End Using
End Sub

---

TS Lim

Private Sub btnBulkCopy_Click(sender As System.Object, e As System.EventArgs) Handles btnBulkCopy.Click
        dt.Columns.Add("Name", GetType(String))
        dt.Columns.Add("Cat", GetType(String))
        dt.Columns.Add("Type", GetType(String))

        dt.Rows.Add("MyName1", "Category1", "Type1")
        dt.Rows.Add("MyName2", "Category2", "Type2")

        Using conn As New SqlCeConnection(connString)
            conn.Open()
            Dim commandSourceData As SqlCeCommand = New SqlCeCommand("SELECT Name, Cat, Type FROM myDatabaseTable;", conn)
            Dim reader As SqlCeDataReader = commandSourceData.ExecuteReader

            Dim bulkCopy As SqlCeBulkCopy = New SqlCeBulkCopy(connString)
     bulkCopy.DestinationTableName = "myDatabaseTable"


            Dim mapName As New SqlCeBulkCopyColumnMapping(0,0)
            Dim mapCat As New SqlCeBulkCopyColumnMapping(1, 1)
            Dim mapType As New SqlCeBulkCopyColumnMapping(2, 2)
            bulkCopy.ColumnMappings.Add(mapName)
            bulkCopy.ColumnMappings.Add(mapCat)
            bulkCopy.ColumnMappings.Add(mapType)
           
       
            Try
                bulkCopy.WriteToServer(dt)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
              End Try
        End Using
End Sub

Answer 11

Have you tried

Answer 12

Yes, I have  tried following the advise from you, but still not able to copy the data.

---

TS Lim

Answer 13

please debug the codes, check bulkCopy content

 bulkCopy.WriteToServer(dt)

Answer 14

By checking the bulkcopy object content,

it shows:
ColumnsMapping count = 3
Destination TableName = myDatabaseTable
Notify After = 0

By expanding ColumnsMapping, it show the Destination Ordinal and Source Ordinal are same as that set in the mappings.

---

TS Lim