By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,771 Members | 1,669 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,771 IT Pros & Developers. It's quick & easy.

DataTable in Thread

P: n/a
I've got a routine that builds a table using different queries, different SQL
Tables, and adding custom fields.

It takes a while to run (20 - 45 seconds) so I wrote a thread to handle the
table population.

Whenever I call the thread, I pass it a structure containing the table and a
few other parameters. The table goes in as a reference, but the other items
are passed normally.

The thread fills the table just fine and I can step through the thread to
see that the table contains about 7,000 to 10,000 rows (depending on the type
of call).

However, whenever the thread ends, the table that I passed as a reference is
empty.

What are some ideas of getting the data back from the thread along with my
table?
Aug 24 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Depending on how often other threads will need to access the resource
being passed by reference, generally a reference type is the best
approach. The behavior you're describing (reference type being set to
null after thread processes) is not the expected behavior. There has
to be something in the code that's causing that reference type to
become null. Without seeing a code example it's hard to diagnose why
that may be.

If you're having to implement lock() blocks thought your worker
thread, you may consider passing in two DataTable objects by reference
and allow the worker thread to use one during processing (lock free)
and once the rows are all populated call the .Copy() method of the
populated table to create a populated clone in the other table, then
destroy DataTable used during the processing and only place your lock
on the copied table. You'll just have to experiment to see how
resource intensive this would be. If you're dataset is going to be
consistantly be in 7000-10000 row ballpark I think the copy approach
might work best.

Could you post some sample code of how you are instantiating the
thread and passing the table in?


Aug 24 '08 #2

P: n/a
Would BackGroundWorkerThread from Net.20 work for this
and you would use the WorkerCompleted event to get ur datatable , then
destroy the thread
DaveL

"jp2msft" <jp*****@discussions.microsoft.comwrote in message
news:C5**********************************@microsof t.com...
I've got a routine that builds a table using different queries, different
SQL
Tables, and adding custom fields.

It takes a while to run (20 - 45 seconds) so I wrote a thread to handle
the
table population.

Whenever I call the thread, I pass it a structure containing the table and
a
few other parameters. The table goes in as a reference, but the other
items
are passed normally.

The thread fills the table just fine and I can step through the thread to
see that the table contains about 7,000 to 10,000 rows (depending on the
type
of call).

However, whenever the thread ends, the table that I passed as a reference
is
empty.

What are some ideas of getting the data back from the thread along with my
table?

Aug 24 '08 #3

P: n/a
On Sun, 24 Aug 2008 09:06:19 -0700, jp2msft
<jp*****@discussions.microsoft.comwrote:
[...]
The thread fills the table just fine and I can step through the thread to
see that the table contains about 7,000 to 10,000 rows (depending on the
type
of call).

However, whenever the thread ends, the table that I passed as a
reference is empty.
As others have suggested, you should show some code. Had the thread
really been filling the same DataTable you passed to it, then the table
you passed to it would not be empty when the thread was done. Obviously
the table you see in the worker thread is not the same table you think it
is.

But without any code, there's no way for anyone to explain _why_ that is
the case. We can only state that it is.

Pete
Aug 25 '08 #4

P: n/a
In the main body, the DataTable is constructed with the SQL statement:

DataTable table = new DataTable("System_ID");
Global.FillTable(ref table, "EXEC AcpReport_GetSystemIDs");
do {
Thread.Sleep(100);
Application.DoEvents();
} while (Global.Working == true);
foreach (DataRow row in table.Rows) { // add the generic search first
cboFilter2.Items.Add(row["System_ID"].ToString().Trim());
}

The Global FillTable method passes information to and creates the Thread:

public static void FillTable(ref DataTable table, string sqlCommand) {
if (Working == false) {
Working = true;
DataTableThreader dtParam = new DataTableThreader(ref table, sqlCommand);
Thread dbThread = new Thread(fillTableThread);
dbThread.IsBackground = true;
dbThread.Priority = ThreadPriority.AboveNormal;
dbThread.Start((object)dtParam);
}
}

private static void fillTableThread(object table) {
DataTableThreader param = (DataTableThreader)table;
SqlDataAdapter da = new SqlDataAdapter(param.sqlString, g_conn);
int records = -1;
try {
records = da.Fill(param.table);
} catch (SqlException err) {
Console.WriteLine(err.Message);
}
Working = false;
}

In the actual thread above, the SqlDataAdapter properly Fills the DataTable
and the integer 'records' indicates the number of records that are in the
table (I debug into the DataTable to confirm that it actually has the number
of records that it should).

Here's a curious bit: If I name my DataTable using the TableName property
and send it to my thread, it comes back with that name. The DataTable is no
null - it just does not contain any rows (I have not checked the columns).

"JDeats" wrote:
Depending on how often other threads will need to access the resource
being passed by reference, generally a reference type is the best
approach. The behavior you're describing (reference type being set to
null after thread processes) is not the expected behavior. There has
to be something in the code that's causing that reference type to
become null. Without seeing a code example it's hard to diagnose why
that may be.

If you're having to implement lock() blocks thought your worker
thread, you may consider passing in two DataTable objects by reference
and allow the worker thread to use one during processing (lock free)
and once the rows are all populated call the .Copy() method of the
populated table to create a populated clone in the other table, then
destroy DataTable used during the processing and only place your lock
on the copied table. You'll just have to experiment to see how
resource intensive this would be. If you're dataset is going to be
consistantly be in 7000-10000 row ballpark I think the copy approach
might work best.

Could you post some sample code of how you are instantiating the
thread and passing the table in?


Aug 25 '08 #5

P: n/a
Not really. I've got some background worker threads in the project already to
fill Tree controls. There would not be any advantage to using a background
worker thread in this context over a standard thread. The standard thread is
processing the data the way it is supposed to, its just the data is getting
lost before it gets back.

"daveL" wrote:
Would BackGroundWorkerThread from Net.20 work for this
and you would use the WorkerCompleted event to get ur datatable , then
destroy the thread
DaveL

"jp2msft" <jp*****@discussions.microsoft.comwrote in message
news:C5**********************************@microsof t.com...
I've got a routine that builds a table using different queries, different
SQL
Tables, and adding custom fields.

It takes a while to run (20 - 45 seconds) so I wrote a thread to handle
the
table population.

Whenever I call the thread, I pass it a structure containing the table and
a
few other parameters. The table goes in as a reference, but the other
items
are passed normally.

The thread fills the table just fine and I can step through the thread to
see that the table contains about 7,000 to 10,000 rows (depending on the
type
of call).

However, whenever the thread ends, the table that I passed as a reference
is
empty.

What are some ideas of getting the data back from the thread along with my
table?


Aug 25 '08 #6

P: n/a
While deliberating over the code I wrote this weekend, it occured to me that
DataTables might not be thread safe.

The main thing I wanted to know in the post was if a DataTable could be
passed to a thread.

From the responses, it seems obvious that it can. However, looking over my
original question, I see I never stated my main concern.

Code was posted in response to JDeats post.

"Peter Duniho" wrote:
On Sun, 24 Aug 2008 09:06:19 -0700, jp2msft
<jp*****@discussions.microsoft.comwrote:
[...]
The thread fills the table just fine and I can step through the thread to
see that the table contains about 7,000 to 10,000 rows (depending on the
type
of call).

However, whenever the thread ends, the table that I passed as a
reference is empty.

As others have suggested, you should show some code. Had the thread
really been filling the same DataTable you passed to it, then the table
you passed to it would not be empty when the thread was done. Obviously
the table you see in the worker thread is not the same table you think it
is.

But without any code, there's no way for anyone to explain _why_ that is
the case. We can only state that it is.

Pete
Aug 25 '08 #7

P: n/a
Ouch. Well, the good news is, since we all learn best from our mistakes,
there's a lot of opportunity for learning here. :)

With respect to the code you posted, the first thing I'll note is that at
first blush, it looks basically "correct" in the sense that it looks like
it _should_ do what you want it to. It's not "correct" in the sense of
being _good_ code, but it ought to at least get the job done.

Since apparently it doesn't actually get the job done, that means there's
something to the question you still haven't told us. For example, we have
no idea what the DataTableThreader class is or does. Hopefully it's
nothing but a container for your arguments, but it might be something
else. We just don't know.

There are a number of areas in which it's more complicated to come up with
a concise-but-complete code sample. Code that involves querying a
database is one of them. However, because one can in fact create
DataTables and DataSets in memory, it's not impossible. It just takes a
little reworking. If you are unable to figure this out yourself, I'll
suggest that we _really_ need a true concise-but-complete code sample.
That is, it needs to be short, including _nothing_ that isn't directly
related to reproducing the problem. And it needs to be complete:
something that we can copy and paste into an empty file, and compile
without having to add _anything_ to produce a working executable that can
be debugged.

Without that, it's not really possible to answer the question.

Now, that said, let's look at the code you posted, and then I'll provide
an alternative that IMHO would work better (and which will show that while
you might think that BackgroundWorker wasn't useful, "daveL"'s suggestion
was actually on-point and relevant :) )...

On Mon, 25 Aug 2008 06:16:01 -0700, jp2msft
<jp*****@discussions.microsoft.comwrote:
In the main body, the DataTable is constructed with the SQL statement:

DataTable table = new DataTable("System_ID");
Global.FillTable(ref table, "EXEC AcpReport_GetSystemIDs");
It is almost certain that you should not be passing the "table" argument
by "ref". It's impossible to know for sure, since you didn't post the
code for DataTableThreader. But the "ref" keyword is for when the callee
needs to modify the original _variable_ being passed. From all
indications, you don't want your code to do that. You just want the
callee to modify the DataTable you've already allocated, not replace that
DataTable with some other one.
do {
Thread.Sleep(100);
Application.DoEvents();
} while (Global.Working == true);
The above is a big warning sign that you've got some problem. There are
two things that you practically never need to call from your own code:
Thread.Sleep() and Application.DoEvents(). You've done both.

The Thread.Sleep() method is good for two things: yielding to other
threads on purpose (in which case you pass 0 or 1, depending on which
threads you want to yield to) or delaying a thread for some very specific
amount of time, because _that time_ is important for the behavior of your
code.

Calling Thread.Sleep() with an arbitrarily selected value of 100ms is a
clear indication that you shouldn't be calling it at all.

Application.DoEvents() is just plain bad news. It creates re-entrancies
in code that almost certainly was not written with reentrancy in mind, and
it means you've co-opted the main GUI thread for something that's blocking
when it shouldn't.
foreach (DataRow row in table.Rows) { // add the generic search first
cboFilter2.Items.Add(row["System_ID"].ToString().Trim());
}

The Global FillTable method passes information to and creates the Thread:

public static void FillTable(ref DataTable table, string sqlCommand) {
if (Working == false) {
Working = true;
DataTableThreader dtParam = new DataTableThreader(ref table,
sqlCommand);
Thread dbThread = new Thread(fillTableThread);
dbThread.IsBackground = true;
dbThread.Priority = ThreadPriority.AboveNormal;
Never increase your thread priority. As with Thread.Sleep() and
Application.DoEvents(), it's practically never the right thing to do. And
it's even more dangerous than Application.DoEvents(), because thread
priorities are very important to Windows for the proper management of
resources, and doing it incorrectly can lead to the system becoming
unresponsive or, even worse, unstable.

It's especially pointless and potentially dangerous to raise the priority
of a thread that's mostly doing i/o. Such as, a thread that's querying a
SQL database.

That's about all the really important stuff to note in the code you
posted. So here's how _I_ would have written it (based on what you've
posted so far, that is...I don't know enough about the larger design to
comment on how appropriate it really is :) )...

Wherever you had the "initialize DataTable" code before:

DataTable table = new DataTable("System_ID");

using (BackgroundWorker worker = new BackgroundWorker())
{
worker.DoWork += delegate { Global.FillTable(table, "EXEC
AcpReport_GetSystemIDs"); };
worker.RunWorkerCompleted += delegate
{
foreach (DataRow row in table.Rows)
{ // add the generic search first
cboFilter2.Items.Add(row["System_ID"].ToString().Trim());
}
};
worker.RunWorkerAsync();
}

and in your "Global" class:

public static void FillTable(DataTable table, string sqlCommand)
{
SqlDataAdapter da = new SqlDataAdapter(sqlCommand, g_conn);
int records = -1;

try
{
records = da.Fill(table);
}
catch (SqlException err)
{
Console.WriteLine(err.Message);
}
}

Note: I didn't actually compile the above, so my apologies if there's some
typographical error. That's the basic idea though. No need for all the
other awkward sleeping, setting flags, busy-waiting, etc. And yes, it
uses BackgroundWorker. :)

I suppose there's a chance that changing the code to the above would fix
whatever problem you have. But if not, you really do need to post a
concise-but-complete code sample that demonstrates the problem.

Pete
Aug 25 '08 #8

P: n/a
Don't worry, Mr. Duniho. I don't bruse easily. :) One reason for having a
nice alias is so people that know me won't say, "Can you believe he asked
that stupid question?"

I tried passing the DataTable without the 'ref' keyword like you suggested,
but whenever I do that all of my tables come back empty.

I did impliment the background worker more, as you instructed, and it is
much faster than Microsoft's example where they specify all of their
parameters.

I've got progress bars and status bars on my MDI form, and getting them to
update their text in my existing background worker's ProgressChanged event
doesn't seem to happen like I would like it to. Sleep and DoEvents were added
in hopes that my main GUI could catch everything, but it didn't seem to help.
They've been removed.

I looked into Microsoft's Help included with VS2005 Pro for Thread.Sleep,
and there is no mention of sending 0 or 1, but I'll use it.

"Peter Duniho" wrote:
Ouch. Well, the good news is, since we all learn best from our mistakes,
there's a lot of opportunity for learning here. :)

With respect to the code you posted, the first thing I'll note is that at
first blush, it looks basically "correct" in the sense that it looks like
it _should_ do what you want it to. It's not "correct" in the sense of
being _good_ code, but it ought to at least get the job done.

Since apparently it doesn't actually get the job done, that means there's
something to the question you still haven't told us. For example, we have
no idea what the DataTableThreader class is or does. Hopefully it's
nothing but a container for your arguments, but it might be something
else. We just don't know.

There are a number of areas in which it's more complicated to come up with
a concise-but-complete code sample. Code that involves querying a
database is one of them. However, because one can in fact create
DataTables and DataSets in memory, it's not impossible. It just takes a
little reworking. If you are unable to figure this out yourself, I'll
suggest that we _really_ need a true concise-but-complete code sample.
That is, it needs to be short, including _nothing_ that isn't directly
related to reproducing the problem. And it needs to be complete:
something that we can copy and paste into an empty file, and compile
without having to add _anything_ to produce a working executable that can
be debugged.

Without that, it's not really possible to answer the question.

Now, that said, let's look at the code you posted, and then I'll provide
an alternative that IMHO would work better (and which will show that while
you might think that BackgroundWorker wasn't useful, "daveL"'s suggestion
was actually on-point and relevant :) )...

On Mon, 25 Aug 2008 06:16:01 -0700, jp2msft
<jp*****@discussions.microsoft.comwrote:
In the main body, the DataTable is constructed with the SQL statement:

DataTable table = new DataTable("System_ID");
Global.FillTable(ref table, "EXEC AcpReport_GetSystemIDs");

It is almost certain that you should not be passing the "table" argument
by "ref". It's impossible to know for sure, since you didn't post the
code for DataTableThreader. But the "ref" keyword is for when the callee
needs to modify the original _variable_ being passed. From all
indications, you don't want your code to do that. You just want the
callee to modify the DataTable you've already allocated, not replace that
DataTable with some other one.
do {
Thread.Sleep(100);
Application.DoEvents();
} while (Global.Working == true);

The above is a big warning sign that you've got some problem. There are
two things that you practically never need to call from your own code:
Thread.Sleep() and Application.DoEvents(). You've done both.

The Thread.Sleep() method is good for two things: yielding to other
threads on purpose (in which case you pass 0 or 1, depending on which
threads you want to yield to) or delaying a thread for some very specific
amount of time, because _that time_ is important for the behavior of your
code.

Calling Thread.Sleep() with an arbitrarily selected value of 100ms is a
clear indication that you shouldn't be calling it at all.

Application.DoEvents() is just plain bad news. It creates re-entrancies
in code that almost certainly was not written with reentrancy in mind, and
it means you've co-opted the main GUI thread for something that's blocking
when it shouldn't.
foreach (DataRow row in table.Rows) { // add the generic search first
cboFilter2.Items.Add(row["System_ID"].ToString().Trim());
}

The Global FillTable method passes information to and creates the Thread:

public static void FillTable(ref DataTable table, string sqlCommand) {
if (Working == false) {
Working = true;
DataTableThreader dtParam = new DataTableThreader(ref table,
sqlCommand);
Thread dbThread = new Thread(fillTableThread);
dbThread.IsBackground = true;
dbThread.Priority = ThreadPriority.AboveNormal;

Never increase your thread priority. As with Thread.Sleep() and
Application.DoEvents(), it's practically never the right thing to do. And
it's even more dangerous than Application.DoEvents(), because thread
priorities are very important to Windows for the proper management of
resources, and doing it incorrectly can lead to the system becoming
unresponsive or, even worse, unstable.

It's especially pointless and potentially dangerous to raise the priority
of a thread that's mostly doing i/o. Such as, a thread that's querying a
SQL database.

That's about all the really important stuff to note in the code you
posted. So here's how _I_ would have written it (based on what you've
posted so far, that is...I don't know enough about the larger design to
comment on how appropriate it really is :) )...

Wherever you had the "initialize DataTable" code before:

DataTable table = new DataTable("System_ID");

using (BackgroundWorker worker = new BackgroundWorker())
{
worker.DoWork += delegate { Global.FillTable(table, "EXEC
AcpReport_GetSystemIDs"); };
worker.RunWorkerCompleted += delegate
{
foreach (DataRow row in table.Rows)
{ // add the generic search first
cboFilter2.Items.Add(row["System_ID"].ToString().Trim());
}
};
worker.RunWorkerAsync();
}

and in your "Global" class:

public static void FillTable(DataTable table, string sqlCommand)
{
SqlDataAdapter da = new SqlDataAdapter(sqlCommand, g_conn);
int records = -1;

try
{
records = da.Fill(table);
}
catch (SqlException err)
{
Console.WriteLine(err.Message);
}
}

Note: I didn't actually compile the above, so my apologies if there's some
typographical error. That's the basic idea though. No need for all the
other awkward sleeping, setting flags, busy-waiting, etc. And yes, it
uses BackgroundWorker. :)

I suppose there's a chance that changing the code to the above would fix
whatever problem you have. But if not, you really do need to post a
concise-but-complete code sample that demonstrates the problem.

Pete
Aug 25 '08 #9

P: n/a
On Mon, 25 Aug 2008 12:12:02 -0700, jp2msft
<jp*****@discussions.microsoft.comwrote:
Don't worry, Mr. Duniho. I don't bruse easily. :) One reason for having a
nice alias is so people that know me won't say, "Can you believe he asked
that stupid question?"
Heh...I suppose. But, there are no stupid questions. Only stupid
answers. :)
I tried passing the DataTable without the 'ref' keyword like you
suggested,
but whenever I do that all of my tables come back empty.
Well, again, without actual code to look at it's hard to say what that's
about. But passing "by reference" (which is what "ref" does) has to do
with the value of the variable used as the argument, not the object that
variable references.
I did impliment the background worker more, as you instructed, and it is
much faster than Microsoft's example where they specify all of their
parameters.
Yes, anonymous methods can make a lot of these delegate-based APIs a lot
cleaner. The whole "passing arguments" thing, for example, gets a lot
simpler.
[...]
I looked into Microsoft's Help included with VS2005 Pro for Thread.Sleep,
and there is no mention of sending 0 or 1, but I'll use it.
But, now that you have a non-sleep technique to use, I assume not in this
case. :)

Pete
Aug 25 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.