4.5. Hello World Example
This section shows how to create, configure, and use a SQL Server CLR routine by way of a sample CLR stored procedure that returns the text message "Hello world." This example is followed by an example that shows how to create the same .NET Framework assembly using a command-line compiler.
Follow these steps in Visual Studio 2005 to create the .NET Framework assembly containing the CLR stored procedure:
Once the stored procedure is compiled, you need to register the assembly with SQL Server before you can access the CLR stored procedure. This walkthrough and many of the examples in this book use a database called ProgrammingSqlServer2005. Follow these steps to register the assembly with SQL Server:
CREATE PROCEDURE HelloWorldSP AS EXTERNAL NAME HelloWorld.StoredProcedures.HelloWorldStoredProcedure
The EXTERNAL NAME clause has three parts, separated by periods:
You can confirm that the stored procedure was created by expanding the Databases ProgrammingSqlServer2005 Stored Procedure node in the Object Explorer tree view, as shown in Figure 4-7.
Figure 4-7. Object Explorer Stored Procedures node
Execute the HelloWorldSP stored procedure with the following T-SQL statement:
The results follow:
The results are exactly the same as they would be if you had created and executed the following T-SQL stored procedure:
CREATE PROCEDURE HelloWorldSP2 AS PRINT 'Hello world.'
Once you have finished with the sample, you can remove the CLR stored procedure and registered .NET Framework assembly by executing the following statements:
DROP PROCEDURE HelloWorldSP DROP ASSEMBLY HelloWorld
4.5.1. Command-Line Compiler
While the examples in this book use Visual Studio 2005, you can create the program files using any text editor and compile them using a .NET command-line compiler. SQL Server 2005 installs .NET Framework redistribution files, including command-line language compilersfor example csc.exe for C# and vbc.exe for VB.NET. The command-line compilers are installed in the directory C:\<windir>\Microsoft.NET\Framework\<version>, where:
To use the compiler, add the directory containing the compiler to your Path environment system variable defined in the System variables list box accessed through Control Panel System Advanced Environment Variables.
To use the command-line C# compiler to compile the HelloWorldStoredProcedure.cs file created in the previous section, execute the following command:
csc /target:library /out:HelloWorld.dll HelloWorldStoredProcedure.cs
The /target compiler flag instructs the compiler to build a DLL. The /out flag instructs the compiler to override the default DLL name HelloWorldStoredProcedure.dll with the name HelloWorld.dll. For more information about Visual Studio .NET compilers and compiler flags, consult the Microsoft Developer Network (MSDN).
Once you have compiled the .NET Framework assembly, you register it and CLR routines in the same way as if you had used the Visual Studio 2005 compiler.