Use C # To write extended stored procedures

What is expansion?

Extended stored procedures allow you to use external routines such as C to create your own. For users, the extended stored procedure and common stored procedure have the same execution method. Parameters can be passed to the extended stored procedure. The extended stored procedure can return results or status. Extended stored procedures can be used to extend Microsoft SQL Server2000 functionality.

The extended stored procedure is a dynamic link library (DLL) that SQL server can dynamically load and execute ). The extended storage process runs directly in the address space of SQL Server, and uses SQL Server Open Data Service (ODS) API programming.

After the extended stored procedure is compiled, members of the fixed server role SysAdmin can register the Extended Stored Procedure in SQL Server and grant other users the permission to execute the procedure. The extended stored procedure can only be added to the master database.

Use C # To write extended stored procedures

The following is a simple example to demonstrate how to use C # to compile an extended stored procedure.
First, create a simple C # class library file:

// C # file: csserver. CS

Usingsystem;
Usingsystem. runtime. interopservices;
Usingsystem. reflection;
Usingsystem. runtime. compilerservices;

[Assembly: assemblytitle ("csserver")]
[Assembly: assemblydescription ("test SQL. Net InterOP")]
[Assembly: assemblyversion ("1.0.0.1")]
[Assembly: assemblydelaysign (false)]
[Assembly: assemblykeyfile ("mykey. SNK")]
Namespace sqlinterop {public interface itest {string sayhello ();}
[Classinterface (classinterfacetype. autodual)]
Public class csharphelper: itest {Public String sayhello () {return "hello from CSHARP ";}
}
}

Then create a strong-name key file for the class library with Sn-K and compile it.
Sn-K mykey. SNK
CSC/T: Library csserver. CS

Register the Class Library:
Regasm/TLB: csserver. TLB csserver. dll/codebase

In this way, the extended storage process is compiled and registered. Let's test the effect in sqlserver.

T-sqlstoredproc.

Declare @ object int
Declare @ HR int
Declare @ property varchar (255)
Declare @ return varchar (255)
Declare @ SRC varchar (255), @ DESC varchar (255)

-- Create an object instance.
Exec @ hR = sp_oacreate 'sqlinterop. csharphelper ', @ object out
If @ HR <> 0
Begin
Exec sp_oageterrorinfo @ object, @ SRC out, @ DESC out
Select hR = convert (varbinary (4), @ HR), source = @ SRC, description = @ DESC
Return
End

-- Call the object method.
Exec @ hR = sp_oamethod @ object, 'sayhel', @ return out
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