Threads and the OleDbDataReader Class

[Dom]

I have a program in which the Form1_Load event does a lot of database
work. Meanwhile, the user looks at a blank screen when he could be
doing other work while the DB stuff is getting done. So I thread out
the DB work. The Thread is sent a Form1 method as a "Callback". And
when the thread is finished, it invokes the Callback, and passes the
DB information. This has always worked when the DB was SQL Server,
and I used the SqlDataReader class. But now, I have to use the Oracle
server, and the OleDbDataReader class, and it fails. I've recreated
the problem in a paired down program. Am I doing something wrong, or
have I found a bug in the OleDbDataReader class?

Here is Form1.cs
---------------------------------------------------------------------------------

using System;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Threading;

namespace OleDB_Thread
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
Builder b = new Builder();
Thread t = new Thread(b.Fill);
t.Start((Builder.CallbackDelegate)Form1_Callback);
}

private void Form1_Callback(OleDbDataReader r)
{
[ Use "r"]
}
}
}



Here is Builder.cs which is called in a Thread
----------------------------------------------------------------

using System;
using System.Data.OleDb;
using System.Windows.Forms;

namespace OleDB_Thread
{
class Builder
{
public delegate void CallbackDelegate(OleDbDataReader r);

public void Fill(object o)
{
OleDbConnection c = new
OleDbConnection([ConnectionString]);
c.Open();

OleDbCommand m = new OleDbCommand([SQL Statement], c);
OleDbDataReader r = m.ExecuteReader();

CallbackDelegate Callback = (CallbackDelegate)o;
((Form)Callback.Target).Invoke(Callback, r);
}
}
}


-------------------------------------------------------------------------------------------------------------------

The program fails where I have written [Use "r"]. I get the message:

"Unable to cast COM object of type 'System.__ComObject' to interface
type 'IRowset'. This operation failed because the QueryInterface call
on the COM component for the interface with IID '{0C733A7C-2A1C-11CE-
ADE5-00AA0044773D}' failed due to the following error: No such
interface supported"

Keep in mind that if I use SQL Server, and make the relevant changes
in the code, it works fine.

Any ideas?

 

[Dom]

[...]
The program fails where I have written [Use "r"].  I get the message:

"Unable to cast COM object of type 'System.__ComObject' to interface
type 'IRowset'. This operation failed because the QueryInterface call
on the COM component for the interface with IID '{0C733A7C-2A1C-11CE-
ADE5-00AA0044773D}' failed due to the following error: No such
interface supported"

Keep in mind that if I use SQL Server, and make the relevant changes
in the code, it works fine.

Any ideas?

Well, you omitted exactly the part of the code where it actually fails.
  So it's impossible to know what's actually going on there.

However, I suppose it's possible that the issue is the STA/MTA mis-match
between your threads and the COM object that was created.  I don't think
that normally this would result in a failed QI call, but I can't rule it
out either.  OLE-based code is very sensitive to having the right
apartment model when executing, and failures vary.

You can test that theory by changing the code so that the reader isn't
used on the GUI thread, but rather only on the worker thread executing
your Builder code.  Only use Invoke() to pass specific data from the
reader to individual GUI elements as needed, either one at a time (i.e.
several Invoke() calls) or batch all the data together in a separate
data structure and pass it all at once.  Make sure that the reader
itself is only used on the worker thread.

If that doesn't turn out to fix the problem, then you need to post a
proper code example.  At the very least, show a specific program
statement that when executed in context, will immediately result in the
error you get.

Pete

"Well, you omitted exactly the part of the code where it actually
fails."

Boy, I tried to be so precise and concise that I ended up doing
something stupid like that. The program fails when I access the
reader, for example, r.GetString(0), or any of the Get methods, will
fail.

"However, I suppose it's possible that the issue is the STA/MTA mis-
match
between your threads and the COM object that was created."

Can you explain this. I'm not sure what STA or MTA i, and I don't see
any mismatch anywhere.

"...batch all the data together in a separate
data structure and pass it all at once."

That works. I did that when I tried to fix the problem on my own. I
put all the data in a object[][] class, and passed it along. But I'm
curious about why it failed. Can you tell me more about the mismatch
that you see?

Dom

 

[Dom]

On 3/24/11 6:30 AM, Dom wrote:

[...]
The program fails where I have written [Use "r"].  I get the message:
"Unable to cast COM object of type 'System.__ComObject' to interface
type 'IRowset'. This operation failed because the QueryInterface call
on the COM component for the interface with IID '{0C733A7C-2A1C-11CE-
ADE5-00AA0044773D}' failed due to the following error: No such
interface supported"
Keep in mind that if I use SQL Server, and make the relevant changes
in the code, it works fine.
Any ideas?

Well, you omitted exactly the part of the code where it actually fails.
  So it's impossible to know what's actually going on there.

However, I suppose it's possible that the issue is the STA/MTA mis-match
between your threads and the COM object that was created.  I don't think
that normally this would result in a failed QI call, but I can't rule it
out either.  OLE-based code is very sensitive to having the right
apartment model when executing, and failures vary.

You can test that theory by changing the code so that the reader isn't
used on the GUI thread, but rather only on the worker thread executing
your Builder code.  Only use Invoke() to pass specific data from the
reader to individual GUI elements as needed, either one at a time (i.e.
several Invoke() calls) or batch all the data together in a separate
data structure and pass it all at once.  Make sure that the reader
itself is only used on the worker thread.

If that doesn't turn out to fix the problem, then you need to post a
proper code example.  At the very least, show a specific program
statement that when executed in context, will immediately result in the
error you get.

Pete

"Well, you omitted exactly the part of the code where it actually
fails."

Boy, I tried to be so precise and concise that I ended up doing
something stupid like that.  The program fails when I access the
reader, for example, r.GetString(0), or any of the Get methods, will
fail.

"However, I suppose it's possible that the issue is the STA/MTA mis-
match
between your threads and the COM object that was created."

Can you explain this.  I'm not sure what STA or MTA i, and I don't see
any mismatch anywhere.

"...batch all the data together in a separate
data structure and pass it all at once."

That works.  I did that when I tried to fix the problem on my own.  I
put all the data in a object[][] class, and passed it along.  But I'm
curious about why it failed.  Can you tell me more about the mismatch
that you see?

Dom- Hide quoted text -

- Show quoted text -

I did a little research and changed:

Thread t = new Thread(b.Fill);

.... to ...

Thread t = new Thread((ParameterizedThreadStart)b.Fill);

Didn't fix anything.

 

[Dom]

[...]
"...batch all the data together in a separate
data structure and pass it all at once."

That works.  I did that when I tried to fix the problem on my own.  I
put all the data in a object[][] class, and passed it along.  But I'm
curious about why it failed.  Can you tell me more about the mismatch
that you see?

Bing can help you learn more about MTA vs STA.  There are even a couple
of past discussion threads in this newsgroup concerning the topic.  The
short version is that MTA is "multi-threaded apartment" and STA is
"single-threaded apartment", and COM objects support one or the other
but often not both.

An STA COM object must be used only in one of the single-threaded
apartments for the process, and specifically only in the STA in which it
was created.  An MTA COM object must be used in the single
multi-threaded apartment (there's only one, because all MTA COM objects
can run on any thread, and the one MTA can include all threads that
aren't in an STA).  The thread in which the code is executing determines
which apartment is being used.

In .NET, a Forms application has a main STA, within which is the main
thread (i.e. the entry point for the program).  New threads are in the
MTA by default.  You can force a new thread to be in its own STA by
calling Thread.SetApartmentState() before calling .Start(), but even
doing so might not solve the issue you're running into, because the main
thread is still going to be in a different STA and calling an STA COM
object from the wrong apartment is still wrong, whether that apartment
happens to be an STA or the MTA.

Calling into a COM object that "lives" in one apartment from a different
apartment (e.g. calling a COM object created in the MTA from an STA
thread, which is what your code appears to do) requires at a minimum
marshaling of the data for the call.  .NET is capable of wrapping this
somewhat, but it has its limits, and sometimes marshaling the data isn't
sufficient.  A COM object may have other dependencies as well that tie
it to a specific apartment.

So, if the OleDb reader is like that, and is closely tied to the
apartment in which it was created, one could expect failures trying to
use it from any other apartment.

I'm glad to hear that batching the results from the worker thread works.
  It's probably the easiest, simplest way to fix the issue, so as long
as that solves your problem, I think it's the way to go.

Pete

Thanks. I'll stick with the object[][] like you suggest.