Section 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:

Select File New Project.
Select SQL Server Project in the New Project dialog box, shown in Figure 4-1, name it HelloWorld, specify the location, and click OK.

Figure 4-1. New Project dialog box
Because the stored procedure will not be accessing any data, click Cancel in the Add Database Reference dialog box, shown in Figure 4-2.

Figure 4-2. Add Database Reference dialog box
In Solution Explorer, right-click the HelloWorld project and select Add Stored Procedure from the context menu, as shown in Figure 4-3.

Figure 4-3. Add Stored Procedure menu item
In the Add New Item dialog box, shown in Figure 4-4, select the Stored Procedure template. Enter the name HelloWorldStoredProcedure.cs and click Add.

Add the following line of code to the HelloWorldStoredProcedure( ) method in HelloWorldStoredProcedure.cs:

    SqlContext.Pipe.Send("Hello world.\n");

The complete code should now be as follows:

    using System;
    using System.Data;
    using System.Data.Sql;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;

    public partial class StoredProcedures
    {
        [SqlProcedure]
        public static void HelloWorldStoredProcedure(  )
        {
            SqlContext.Pipe.Send("Hello world.\n");
        }
    };

Figure 4-4. Add New Item dialog box

Build the solution by selecting Build Build Solution from the Visual Studio 2005 main menu, by clicking the Build Solution button on the Build toolbar, or by right-clicking the HelloWorld project in Solution Explorer and selecting Build from the context menu. The stored procedure is compiled into an assembly called HelloWorld.dll in the bin\Debug subdirectory.

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:

Right-click the ProgrammingSqlServer2005 database in Object Explorer and select New Query from the context menu, as shown in Figure 4-5.
Register the assembly HelloWorld.dll with the SQL Server assembly name HelloWorld by executing the following T-SQL statement:
```
    USE ProgrammingSqlServer2005
    GO

    CREATE ASSEMBLY HelloWorld
    FROM 'C:\PSS2005\HelloWorld\HelloWorld\bin\Debug\HelloWorld.dll'
```
Figure 4-5. New Query menu item

The SQL Server assembly name and the .NET Framework assembly DLL name do not have to be the same. The SQL Server assembly name must be unique in the database.
You can confirm that the assembly is registered by expanding the Databases ProgrammingSqlServer2005 Programmability Assemblies node in the Object Explorer tree view, as shown in Figure 4-6.

Figure 4-6. Object Explorer Assemblies node
Create a CLR stored procedure called HelloWorldSP based on the HelloWorld StoredProcedure( ) static method in the HelloWorld.dll assembly registered in Step 2. Execute the following query:

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.

The results are exactly the same as they would be if you had created and executed the following T-SQL stored procedure:

Once you have finished with the sample, you can remove the CLR stored procedure and registered .NET Framework assembly by executing the following statements:

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:

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.

4.5. Hello World Example

Figure 4-1. New Project dialog box

Figure 4-2. Add Database Reference dialog box

Figure 4-3. Add Stored Procedure menu item

Figure 4-4. Add New Item dialog box

Figure 4-5. New Query menu item

Figure 4-6. Object Explorer Assemblies node

Figure 4-7. Object Explorer Stored Procedures node

4.5.1. Command-Line Compiler