C # compile the extended storage process

Microsoft SQL Server 2000 supports extended stored procedures, allowing you to write components in other languages to expand database functions. In the past, Local Code such as VC can be directly put into the System32 directory after the dynamic link library is established, and then call sp_addextendedproc to implement the function by creating a storage process in the master database.
. NET and other hosting languages to write DLL more steps. First, the hosted class library needs to register the COM interface. To register the COM interface, you need to add an interface, declare the method to be exposed, and add the guid and dispid, for example: [GUID ("200d1d86-e232-4b52-84f1-6d7fed6ea99f")]
Public interface com_interface
{
[Dispid (1)]
Void show (ref long hwndparent, ref u8login. clslogin login, object mrepmanage, object mrepsysinfo );

}

// Sort production books
[GUID ("CD5C1120-FEA4-4abe-AD4D-BF20686A0E05"), classinterface (classinterfacetype. None)]
Public class com_somain: com_interface
{
Public void show (ref long hwndparent, ref u8login. clslogin login, object mrepmanage, object mrepsysinfo)
{
If (func. cuser_name = string. Empty)
{
Data. conn_default = login. ufdbname. substring (login. ufdbname. indexof (";") + 1 );
Func. cUser_ID = Login. cUserId;
Func. cUser_Name = Login. cUserName;
Func. DataBaseName = Login. cAccName;
MessageBox. Show (Login. cUserId + Login. cUserName + Data. conn_Default );
}
New FormLayer. SaleOrder. SaleOrderList (0, true );
}

COM_Interface member # region COM_Interface Member

Void COM_Interface.Show (ref long hWndParent, ref U8Login. clsLogin Login, object mRepManage, object mRepSysInfo)
{
Throw new Exception ("This method cannot be implemented! ");
}

# Endregion
}

Compile and use Regasm for registration (or directly select this option in VS2005 project properties ).
Then we write the stored procedure. here we can use sp_OACreate and sp_OADestroy to create and destroy objects, call objects and catch errors through sp_OAMethod and sp_OAGetErrorInfo. Below is a piece of code of my previous email sending: create procedure dbo. sendMail
@ To varchar (50 ),
@ Subject varchar (255 ),
@ Body varchar (500)
AS
DECLARE @ object int
DECLARE @ hr int
DECLARE @ return varchar (255)
DECLARE @ src varchar (255), @ desc varchar (255)

-- Create an object instance.
EXEC @ hr = sp_OACreate 'extendprocc. SendMails ', @ object OUT
IF @ hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ object, @ src OUT, @ desc OUT
SELECT hr = convert (varbinary (4), @ hr), Source = @ src, Description = '20140901'
RETURN
END
-- Call the object method.
EXEC @ hr = sp_OAMethod @ object, 'send', @ return OUT, 'smtp .163.com ', '192 @ 163.com', '123', '192 @ 163.com ', 'xx ', @ to, '1', @ SUBJECT, @ body
IF @ hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ object, @ src OUT, @ desc OUT
SELECT hr = convert (varbinary (4), @ hr), Source = @ src, Description = @ desc
RETURN
END
-- PRINT @ return

-- Destroys an object instance.
EXEC @ hr = sp_OADestroy @ object
IF @ hr <> 0
BEGIN
Exec sp_oageterrorinfo @ object, @ SRC out, @ DESC out
Select hR = convert (varbinary (4), @ HR), source = @ SRC, description = @ DESC
Return
End
Go

The dll that expands the stored procedure will run as a COM object in the address space of sqlserver. Therefore, it is best to use only the method in the implementation class and use the global field with caution.