Extended Stored Procedure in C#

Extended stored procedures are created and loaded using DLL's

Hi,

Please explain, what you want?

Extended Stored Procedures is written as DLL's and placed inside the BINN directory of SQL Server. You can than register the Extended Stored Procedure with SQL Server and call it like any other SP. This way you extend SQL Server to send a message to MSMQ for example.

Here is the spec from Microsoft:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_ref_02_8k2t.asp

An example in C++
http://www.codeproject.com/database/extended_sp.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_ref_02_8k2t.asp

I would like to write one in C# but would like to have an example to work from.

I know that one issue around C# is that it cannot export DLL methods, but MSIL can, here is an artical on that:
http://www.c-sharpcorner.com/Code/2003/Aug/ExportManagedCodeasUnmanaged.asp.
I don't mind editing the MSIL each time. But I need a Guru on Interop to help me to get this working somehow...

So basically, I think it must be technically possible to do this.

Can anyone help PLEASE

I'm not sure you can do what you want to in SQL server 2000
SQL server 2005 supports stored Procedures in C# and in VB.net as well as TSQL.

They covered all this stuff at a recent one day developers conference I attended.

At first I was really excited, as I pictured writing these in something like the enterprise manager I was used to with 2000, however after getting down and dirty with the MS guys, it appears that what you are asking to do with SQL2000, is exactly what and how they did it with SQL2005 Stored Procedures written in C# (or VB) are still run in the "managed code" runtime environment so they are not as fast as native TSQL, and in some cases they might not be portable, as the C#/VB.NET Stored Procedure code appeared to have some additional installation requirements at the time I saw it (that could change, I saw the beta/CTP version)

My advice would be to go on MS's website and order  a free Community Technology Preview version of SQL 2005 - and play with it, then decide how to proceed.


- Jack

Thanks for the answer,

Yes it will be much easier in SQL 2005, the problem is that SQL 2000 will be still around for a very long time. The clients I work for need this for SQL 2000 and they will probably only move to SQL 2005 in middle 2007. (or after SP2 as they all say)

I know this is extemely difficult, but I don't think impossible. If I could give 50 000 points for an example I would.

If it is impossible, can someone please tell me for a learning experience why it cannot be done.

It is not possible to call C# code from SQL Server 2000 directly. You can call only C/C++ libraries, just like from a VB 6 application.

There are however some workarounds.

- Create a class library with C#. Create a type library as a wrapper around the class library and register it as a COM object. Create a C/C++ library project with VS 6 to instanciate the COM object and call this C/C++ library from SQL Server. An example for creating a COM object and its type library with C# can be found around the net, e.g. under http://www.csharphelp.com/archives/archive281.html. This solution is however slow because of the COM objects, whose model is heavy.

- Create a class library with C#. Create a mixed managed/unmanaged library in C++ .Net. This library can expose functions as a normal Win32 DLL and can call .Net class libraries, so it can access also your C# library. The normal Win32 DLL part can be used from SQL Server 2000. So you would have something like: SQL Server <-> Bridge library C++ .Net <-> C# library. Some examples are availabe on the Web as well.

I think there are some other options as well. You can google for something like "calling managed code from unmanaged code" and see different solutions. I personally used the second option I described and it works well for me but I had to do some programming in C++/C++ .Net

One thing that I didn't realize in the early days of playing with .net is that is very similar to Java in how it is implimented.  Java apps aren't native win32 code, they can't run on a PC without the java interpreter.  This was done on purpose - the creators of Java felt if they could use the interpreter on windows to run the java code, then they could also create interpreters for other systems, and indeed they have - java code can be run on many different platforms.

Microsoft choose to go the same route with .net. They use the term "Managed Code" - but it means the same thing, you're not really running a windows application, your're running a .net application completely inside of it's "managed environment"  Thats why you see these articles like the one Avenger had you search on (Calling managed code from unmanaged code)  Thats also why this is so difficult and why you're having a hard time finding information about it.

I can't really say why microsoft took this route, as I can't imagine that they plan to port the "managed environment" to other platforms as sun did with Java.

I still feel strongly that you should get a hold of SQL 2005 CTP and try the C# stored procedures - from what I remember it wasn't that impressive, there were limitations.  I came away feeling that the C# support was more of a marketing ploy than a usable feature. It made me want to dig in deeper with Transact-SQL.

@fruhj: Just some small remarks:

- Managed code is not called like this just to avoid calling it Win32 code. It is really "managed": you don't care about memory allocation or cleaning, it manages your arrays, etc. The name of code, produced when you compile the application is IL (intermediate language). Of course, the story with Java is the same with the exception that .Net has a much better support for Windows systems

- Microsoft indeed consider porting .Net to other platforms. There are also at least several open projects for implementing .Net on Linux, one of which I think already supports ASP.NET very well.

- A connection between C# and Win32 DLLs is still much easier than Java - Win32 DLLs, especially the part of using Win32 code from C#

I think we are getting a little off-track here.  The questioner has been very clear what they want (to refresh your memories see here: http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21548274.html#14807749) but we seem to be ignoring his/her comments.  

Here is what they do not want:
A way to access .NET or COM components.  

For a very good example of what they do want to achieve, see here:
http://www.sqldev.net/xp/xpsmtp.htm

If you are not familiar with XPSMTP.DLL it is a DLL to send SMTP emails.  No, I realize it is not written in C#, that is not the point.  What it is is a way to access functionality from SQL Server as an extended stored procedure as if it was native to SQL Server.

Hey Brett's worked with this stuff infinitely more than I have (and probably more than most people here) so if he says it can be done and theres' no performance hit for doing so then It looks like youre on the right track - sorry if I led you astray.
- Jack