Use C # to write extended stored procedures
What is an expansion stored procedure? The extended stored procedure enables you to create your own external routines using programming languages like C. For the user, the extended stored procedure is the same as the normal stored procedure, and the implementation method is also the same. The parameters can be passed to the extended stored procedure, and the extended stored procedure can return the result or return a state. The extended stored procedure can be used to extend the functionality of Microsoft® SQL ServerTM 2000. The extended stored procedure is a dynamic link library (DLL) that SQL Server can dynamically load and execute. The extended stored procedure runs directly on the address space of SQL Server and programs with SQL Server Open Data Services (ODS) API. After writing the extended stored procedure, the member of the fixed server role sysadmin can register the extended stored procedure in SQL Server, and then granted other users to perform the permissions of the process. The extended stored procedure can only be added to the Master database. Using the C # Writing Extended Store By following the use of a simple example to demonstrate how to write extended stored procedures with C #. First, we create a simple C # class library file: // C # file: Csserver.cs using System; using System.Runtime.InteropServices; using System.Reflection; using System.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";}}} is then created with the class sn -k The library creates a strong name key file and compiled. Sn -k mykey.snk CSC / T: library csserver.cs Register this category library: regasm /tlb:csserver.tlb csserver.dll / codebase This extension stored procedure is written, below we test the effect in SQL Server . T-SQL Stored Proc. 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.
