SqlDataAdapter, SqlConnection, SqlCommand - Thread Safety

[Guest]

Hello,

I'm using a lot of threads, each one using it's own instance of my
TableAdapter, Connection and DataTables. But all of them will access the same
table at the same sql server maybe at the same time (optimistic concurrency,
etc.)

I found the following in the Documentation:
Any public static (Shared in Visual Basic) members of this type are thread
safe. Any instance members are not guaranteed to be thread safe.

Well, what does this mean exactly?
As I understand, this may not be any problem for me.
Only, if I would use e.g. the same instance of a SqlDataAdapter in more than
one threads (what I'm doing not) I may run into problems.
Is this correct?

Thanks in advance,
Robert Witter

 

[Miha Markic]

Hi,

Hello,

I'm using a lot of threads, each one using it's own instance of my
TableAdapter, Connection and DataTables. But all of them will access the
same
table at the same sql server maybe at the same time (optimistic
concurrency,
etc.)

I found the following in the Documentation:
Any public static (Shared in Visual Basic) members of this type are thread
safe. Any instance members are not guaranteed to be thread safe.

Well, what does this mean exactly?
As I understand, this may not be any problem for me.
Only, if I would use e.g. the same instance of a SqlDataAdapter in more
than
one threads (what I'm doing not) I may run into problems.
Is this correct?

Yes. Thread safety is regarding code, not sql server. Just make sure that
you don't use same instance in more than one thread and you'll be fine.
Otherwise you have to implement synchronization mechanism.

 

[Guest]

I just wanted to be sure - thread errors are so difficult to find.
Thanks for confirming me!

Regards,
Robert Witter

 

[Jim Rand]

Just make sure that if a dataset is bound to a GUI element that you call the
data adapter's fill / update methods on the GUI thread.

 

[Guest]

Ah, yes!
As I painfully figured out, ANY changes to bound DataSet elements must be
done in the GUI thread! In this case, there are no bounds to GUI elements,
but in an other program, I'm doing exactly this - now with invoking the GUI
thread for every change.

Maybe, you know a trick or someting to make a DataGridView thread safe?
If it would be possible to syncronize the parts, where the DataGridView
accesses the DataTable with my own thread(s)...

But I'm afraid, that it is not possible to overwrite this parts of the
DataGridView to implement at least a DataGridView which is thread save if
it's only reading from (not writing to) the DataTable.

However, thanks for your help,
Robert Witter

 

[Miha Markic]

Just make sure that if a dataset is bound to a GUI element that you call
the data adapter's fill / update methods on the GUI thread.

Yes, or detach source, fill it and re-attach it. This is the way how windows
controls work - you can't modify them from non-GUI thread.

 

[Jim Rand]

I have a dataset that contains all data for lookup tables. It is refreshed
each 10 minutes in background picking up new and modifed rows using the
CAST(TS AS INT) > @MaxTS in the WHERE clause.

DataSet dsNewData= dsLookupTbls.Clone();

.... Fill dsNewData in background

private bool UpdateDataSet()
{
bool ok = false;
if (_GUISync.InvokeRequired)
{
ok = (bool) _GUISync.Invoke(new UpdateDataSetDelegate(UpdateDataSet),
null);
}
else
{
try
{
this.AcceptChanges();
this.Merge(_dsNewData, false);
this.AcceptChanges();

NoteMaxTimestamps();

ok = true;

}
catch (System.Exception ex)
{
_log.Warn("UpdateDataSet merge failed", ex);
ok = false;
}
return ok;
}
return ok;
}