Click here to Skip to main content
12,454,164 members (57,037 online)
Rate this:
Please Sign up or sign in to vote.
I'm hoping for a helping hand, a point in the right direction perhaps.

The problem with the code below is the returnTable in SearchMyDBs does not correctly merge data into the table and it returns back empty.

Why does the DataTable I'm using in the .Merge() have 0 rows in it while using the BackgroundWorker, and yet it's full when calling the function as a single-threaded application?

I've done a fair amount of reading and I'm still a little confused on how I should handle this code to get information in the datatables of the main thread from a different thread. (or maybe a different method to use?)


Public Class DBHandle 'holds raw data
   'databases have been filled with data in this form
End Class
Public Class SearchMyDBs 'call for the searches
   Public Function GatherDataForViewer as DataTable
       Dim returnTable as new DataTable("Inventory")
       'more data merges
       return returnTable
   End Function
End Class
Public Class ProjectViewer 'allow user to view data
   Dim _FullTable as datatable
   Public Sub LoadMe()
       'call initialize functions
       Me.BackgroundWorker.WorkerReportsProgress = True
   End Sub
   Private Sub BackgroundWorker1_DoWork(parameters) Handle
       _FullTable = SearchMyDBs.GatherDataForViewer
   End Sub
   Private Sub BackgroundWorker1_Completed(parameters) Handle
       Me.DataGrid.Datasource = _FullTable
   End Sub
End Class
Posted 2-Mar-12 5:47am
Updated 2-Mar-12 8:17am
Simon_Whale 2-Mar-12 12:18pm
does it give you an error?
RavonX 2-Mar-12 12:59pm
nah there's no error. But when it goes through all the .merge functions, nothing is ever added to the temp tables.

The "DBHandle._InventoryDataSet.Inv02" is empty for some reason, yet this is what I filled with initial loads on the program.
SAKryukov 2-Mar-12 12:43pm
Well, "different thread"... different from each one? Yes, it can be different.
RavonX 2-Mar-12 13:01pm
as far as I understand what's happening..

main thread contains the table adapters holding the data im trying to reach

in a backgroundworker thread im trying to access the ^^ data to create a table for the user to view all the data put together.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

I think the issue is that you have a DataTable object called _FullTable that you are using on two separate threads. The code in the BackgroundWorker_DoWork runs on a different thread from the rest of your code. You shouldn't make references to variables that were declared in the original thread directly. Instead of accessing _FullTable you should send the table to the BackgroundWorker_Completed method (Which is running on the original thread) by making use of the DoWorkEventArgs. See the MSDN info on that

Your code above doesn't look complete, as you don't show event argurments in your event handlers. It would look something like this:
Private Sub bgwSync_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bgwSync.DoWork
   e.Result = SearchMyDBs.GatherDataForViewer
End Sub
Private Sub bgwSync_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgwSync.RunWorkerCompleted
   _FullTable - CType(e.Result, DataTable)
End Sub

---- UPDATE -----
If you're sure the .Merge works without the threading, another possible issue that I see is that you're calling
DBHandle._InventoryDataSet.Inv02 within the Do_Work event. (I'm not super confident that's your issue, but you can give it a try at least). In order to pass something INTO a backgroundworker you use the .RunWorkerAsync method with an argument. (MSDN for RunWorkerAsync[^])

You would do something like this:
Public Class SearchMyDBs 'call for the searches
    'Add a parm to this function so you can pass in the inv02 table to use in the merge
    Public Function GatherDataForViewer(ByRef dt As DataTable) As DataTable
        Dim returnTable As New DataTable("Inventory")
        'more data merges
        Return returnTable
    End Function
End Class
Public Class ProjectViewer 'allow user to view data
    Dim _FullTable As DataTable
    Public Sub LoadMe()
        'call initialize functions
        Me.BackgroundWorker.WorkerReportsProgress = True
        Me.BackgroundWorker.RunWorkerAsync(DBHandle._InventoryDataSet.Inv02) 'Pass in the inv02 table as the argument
    End Sub
    Private Sub bgwSync_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bgwSync.DoWork
        'Pull the inv02 out of the argument to use in the GatherDataForViewer function
        Dim dtInv02 As DataTable = CType(e.Argument, DataTable)
        e.Result = SearchMyDBs.GatherDataForViewer(dtInv02)
    End Sub
    Private Sub bgwSync_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgwSync.RunWorkerCompleted
        _FullTable = CType(e.Result, DataTable)
    End Sub
End Class
RavonX 2-Mar-12 14:49pm
I was really excited thinking this would work but it's doing the same thing.

The ReturnTable back in SearchMyDBs.GatherDataForViewer is still returning with 0 rows.

Might it be that the (DBHandle._InventoryDataSet.Inv02) table I refer to in the merge is a different instance of the dataset created in a new thread? and therefor holding no data..?
Kschuler 2-Mar-12 14:59pm
I didn't read all of the comments about your question closely enough. There is definately something else going on with your code. Generally the problem I was trying to fix for you is one that would cause an error to happen. Cross threading issues. I don't generally use the .Merge function on a DataTable, but I'm not sure you are using it correctly. In the sample code you have, it doesn't look like your _FullTable variable has any columns setup in it. And from what I can see in the MSDN on the .Merge, it's expecting that the two tables have at least similar schema. I suggest you take out all your background worker code and focus on getting the .Merge to work without it. Then once that's working you can work on the threading. Here is the MSDN link for the .Merge:
RavonX 2-Mar-12 15:07pm
the .merge works perfectly without the backgroundworker.

with the backgroundworker the .merge results in an empty table with the same schema as the tables i am trying to merge (it does transfer the column setup) but then left with 0 rows where there should be 10,000.
Kschuler 2-Mar-12 15:22pm
I updated my solution with another issue that I see in your code, however I'm not super confident that it's really what the problem is. It's still good practice to get into....passing things into/out of the background worker.
RavonX 2-Mar-12 15:48pm
heeyyoooooo it works!
Now Since I have to merge a bunch of tables I can perhaps create a dataset with reference to all the tables and pass a dataset? It's that or create multiple backgroundworker threads i think..

I have about 30 tables in all.

Thanks Kschuler for helping me to get it working! Much appreciated.
And thanks to SAKryukov for the discussions.
Kschuler 2-Mar-12 15:54pm
Yes you can pass a dataset, or any object really. You could create a completely new class and pass an instance of it as the argument if you find that you have other values you'd like to use on the new thread as well. Glad this worked for you.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

To answer your question "How do I access…": you should understand that there is no difference between threads in this respect. Due to the timing of database access, you should really create a separate thread for your database operations in many or most of the cases. This should not create any problems. If you have a problem of merging, it is not related to the thread you are running.


In response to follow-up discussion on threading(see below):

So, BackgroundWorker is a simplified ready-to-use way of threading; it's more adequate to temporary task. Most database communication last about the run time of the whole application.

You need to do all database operations in the same thread (per database server); and this thread should not be the UI thread. Instead of creation of a thread from time to time, it's the best to create a background thread in the very beginning of the application and use it all the time until the whole application process terminates. You need to reuse the same very thread each time you need to work with the same database server, feeding data (for example, command or other request) to the thread as it is needed.

First of all, such thread should be created using the constructor System.Threading.Thread.Thread. The best way to use the thread is a thread wrapper. I helps to encapsulate thread data, avoid parametrized thread start (which is bad due to required type case); as you can pass an instance (non-static) method to the thread constructor, you pass an implicit "this" parameter (a reference to the wrapper instance), and hence, provide the access to all wrapper member. You can synchronize access to those members from its encapsulated thread and "foreign" thread (say, a UI thread) using lock or other thread synchronization primitives. That is, you centralize thread-safety in a wrapper. Too many benefits to miss this opportunity. Please see my posts on thread wrapper:
How to pass ref parameter to the thread[^],
change paramters of thread (producer) after it started[^] (this one with lock).

Now, you feed data to the thread via the wrapper. In there is not a job for a thread, is should be kept in a wait state wasting CPI no time until waken up by some event with data feed. The best way to do that is using a blocking queue. It can be a queue of data element ("tasks") but it could be event a queue of delegate instances (semantically, direct instructions on what to do). The principles of such queue are very similar to inter-thread invocation applied to UI thread. Please see my Tips&Tricks article where I provide a complete source code with comprehensive code samples:
Simple Blocking Queue for Thread Communication and Inter-thread Invocation[^].

You also should be able to notify UI thread from you non-UI thread, just to update the views, etc. So, you need to use invocation. Please see my past answers where I explain it in detail:
Control.Invoke() vs. Control.BeginInvoke()[^],
Problem with Treeview Scanner And MD5[^].

See also more references on threading:
How to get a keydown event to operate on a different thread in[^],
Control events not firing after enable disable + multithreading[^].

RavonX 2-Mar-12 13:03pm
It has to be something with the background worker .. and therefor i assume the threading.

If I take the same line out of the backgroundworker_dowork, and replace the backgroundworker.Runasync() with the _FullTable = SearchMyDBs.GatherDataForViewer command, the program works just fine.. yet it freezes the program for 2-4 seconds while it loads.
SAKryukov 2-Mar-12 13:06pm
Of course you should use a separate thread. What do you mean "freezes the program"? It should freeze just one thread, if you do it right. Delay in UI presentation in UI is not freezing.
RavonX 2-Mar-12 14:05pm
it does freeze one thread. This is the thread that contains everything (UI & data gathering). Without the backgroundworker it's acting as a single threaded application.

The question again & perhaps more precisely is back in the .merge, why are the tables empty while using backgroundworker? They aren't empty if I use it in the single thread.
SAKryukov 2-Mar-12 14:36pm
I don't think threading caused a problem, but try to do all of database operations in the same thread. Why would you do merge in UI thread? Don't do it.

Better yet, don't use background worker. Use only one thread created with its constructor and permanently use it during the like file of your application.
RavonX 2-Mar-12 14:56pm
I don't know much about threading yet, I think that's what creates the problem.

"but try to do all of database operations in the same thread."
That's what I mentioned in the previous response that I did. And it worked perfectly other than the whole being single-threaded while loading lots of data issue.

"Why would you do merge in UI thread?"
That's what I'm trying to avoid.

"Use only one thread created with its constructor and permanently use it during the like file of your application."
Sorry, I'm not really sure what you mean by this.. but thanks i'll Google this and check into it.
SAKryukov 2-Mar-12 22:06pm
What I mean is:
1) you should use the same thread for all database operations;
2) but the thread mentioned above should be not a UI thread; this is because UI thread should be always very responsive, but and some database operations are too time-consuming.

Now, about the "permanent thread": I'll update my answer above and add some links to my past answers and one article.

SAKryukov 2-Mar-12 22:26pm
The answer is updated, please see after [EDIT].
You can digest it and ask some follow-up questions if you need that.
Good luck,

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.160826.1 | Last Updated 2 Mar 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100