Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Threading database VB.NET , +
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?)
 
Thanks
 
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")
       returnTable.Merge(DBHandle._InventoryDataSet.Inv02)
       '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.Show()
       Me.BackgroundWorker.WorkerReportsProgress = True
       Me.BackgroundWorker.RunWorkerAsync()
   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 6:47am
RavonX313
Edited 2-Mar-12 9:17am
v2
Comments
Simon_Whale at 2-Mar-12 12:18pm
   
does it give you an error?
RavonX at 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 at 2-Mar-12 12:43pm
   
Well, "different thread"... different from each one? Yes, it can be different.
--SA
RavonX at 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
good
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
here[^].
 
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")
            returnTable.Merge(dt)
            '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.Show()
            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
  Permalink  
v2
Comments
RavonX at 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 at 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:
http://msdn.microsoft.com/en-us/library/system.data.datatable.merge.aspx
RavonX at 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 at 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 at 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 at 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
good
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.
 
[EDIT]
 
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 vb.net[^],
Control events not firing after enable disable + multithreading[^].
 
—SA
  Permalink  
v2
Comments
RavonX at 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 at 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.
--SA
RavonX at 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 at 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.
--SA
RavonX at 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 at 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.
 
--SA
SAKryukov at 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,
-SA

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

  Print Answers RSS
0 Marcin Kozub 330
1 OriginalGriff 256
2 Sergey Alexandrovich Kryukov 215
3 Praneet Nadkar 197
4 Richard MacCutchan 182
0 OriginalGriff 8,048
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 2 Mar 2012
Copyright © CodeProject, 1999-2014
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