No its ok I was expecting someone to suggest it. I face the age old problem of not being able to convince a customer to upgrade. I have used SQL CLR integration with great success and it is a pleasure to work with. Unfortunately I'm forced to do this. I have done this with VFP MTDLLS with no problems over a long period of time. Its just I need to use .NET for some of the framework libraries with will speed up development.
Now I did consider using a VFP COM server and calling into .NET via COM but the performance and other potential issues of this just seems nasty. I just cannot see the point if I can call into .NET via COM directly.
I am planning to keep this class as simple as possible, but I need to know whether .NET COM servers support SQL's COM threading model. I think it will work fine, but I need to work out if I should make the code thread safe and if so whats the best way to do this in my class without causing unecessary blocking issues. Once I've established this I will be happy to proceed and look to a brighter future of SQL CLR integraion <g>
Thanks
Regards
Richard
Not to sound snippy, but if you want to integrate .NET code into SQL Server why don't you upgrade to SQL 2005 or later that has native .NET support? You'll get much better performance, integration and most important stability and security.
Putting COM into any multi-threaded environment if you can avoid it is a disaster waiting to happen :-}...
+++ Rick ---
Hi all,
I think I find I'm OK.
It appears that sp_OA calls in SQL Server are created on seperate threads that make there own calls to OleInitialze() (essenitally putting these threads in their own STA). There is a main STA which keeps a reference count per thread STA created. So as .NET COM classes are set to Both my class will be put in STA allowing SQL server direct access to the class on one thread per STA. As SQL supports multiple STA's there will not be any real issues with blocking so I do not need to worry too much about this. Am I making the correct assumption here?
Also, as .NET classes can be placed in an MTA does that mean from now on I need to ensure thread safety to some degree? I don't normall check for MTA when I assume an app is not concurrently using my class. Of course this could be a mistake if the process uses multiple threads in a single MTA to access my object(s). Do I need to cater for this with my own thread synchronisation and is this best practice for C# COM classes anyway?
TIA
Richard
Hi all,
I have built COM classes in C# before either using ClassInterfaceType.AutoDual for automatic late and early bound type exports and ClassInterfaceType.None to explicitly specify an interface for my class. The later I use for versioning and to implement COM events.
Typically I have used these COM classes in VFP, called via VFP's single threaded model, and have not worried about threads or COM apartments. I now have a scenareo where I want to expose a .NET COM Class to SQL Server 2000 which I want concurrent use of my class. I assume that SQL server will use multiple threads to call my class but I am unsure if it sets its threads to be in an STA or MTA?
I assume my .NET COM classes are STA?? Now if SQL Server is MTA, ideally I would want to make my class MTA to avoid marshalling, but would I then have to handle thread synchronization within my COM methods? Can I even make my C# based COM class MTA?
If my understanding of this is correct. . . If I have an STA I assume that any calls from other threads during a call from another thread will block until the first thread is complete? Is this correct? I know that MTA is marshalled to STA in this way which causes blocking. Basically will an STA block calls from an STA and MTA with multiple threads calling the COM object?
Looking at the COM registration in the registry the class is actually using mscoree as a proxy to my .NET class. Its threading model is set to BOTH which means the apartment type is determined by the caller. In which case how does mscoree handle this? Does I not have to worry about this?
I must sound rather confused, and that is because I am <g>. I got some grounding on the theory but not a firm enough grasp to take things forward. I just want to create a COM class that works efficiantly with SQL Server with the possiblility of avoiding:
1) COM Apartment Marshalling causing bad performance.
2) Blocking, effectively causing bad peformance.
3) Race conditions, deadlocks due to lack of thread safety.
Can anyone offer any advice, tips or point me to an example anywhere?
TIA
Richard