Reading ListView from BackgroundWorker

Question

I've tried doing some searchs but for the life of me I don't seem to be able to find the answer, or a suggested solution that works. Its probably my understanding, but hopefully asking my own question will give me an answer that works :o)

I have a Windows Form Application which consists of one item, ListView1

This ListView has items added to it from a file via a Drag / Drop which is done on the main UI Thread, no background worker, it consists of around 1500 rows.

I'm trying to get a background worker now to read this ListView, but I'm getting a Cross Threading error as ListView1 was not created on the same thread.

The error comes on the simplest of pieces of code, but I don't seem to be able to think of a way around it or implementing an invoke etc.

    For i = 0 To Me.ListView1.Items.Count - 1

        ValueStatement = ValueStatement & "(" & Me.ListView1.Items(i).SubItems(0).Text

        If i = Me.ListView1.Items.Count - 1 Or counter = 500 Then
            CommaTerminate = ";"
        Else
            CommaTerminate = ","
        End If


        For y = 0 To Me.ListView1.Columns.Count - 1
            ValueStatement = ValueStatement & "'" & Me.ListView1.Items(i).SubItems(y).Text & "'"
            If y = Me.ListView1.Columns.Count - 1 Then
                ValueStatement = ValueStatement & ")"
            Else
                ValueStatement = ValueStatement & ","
            End If

        Next
        ValueStatement = ValueStatement & CommaTerminate & vbNewLine


        If counter = 500 Then
            SQLStatement = "INSERT INTO RAW_CLI_DATA_" & GlobalVariables.CDR_Company & " VALUES " & vbNewLine & ValueStatement
            GenericDatabaseRequest(SQLStatement, "Loading RAW table with data..")
            counter = 0
            ValueStatement = ""
        End If

        counter = counter + 1
    Next

The error comes on the line ValueStatement = ValueStatement & "(" & Me.ListView1.Items(i).SubItems(0).Text

Thanks for any help!

Answer 1

It sounds like you went down the wrong road early on. The ListView is supremely illsuited for database ops:

• Everything is contained as String which means somewhere you will have code to convert it to other types
• It does not support databinding which means you have to manually create rows...
• ... then later iterate them to fish the data back out.

A DataGridView and DataTable would be simpler: When the user enters data into the control and it would be stored in the table and as the proper type. Setting the DataTable as the Datasource, the DataGridView would create the display (rows and columns) for you.

Some of the time it takes will be consumed by SQLite to perform the INSERT, but it also looks like you are spending a lot of time iterating and concatenating SQL. It's usually better to work with the data than the user's View of it anyway, so extract and pass the data to the worker.

First, suck the data out of the ListView into a String()() container:

Dim data = lv.Items.
    Cast(Of ListViewItem).
    Select(Function(s) New String() {s.SubItems(0).Text,
                                     s.SubItems(1).Text,
                                     s.SubItems(2).Text}).
        ToArray()

Then pass it to the BackGroundWorker:

bgw.RunWorkerAsync(data)

The DoWork Event:

Private Sub bgw_DoWork(sender As Object, e As DoWorkEventArgs) Handles bgw.DoWork

    ' unbox the data
    Dim dataToInsert = CType(e.Argument, String()())

    For n As Int32 = 0 To 2
        Console.WriteLine("[{0}], [{1}], [{2}]", dataToInsert(n)(0),
                          dataToInsert(n)(1),
                          dataToInsert(n)(2))
    Next

End Sub

Results:

[Patient Tempest], [Lorem ipsum dolor sit], [Swordfish]
[Sour Priestess], [hendrerit nibh tempor], [Perch]
[Frozen Justice], [Interdum ex felis], [Swordfish]

It correctly prints the random data I put into the LV.

This will allow you to process the ListView Data in the BackGroundWorker but it wont really save any time, it just keeps the UI unlocked. The real problem is elsewhere, probably in the DB ops.